Sécuriser PostgreSQL : pg_hba.conf, TLS, rôles et moindre privilège

Un PostgreSQL en production avec trust dans pg_hba.conf, un superuser partagé par toute l’équipe et pas de chiffrement TLS : c’est le point de départ de la plupart des installations. Chaque maillon est une porte ouverte — un seul suffit pour compromettre toutes les données.

Ce guide suit une démarche couche par couche : d’abord contrôler qui peut se connecter (pg_hba.conf), puis chiffrer le transport (TLS), ensuite limiter ce que chaque rôle peut faire (moindre privilège), et enfin restreindre l’exposition réseau et tracer les actions (audit). Chaque commande est testée sur un lab réel (PostgreSQL 18.3, Debian 12, base lab_admin).

Version utilisée

Ce guide s’appuie sur PostgreSQL 18.3 (Debian 18.3-1.pgdg12+1) sous Debian 12. Les nouveautés PG 18 sont signalées : dépréciation de MD5 avec md5_password_warnings, ssl_tls13_ciphers, log_lock_failures.

Ce que vous allez apprendre

• Lire et écrire un pg_hba.conf restrictif adapté à votre architecture
• Activer TLS et forcer les connexions chiffrées avec hostssl
• Remplacer md5 par scram-sha-256 et comprendre la dépréciation MD5 en PG 18
• Séparer les rôles avec le pattern owner / writer / reader et DEFAULT PRIVILEGES
• Restreindre l’exposition réseau : listen_addresses, firewall, connection pooling
• Configurer le logging pour l’audit : connexions, DDL, requêtes lentes, échecs de verrous

Dans quel contexte ?

Vous avez besoin de sécuriser PostgreSQL dans ces situations :

• Vous passez d’un environnement de développement (localhost, trust partout) à une mise en production réseau
• Votre équipe partage le compte postgres superuser pour accéder à la base — vous voulez séparer les rôles
• Vous devez prouver la conformité de votre base (audit, chiffrement, traçabilité des accès)
• Votre base est exposée sur le réseau et vous ne savez pas si les connexions sont chiffrées
• Vous utilisez encore md5 comme méthode d’authentification et PostgreSQL vous affiche des warnings de dépréciation
• Vous préparez un durcissement avant un audit de sécurité ou une mise en conformité

Ce guide ne couvre pas…

Configuration serveur shared_buffers, work_mem, max_connections et paramètres de performance

Supervision et maintenance pg_stat_statements, VACUUM, EXPLAIN ANALYZE — diagnostiquer les performances

Sauvegarde et restauration pg_dump, pg_basebackup, PITR — la sécurité des données passe aussi par les backups

Prérequis

• PostgreSQL installé et le service actif (voir le guide Installation)
• Accès au rôle superuser (postgres) via sudo -u postgres psql
• Connaître les bases de psql (voir le guide Prise en main de psql)
• Accès root ou sudo sur le serveur (pour modifier les fichiers de configuration et les certificats)

pg_hba.conf : la politique d’accès de PostgreSQL

Anatomie d’une ligne

pg_hba.conf (Host-Based Authentication) est le fichier qui contrôle qui peut se connecter, depuis où et avec quelle méthode d’authentification. Chaque ligne suit le format :

TYPE    DATABASE    USER    ADDRESS    METHOD

Champ	Signification	Exemples
TYPE	Type de connexion	local (socket Unix), host (TCP), hostssl (TCP + TLS obligatoire)
DATABASE	Base(s) autorisée(s)	all, lab_admin, sameuser
USER	Rôle(s) autorisé(s)	all, postgres, +app_writer (groupe)
ADDRESS	IP ou réseau source	127.0.0.1/32, 192.168.1.0/24, ::1/128
METHOD	Méthode d’authentification	peer, scram-sha-256, cert, reject

Glissez pour voir

Voici le pg_hba.conf par défaut de notre lab (Debian 12, PostgreSQL 18.3) :

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Lecture : Les connexions locales (socket Unix) utilisent peer — le système vérifie que l’utilisateur Unix correspond au rôle PostgreSQL, sans mot de passe. Les connexions TCP (host) sur 127.0.0.1 exigent scram-sha-256 — un mot de passe hashé avec un algorithme résistant aux attaques par rejeu.

Les méthodes d’authentification

Méthode	Fonctionnement	Sécurité	Usage recommandé
trust	Aucune vérification	Dangereuse	Jamais en production
peer	Vérifie l’utilisateur OS (socket local uniquement)	Forte	Accès local pour postgres
md5	Mot de passe hashé MD5	Déprécié en PG 18	Migrer vers scram-sha-256
scram-sha-256	Mot de passe hashé SCRAM	Forte	Recommandé pour TCP
cert	Certificat TLS client	Très forte	Automatisation, services
reject	Refuse la connexion	—	Bloquer explicitement un rôle/réseau

Glissez pour voir

