Aller au contenu

PgBouncer

Présentation

PgBouncer est un outil de pooling pour PostgreSQL (mutualisation des sessions).

Il présente les avantages suivants :

  • Il peut être reconfiguré à chaud.
  • Il peut réaliser l’authentification (en s’appuyant sur l’authentification PostgreSQL).
  • Il peut mettre en pause temporairement un pool, afin de faire des bascules quasi transparentes (pas de déconnexion).
  • Plusieurs instances peuvent être lancées simultanément pour faire de la répartition de charge ou des bascules transparentes.

Par rapport à Pgpool, autre outil connu, PgBouncer est très économe :

  • Il s’appuie sur libevent, n’utilise donc ni threads ni processus lourds, et ne consomme donc que 2 ko de mémoire par session ;
  • Il ne réalise pas d’analyse de requêtes.
  • Il n’utilise que des informations au niveau du protocole réseau PostgreSQL, pour un impact minimal sur la latence.

PgBouncer propose trois modes de pooling :

Pooling par session

Quand un client se connecte à PgBouncer, il est affecté à une session à PostgreSQL, qu’il garde jusqu’à sa déconnexion. Depuis la 1.23, les connexions de réplication sont supportées par pgBouncer.

C’est le mode par défaut, qui est transparent pour le client. C’est aussi celui qui amène le moins de mutualisation.

Pooling par transaction

Une session à PostgreSQL n’est attribuée à un client que s’il demande explicitement une transaction. Le reste du temps, ses requêtes sont exécutées sur n’importe quelle session disponible.

Warning

Avant PgBouncer 1.21, ce mode n’était pas compatible avec l’utilisation de requêtes préparées. Ensuite, il y a des restrictions (voir le billet [Prepared Statements in Transaction Mode for PgBouncer]).

Pooling par statement

Similaire au mode Transaction, mais les transactions multi-instructions sont rejetées.

Ce mode avait été développé initialement pour PL/Proxy, et n’a qu’un intérêt très limité.

Installation

PgBouncer est packagé dans la plupart des distributions. Il suffit donc d’installer le package.

$ sudo apt install pgbouncer
En démon, PgBouncer tournera sous l’utilisateur postgres.

$ sudo dnf install pgbouncer
En démon, PgBouncer tournera sous l’utilisateur pgbouncer.

Configuration

Le fichier de configuration se trouve par défaut dans le répertoire /etc/pgbouncer/pgbouncer.ini. Il peut contenir 3 sections :

  • [pgbouncer] : paramètres globaux
  • [database] : paramètres de chaque base de données
  • [user] : paramètres pour chaque utilisateur

Exemple de configuration :

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = userslist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
pool_mode = transaction
default_pool_size=56
max_client_conn=600

[databases]
b1 = host=127.0.0.1 port=5432 dbname=b1

[pgbouncer]

La section pgbouncer contient les différents paramètres généraux :

  • listen_port : port d’écoute de PgBouncer (traditionnellement 6432)
  • listen_addr : interface d’écoute de PgBouncer
  • auth_type : type d’authentification utilisé par PgBouncer
  • auth_file : nom du fichier utilisé pour charger les utilisateurs et leur mot de passe (généralement userlist.txt)
  • logfile : répertoire de traces
  • pidfile : répertoire pour le PID (nécessaire pour utiliser PgBouncer en mode démon)
  • admin_users : utilisateurs autorisés à se connecter à la base virtuelle pgbouncer et à utiliser des commandes d’administration
  • pool_mode : mode de pooling utilisé
  • default_pool_size : nombre maximum de connexions PgBouncer/PostgreSQL, par rôle et par base
  • max_client_conn : nombre maximum de connexions client autorisées à se connecter à PgBouncer, toutes bases confondues.

[databases]

La section database prend en paramètre le nom de la base de données. Ce paramètre peut être constitué de plusieurs autres valeurs dont :

  • host : adresse IP su serveur PostgreSQL ;
  • port : port TCP/IP pour la connexion au serveur PostgreSQL ;
  • dbname : nom de la base de données ;
  • pool_size : spécifie la taille maximale des pools sur cette base de données ;
  • reserve_pool : permet d’ajouter des connexions supplémentaires sur cette base de données.

Console d’administration

