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. Chacun de ces trois réglages suffit seul à donner un accès complet aux données, sans que les deux autres n'y changent rien.

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).

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 ?

Le durcissement d'une base n'est presque jamais un chantier spontané : il répond à un changement d'exposition ou à une exigence externe. Les situations ci-dessous couvrent les déclencheurs les plus fréquents. Si vous vous reconnaissez dans plusieurs d'entre eux, suivez le guide dans l'ordre : les couches se renforcent mutuellement, et activer TLS sur un pg_hba.conf permissif ne protège de rien.

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…

La sécurité d'une base ne se limite pas au contrôle d'accès : le dimensionnement, la supervision et la capacité à restaurer en font partie. Ces sujets sont traités dans des pages dédiées pour garder ce guide centré sur le durcissement. La sauvegarde mérite une attention particulière, une base parfaitement verrouillée mais irrécupérable reste une perte de données.

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

Aucune requête n'est exécutée avant que pg_hba.conf n'ait donné son accord : ce fichier décide, pour chaque tentative de connexion, si elle est acceptée et comment l'identité du demandeur est vérifiée. Il se situe donc avant les privilèges SQL, qui n'entrent en jeu qu'une fois la session ouverte. Une erreur ici annule tout le travail fait sur les rôles, d'où l'intérêt de commencer par cette couche.

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`

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

Le champ METHOD détermine la preuve d'identité exigée du client. Toutes ne sont pas disponibles partout : peer interroge le noyau pour connaître l'utilisateur système à l'autre bout de la socket, il ne fonctionne donc que sur les lignes local. À l'inverse, scram-sha-256 et cert s'utilisent sur des connexions TCP, avec un niveau de garantie très différent.

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	Sans objet	Bloquer explicitement un rôle/réseau

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

Un fichier restrictif se construit en énumérant les accès légitimes, puis en fermant explicitement le reste. 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

Le champ TYPE ne se contente pas de distinguer socket locale et TCP : il conditionne aussi l'exigence de chiffrement. Une ligne host accepte indifféremment une connexion chiffrée et une connexion en clair, c'est donc le client qui décide, ce qui revient à ne rien imposer. hostssl inverse la responsabilité en refusant toute session non chiffrée côté serveur.

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

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.

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

Le contrôle d'accès dit qui entre, le chiffrement protège ce qui circule une fois la porte franchie. PostgreSQL implémente TLS nativement dans le protocole client-serveur : pas besoin de tunnel externe, il suffit d'un certificat et d'un paramètre. Cette section active ssl, vérifie qu'une session est réellement chiffrée, puis détaille le paramètre sslmode côté client, celui qui décide au final du niveau de protection.

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

Commencez par constater l'état réel du serveur plutôt que de lire le fichier de configuration, qui peut être surchargé par postgresql.auto.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

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.

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)

Rôles et moindre privilège

Une fois la connexion établie et chiffrée, la question devient : que peut faire ce compte une fois à l'intérieur ? PostgreSQL ne connaît qu'un seul type d'objet pour cela, le rôle, qui joue tour à tour le rôle d'utilisateur et de groupe selon qu'il porte ou non l'attribut LOGIN. Le moindre privilège consiste à distribuer les droits à des rôles-groupes, puis à y rattacher les comptes de connexion, ce qui permet de retirer un accès sans réécrire les GRANT.

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

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;

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;

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;

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;

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.

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.

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

Un service qui n'écoute pas sur une interface est inattaquable depuis cette interface, quelle que soit la qualité de son authentification. Réduire la surface exposée vient donc compléter pg_hba.conf, pas le remplacer. Trois leviers se combinent : le paramètre listen_addresses côté PostgreSQL, le pare-feu du système, et un pooler de connexions placé en frontal.

listen_addresses : ne pas écouter sur 0.0.0.0 sans raison

Ce paramètre définit les interfaces réseau sur lesquelles le processus se met en écoute. Vérifiez sa valeur courante en même temps que le port et les limites de connexions, qui conditionnent aussi la résistance du serveur à une saturation :

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

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 :

# 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

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

# 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

Les trois couches précédentes empêchent des actions ; le journal, lui, permet de savoir ce qui s'est réellement passé. Sans traces, un incident reste indémontrable et un accès légitime devient indistinguable d'un accès frauduleux. Le réglage consiste à trouver l'équilibre entre une trace exploitable et un volume de logs tenable : tracer toutes les requêtes sature le disque en quelques heures sur une base active.

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)

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

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

Chaque ligne de journal est préfixée par les informations décrites dans ce paramètre. Un préfixe pauvre rend le journal inexploitable en corrélation : sans utilisateur ni base, impossible de rattacher une erreur à un compte. Le token %q mérite une explication, il indique où couper le préfixe pour les messages émis hors session, comme ceux du processus de démarrage.

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)	Sans objet

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.

OAuth (cas avancé)

Gérer des mots de passe dans PostgreSQL devient vite ingérable dès qu'une organisation dispose déjà d'un annuaire central. PostgreSQL 18 ouvre une voie de sortie en déléguant l'authentification à un fournisseur d'identité externe. La mise en œuvre reste exigeante et n'est pas un prérequis du durcissement décrit ici, l'encadré ci-dessous en donne les grandes lignes et les conditions à réunir.

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é)

Les privilèges vus jusqu'ici s'arrêtent à la table : un rôle qui peut lire app.commandes lit toutes les lignes de la table. Quand plusieurs clients cohabitent dans les mêmes tables, ce grain est insuffisant et il faut descendre au niveau de la ligne. C'est le domaine du Row-Level Security, présenté ici dans ses principes.

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

Cette liste sert de contrôle final : chaque ligne se vérifie par une commande dont le résultat attendu est explicite, sans interprétation. Passez-la sur la base réelle et non sur les fichiers de configuration, un ALTER SYSTEM ou une inclusion peut avoir modifié la valeur effective. Conservez les sorties, elles constituent une preuve utilisable en audit de conformité.

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

Dépannage

Durcir une base produit mécaniquement des refus, et la difficulté consiste à distinguer un refus voulu d'une erreur de configuration. Les messages de PostgreSQL sont précis : ils indiquent la couche qui a bloqué, pg_hba.conf pour un refus de connexion, les privilèges pour un refus d'action. Repérez d'abord le mot-clé du message dans la colonne de gauche, il désigne directement le fichier ou la commande à corriger.

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`)

À 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