trust : choix de durcissement

trust autorise n’importe qui à se connecter sans mot de passe. La doc PostgreSQL admet que trust peut être acceptable dans des cas très limités : sur un poste mono-utilisateur, ou sur une socket locale protégée par les permissions du système de fichiers (unix_socket_permissions). PostgreSQL précise aussi que trust est rarement justifié sur des connexions TCP autres que localhost.

Dans ce guide de durcissement, nous bannissons trust : c’est un choix de baseline de production, pas une règle universelle. Si trust apparaît sur une ligne host, tout utilisateur réseau peut se connecter comme superuser — c’est inacceptable en production. Préférez peer pour le local et scram-sha-256 pour TCP.

Ordre d’évaluation : la première ligne qui matche gagne

PostgreSQL évalue les lignes de pg_hba.conf de haut en bas et s’arrête à la première correspondance. L’ordre est critique.

# ❌ PROBLÈME : la ligne 1 matche avant la ligne 2
host    all    all       192.168.1.0/24    scram-sha-256
host    all    intrus    192.168.1.0/24    reject

# ✅ CORRECT : le reject en premier bloque l'utilisateur intrus
host    all    intrus    192.168.1.0/24    reject
host    all    all       192.168.1.0/24    scram-sha-256

Règle pratique : placez les lignes les plus restrictives (reject, utilisateurs spécifiques) avant les lignes génériques (all).

Pour vérifier les règles chargées par PostgreSQL sans ambiguïté, interrogez pg_hba_file_rules :

SELECT rule_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY rule_number;

 rule_number | type  |   database    | user_name  |  address  |  auth_method
-------------+-------+---------------+------------+-----------+---------------
           1 | local | {all}         | {postgres} |           | peer
           2 | local | {all}         | {all}      |           | peer
           3 | host  | {all}         | {all}      | 127.0.0.1 | scram-sha-256
           4 | host  | {all}         | {all}      | ::1       | scram-sha-256
           5 | local | {replication} | {all}      |           | peer
           6 | host  | {replication} | {all}      | 127.0.0.1 | scram-sha-256
           7 | host  | {replication} | {all}      | ::1       | scram-sha-256

Construire un pg_hba.conf restrictif

Voici un exemple adapté à un serveur de production accessible depuis un réseau applicatif 10.0.1.0/24 :

# TYPE   DATABASE        USER            ADDRESS              METHOD

# Superuser : socket local uniquement, jamais en TCP
local    all             postgres                             peer

# Application : réseau dédié, SSL obligatoire
hostssl  lab_admin       app_writer      10.0.1.0/24          scram-sha-256
hostssl  lab_admin       app_reader      10.0.1.0/24          scram-sha-256

# Monitoring : IP spécifique, lecture seule
hostssl  lab_admin       monitoring      10.0.1.50/32         scram-sha-256

# Réplication : serveur standby dédié
hostssl  replication     replicator      10.0.2.10/32         scram-sha-256

# Bloquer tout le reste explicitement
host     all             all             0.0.0.0/0            reject
host     all             all             ::0/0                reject

Principes appliqués :

• Le superuser ne se connecte que localement (socket Unix, peer)
• Chaque rôle est restreint à sa base et son réseau
• hostssl force le chiffrement TLS
• Les lignes reject en fin de fichier bloquent tout ce qui n’est pas explicitement autorisé

hostssl vs host : forcer le chiffrement

Type	Connexion TCP	TLS requis	Usage
host	Oui	Non	Dev local, connexions internes de confiance
hostssl	Oui	Oui	Production — connexions chiffrées uniquement
hostnossl	Oui	Non (TLS interdit)	Rarement utile

Glissez pour voir

hostssl sans SSL activé = règle ignorée

Si le serveur n’a pas ssl = on dans postgresql.conf, ou si PostgreSQL n’a pas été compilé avec le support SSL, une règle hostssl est silencieusement ignorée (avec un warning dans les logs). Vérifiez toujours que SHOW ssl; renvoie on avant de compter sur hostssl pour forcer le chiffrement.

Sur notre lab, remplaçons host par hostssl pour forcer le chiffrement sur les connexions TCP :

# Avant
host    all    all    127.0.0.1/32    scram-sha-256

# Après
hostssl all    all    127.0.0.1/32    scram-sha-256

Après ce changement, une connexion sans TLS est refusée :

$ PGSSLMODE=disable psql -U admin_lab -h 127.0.0.1 -d lab_admin
psql: error: connection to server at "127.0.0.1", port 5432 failed:
  FATAL: no pg_hba.conf entry for host "127.0.0.1", user "admin_lab",
  database "lab_admin", no encryption

Et avec TLS, la connexion fonctionne :

$ PGSSLMODE=require psql -U admin_lab -h 127.0.0.1 -d lab_admin
SSL Connection       | true
SSL Protocol         | TLSv1.3
SSL Cipher           | TLS_AES_256_GCM_SHA384