Le paramètre admin_users permet de spécifier les utilisateurs autorisés à se connecter à la base virtuelle pgbouncer et de réaliser des opérations d’administration.

Les fonctionnalités de la console d’administration sont visibles avec la commande SHOW HELP :

psql -X -p 6432 -h localhost -d pgbouncer
psql (16.2 (Debian 16.2-1.pgdg120+2), serveur 1.22.0/bouncer)
ATTENTION : psql version majeure 16, version majeure du serveur 1.22.
         Certaines fonctionnalités de psql pourraient ne pas fonctionner.
Saisissez « help » pour l'aide.

pgbouncer=# SHOW help ;
NOTICE:  Console usage
DÉTAIL :
        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
        SHOW PEERS|PEER_POOLS
        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
        SHOW DNS_HOSTS|DNS_ZONES
        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
        SET key = arg
        RELOAD
        PAUSE [<db>]
        RESUME [<db>]
        DISABLE <db>
        ENABLE <db>
        RECONNECT [<db>]
        KILL <db>
        SUSPEND
        SHUTDOWN
        WAIT_CLOSE [<db>]
SHOW

Pour les commandes d’administration, voir Utilisation et base d’administration.

Utilisation

Pour lancer PgBouncer :

$ pgbouncer /etc/pgbouncer/pgbouncer.ini
2024-02-13 13:48:47.064 UTC [29589] LOG kernel file descriptor limit: 1024 (hard: 262144); max_client_conn: 600, max expected fd use: 668
2024-02-13 13:48:47.068 UTC [29589] LOG listening on 127.0.0.1:6432
2024-02-13 13:48:47.069 UTC [29589] LOG listening on unix:/tmp/.s.PGSQL.6432
2024-02-13 13:48:47.069 UTC [29589] LOG process up: PgBouncer 1.22.0, libevent 2.1.8-stable (epoll), adns: evdns2, tls: OpenSSL 1.1.1k  FIPS 25 Mar 2021

Le processus peut être lancé en mode démon avec l’option -d :

$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Cependant, les paquets habituels prévoient l’utilisation de systemd :

sudo systemctl start|stop|reload|restart pgbouncer

Authentification via auth_file

