Add pgbouncer configuration (!182) · Merge requests · Platform / Infrastructure / Puppet / puppet-swh-site

Antoine R. Dumont requested to merge generated-differential-D1550-source into generated-differential-D1550-target Jun 05, 2019

This installs a pgbouncer instance on the belvedere machine.

The setup matches closely the one already installed on both prado and belvedere (mostly the db connections for now only bounces to swh on prado, swh-deposit, swh-scheduler, swh-scheduler-updater to belvedere itself, and then keep the initial number of connections, the type of connection trust, already setuped there etc...).

Once deployed, the differences i saw will be:

the current /etc/pgbouncer/pgbouncer.ini is complete and commented (probably installed through a template file then got adapted). The new one will only be a subset of what's defined here (without comments).
the actual /etc/pgbouncer/userlist.txt is plain and the password is human-readable, it will no longer be the case. The password will be hashed (md5 though).
dropped the default host * default connection. I prefer we have failure connections when something is not defined.

Related #1784 (closed) Related T1234

Test Plan

bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to update_configuration belvedere
Found host belvedere.internal.softwareheritage.org
Cloning into '/tmp/swh-ocd.Inbkkk8A/environments/production/data/private'...
done.
Cloning into '/tmp/swh-ocd.Inbkkk8A/environments/update_configuration/data/private'...
done.
*** Running octocatalog-diff on host belvedere.internal.softwareheritage.org
I, [2019-06-06T11:21:01.348618 #7626]  INFO -- : Catalogs compiled for belvedere.internal.softwareheritage.org
I, [2019-06-06T11:21:02.050810 #7626]  INFO -- : Diffs computed for belvedere.internal.softwareheritage.org
diff origin/production/belvedere.internal.softwareheritage.org current/belvedere.internal.softwareheritage.org
*******************************************
+ Anchor[pgbouncer::begin]
*******************************************
+ Anchor[pgbouncer::end]
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_database] =>
   parameters =>
      "order": "01"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[databases]
<<<
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_params] =>
   parameters =>
      "order": "03"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[pgbouncer]

;; Set hieradata hash value for "pgbouncer::params::config_params" to override
;; below values
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
unix_socket_dir = /var/run/postgresql
auth_file = /etc/pgbouncer/userlist.txt
listen_addr = 127.0.0.1,192.168.100.210
listen_port = 5432
admin_users = postgres,olasd
stats_users = postgres
auth_type = hba
pool_mode = session
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30
max_client_conn = 2000
default_pool_size = 2000
client_tls_sslmode = allow
client_tls_ca_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
server_tls_sslmode = allow
auth_hba_file = /etc/postgresql/11/secondary/pg_hba.conf
max_db_connections = 2000
max_user_connections = 2000
log_connections = 0
log_disconnections = 0
<<<
*******************************************
+ Concat::Fragment[/tmp/pgbouncer-paramtmpfile_users] =>
   parameters =>
      "order": "05"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[users]
<<<
*******************************************
+ Concat::Fragment[postgres] =>
   parameters =>
      "order": "01"
      "target": "/etc/pgbouncer/userlist.txt"
      "content": >>>
"postgres" "md5e8a48653851e28c69d0506508fb27fc5"
<<<
*******************************************
+ Concat::Fragment[postgres_users] =>
   parameters =>
      "order": "06"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_userlist
<<<
*******************************************
+ Concat::Fragment[softwareheritage_postgres] =>
   parameters =>
      "order": "02"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_databases
softwareheritage = host=prado.internal.softwareheritage.org port=5433 user=postgres
softwareheritage-deposit = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler-updater = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
<<<
*******************************************
+ Concat[/etc/pgbouncer/pgbouncer.ini] =>
   parameters =>
      "backup": "puppet"
      "ensure": "present"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "path": "/etc/pgbouncer/pgbouncer.ini"
      "replace": true
      "show_diff": true
      "warn": false
*******************************************
+ Concat[/etc/pgbouncer/userlist.txt] =>
   parameters =>
      "backup": "puppet"
      "ensure": "present"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "path": "/etc/pgbouncer/userlist.txt"
      "replace": true
      "show_diff": true
      "warn": false