Recharger sans redémarrer : pg_reload_conf()

Après toute modification de pg_hba.conf, appliquez les changements sans couper les connexions :

SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t

pg_reload_conf() relit le fichier et applique les nouvelles règles aux prochaines connexions. Les connexions déjà établies ne sont pas affectées. Vérifiez ensuite avec pg_hba_file_rules que les règles sont correctement chargées — la colonne error signale les lignes mal formées.

Attention : pas de rollback automatique

Si votre pg_hba.conf contient une erreur de syntaxe, pg_reload_conf() garde les anciennes règles et signale l’erreur dans les logs. Mais si vous bloquez accidentellement votre propre connexion, vous ne pourrez plus vous reconnecter en TCP. Gardez toujours une session locale (sudo -u postgres psql) ouverte pendant les modifications.

PostgreSQL 18 : dépréciation de MD5

PostgreSQL 18 introduit md5_password_warnings (activé par défaut). Quand vous créez un rôle avec un mot de passe MD5, PostgreSQL affiche un avertissement explicite :

SET password_encryption = 'md5';
CREATE ROLE test_md5 LOGIN PASSWORD 'md5test';

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed
         in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about
       migrating to another password type.

L’entrée md5 dans pg_hba.conf bascule déjà automatiquement vers SCRAM si le mot de passe stocké est en format SCRAM. La migration est simple : changez password_encryption en scram-sha-256 (c’est le défaut en PG 18) et réattribuez les mots de passe.

Pour vérifier que tous vos mots de passe sont bien en SCRAM :

SELECT rolname, LEFT(rolpassword, 14) AS pass_prefix
FROM pg_authid WHERE rolpassword IS NOT NULL;

  rolname  |  pass_prefix
-----------+----------------
 admin_lab | SCRAM-SHA-256$
 alice     | SCRAM-SHA-256$
 bob_ro    | SCRAM-SHA-256$

Tous les mots de passe sont bien stockés en SCRAM — aucun risque de dépréciation.

Chiffrement TLS

Pourquoi chiffrer les connexions

Sans TLS, les données transitent en clair sur le réseau : requêtes SQL, mots de passe SCRAM (le challenge est chiffré mais le trafic applicatif ne l’est pas), résultats de requêtes. Sur un réseau partagé, un simple tcpdump ou Wireshark suffit à capturer tout le trafic.

Certificat : auto-signé pour le lab, PKI en production

Sur notre lab Debian, PostgreSQL utilise le certificat snakeoil généré à l’installation :

ssl_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
ssl_key_file  = /etc/ssl/private/ssl-cert-snakeoil.key

subject=CN = pg-lab.lab
issuer=CN = pg-lab.lab
notBefore=Apr 13 08:49:03 2026 GMT
notAfter=Apr 10 08:49:03 2036 GMT

Ce certificat auto-signé suffit pour le lab mais ne permet pas au client de vérifier l’identité du serveur (verify-ca et verify-full échouent).

Pour générer un certificat auto-signé dédié (si le snakeoil n’existe pas ou si vous voulez un CN spécifique) :

openssl req -new -x509 -days 365 -nodes \
  -keyout /etc/postgresql/18/main/server.key \
  -out /etc/postgresql/18/main/server.crt \
  -subj "/CN=pg-lab.example.com"

# Permissions : la clé doit être lisible uniquement par postgres
chown postgres:postgres /etc/postgresql/18/main/server.key
chmod 600 /etc/postgresql/18/main/server.key

En production, utilisez un certificat signé par une PKI interne ou Let’s Encrypt. Cela permet aux clients d’utiliser sslmode=verify-full pour vérifier à la fois le certificat et le nom d’hôte.

Activer ssl=on dans postgresql.conf

SHOW ssl;

 ssl
-----
 on

Sur Debian, ssl = on est activé par défaut. Si ce n’est pas le cas :

ALTER SYSTEM SET ssl = on;

# Redémarrage requis (paramètre postmaster)
sudo systemctl restart postgresql@18-main

Vérifier la connexion SSL

Depuis le client, utilisez \conninfo dans psql pour voir si la connexion est chiffrée :

$ PGPASSWORD='Lab2026!secure' psql -U admin_lab -h 127.0.0.1 -d lab_admin

\conninfo
 SSL Connection       | true
 SSL Library          | OpenSSL
 SSL Protocol         | TLSv1.3
 SSL Key Bits         | 256
 SSL Cipher           | TLS_AES_256_GCM_SHA384

Ou interrogez pg_stat_ssl depuis la session elle-même :

SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

 ssl | version |         cipher         | bits
-----+---------+------------------------+------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256

sslmode côté client

Le paramètre sslmode (variable d’environnement PGSSLMODE ou paramètre de connexion) contrôle le niveau de vérification SSL côté client :

