Configuration de pgBouncer avec des connexions TLS

27/10/2017, 15:44:20

Autorité de certification avec EasyRSA

Installation

Télécharger et installer la dernière version de EasyRSA. La version 3 est différente de la version 2, fournie dans Debain Stretch.

wget https://github.com/OpenVPN/easy-rsa/releases/download/v3.0.3/EasyRSA-3.0.3.tgz
tar xzf EasyRSA-3.0.3.tgz
cd EasyRSA-3.0.3

Création de l’autorité de certification

Créer un fichier de configuration :

cp vars.example vars
vi vars

Changer quelques informations :

set_var EASYRSA_REQ_COUNTRY	"FR"
set_var EASYRSA_REQ_PROVINCE	"Ile-de-France"
set_var EASYRSA_REQ_CITY	"Paris"
set_var EASYRSA_REQ_ORG		"MyOrg"
set_var EASYRSA_REQ_EMAIL	"admin@myorg.lo"
set_var EASYRSA_REQ_OU		"Clients"

Créer l’autorité :

./easyrsa init-pki
./easyrsa build-ca

Il faut lui donner un nom, par exemple CA Clients ou PostgreSQL ou un truc significatif. Il faut aussi saisir un mot de passe pour la clé du certificat racine, celui qui permet de signer tout le reste.

Création des certificats

Pour créer un bi-clé pour un serveur :

./easyrsa build-server-full postgresql nopass

Si la clé et le certificat du serveur doivent être transmis pas un moyen peu sécurisé, comme le mail, créer un fichier PKCS#12 plutot qu’un tar :

./easyrsa export-p12 postgresql

On peut mettre un mot de passe au fichier.

./easyrsa build-server-full pgbouncer nopass

Générer aussi la liste de révocation, qui permet rendre des certificats invalides.

./easyrsa gen-crl

Extraction des éléments du p12

La clé privée :

openssl pkcs12 -in postgresql.p12 -nocerts -nodes -out postgresql.key
chmod 600 postgresql.key

Le certificat :

openssl pkcs12 -in postgresql.p12 -clcerts -out postgresql.crt

Le certificat de l’AC :

openssl pkcs12 -in postgresql.p12 -cacerts -out ca.crt

Configurer PostgreSQL pour le TLS

il faut les fichiers suivants :

pki/ca.crt
pki/crl.pem
pki/issued/postgresql.crt
pki/private/postgresql.key

ou

pki/crl.pem
pki/private/postgresql.p12

et extraire le contenu du fichier PKCS#12 (voir plus haut).

Le plus simple est de placer ces fichiers dans le répertoire $PGDATA, les chemins dans la configuration sont relatifs à ce répertoire.

Dans $PGDATA/postgresql.conf, configurer au minimum :

ssl = on
ssl_cert_file = 'postgresql.crt'
ssl_key_file = 'postgresql.key'
ssl_ca_file = 'ca.crt'
ssl_crl_file = 'crl.pem'

Un reload de la configuration suffit pour prendre en compte les modifications. Ce paramétrage permet à l’instance de chiffrer les connexions. On définit si la connexion TLS est imposé au clients et l’authentification par certificat dans le fichier pg_hba.conf

Le TLS ne concerne pas les connections locales par socket unix, géré par le type ‘local’.

Le type ‘host’, concerne les connexions en clair ou TLS, utiliser ‘hostssl’ ou ‘hostnossl’ pour distinguer le TLS dans les autorisations.

On peut interdire les connexions en clair avec ces deux lignes :

hostnossl   all   all   0.0.0.0/0   reject
hostnossl   all   all   ::/0        reject

Toutes les lignes ‘host’ suivantes ne concerneront que les connexions TLS, du fait de l’interdiction précédente.

Configurer pgBouncer pour le TLS

Clients

il faut les fichiers suivants :

pki/ca.crt
pki/crl.pem
pki/issued/postgresql.crt
pki/private/postgresql.key

ou

pki/crl.pem
pki/private/postgresql.p12

et extraire le contenu du fichier PKCS#12 (voir plus haut).

Dans /etc/pgbouncer/pgbouncer.ini, il faut configurer ‘client_tls_sslmode’ :

Pour les chemins vers les fichiers :

client_tls_ca_file = /etc/pgbouncer/ca.crt
client_tls_key_file = /etc/pgbouncer/pgbouncer.key
client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt

Pour authentifier les clients avec un certificat, il faut :

auth_type = cert

Le CN du certificat doit alors être identique au nom d’utilisateur de connexion.

Cela ne dispense pas de la configuration de l’authenfication entre pgBouncer et PostgreSQL, avec ‘auth_file’, voire ‘auth_user’ et ‘auth_query’.

Backends PostgreSQL

Pour que pgBouncer se connecte aux serveurs PostgreSQL en TLS, dans /etc/pgbouncer/pgbouncer.ini, il faut configurer :

server_tls_sslmode = require

Si on souhaite que pgBouncer vérifie le certificat de l’AC du serveur PostgreSQL :

server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/ca.crt

On peut enfin configurer pgBouncer et PostgreSQL pour que pgBouncer s’authentifie avec un certificat client, dans /etc/pgbouncer.ini :

server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/ca.crt
server_tls_key_file = /etc/pgbouncer/pgbouncer_back.key
server_tls_cert_file = /etc/pgbouncer/pgbouncer_back.crt

Le fichier pg_hba.conf devra alors demander un certificat avec l’option clientcert=1 :