L’auth_file contient les mots de passe que PgBouncer contrôlera auprès de ses clients, et ceux qu’il enverra pour se connecter en leur nom à PostgreSQL (détails.

  • Création d’un fichier /etc/pgbouncer/pgbouncer.ini avec une configuration minimale :
$ vi /etc/pgbouncer/pgbouncer.ini

[databases]
db1 = host=127.0.0.1 dbname=db1

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# debian
pidfile=/var/run/postgresql/pgbouncer.pid
# redhat
# pidfile=/var/run/pgbouncer/pgbouncer.pid
  • Création d’un utilisateur user1 et d’une base db1 :
$ psql -c "CREATE ROLE user1 LOGIN PASSWORD '<mot-de-passe>';"
CREATE ROLE

$ psql -c "CREATE DATABASE db1 OWNER user1;"
CREATE DATABASE
  • Récupérer le mot de passe SCRAM-SHA-256 de l’utilisateur qui vient d’être créé :
$ psql -c "SELECT rolname,rolpassword FROM pg_authid WHERE rolname like 'user1';"
 rolname |                                                              rolpassword                                                              
---------+---------------------------------------------------------------------------------------------------------------------------------------
 user1   | SCRAM-SHA-256$4096:j2MdHlYBkbXN4eLHu+f4UA==$RF1kYNdjMJ3QusN8Uf+r+G5TR7XHBeqz2WGhb72szSg=:BTjjAA3cS/XLeKzWlzfyUPC16hVDNnlFfaJQ0U9vciM=
  • Ajouter le résultat de la commande dans le fichier /etc/pgbouncer/userlist.txt sous le format suivant :
"user1" "SCRAM-SHA-256$4096:j2MdHlYBkbXN4eLHu+f4UA==$RF1kYNdjMJ3QusN8Uf+r+G5TR7XHBeqz2WGhb72szSg=:BTjjAA3cS/XLeKzWlzfyUPC16hVDNnlFfaJQ0U9vciM="
  • Lancement de pgbouncer en mode démon :
# au choix
$ /usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
sudo systemctl restart pgbouncer
  • Se connecter à la base db1 avec l’utilisateur user1 via pgbouncer :
$ psql -U user1 -p 6432 -h 127.0.0.1 db1
Password for user user1:
psql (15.5)
Type "help" for help.

db1=>
  • Vérifier que vous êtes bien connectés sur l’instance PostgreSQL qui écoute sur le port 5432 :
db1=> SELECT inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 127.0.0.1        |             5432
  • En cas de problème, penser à aller voir d’abord les traces de PgBouncer.

Authentification via auth_query

Le client envoie le mot de passe à PgBouncer, qui se connecte à PostgreSQL avec un utilisateur dédié, pour comparer le mot de passe chiffré.

Dans le fichier pgbouncer.ini ajouter le paramètre auth_user dans la section [databases] et les paramètres auth_type et auth_query dans la section [pgbouncer] :

[databases]
db1 = host=127.0.0.1 dbname=db1 auth_user=myuserauth

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432

auth_type = scram-sha-256
#auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Créer le role myuserauth sur l’instance PostreSQL :

$ psql -c "CREATE ROLE myuserauth LOGIN PASSWORD '<mot-de-passe>';"

Récupérer le mot de passe SCRAM-SHA-256 du rôle :

$ psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'myuserauth';"

Ajouter le mot de passe dans le fichier /etc/pgbouncer/userlist.txt :

"myuserauth" "SCRAM-SHA-256$4096:yjVxQYlGI4Lik04WFxk4AA==$qgS0QenfI50PAxpOFHDAWWqiARsuNy8DuTM2gPpkR4Y=:V+Z/X2vNyeoDanGRalAawT+2U3LhzlCiLFDCemFJ+/0="

Ajouter les lignes suivantes dans le fichier pg_hba.conf :

#IPv4
host    db1             myuserauth      127.0.0.1/32            scram-sha-256

Recharger la configuration de PostgreSQL :

sudo systemctl reload postgresql-15

Accorder les droits SELECT à l’utilisateur sur le catalogue pg_shadow :

$ psql -d db1 -c "GRANT SELECT ON pg_catalog.pg_shadow TO myuserauth;"

Créer un nouvel utilisateur et tenter de se connecter via PgBouncer :

$ psql -c "CREATE ROLE user2 LOGIN PASSWORD '<mot-de-passe>';"

$ psql -U user2 -p 6432 -h 127.0.0.1 db1
Password for user user2: 
psql (15.5)
Type "help" for help.

db1=> select inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 127.0.0.1        |             5432
(1 row)

Authentification via auth_hba_file

En complément de l’utilisation du auth_file ou du auth_query, il est possible de configurer l’authentification de pgbouncer via un fichier similaire à pg_hba.conf. Pour réaliser cela il est nécessaire d’ajouter les paramètres auth_type et auth_hba_file dans le fichier de configuration de pgbouncer :

auth_type = hba
auth_hba_file = /etc/pgbouncer/pgb_hba.conf

Le format du fichier reste similaire à celui de PostgreSQL :

# TYPE  DATABASE            USER            ADDRESS                 METHOD
host    db1             user1           127.0.0.1/32                scram-sha-256
host    db1             user3           127.0.0.1/32                scram-sha-256

Info

Le nom de la base de donnée renseignée dans le fichier pg_hba.conf doit être celui défini spécifiquement dans le fichier pgbouncer.ini (db1)

Connexions TLS avec PgBouncer

Générations de certificats auto-signés côté serveur :

$ cd /var/lib/pgsql/15/data
$ openssl genrsa -out server.key 2048
$ chmod 400 server.key
$ openssl req -new -key server.key -days 365 -out server.crt -x509 -subj '/C=FR/ST=Ile-de-France/L=Paris/O=Smith'
$ cp server.crt root.crt

Activation du SSL

Activation des paramètres SSL dans le fichier postgresql.conf :

ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Afin de s’assurer que tous les utilisateurs passent par une connexion TLS, la ligne suivante sera ajoutée dans le fichier pg_hba.conf :

hostssl    all          all    127.0.0.1/32  scram-sha-256

Configuration pgbouncer

Afin que pgbouncer se connecte à PostgreSQL avec des connexions TLS, récupérer les fichiers root.crt, server.key et server.crt, puis ajouter les paramètres suivants dans le fichier pgbouncer.ini :

server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/root.crt
server_tls_key_file = /etc/pgbouncer/pgbouncer_server.key
server_tls_cert_file = /etc/pgbouncer/pgbouncer_server.crt
* server_tls_sslmode : mode ssl utilisé * server_tls_ca_file : fichier contenant le certificat racine de l’autorité de certification * server_tls_key_file : fichier contenant la clé privée du serveur * server_tls_cert_file : fichier contenant le certificat du serveur.

Lors de la connexion, ce message indique que PgBouncer se connecte à PostgreSQL via une connexion TLS :

$ psql -p 6432 -U my_ssl_user -d dbtestssl -h 127.0.0.1
Password for user my_ssl_user: 
psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

dbtestssl=> \q


Ces messages apparaîtront également dans les traces de PgBouncer (si activées) :

```bash
2024-02-14 14:55:55.747 UTC [6493] LOG C-0x55a7dcf1d5c0: dbtestssl/my_ssl_user@127.0.0.1:57940
 login attempt: db=dbtestssl user=my_ssl_user tls=no
2024-02-14 14:55:59.253 UTC [6493] LOG C-0x55a7dcf1d5c0: dbtestssl/my_ssl_user@127.0.0.1:57942
 login attempt: db=dbtestssl user=my_ssl_user tls=no
2024-02-14 14:55:59.267 UTC [6493] LOG S-0x55a7dcf134a0: dbtestssl/my_ssl_user@127.0.0.1:5432 
new connection to server (from 127.0.0.1:41092)
2024-02-14 14:55:59.276 UTC [6493] LOG S-0x55a7dcf134a0: dbtestssl/my_ssl_user@127.0.0.1:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1

Génération de certificats clients

cd /var/lib/pgsql/
mkdir cert_my_ssl_user
cd cert_my_ssl_user/
openssl genrsa -out /tmp/postgresql.key 2048
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=FR/ST=Ile-de-france/L=Paris/O=Smith/CN=my_ssl_user'
openssl x509 -days 1825 -req -in /tmp/postgresql.csr -CA /var/lib/pgsql/15/data/root.crt -CAkey /var/lib/pgsql/15/data/server.key -out /tmp/postgresql.crt -CAcreateserial
openssl x509 -in /tmp/postgresql.crt -text
mv /tmp/postgresql.* .
chmod 600 postgresql.key
cp /var/lib/pgsql/data/root.crt .
cd ..
tar czf cert_myssluser.tar.gz cert_my_ssl_user/
mkdir .postgresql
cp /var/lib/pgsql/cert_my_ssl_user/* /var/lib/pgsql/.postgresql/

Le CN du certificat doit correspondre au nom d’utilisateur dans PostgreSQL

Récupérer les fichiers de certificats clients et ajouter les paramètres suivants dans pgbouncer.ini :

auth_type = cert
client_tls_sslmode = verify-full
client_tls_ca_file = /etc/pgbouncer/root.crt
client_tls_key_file = /etc/pgbouncer/pgbouncer_cli.key
client_tls_cert_file = /etc/pgbouncer/pgbouncer_cli.crt 

$ psql -p 6432 -U my_ssl_user -d dbtestssl -h 127.0.0.1
Password for user my_ssl_user:
psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

dbtestssl=>

Les messages suivants doivent alors apparaître dans les traces de PgBouncer, indiquant que l’utilisateur my_ssl_user se connecte à PgBouncer via TLS :

2024-02-14 15:03:13.954 UTC [6657] LOG C-0x55b1eb8cfee0: dbtestssl/my_ssl_user@127.0.0.1:57984 login attempt: db=dbtestssl user=my_ssl_user tls=TLSv1.3/TLS_AES_256_GCM_SHA384
2024-02-14 15:03:23.250 UTC [6657] LOG C-0x55b1eb8cfee0: dbtestssl/my_ssl_user@127.0.0.1:57986 login attempt: db=dbtestssl user=my_ssl_user tls=TLSv1.3/TLS_AES_256_GCM_SHA384
2024-02-14 15:03:23.258 UTC [6657] LOG S-0x55b1eb8c6170: dbtestssl/my_ssl_user@127.0.0.1:5432 new connection to server (from 127.0.0.1:41136)
2024-02-14 15:03:23.265 UTC [6657] LOG S-0x55b1eb8c6170: dbtestssl/my_ssl_user@127.0.0.1:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1

Références