*******************************************
+ Concat_file[/etc/pgbouncer/pgbouncer.ini] =>
   parameters =>
      "backup": "puppet"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "replace": true
      "show_diff": true
      "tag": "_etc_pgbouncer_pgbouncer.ini"
*******************************************
+ Concat_file[/etc/pgbouncer/userlist.txt] =>
   parameters =>
      "backup": "puppet"
      "ensure_newline": false
      "group": "pgbouncer"
      "mode": "0640"
      "order": "alpha"
      "owner": "pgbouncer"
      "replace": true
      "show_diff": true
      "tag": "_etc_pgbouncer_userlist.txt"
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_database] =>
   parameters =>
      "order": "01"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[databases]
<<<
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_params] =>
   parameters =>
      "order": "03"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[pgbouncer]

;; Set hieradata hash value for "pgbouncer::params::config_params" to override
;; below values
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
unix_socket_dir = /var/run/postgresql
auth_file = /etc/pgbouncer/userlist.txt
listen_addr = 127.0.0.1,192.168.100.210
listen_port = 5432
admin_users = postgres,olasd
stats_users = postgres
auth_type = hba
pool_mode = session
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30
max_client_conn = 2000
default_pool_size = 2000
client_tls_sslmode = allow
client_tls_ca_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
server_tls_sslmode = allow
auth_hba_file = /etc/postgresql/11/secondary/pg_hba.conf
max_db_connections = 2000
max_user_connections = 2000
log_connections = 0
log_disconnections = 0
<<<
*******************************************
+ Concat_fragment[/tmp/pgbouncer-paramtmpfile_users] =>
   parameters =>
      "order": "05"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
;; Section autogenerated by puppet module
;; Do not manually update

[users]
<<<
*******************************************
+ Concat_fragment[postgres] =>
   parameters =>
      "order": "01"
      "tag": "_etc_pgbouncer_userlist.txt"
      "target": "/etc/pgbouncer/userlist.txt"
      "content": >>>
"postgres" "md5e8a48653851e28c69d0506508fb27fc5"
<<<
*******************************************
+ Concat_fragment[postgres_users] =>
   parameters =>
      "order": "06"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_userlist
<<<
*******************************************
+ Concat_fragment[softwareheritage_postgres] =>
   parameters =>
      "order": "02"
      "tag": "_etc_pgbouncer_pgbouncer.ini"
      "target": "/etc/pgbouncer/pgbouncer.ini"
      "content": >>>
; Created from: pgbouncer_module_databases
softwareheritage = host=prado.internal.softwareheritage.org port=5433 user=postgres
softwareheritage-deposit = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
softwareheritage-scheduler-updater = host=belvedere.internal.softwareheritage.org port=5434 user=postgres
<<<
*******************************************
+ File[/etc/default/pgbouncer] =>
   parameters =>
      "content": "START=1"
      "ensure": "file"
*******************************************
+ Package[pgbouncer] =>
   parameters =>
      "ensure": "installed"
*******************************************
+ Pgbouncer::Databases[pgbouncer_module_databases] =>
   parameters =>
      "databases": [{"source_db"=>"softwareheritage", "host"=>"prado.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5433"}, {"source_db"=>"softwareheritage-deposit", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}, {"source_db"=>"softwareheritage-scheduler", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}, {"source_db"=>"softwareheritage-scheduler-updater", "host"=>"belvedere.internal.softwareheritage.org", "auth_user"=>"postgres", "port"=>"5434"}]
*******************************************
+ Pgbouncer::Userlist[pgbouncer_module_userlist] =>
   parameters =>
      "auth_list": [{"user"=>"postgres", "password"=>""}]
      "paramtmpfile": "/tmp/pgbouncer-paramtmpfile"
*******************************************
+ Service[pgbouncer] =>
   parameters =>
      "enable": true
      "ensure": "running"
      "subscribe": ["Concat[/etc/pgbouncer/pgbouncer.ini]", "Concat[/etc/pgbouncer/userlist.txt]"]
*******************************************

Migrated from D1550 (view on Phabricator)

Add pgbouncer configuration

Test Plan

Merge request reports