hostssl all all ip_pgbouncer/32 md5 clientcert=1

On ne peut utiliser la méthode cert uniquement si le CN du certificat client de pgBouncer est identique au nom de l’utilisateur qui se connecte. Ainsi, on ne peut plus utiliser qu’un seul nom d’utilisateur pour les accès à PostgreSQL, dans ce cas là il vaut mieux forcer le nom d’utilisateur dans la section [database].

Réflexions sur l'archivage des fichiers WAL

30/12/2015, 21:01:20

PostgreSQL faisant son bonhomme de chemin, on se retrouve désormais avec des configurations où il faut archiver plusieurs fois les WAL parce qu’on a de la sauvegarde PITR et de la réplication.

Le plus gros piège lorsqu’on a du PITR et de la réplication, ou plusieurs serveurs standby, c’est d’oublier que chaque élément de l’architecture qui consomme du WAL doit avoir son propre répertoire de WAL archivés, car chacun purge les fichiers différemment.

On tombe dans le piège facilement, en se disant, “pas de problème pour la purge des vieux fichiers, c’est le PITR ou le slave le plus éloigné qui purgera”. Si la purge du PITR passe alors que le standby était déconnecté du maitre, cette purge peut casser la réplication.

La solution est d’archiver plusieurs fois le même fichier WAL. Pour optimiser, le plus efficace à l’usage est d’utiliser des hardlinks. En gros, on archive une fois le fichier WAL et on crée autant de lien hard qu’il faut pour les autres consommateurs de WAL archivés. Rappelons, que la donnée n’est supprimée que lorsqu’il n’existe plus aucun lien et que plus aucun processus n’a le fichier ouvert, à ne pas confondre avec un lien symbolique.

Pour archiver vite, il vaut mieux éviter de compresser et stocker les archives soit en local, soit sur un partage NFS, l’archivage par SSH restant le plus lent. Tout est compromis entre chiffrage des communications sur le réseau et espace disque disponible, les liens hard restant rapides à créer et avec une consommation d’espace disque supplémentaire négligeable.

Enfin, PostgreSQL exécute la commande d’archivage avec l’appel system() qui fork un shell : toutes les possibilités du shell sont alors disponibles, par exemple :

archive_command = 'rsync -a %p slave1:/archived_xlog/slave1/%f && ssh slave1 "for h in slave2 pitr; do ln /archived_xlog/slave1/%f /archived_xlog/$h/%f; done"'

Oui, une boucle et une seule copie avec rsync par SSH pour 3 utilisations. On préfèrera surement faire un script pour rendre les choses plus lisibles. Ça marche aussi pour restore_command et archive_cleanup_command dans recovery.conf :

restore_command = 'cp /archived_xlog/$(hostname)/%f %p'
archive_cleanup_command = 'pg_archivecleanup /archived_xlog/$(hostname) %r'

pitrery 1.10

21/10/2015, 16:42:20

Un semaine après la sortie de la version 1.9, un collègue découvre un bug dans le strict de restore des fichiers WAL, restore_xlog. Lorsqu’on utilise SSH pour stocker les archives des fichiers WAL, le script ne tient pas compte des paramètres spécifiant l’utilisateur et la machine distante en provenance du fichier de configuration.

Ce qui fait que la restore ne fonctionne pas lorsqu’on démarre PostgreSQL à moins de modifier l’appel à restore_xlog dans le paramètre restore_command du fichier recovery.conf pour y indiquer ces deux informations, car en ligne de commande ils sont bien pris en compte.

On peut aussi ajouter dans le fichier de configuration :

RESTORE_COMMAND="/path/to/restore_xlog -C <config_file> -h <archive_host> -u <archive_user> %f %p"

La version 1.10 corrige ce problème, voir le site de pitrery.

pitrery 1.9

13/10/2015, 09:33:28

La version 1.9 de pitrery vient de sortir. pitrery est un outil de sauvegarde PITR pour PostgreSQL, qui se focalise sur la sauvegarde d’une instance et la préparation de la restauration. Il est sous la même license que PostgreSQL.

Volontairement simple en terme de fonctionnalités (backup, restore, list et purge), il fournit également des scripts pour gérer l’archivage des journaux de transaction, sans imposer leur utilisation. L’objectif est de sauvegarder et restaurer en ligne de commande avec un outil facile d’utilisation. On peut d’ailleurs surcharger les paramètres trouvés dans le fichier de configuration pour adapter le comportement de l’outil à l’exécution.

Cette dernière version, apporte :

Le site de pitrery, contient le code source, les packages et la documentation.

pg_back 1.2

06/10/2015, 10:47:22

pg_back est sorti en version 1.2 depuis quelques semaines et je n’avais pas encore eu le temps de présenter les nouvelles fonctionnalités ajoutées dans cette version.

L’objectif de pg_back reste toujours d’être un script assez simple pour être modifié, adapté. Il ne faut donc pas hésiter à le modifier pour son propre usage. Je me concentre donc sur des fonctionnalités absolument indispensables.

La version apporte ainsi deux nouvelles fonctionnalités indispensables. En premier, des messages horodatés un peu partout pour avoir des logs d’exécution facilement exploitables, avec un “quiet mode” pour les désactiver. La seconde fonctionnalité permet de lancer pg_back directement sur un serveur hot-standby, celui-ci se charge alors de mettre en pause le rejeu des transactions durant le dump, cela permet d’éviter l’annulation des dumps à cuase de la réplication.

pg_back est disponible sur github, https://github.com/orgrim/pg_back.