sslmode	Chiffrement	Vérifie le certificat	Vérifie le hostname	Protection MITM	Usage
disable	Non	—	—	Non	Tests uniquement
allow	Si proposé	Non	Non	Non	Déconseillé
prefer	Si possible	Non	Non	Non	Défaut — aucune garantie de sécurité
require	Oui	Non	Non	Non	Chiffrement seul, pas de vérification d’identité
verify-ca	Oui	Oui	Non	Partiel	PKI interne — suffisant si la CA est privée
verify-full	Oui	Oui	Oui	Oui	Production — vérifie serveur + hostname

Glissez pour voir

Différence clé require vs verify-full : require chiffre la connexion, mais ne protège pas contre un man-in-the-middle — un attaquant peut présenter son propre certificat et relayer le trafic. Seul verify-full (ou verify-ca avec une CA privée) vérifie l’identité du serveur.

prefer ne protège pas

La doc PostgreSQL est explicite : sslmode=prefer (le défaut) « n’a pas de sens d’un point de vue sécurité » et « n’est pas recommandé pour les déploiements sécurisés ». Un attaquant man-in-the-middle peut forcer un downgrade vers une connexion non chiffrée. En production, utilisez verify-full avec un certificat signé par une CA. Si vous n’avez pas de PKI, require est le minimum — mais sachez qu’il ne protège pas contre le MITM.

Avec un certificat auto-signé, verify-ca échoue car le client ne reconnaît pas la CA :

$ PGSSLMODE=verify-ca psql -U admin_lab -h 127.0.0.1 -d lab_admin
psql: error: root certificate file "/home/lab/.postgresql/root.crt"
  does not exist
Either provide the file, use the system's trusted roots with
  sslrootcert=system, or change sslmode to disable server certificate
  verification.

Pour autoriser verify-ca avec un certificat auto-signé, copiez le certificat dans ~/.postgresql/root.crt côté client.

PostgreSQL 18 : ssl_tls13_ciphers

PostgreSQL 18 ajoute le paramètre ssl_tls13_ciphers qui permet de contrôler précisément les suites cryptographiques TLS 1.3 (séparé de ssl_ciphers qui ne concerne que TLS 1.2 et antérieur).

SELECT name, setting FROM pg_settings WHERE name LIKE 'ssl%ciphers%';

       name        |           setting
-------------------+------------------------------
 ssl_ciphers       | HIGH:MEDIUM:+3DES:!aNULL
 ssl_tls13_ciphers |

La valeur vide signifie « utiliser les défauts d’OpenSSL » — ce qui est correct pour la plupart des cas. Si vous devez restreindre, par exemple pour ne garder que AES-256 :

ALTER SYSTEM SET ssl_tls13_ciphers = 'TLS_AES_256_GCM_SHA384';
SELECT pg_reload_conf();

Autres paramètres SSL importants :

Paramètre	Valeur par défaut	Signification
ssl_min_protocol_version	TLSv1.2	Version TLS minimale acceptée
ssl_max_protocol_version	(vide = toutes)	Version TLS maximale
ssl_prefer_server_ciphers	on	Le serveur choisit le cipher, pas le client
ssl_groups	X25519:prime256v1	Groupes d’échange de clés (PG 18)

Glissez pour voir

Rôles et moindre privilège

Principe : ne jamais utiliser le superuser pour l’applicatif

Le rôle postgres est l’équivalent de root : il peut tout faire, y compris supprimer des bases, modifier la configuration et lire tous les fichiers du data directory. Aucune application ne devrait se connecter avec postgres.

Sur notre lab, seuls deux rôles existent au départ :

 Role name |                         Attributes
-----------+------------------------------------------------------------
 admin_lab | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

L’application utilise admin_lab pour tout — lectures, écritures, DDL. C’est mieux que postgres, mais ce rôle a encore trop de privilèges.

Pattern owner / writer / reader

Le modèle de base pour le moindre privilège repose sur trois rôles-groupes (sans droit de connexion) :

Rôle	Droits	Usage
app_owner	DDL : CREATE, ALTER, DROP sur les objets du schéma	Migrations, déploiement
app_writer	DML : SELECT, INSERT, UPDATE, DELETE	Application backend
app_reader	SELECT uniquement	Monitoring, reporting, BI

Glissez pour voir

1. Créer les rôles-groupes (sans LOGIN — ce sont des groupes) :

   CREATE ROLE app_owner NOLOGIN;
   CREATE ROLE app_writer NOLOGIN;
   CREATE ROLE app_reader NOLOGIN;

2. Transférer la propriété du schéma et des tables à app_owner :

   ALTER SCHEMA app OWNER TO app_owner;
   ALTER TABLE app.clients OWNER TO app_owner;
   ALTER TABLE app.commandes OWNER TO app_owner;
   ALTER TABLE app.logs OWNER TO app_owner;

