PgBouncer¶
Présentation¶
PgBouncer est un outil de pooling pour PostgreSQL (mutualisation des sessions)
Il présente les avantages suivants:
- S’appuie sur libevent. Il n’utilise donc pas de threads ou de processus lourds (comme PGPool), et est donc très économe en terme de ressources (2ko de mémoire par session)
- Ne réalise pas d’analyse des requêtes. Il n’utilise que des informations au niveau du protocole réseau PostgreSQL, afin de n’avoir qu’un impact minimal sur la latence
- Peut être reconfiguré à chaud
- Peut mettre en pause temporairement un pool, afin de faire des bascules quasi-transparentes (pas de déconnexion)
- Propose trois modes de pooling:
- Session: quand un client se connecte à PGBouncer, il est affecté à une session à PostgreSQL, qu’il garde jusqu’à sa déconnexion. C’est le mode par défaut, qui est transparent pour le client. C’est aussi celui qui amène le moins de mutualisation
- 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 à ce moment là. Il n’est souvent pas compatible avec l’utilisation de requêtes préparées: si celles-ci sont créées hors transaction, le requête peut être préparée sur une session, puis tentée d’être utilisée sur une autre, ce qui déclenchera bien sûr une erreur
- Statement: pareil que le 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é. Peut réaliser l’authentification (en s’appuyant sur l’authentification PostgreSQL)
Installation¶
PGBouncer est packagé dans la plupart des distributions. Il suffit donc d’installer le package.
Configuration¶
Le fichier de configuration se trouve par défaut dans le répertoire /etc/pgbouncer/pgbouncer.ini. Il peut contenir 3 sections :
- [pgbouncer] : configure les paramètres globaux.
- [database] : configure les paramètres de chaque bases de données.
- [user] : configure les paramètres pour chaque utilisateur.
Exemple de configuration :
[pgbouncer]
listen_port = 6543
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 : spécifie le port d’écoute de pgbouncer
- listen_addr : spécifie l’interface d’écoute de pgbouncer
- auth_type : spécifie le type d’authentification utilisé par pgbouncer
- auth_file : indique le nom du fichier utilisé pour charger les utilisateurs et leurs mot de passe
- logfile : indique le chemin vers le répertoire de log
- pidfile : précise la location du répertoire pid (nécessaire pour utiliser pgbouncer en mode démon)
- admin_users : défini les utilisateurs autorisés à se connecter a la base virtuelle pgbouncer et a utiliser des commandes d’administration
- pool_mode : défini le 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és à se connecter à Pgbouncer
[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 :
- 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’administrations.
Les fonctionnalités de la console d’administrations sont visible avec la commande SHOW HELP :
pgbouncer=# SHOW HELP;
NOTICE: Console usage
DÉTAIL :
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
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
SHOW
Utilisation¶
$ 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:6543
2024-02-13 13:48:47.069 UTC [29589] LOG listening on unix:/tmp/.s.PGSQL.6543
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 l’option -d :
Athentification via auth_file¶
Création d’un fichier /etc/pgbouncer/pgbouncer.ini avec une configuration minimale :
[databases]
db1 = host=127.0.0.1 dbname=db1
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pidfile = pgbouncer.pid
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
Création d’un utilisateur user1 et un d’une base db1 :
Récuperer le mot de passe scram-sha-256 de l’utilisateur qui vient d’être créer:
$ 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="
Recharger la configuration de PostgreSQL :
Lancement de pgbouncer en mode démon :
Se connecter à la base db1 avec l’utilisateur user1 via pgbouncer :
$ psql -U user1 -p 6543 -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 a l’écoute sur le port 5432 :
db1=> SELECT inet_server_addr(), inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
127.0.0.1 | 5432
(1 row)
Authentification via auth_query¶
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 :
Récupérer le mot de passe scram-sha-256 du role :
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 :
Recharger la configuration de PostgreSQL :
Accorder les droits SELECT à l’utilisateur sur le catalogue pg_shadow :
Créer un nouvelle utilisateur et tenter de se connecter avec via pgbouncer :