PostgreSQL : rôles et droits

PostgreSQL utilise la notion de rôle : un rôle peut être un utilisateur (avec LOGIN) ou un groupe. Les exemples créent un rôle pour l’application qui utilise la base boutique.

Créer des rôles

CREATE ROLE app LOGIN PASSWORD 'mot_de_passe_solide';
CREATE ROLE lecture LOGIN PASSWORD 'autre_mot_de_passe';
CREATE ROLE migrations LOGIN PASSWORD 'mot_de_passe_migrations';

Pour ne pas écrire le mot de passe dans la commande (et l’historique), le saisir de façon interactive :

CREATE ROLE app LOGIN;
\password app

Équivalent en ligne de commande : sudo -u postgres createuser --pwprompt app.

Propriétaire et connexion à la base

CREATE DATABASE boutique OWNER migrations;
REVOKE CONNECT ON DATABASE boutique FROM PUBLIC;       -- par défaut, tout rôle peut se connecter
GRANT CONNECT ON DATABASE boutique TO app, lecture, migrations;

Accorder des droits sur les tables

Depuis PostgreSQL 15, le schéma public n’accorde plus CREATE à tous : les droits sont à donner explicitement. À exécuter connecté à la base (\c boutique) :

\c boutique
GRANT USAGE ON SCHEMA public TO app, lecture;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lecture;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
GRANT ALL ON SCHEMA public TO migrations;

Ces commandes s’appliquent aux tables existantes. Pour celles créées plus tard :

ALTER DEFAULT PRIVILEGES FOR ROLE migrations IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE migrations IN SCHEMA public
  GRANT SELECT ON TABLES TO lecture;

Appliquer le principe du moindre privilège : l’application n’a besoin que de SELECT, INSERT, UPDATE, DELETE. Un rôle distinct (migrations) possède les tables et fait évoluer le schéma.

Rôles de groupe

CREATE ROLE lecteurs NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lecteurs;
GRANT lecteurs TO lecture;               -- lecture hérite des droits du groupe

Consulter

\du                                      -- rôles
\dp clients                              -- droits sur une table
\l                                       -- bases et leurs propriétaires
\ddp                                     -- droits par défaut
SELECT rolname, rolcanlogin, rolsuper FROM pg_roles ORDER BY rolname;
SELECT has_table_privilege('app', 'clients', 'SELECT');

Retirer, modifier, supprimer

REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app;
ALTER ROLE app PASSWORD 'nouveau_mot_de_passe';
ALTER ROLE app VALID UNTIL '2027-01-01';
ALTER ROLE app CONNECTION LIMIT 20;
ALTER ROLE app RENAME TO appli;
ALTER ROLE appli RENAME TO app;

-- Supprimer un rôle : d'abord retirer ses droits et ses objets
REASSIGN OWNED BY ancien TO migrations;
DROP OWNED BY ancien;
DROP ROLE IF EXISTS ancien;

Authentification : `pg_hba.conf`

Le fichier /etc/postgresql/18/main/pg_hba.conf décide qui peut se connecter, d’où, et comment. Format : type base rôle adresse méthode.

# Connexions locales par socket : compte système = rôle
local   all         postgres                    peer
# Connexions locales par mot de passe
host    boutique    app         127.0.0.1/32    scram-sha-256
host    boutique    app         ::1/128         scram-sha-256
# Un poste précis du réseau
host    boutique    app         192.168.1.20/32 scram-sha-256

Méthode	Usage
`peer`	Connexion locale par socket, le nom du compte système doit correspondre au rôle
`scram-sha-256`	Mot de passe, méthode recommandée
`md5`	Mot de passe, ancienne méthode, dépréciée
`reject`	Refuse

Les lignes sont lues dans l’ordre : la première qui correspond s’applique. Après modification :

sudo systemctl reload postgresql

ou, dans psql : SELECT pg_reload_conf();

Autoriser une connexion distante

Modifier /etc/postgresql/18/main/postgresql.conf :
```
listen_addresses = 'localhost,192.168.1.10'
```
Ajouter une ligne dans pg_hba.conf limitée au poste client (voir plus haut).
Redémarrer : sudo systemctl restart postgresql.

Ouvrir le port uniquement pour le client :

sudo ufw allow from 192.168.1.20 to any port 5432 proto tcp

Dépannage

Symptôme	Piste
`permission denied for table clients`	Droit `SELECT` ou autre manquant : `GRANT …` (et `GRANT USAGE ON SCHEMA public`)
`permission denied for schema public`	Depuis PostgreSQL 15, accorder `USAGE` (ou `CREATE`) sur le schéma
`no pg_hba.conf entry for host`	Aucune ligne ne correspond au client : ajouter une ligne puis recharger
`FATAL: password authentication failed`	Mauvais mot de passe, ou méthode `peer` appliquée au lieu de `scram-sha-256`
`role "x" cannot be dropped because some objects depend on it`	`REASSIGN OWNED BY x TO …; DROP OWNED BY x;` puis `DROP ROLE x;`

Pour aller plus loin

Manuel : Database Roles et pg_hba.conf