3. Accorder les privilèges sur les objets existants :

   -- Accès au schéma pour writer et reader
   GRANT USAGE ON SCHEMA app TO app_writer, app_reader;
   
   -- Reader : SELECT uniquement
   GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
   
   -- Writer : SELECT + INSERT + UPDATE + DELETE
   GRANT SELECT, INSERT, UPDATE, DELETE
     ON ALL TABLES IN SCHEMA app TO app_writer;
   GRANT USAGE, SELECT
     ON ALL SEQUENCES IN SCHEMA app TO app_writer;

4. Configurer les DEFAULT PRIVILEGES pour les futurs objets :

   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
       GRANT SELECT ON TABLES TO app_reader;
   
   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
       GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;
   
   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
       GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

5. Vérifier les privilèges :

   \dp app.*

                                           Access privileges
    Schema |       Name       |   Type   |      Access privileges       | Column privileges | Policies
   --------+------------------+----------+------------------------------+-------------------+----------
    app    | clients          | table    | app_owner=arwdDxtm/app_owner+|                   |
           |                  |          | app_reader=r/app_owner      +|                   |
           |                  |          | app_writer=arwd/app_owner    |                   |
    app    | commandes        | table    | app_owner=arwdDxtm/app_owner+|                   |
           |                  |          | app_reader=r/app_owner      +|                   |
           |                  |          | app_writer=arwd/app_owner    |                   |
    app    | logs             | table    | app_owner=arwdDxtm/app_owner+|                   |
           |                  |          | app_reader=r/app_owner      +|                   |
           |                  |          | app_writer=arwd/app_owner    |                   |

Lecture des codes ACL : r = SELECT, a = INSERT, w = UPDATE, d = DELETE, D = TRUNCATE, x = REFERENCES, t = TRIGGER, m = MAINTAIN (PG 18).

DEFAULT PRIVILEGES : droits automatiques sur les futurs objets

Sans DEFAULT PRIVILEGES, chaque nouvelle table créée par app_owner n’a aucun droit pour app_writer et app_reader — il faut les accorder manuellement à chaque migration. DEFAULT PRIVILEGES automatise cette attribution :

\ddp

                 Default access privileges
   Owner   | Schema |   Type   |     Access privileges
-----------+--------+----------+---------------------------
 app_owner | app    | sequence | app_writer=rU/app_owner
 app_owner | app    | table    | app_writer=arwd/app_owner+
           |        |          | app_reader=r/app_owner

Désormais, toute table ou séquence créée par app_owner dans le schéma app recevra automatiquement les bons droits.

Piège classique : les DEFAULT PRIVILEGES dépendent du créateur

Les DEFAULT PRIVILEGES ne s’appliquent que quand l’objet est créé par le rôle spécifié (FOR ROLE app_owner). Si postgres ou un autre rôle crée une table dans le schéma app, les defaults ne s’appliquent pas — les app_writer et app_reader n’auront aucun accès au nouvel objet.

La doc officielle confirme : au moment de la création d’un objet, seuls les default privileges du rôle courant s’appliquent ; ils ne sont pas hérités des rôles dont on est membre.

Règle à systématiser : chaque script de migration doit commencer par :

SET ROLE app_owner;

Ainsi, toutes les tables et séquences créées pendant la migration bénéficient automatiquement des DEFAULT PRIVILEGES.

REVOKE sur le schéma public (PostgreSQL 15+)

Avant PostgreSQL 15, tous les rôles avaient le droit de créer des objets dans le schéma public. C’était un vecteur d’attaque classique : un rôle applicatif pouvait créer une fonction malveillante dans public qui serait exécutée par un autre rôle via le search_path.

Depuis PostgreSQL 15, le droit CREATE sur public est révoqué par défaut pour les bases créées nativement en PG 15+. Sur notre lab (cluster initialisé en PG 18) :

SELECT has_schema_privilege('public', 'public', 'CREATE') AS public_can_create;

 public_can_create
-------------------
 f

Pas d’action nécessaire ici, car le cluster a été créé en PG 18.

Bases issues d'un upgrade depuis PG 14 ou antérieur

Si votre base provient d’un upgrade depuis PostgreSQL 14 ou antérieur (via pg_upgrade ou dump/restore), le droit CREATE sur le schéma public n’a pas été révoqué automatiquement. Vous devez le faire manuellement :

-- Obligatoire sur les bases upgradées depuis PG 14 ou antérieur
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

La documentation PostgreSQL recommande aussi d’auditer le schéma public pour détecter des objets portant le même nom que des objets de pg_catalog (vecteur d’attaque via le search_path).

Si vous êtes sur une version antérieure à PG 15, la même commande REVOKE est nécessaire dans tous les cas.

Cas pratique : tester les 3 rôles sur le lab

Créons des rôles de connexion qui héritent des groupes :

CREATE ROLE alice LOGIN PASSWORD 'Alice2026!' IN ROLE app_writer;
CREATE ROLE bob_ro LOGIN PASSWORD 'BobRO2026!' IN ROLE app_reader;

