Commandes essentielles pour gérer les utilisateurs, les rôles et les permissions dans PostgreSQL

Les utilisateurs, rôles et permissions dans PostgreSQL déterminent qui peut se connecter, ce qu'ils peuvent lire et ce qu'ils peuvent modifier. Si ces permissions sont gérées de manière approximative, votre base de données devient difficile à auditer et facile à surexposer.

PostgreSQL utilise un seul concept, le rôle, à la fois pour les utilisateurs de connexion et les groupes. Un rôle avec LOGIN peut se connecter. Un rôle sans LOGIN est souvent utilisé comme un groupe qui porte des privilèges partagés.

Créer et modifier des rôles

Créez un rôle de connexion applicative avec un mot de passe :

CREATE ROLE app_user WITH LOGIN PASSWORD 'mot_de_passe_fort_ici';

Créez un rôle de groupe pour un accès en lecture partagé :

CREATE ROLE reporting_readonly NOLOGIN;

Les attributs courants des rôles incluent :

Attribut	Ce qu'il permet
LOGIN	Se connecter au serveur de base de données.
CREATEDB	Créer des bases de données.
CREATEROLE	Créer, modifier et supprimer de nombreux rôles, avec des limites importantes concernant les rôles superutilisateurs.
REPLICATION	Utiliser les connexions de réplication.
SUPERUSER	Contourner les vérifications normales de permissions. À utiliser uniquement pour des rôles d'administration strictement contrôlés.
INHERIT	Utiliser les privilèges des rôles dont il est membre. C'est le comportement par défaut.

Modifiez un mot de passe ou désactivez la connexion avec ALTER ROLE :

ALTER ROLE app_user WITH PASSWORD 'nouveau_mot_de_passe_fort';
ALTER ROLE ancien_employe NOLOGIN;

Avant de supprimer un rôle, gérez tout ce qu'il possède et tous les privilèges qui lui ont été accordés :

REASSIGN OWNED BY ancien_employe TO postgres;
DROP OWNED BY ancien_employe;
DROP ROLE ancien_employe;

REASSIGN OWNED transfère les objets possédés. DROP OWNED supprime les privilèges accordés au rôle et peut supprimer les objets encore possédés par celui-ci dans la base de données actuelle, donc examinez attentivement avant de l'exécuter.

Utiliser des rôles de groupe pour un accès plus propre

Accordez des permissions aux rôles de groupe, puis ajoutez des personnes ou des rôles applicatifs à ces groupes.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION permet au membre d'accorder et de révoquer cette appartenance au rôle pour d'autres. Cela ne fait pas du membre un superutilisateur et ne donne pas un pouvoir illimité sur la base de données.

GRANT reporting_readonly TO chef_equipe WITH ADMIN OPTION;

Ce modèle rend l'accès plus facile à auditer. Au lieu de vérifier 50 utilisateurs pour les octrois de tables, vous pouvez vérifier le rôle de groupe.

Accorder des permissions sur les objets

Utilisez GRANT pour autoriser des actions spécifiques sur des objets spécifiques.

GRANT SELECT ON TABLE production.commandes TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.commandes TO app_user;

Les privilèges courants sur les tables sont SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES et TRIGGER.

Les permissions sur les schémas sont distinctes des permissions sur les tables. Un rôle a souvent besoin de USAGE sur le schéma et de privilèges sur les tables à l'intérieur :

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

Pour les insertions qui utilisent des séquences, accordez également l'accès aux séquences :

GRANT USAGE, SELECT ON SEQUENCE app.commandes_id_seq TO app_user;

Pour les fonctions, accordez explicitement l'exécution :

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Révoquer des permissions

REVOKE supprime un octroi direct.

REVOKE INSERT ON TABLE production.commandes FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.commandes FROM reporting_readonly;

Si un utilisateur a toujours accès après une révocation, vérifiez l'appartenance au groupe. L'utilisateur peut hériter du même privilège via un autre rôle.

Définir des permissions pour les tables futures

GRANT ON ALL TABLES n'affecte que les tables existantes. Pour les tables créées ultérieurement, utilisez ALTER DEFAULT PRIVILEGES à partir du rôle qui créera ces tables.

ALTER DEFAULT PRIVILEGES FOR ROLE proprietaire_app IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

C'est l'une des étapes manquantes les plus courantes dans les configurations de permissions PostgreSQL. Un utilisateur peut lire les tables d'aujourd'hui mais se voit refuser l'accès à la table de demain car les privilèges par défaut n'ont jamais été configurés.

Auditer l'accès actuel

Dans psql, ces commandes sont des vérifications rapides :

\du
\du+ app_user
\dp app.commandes
\dn+

Les vues du catalogue sont meilleures pour des rapports reproductibles :

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Pour les octrois sur les tables :

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Habitudes de sécurité qui paient

Donnez aux applications des rôles dédiés au lieu d'utiliser un compte DBA humain. Accordez le plus petit ensemble de privilèges dont l'application a besoin. Évitez SUPERUSER et les octrois larges de ALL PRIVILEGES pour le travail quotidien.

Utilisez des rôles de groupe pour l'accès partagé et examinez régulièrement les appartenances. Lorsque quelqu'un change d'équipe, supprimer une appartenance à un groupe est plus sûr que de rechercher des octrois au niveau des tables.

L'étape pratique suivante consiste à choisir un schéma de production et à lister ses rôles, appartenances, octrois de tables et privilèges par défaut. Corrigez les lacunes là-bas avant d'étendre le même modèle au reste de vos bases de données.