Test du reader (bob_ro) :

$ PGPASSWORD='BobRO2026!' psql -U bob_ro -h 127.0.0.1 -d lab_admin

-- SELECT fonctionne
SELECT count(*) FROM app.clients;
 count
-------
     5

-- INSERT refusé
INSERT INTO app.clients (nom, email, ville) VALUES ('intrus', 'intrus@test.fr', 'Paris');
ERROR:  permission denied for table clients

Test du writer (alice) :

$ PGPASSWORD='Alice2026!' psql -U alice -h 127.0.0.1 -d lab_admin

-- INSERT fonctionne
INSERT INTO app.clients (nom, email, ville) VALUES ('test_alice', 'alice@lab.fr', 'Lyon');
INSERT 0 1

-- SELECT fonctionne
SELECT * FROM app.clients WHERE email = 'alice@lab.fr';
 id |    nom     |    email     | ville | inscription
----+------------+--------------+-------+-------------
 42 | test_alice | alice@lab.fr | Lyon  | 2026-04-13

-- DROP TABLE refusé
DROP TABLE app.clients;
ERROR:  must be owner of table clients

-- CREATE TABLE dans le schéma app refusé
CREATE TABLE app.test_alice (id int);
ERROR:  permission denied for schema app

Le moindre privilège fonctionne : chaque rôle ne peut faire que ce qui lui est explicitement autorisé.

Restreindre l’exposition réseau

listen_addresses : ne pas écouter sur 0.0.0.0 sans raison

SELECT name, setting FROM pg_settings
WHERE name IN ('listen_addresses', 'port', 'max_connections', 'superuser_reserved_connections');

              name              |  setting
--------------------------------+-----------
 listen_addresses               | localhost
 max_connections                | 100
 port                           | 5432
 superuser_reserved_connections | 3

listen_addresses = localhost signifie que PostgreSQL n’écoute que sur 127.0.0.1 — aucune connexion réseau externe n’est possible. C’est la configuration la plus sûre.

Si vous devez accepter des connexions réseau, restreignez au maximum :

-- Écouter sur une seule IP (réseau applicatif)
ALTER SYSTEM SET listen_addresses = 'localhost,10.0.1.5';

Configuration	Exposition	Usage
localhost	Socket local + 127.0.0.1 uniquement	Dev, instance unique
10.0.1.5	Une seule IP réseau	Production — réseau applicatif dédié
* ou 0.0.0.0	Toutes les interfaces	Déconseillé sauf derrière un pooler/firewall

Glissez pour voir

listen_addresses nécessite un redémarrage

listen_addresses est un paramètre postmaster — il faut redémarrer PostgreSQL pour l’appliquer. Un pg_reload_conf() ne suffit pas.

Firewall système : restreindre le port 5432

Même si pg_hba.conf bloque les connexions non autorisées, un firewall système ajoute une couche de défense :

nftables (Debian 12+)

# Autoriser le réseau applicatif uniquement
nft add rule inet filter input tcp dport 5432 ip saddr 10.0.1.0/24 accept
nft add rule inet filter input tcp dport 5432 drop

ufw (Ubuntu)

# Autoriser une IP spécifique
sudo ufw allow from 10.0.1.0/24 to any port 5432
sudo ufw deny 5432

firewalld (RHEL/Rocky)

# Zone dédiée pour PostgreSQL
sudo firewall-cmd --new-zone=postgres --permanent
sudo firewall-cmd --zone=postgres --add-source=10.0.1.0/24 --permanent
sudo firewall-cmd --zone=postgres --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Connection pooling (PgBouncer) comme point d’entrée unique

En production, placez un PgBouncer entre les applications et PostgreSQL :

• Les applications se connectent à PgBouncer (port 6432)
• PgBouncer se connecte à PostgreSQL (port 5432 sur localhost)
• PostgreSQL écoute uniquement sur localhost

Avantages :

• Un seul point d’entrée réseau à sécuriser
• Limitation des connexions : 100 clients applicatifs → 20 connexions PostgreSQL
• Coupure propre : PgBouncer peut refuser les nouvelles connexions sans toucher PostgreSQL

Logging pour l’audit

Configuration recommandée

Le logging est votre trace de preuve en cas d’incident. Voici la configuration de notre lab :

SELECT name, setting FROM pg_settings
WHERE name IN ('log_connections','log_disconnections','log_statement',
               'log_line_prefix','log_lock_failures',
               'log_min_duration_statement','logging_collector');

            name            |     setting
----------------------------+------------------
 log_connections            | all
 log_disconnections         | on
 log_line_prefix            | %m [%p] %q%u@%d
 log_lock_failures          | off
 log_min_duration_statement | 500
 log_statement              | ddl
 logging_collector          | on

Paramètre	Valeur recommandée	Pourquoi
log_connections	all	Trace toutes les phases de connexion (voir encadré ci-dessous)
log_disconnections	on	Trace la durée de chaque session
log_statement	ddl	Trace les CREATE, ALTER, DROP — pas les SELECT/INSERT (trop verbeux)
log_min_duration_statement	500	Trace les requêtes > 500 ms — complète pg_stat_statements
log_line_prefix	%m [%p] %q%u@%d	Horodatage, PID, utilisateur@base
logging_collector	on	Écrit les logs dans des fichiers dédiés (rotation automatique)

Glissez pour voir

PostgreSQL 18 : log_connections n'est plus un simple booléen

Depuis PostgreSQL 18, log_connections accepte une liste d’options à la place de on/off :

Option	Ce qui est tracé
receipt	Réception de la connexion TCP
authentication	Identité utilisée par la méthode d’authentification
authorization	Autorisation réussie (user, base, application)
setup_durations	Durées d’établissement (total, fork, authentification)
all	Toutes les options ci-dessus

Glissez pour voir

Les valeurs on/off restent acceptées pour compatibilité : on équivaut à receipt,authentication,authorization (mais pas à all — setup_durations est exclu). Pour un audit complet, utilisez explicitement all.

Ce que montrent les logs

Voici un extrait réel des logs de notre lab après les tests de permissions :

2026-04-13 11:23:55.548 UTC [9273] alice@lab_admin LOG:
  connection authorized: user=alice database=lab_admin application_name=psql
  SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)

2026-04-13 11:23:55.551 UTC [9273] alice@lab_admin LOG:
  statement: DROP TABLE app.clients;
2026-04-13 11:23:55.551 UTC [9273] alice@lab_admin ERROR:
  must be owner of table clients

On voit que :

• alice s’est connectée depuis lab_admin avec TLS 1.3
• Elle a tenté un DROP TABLE (statement DDL tracé par log_statement = ddl)
• Le DROP a échoué (pas owner) — l’erreur est tracée
• Le log inclut le PID, l’utilisateur, la base et l’horodatage — suffisant pour croiser avec les autres systèmes

log_line_prefix : les tokens importants

Token	Signification	Exemple
%m	Horodatage avec millisecondes	2026-04-13 11:23:55.548 UTC
%p	PID du backend	9273
%u	Utilisateur	alice
%d	Base de données	lab_admin
%h	Nom d’hôte ou IP du client	10.0.1.20
%r	Hôte:port du client	10.0.1.20(54321)
%q	Séparateur (rien si pas de session)	—

Glissez pour voir

Le préfixe recommandé pour la production incluant l’IP client :

ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d %h ';
SELECT pg_reload_conf();

PostgreSQL 18 : log_lock_failures

PostgreSQL 18 introduit log_lock_failures — quand il est activé, les échecs d’acquisition de verrou liés à SELECT ... NOWAIT sont tracés dans les logs :

ALTER SYSTEM SET log_lock_failures = on;
SELECT pg_reload_conf();

C’est un paramètre superuser — il ne nécessite pas de redémarrage.

Portée actuelle limitée

Actuellement, log_lock_failures ne couvre que les échecs liés à SELECT ... NOWAIT. Ce n’est pas encore un traceur générique de tous les échecs de lock_timeout ou des contentions ordinaires de verrous. Pour tracer les attentes de verrous (pas les échecs), utilisez log_lock_waits, qui trace les sessions bloquées plus longtemps que deadlock_timeout.

OAuth (cas avancé)

PostgreSQL 18 : aperçu OAuth 2.0 (configuration avancée)

PostgreSQL 18 introduit le support OAuth 2.0 comme méthode d’authentification dans pg_hba.conf. Cela permet de déléguer l’authentification à un Identity Provider (Keycloak, Azure AD, Okta…) au lieu de gérer des mots de passe dans PostgreSQL.

La configuration nécessite au minimum les paramètres issuer et scope dans pg_hba.conf, et côté serveur, le paramètre oauth_validator_libraries doit lister au moins une bibliothèque de validation de tokens. Si oauth_validator_libraries est vide (le défaut), les connexions OAuth sont refusées.

# pg_hba.conf — exemple INCOMPLET (aperçu uniquement)
# Nécessite : un IdP configuré, une bibliothèque validator installée,
# et oauth_validator_libraries renseigné dans postgresql.conf
hostssl  all  all  10.0.1.0/24  oauth issuer="https://idp.example.com" scope="openid postgres"

C’est un sujet à part entière qui dépasse le cadre de ce guide de durcissement. La mise en œuvre nécessite un Identity Provider opérationnel, un module validator compilé, et une configuration précise côté client (libpq + oauth_issuer).

Row-Level Security (cas avancé)

RLS : contrôle d'accès au niveau des lignes

Row-Level Security (RLS) permet de restreindre l’accès à certaines lignes d’une table selon le rôle connecté. Par exemple, un utilisateur ne voit que les commandes de ses propres clients.

-- Activer RLS sur une table
ALTER TABLE app.commandes ENABLE ROW LEVEL SECURITY;

-- Politique : un utilisateur ne voit que ses propres commandes
CREATE POLICY own_orders ON app.commandes
    FOR ALL TO app_writer
    USING (client_id = current_setting('app.current_client')::int);

RLS est puissant mais complexe à maintenir. Utilisez-le quand le moindre privilège au niveau table/schéma ne suffit pas (applications multi-tenant, données sensibles compartimentées).

Checklist de durcissement

Vérification	Commande	Attendu
Pas de trust dans pg_hba.conf	grep trust /etc/postgresql/18/main/pg_hba.conf	Aucune ligne
Pas de md5 (ou migration en cours)	grep md5 pg_hba.conf	Aucune ligne, ou plan de migration
SSL activé	SHOW ssl;	on
hostssl pour les connexions réseau	SELECT * FROM pg_hba_file_rules WHERE type = 'host';	Aucune ligne host pour les réseaux non-local
Mots de passe en SCRAM	SELECT rolname, LEFT(rolpassword, 5) FROM pg_authid;	Tous SCRAM
Superuser pas en TCP	pg_hba_file_rules	postgres en local/peer uniquement
listen_addresses restreint	SHOW listen_addresses;	Pas * ni 0.0.0.0
log_connections activé	SHOW log_connections;	all (ou on minimum)
log_statement DDL minimum	SHOW log_statement;	ddl ou all
Rôles séparés (pas de superuser applicatif)	\du	Owner/writer/reader distincts
DEFAULT PRIVILEGES configurés	\ddp	Droits auto pour writer/reader

Glissez pour voir

Dépannage

Symptôme	Cause probable	Solution
FATAL: no pg_hba.conf entry for host...	Pas de ligne dans pg_hba.conf pour cette IP/user/db	Ajouter une ligne, vérifier l’ordre
FATAL: no pg_hba.conf entry ... no encryption	hostssl mais client sans TLS	Configurer sslmode=require côté client
FATAL: password authentication failed	Mot de passe incorrect ou rôle inexistant	Vérifier avec \du, réinitialiser avec ALTER ROLE ... PASSWORD
root certificate file does not exist	sslmode=verify-ca mais pas de root.crt	Copier le certificat CA dans ~/.postgresql/root.crt
Connexion SSL mais cipher faible	ssl_ciphers trop permissif	Restreindre ssl_ciphers et ssl_tls13_ciphers
permission denied for table ...	Le rôle n’a pas les droits	GRANT ... TO role ou vérifier le groupe
permission denied for schema ...	Pas de GRANT USAGE ON SCHEMA	GRANT USAGE ON SCHEMA app TO role;
WARNING: setting an MD5-encrypted password	password_encryption = md5	SET password_encryption = 'scram-sha-256'; et re-SET le mot de passe
DEFAULT PRIVILEGES ne s’appliquent pas	Table créée par un autre rôle	Utiliser SET ROLE app_owner; dans les migrations
pg_reload_conf() ne change rien	Erreur de syntaxe dans pg_hba.conf	Vérifier les logs + pg_hba_file_rules (colonne error)

Glissez pour voir

À retenir

• pg_hba.conf est la première ligne de défense — la première règle qui matche gagne. Placez les restrictions (reject) en haut.
• trust est interdit en production — même sur localhost. Utilisez peer pour les connexions locales, scram-sha-256 pour TCP.
• TLS chiffre le transport. Activez ssl = on et utilisez hostssl dans pg_hba.conf pour forcer le chiffrement.
• sslmode=prefer (le défaut) ne protège pas — un attaquant peut forcer un downgrade. Utilisez require au minimum, verify-full en production.
• MD5 est déprécié en PostgreSQL 18 — scram-sha-256 est le défaut. Migrez vos mots de passe existants.
• Le pattern owner / writer / reader sépare les responsabilités : DDL pour les migrations, DML pour l’application, SELECT pour le monitoring.
• DEFAULT PRIVILEGES automatise l’attribution des droits sur les futurs objets — mais uniquement pour les objets créés par le rôle spécifié.
• listen_addresses = localhost + firewall + PgBouncer : trois couches de restriction réseau.
• Le logging (log_connections, log_statement = ddl, log_line_prefix avec user/db/IP) est votre trace de preuve pour l’audit.

Prochaines étapes

Réplication PostgreSQL Réplication physique, streaming, failover — haute disponibilité et lecture distribuée

Configuration PostgreSQL shared_buffers, work_mem, checkpoint — les réglages qui impactent les performances

Supervision et maintenance pg_stat_statements, VACUUM, EXPLAIN ANALYZE — observer avant de corriger

Sauvegarde et restauration pg_dump, pg_basebackup, PITR — la sécurité passe aussi par la capacité à restaurer

×

📖 Voir la documentation →