38. PostgreSQL : Comptes et privilèges

Une instance en langage PostgreSQL garde le système de comptes, de privilèges et de rôles de MIRAJ. Les clients s'y authentifient comme sur un serveur PostgreSQL (SCRAM-SHA-256, TLS), et le catalogue pg_roles montre les comptes ; mais les instructions qui gèrent les comptes et les droits sont celles de MIRAJ, écrites avec la lexicographie de PostgreSQL (identifiants entre guillemets doubles, chaînes entre apostrophes). Les options de rôle usuelles de PostgreSQL (CREATE USER … WITH PASSWORD '…', CREATE ROLE … LOGIN PASSWORD '…', ALTER ROLE …) sont reconnues et traduites dans ce système (38.2). Un lecteur venu de PostgreSQL retiendra d'emblée trois différences :

• un compte est une paire nom + hôte (app@'%', compta@localhost), créée par CREATE USER … IDENTIFIED BY '…' ; la forme de PostgreSQL CREATE USER app WITH PASSWORD '…' crée app@'%' ;
• un privilège s'accorde sur un schéma entier par schéma.* (présent et à venir) — il n'y a ni GRANT … ON SCHEMA, ni ON ALL TABLES IN SCHEMA, ni ALTER DEFAULT PRIVILEGES ;
• il n'y a pas de propriétaire d'objet : ce sont les privilèges, et eux seuls, qui disent ce qu'un compte peut faire.

Ce chapitre couvre l'authentification et TLS, les comptes, les privilèges à leurs cinq niveaux, les rôles, l'audit des droits et les erreurs de privilège. Les exemples portent sur le schéma ventes du chapitre 31. Prise en main.

38.1 Authentification#

Mécanismes#

Le mécanisme proposé au client dépend du compte :

SHOW password_encryption rend scram-sha-256. Le serveur ne garde qu'un vérificateur du mot de passe, jamais le mot de passe : il n'existe aucun moyen de retrouver un mot de passe oublié, seulement d'en fixer un nouveau. Pour donner un vérificateur SCRAM à un compte qui n'a qu'une empreinte, redéfinissez son mot de passe.

Un client qui exige la liaison au canal TLS (channel_binding=require) est refusé par son propre pilote, le serveur n'annonçant que SCRAM-SHA-256 ; la valeur par défaut (channel_binding=prefer) fonctionne.

Échecs de connexion#

Les refus arrivent en FATAL, avec un SQLSTATE de PostgreSQL :

Rien ne distingue un compte inconnu d'un mot de passe faux : les deux reçoivent le même échange SCRAM et le même refus. Les échecs répétés d'une même adresse sont comptés et ralentis, puis l'adresse est bloquée (15.5).

Base de connexion#

Le paramètre dbname du client choisit le schéma courant de la session (37.8) :

• le nom d'un schéma de MIRAJ (dbname=ventes) en fait le schéma courant ;
• miraj, postgres ou le nom de l'utilisateur (valeurs par défaut des clients) sont ignorés : la session s'ouvre sans schéma courant (current_schema rend NULL) ;
• un nom inconnu ouvre la connexion sans schéma courant, avec un avis :

NOTICE:  database "nexistepas" does not exist; connected without a current database   (3D000)

Chiffrement TLS#

Le serveur chiffre les connexions quand il est démarré avec un certificat (--tls-cert et --tls-key, voir 15.10.2). Dès qu'un certificat est donné, TLS est exigé (sauf --allow-plain-with-tls), ce que le démarrage annonce :

MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, langage PostgreSQL, dossier "D:\donnees", TLS exigé, 12 fils.

Le client règle le chiffrement par son paramètre sslmode, comme pour un serveur PostgreSQL :

import psycopg2
conn = psycopg2.connect(host="serveur.exemple", port=7007, user="app", password="…",
                        dbname="ventes", sslmode="verify-full", sslrootcert="ca.pem")
print(conn.info.ssl_in_use, conn.info.ssl_attribute("protocol"))    # True TLSv1.3

psql "host=serveur.exemple port=7007 user=app dbname=ventes sslmode=verify-full sslrootcert=ca.pem"

L'état du chiffrement se lit du côté du client (conn.info.ssl_in_use, \conninfo de psql) : la vue pg_stat_ssl n'existe pas.

38.2 Comptes#

Identité d'un compte : nom et hôte#

Un compte est identifié par la paire nom@hôte. Le même nom peut désigner des comptes différents, avec des mots de passe et des droits différents, selon la machine d'où vient la connexion :

Le nom et l'hôte s'écrivent nus, entre guillemets doubles ou entre apostrophes (app, "app", 'app' désignent le même compte). Quand plusieurs comptes de même nom conviennent, le plus précis l'emporte : un hôte exact ou localhost passe avant un motif, et % vient en dernier.

Le client PostgreSQL ne donne que le nom ; le serveur choisit le compte d'après l'adresse d'où vient la connexion. current_user, session_user et user rendent ce nom, sans l'hôte.

Créer un compte#

CREATE USER [IF NOT EXISTS] compte [IDENTIFIED BY 'mot de passe'] [, compte …];

CREATE USER app IDENTIFIED BY 'Appli-2026';
CREATE USER lecteur@'%' IDENTIFIED BY 'Lecture-2026',
            compta@localhost IDENTIFIED BY 'Compta-2026';
CREATE USER IF NOT EXISTS app IDENTIFIED BY 'Appli-2026';

CREATE USER
CREATE USER
WARNING:  Authorization ID 'app'@'%' already exists.
CREATE USER

• IDENTIFIED BY 'texte' fixe le mot de passe (converti aussitôt en vérificateurs) ; IDENTIFIED BY PASSWORD '*…' fixe une empreinte déjà calculée, telle que l'écrit SHOW CREATE USER (un tel compte n'a pas de vérificateur SCRAM, voir 38.1).
• Un compte tout juste créé n'a aucun droit sur les données : seulement USAGE, le droit de se connecter. Accordez-lui des privilèges par GRANT (38.3).
• Créer un compte qui existe : role "app" already exists (42710), ou un simple avertissement avec IF NOT EXISTS.

Comptes distants sans mot de passe refusés. Tant que le paramètre require_password vaut 1 (défaut), un compte joignable d'une autre machine doit avoir un mot de passe :

CREATE USER sansmdp;

ERROR:  Account 'sansmdp'@'%' can connect from other hosts and has no password: give it one
        (IDENTIFIED BY) or set require_password = OFF   (XX000)

Un compte limité à la boucle locale (CREATE USER outil@localhost) peut ne pas en avoir.

Options de rôle de PostgreSQL#

Les formes de PostgreSQL sont acceptées pour un compte nommé sans hôte (nom@'%') :

CREATE {USER | ROLE} [IF NOT EXISTS] nom [WITH] option [option …];
ALTER  {USER | ROLE} nom [WITH] option [option …];

CREATE USER app WITH PASSWORD 'Appli-2026';
CREATE ROLE outil WITH LOGIN ENCRYPTED PASSWORD 'Outil-2026' NOSUPERUSER IN ROLE role_lecture;
CREATE ROLE role_lecture NOLOGIN;              -- un rôle de MIRAJ (8.4)
ALTER USER app WITH PASSWORD 'Appli-2027';
ALTER ROLE app NOLOGIN;                        -- ACCOUNT LOCK

Chaque option est honorée par le mécanisme de MIRAJ qui lui correspond, ou refusée (0A000, le message nomme l'option) : aucune n'est ignorée en silence.

L'instruction est appliquée d'un bloc : si l'un des GRANT qu'elle entraîne échoue (rôle de IN ROLE inconnu, privilège que le compte de la session ne peut pas accorder), le compte n'est pas créé. Les mêmes privilèges qu'avec les formes de MIRAJ sont exigés : CREATE USER pour créer ou modifier un compte, et, pour SUPERUSER, CREATEDB ou CREATEROLE, le droit d'accorder les privilèges correspondants (42501 sinon). Un rôle reçu par IN ROLE s'active comme tout rôle accordé (38.4). require_password s'applique : CREATE USER app sans mot de passe est refusé.

Les formes de MIRAJ (IDENTIFIED BY, compte@hôte, plusieurs comptes, ACCOUNT LOCK) restent celles à employer dès qu'un hôte est précisé.

Modifier, verrouiller, supprimer#

ALTER USER compte IDENTIFIED BY 'nouveau mot de passe';
ALTER USER compte ACCOUNT {LOCK | UNLOCK};
ALTER USER compte DEFAULT ROLE rôle;
DROP USER [IF EXISTS] compte [, compte …];

ALTER USER app IDENTIFIED BY 'Appli-2027';
ALTER USER app ACCOUNT LOCK;        -- connexion refusée (28000), droits conservés
ALTER USER app ACCOUNT UNLOCK;
DROP USER lecteur, compta@localhost;

ACCOUNT LOCK suspend un compte sans le supprimer ni toucher à son mot de passe ou à ses droits. DROP USER retire le compte et tous ses privilèges ; une session déjà ouverte sous ce compte reste connectée, mais n'a plus aucun droit (42501 à l'instruction suivante). Comme dans PostgreSQL, DROP ROLE supprime aussi bien un compte (rôle doté de LOGIN) qu'un rôle, et DROP USER un rôle.

Changer un mot de passe#

SET PASSWORD = 'nouveau mot de passe';                -- le compte de la session
SET PASSWORD FOR app = 'nouveau mot de passe';        -- un autre compte
ALTER USER lecteur IDENTIFIED BY 'nouveau mot de passe';

Un compte peut toujours changer son propre mot de passe, par SET PASSWORD ou par ALTER USER sur lui-même. Gérer les autres comptes (CREATE USER, ALTER USER, DROP USER, SET PASSWORD FOR) demande le privilège global CREATE USER :

ERROR:  Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation   (42501)

Un compte qui détient le privilège SYSTEM_USER — c'est le cas de root — ne peut être modifié, supprimé ou pourvu de droits que par un compte qui le détient aussi : un administrateur à qui l'on a confié CREATE USER ne peut pas prendre la main sur root.

Voir les comptes#

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

   rolname    | rolsuper | rolcanlogin
--------------+----------+-------------
 app          | f        | t
 compta       | f        | t
 lecteur      | f        | t
 role_lecture | f        | f
 root         | t        | t
(5 lignes)

pg_roles, pg_user et pg_authid montrent un rôle par nom de compte (deux comptes app@'%' et app@localhost n'y font qu'une ligne) ; root y est le superutilisateur, d'OID 10. Le mot de passe y est toujours masqué (******** dans pg_roles et pg_user, NULL dans pg_authid). C'est ce que lisent \du de psql et les outils graphiques.

Les attributs y suivent les privilèges globaux du compte, ceux que posent les options de rôle de PostgreSQL (38.2) :

pg_roles et pg_user sont lisibles de tout compte, comme dans PostgreSQL : ils ne portent aucun secret. pg_authid est réservée à qui administre les comptes (privilège CREATE USER sur *.*) ; un autre compte reçoit 42501 (permission denied for table pg_authid). pg_shadow (les comptes qui se connectent, mêmes colonnes que pg_user) est réservée de la même façon ; sa colonne passwd, comme pg_authid.rolpassword, est toujours NULL : ni mot de passe ni vérificateur ne sort du coffre des comptes.

SHOW CREATE USER app;

CREATE USER "app"@"%" IDENTIFIED BY PASSWORD '*D7B9…7062'

SHOW CREATE USER compte rend l'instruction qui recrée le compte, avec l'empreinte de son mot de passe et son verrou (… ACCOUNT LOCK) ; le compte doit être nommé (SHOW CREATE USER seul est une erreur de syntaxe). Le compte y est écrit en identifiants, entre guillemets doubles, comme dans SHOW GRANTS (les apostrophes sont réservées aux chaînes) ; un rôle donne CREATE ROLE "nom"@"%". Le texte se rejoue tel quel.

38.3 Privilèges : GRANT et REVOKE#

Les niveaux#

Un privilège détenu à un niveau vaut pour tout ce que ce niveau englobe : SELECT sur ventes.* couvre les tables que l'on créera demain dans ventes, sans nouvel octroi. C'est ce qui remplace GRANT … ON ALL TABLES IN SCHEMA et ALTER DEFAULT PRIVILEGES de PostgreSQL.

Le nom du schéma peut être un motif, écrit entre guillemets : GRANT SELECT ON "vent%".* TO ….

Syntaxe#

GRANT privilège [(colonnes)] [, …] ON [TABLE] cible TO compte [, compte …] [WITH GRANT OPTION];
REVOKE [IF EXISTS] privilège [(colonnes)] [, …] ON [TABLE] cible FROM compte [, compte …];
REVOKE ALL [PRIVILEGES], GRANT OPTION FROM compte [, compte …];

Privilèges courants :

• WITH GRANT OPTION permet au bénéficiaire de retransmettre ces privilèges.
• Pour accorder ou retirer un privilège, il faut le détenir soi-même au niveau visé avec l'option GRANT.
• GRANT ne crée pas de compte : le bénéficiaire doit exister (42000, You are not allowed to create a user with GRANT).
• Les droits s'appliquent dès l'instruction qui les change, aux sessions déjà ouvertes aussi. FLUSH PRIVILEGES est accepté sans effet.
• REVOKE d'un privilège qui n'a pas été accordé à ce niveau est une erreur (42000, There is no such grant defined for user 'lecteur' on host '%' on table 'article'), ramenée à un avertissement par IF EXISTS. Un privilège accordé sur ventes.* se retire sur ventes.*, pas table par table.

Exemples#

Compte en lecture seule sur un schéma :

CREATE USER lecteur IDENTIFIED BY 'Lecture-2026';
GRANT SELECT ON ventes.* TO lecteur;

Compte applicatif qui lit, écrit et appelle les routines, sans toucher au schéma :

CREATE USER app IDENTIFIED BY 'Appli-2026';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON ventes.* TO app;

Compte limité à quelques tables :

GRANT SELECT ON ventes.client TO compta@localhost;
GRANT SELECT, UPDATE ON TABLE ventes.commande TO compta@localhost;

Administrateur d'un schéma, qui peut déléguer :

GRANT ALL ON ventes.* TO app WITH GRANT OPTION;

Retirer :

REVOKE INSERT, UPDATE, DELETE ON ventes.* FROM app;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM app;        -- tout, à tous les niveaux

Ce que voit le compte lecteur :

SELECT count(*) FROM client;                 -- 3
INSERT INTO client (nom, email) VALUES ('x', '[email protected]');
CREATE TABLE t (a int);
SELECT * FROM archives.client;               -- schéma sur lequel lecteur n'a aucun droit

ERROR:  permission denied for table client   (42501)
ERROR:  permission denied for table t        (42501)
ERROR:  permission denied for table client   (42501)

Privilèges par colonne#

-- Le service client lit l'identité des clients et corrige leur ville, jamais leur adresse
CREATE USER support@localhost IDENTIFIED BY 'Support-2026';
GRANT SELECT (id, nom, ville), UPDATE (ville) ON ventes.client TO support@localhost;

Chaque colonne employée par une instruction est contrôlée : liste du SELECT, WHERE, ORDER BY, jointures, valeurs d'un UPDATE, clause RETURNING. SELECT * demande toutes les colonnes ; count(*) n'en demande aucune.

-- session du compte support
SELECT id, nom, ville FROM client ORDER BY id;          -- 3 lignes
SELECT count(*) FROM client;                            -- 3
UPDATE client SET ville = 'Lyon' WHERE id = 3;          -- UPDATE 1
SELECT * FROM client;
SELECT nom FROM client WHERE email LIKE '%@example.com';
UPDATE client SET nom = 'X' WHERE id = 1;
UPDATE client SET ville = 'Lyon' WHERE id = 3 RETURNING email;

ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)
ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)
ERROR:  UPDATE command denied to user 'support'@'localhost' for column 'nom' in table 'client'     (42501)
ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)

Une vue est une autre façon de n'exposer que certaines colonnes ou certaines lignes : le compte reçoit SELECT sur la vue, pas sur la table.

CREATE VIEW v_client_public AS SELECT id, nom, ville FROM client;
GRANT SELECT ON ventes.v_client_public TO support@localhost;

Séquences#

Une séquence se désigne comme une table : INSERT permet nextval et setval, SELECT permet currval et la lecture de la séquence.

GRANT SELECT, INSERT ON ventes.seq_facture TO app;

Sans ce droit, SELECT nextval('seq_facture') rend permission denied for table seq_facture (42501). Les formes GRANT USAGE ON SEQUENCE … de PostgreSQL ne sont pas reconnues. Une colonne serial ou d'identité ne demande aucun droit de plus que INSERT sur sa table ; lire la valeur attribuée par INSERT … RETURNING id demande en revanche SELECT.

Routines#

Appeler une fonction ou une procédure demande EXECUTE sur la routine, ou sur son schéma (toutes ses routines, présentes et à venir) :

GRANT EXECUTE ON outils.* TO support@localhost;                       -- tout le schéma
GRANT EXECUTE ON FUNCTION outils.nb_articles() TO compta@localhost;   -- une seule fonction
GRANT EXECUTE ON PROCEDURE outils.relancer(integer) TO compta@localhost;
GRANT EXECUTE ON ROUTINE outils.purger TO exploitation WITH GRANT OPTION;
REVOKE EXECUTE ON FUNCTION outils.nb_articles() FROM compta@localhost;

Sans lui, l'appel échoue avant toute exécution du corps :

ERROR:  permission denied for function nb_articles   (42501)
ERROR:  permission denied for procedure relancer   (42501)      -- CALL outils.relancer(1)

• La liste des types entre parenthèses est acceptée et ignorée : MIRAJ n'a qu'une routine par nom et par schéma (pas de surcharge). ROUTINE désigne la fonction de ce nom, sinon la procédure.
• La routine doit exister (42883, FUNCTION outils.absente does not exist) ; une seule routine par instruction (GRANT … ON FUNCTION a, b : 0A000).
• EXECUTE et ALTER ROUTINE (qui permet DROP FUNCTION / DROP PROCEDURE de cette routine) sont admis à ce niveau ; un autre privilège rend 42000 (Illegal GRANT/REVOKE command). Retransmettre le droit demande l'option GRANT sur la routine ou son schéma (42501).
• REVOKE d'un droit absent : 42000 (There is no such grant defined for user 'compta' on host 'localhost' on routine 'nb_articles').
• Supprimer la routine retire les droits accordés sur elle ; recréée, elle n'en porte plus.
• Un droit sur une routine rend son schéma visible (pg_namespace, \dn).
• SHOW GRANTS les montre (GRANT EXECUTE ON FUNCTION "outils"."nb_articles" TO "compta"@"localhost"), information_schema.routine_privileges et has_function_privilege les lisent (38.5).

Le corps d'une routine s'exécute avec les droits de l'appelant (SECURITY INVOKER, valeur par défaut comme dans PostgreSQL) ou avec ceux de son créateur (SECURITY DEFINER). Une fonction SECURITY DEFINER laisse donc un compte lire, par elle seule, une table sur laquelle il n'a aucun droit :

-- créées par root dans le schéma outils
CREATE FUNCTION outils.nb_articles() RETURNS integer LANGUAGE sql SECURITY DEFINER
  AS $$ SELECT count(*) FROM ventes.article $$;
CREATE FUNCTION outils.nb_articles_inv() RETURNS integer LANGUAGE sql
  AS $$ SELECT count(*) FROM ventes.article $$;

-- session du compte support, qui n'a aucun droit sur ventes.article
SELECT outils.nb_articles();          -- 3
SELECT outils.nb_articles_inv();
-- ERROR:  permission denied for table article   (42501)

Dans le corps d'une routine SECURITY DEFINER, current_user rend le créateur et session_user reste le compte de la session, comme dans PostgreSQL : une fonction SECURITY DEFINER créée par root qui rend current_user || '/' || session_user rend root/support au compte support.

Les déclencheurs s'exécutent toujours avec les droits de leur créateur. Voir 39. Routines et déclencheurs.

38.4 Rôles#

Un rôle regroupe des privilèges sous un nom, pour les accorder en une fois à plusieurs comptes. À la différence de PostgreSQL, un rôle et un compte sont deux choses : un rôle ne se connecte pas (rolcanlogin = f). CREATE ROLE r et CREATE ROLE r NOLOGIN créent un rôle ; CREATE ROLE r LOGIN PASSWORD '…' crée un compte (38.2).

Créer un rôle et l'accorder#

CREATE ROLE [IF NOT EXISTS] rôle;
DROP ROLE [IF EXISTS] rôle;
GRANT rôle TO compte [WITH ADMIN OPTION];
REVOKE rôle FROM compte;

CREATE ROLE role_lecture;
GRANT SELECT ON ventes.* TO role_lecture;
CREATE ROLE role_ecriture;
GRANT INSERT, UPDATE, DELETE ON ventes.* TO role_ecriture;

GRANT role_lecture  TO compta@localhost;
GRANT role_ecriture TO compta@localhost;

CREATE ROLE demande le privilège CREATE ROLE (ou CREATE USER), DROP ROLE le privilège DROP ROLE (ou CREATE USER). Accorder ou retirer un rôle demande ROLE_ADMIN (ou SUPER), ou l'option ADMIN sur ce rôle (WITH ADMIN OPTION). Supprimer un rôle le retire aussitôt de tous les comptes qui le portaient.

Activer un rôle#

Un rôle accordé n'est pas actif à la connexion, sauf s'il est un rôle par défaut du compte — autre différence avec PostgreSQL, où un membre hérite de ses rôles.

SET DEFAULT ROLE role_lecture TO compta@localhost;     -- actif à chaque connexion
SET DEFAULT ROLE ALL TO compta@localhost;              -- tous les rôles accordés
SET DEFAULT ROLE NONE TO compta@localhost;
ALTER USER compta@localhost DEFAULT ROLE role_lecture; -- même effet

Dans la session, SET ROLE choisit les rôles actifs parmi ceux accordés au compte :

SET ROLE ne prend qu'un rôle, comme dans PostgreSQL : SET ROLE a, b rend 42601 (SET role takes only one argument).

-- session de compta : role_lecture par défaut, role_ecriture accordé
SELECT count(*) FROM article;                        -- 3 (role_lecture)
INSERT INTO commande (client_id) VALUES (1);         -- 42501 : role_ecriture n'est pas actif
SET ROLE role_ecriture;
INSERT INTO commande (client_id) VALUES (1);         -- INSERT 0 1
SELECT count(*) FROM article;                        -- 42501 : role_lecture n'est plus actif
SET ROLE DEFAULT;
SELECT count(*) FROM article;                        -- 3

La forme paramètre est la même instruction : SET [SESSION | LOCAL] role {TO | =} … (SET role TO role_ecriture, SET role = 'none', SET role TO DEFAULT) et RESET role. SHOW role, current_setting('role') et pg_settings rendent le rôle choisi (none sans rôle actif, le rôle par défaut après SET ROLE DEFAULT).

SET ROLE est un réglage de la transaction, comme SET : fait dans un bloc BEGIN …, il est annulé par ROLLBACK (et par ROLLBACK TO SAVEPOINT s'il a été fait après le point de sauvegarde), gardé par COMMIT. Il ne valide donc pas la transaction ouverte. SET LOCAL ROLE … (ou set_config('role', …, true)) ne vaut que jusqu'à la fin de la transaction — c'est la forme qu'emploient les serveurs d'API qui prennent un rôle par requête ; hors transaction, il est sans effet, avec l'avertissement 25P01.

BEGIN;
SET LOCAL ROLE role_ecriture;
INSERT INTO commande (client_id) VALUES (2);         -- INSERT 0 1
COMMIT;                                              -- les rôles d'avant le BEGIN reviennent

Un rôle inconnu : role "…" does not exist (42704) ; un rôle qui existe mais n'est pas accordé au compte : 42501 ("role_ecriture"@"%" is not granted to "lecteur"@"%"). current_user et current_role rendent toujours le nom du compte ; les rôles actifs se lisent dans information_schema.enabled_roles.

Changer d'identité : SET SESSION AUTHORIZATION#

SET SESSION AUTHORIZATION compte;      -- ou 'compte'
SET SESSION AUTHORIZATION DEFAULT;     -- revenir au compte de la connexion
RESET SESSION AUTHORIZATION;           -- de même
SET LOCAL SESSION AUTHORIZATION compte; -- jusqu'à la fin de la transaction
SET session_authorization TO 'compte';  -- forme paramètre : la même instruction

La forme paramètre est la même instruction, avec les mêmes règles : SET [SESSION | LOCAL] session_authorization {TO | =} {compte | 'compte' | DEFAULT}, RESET session_authorization, set_config('session_authorization', 'compte', is_local).

SET SESSION AUTHORIZATION fait réellement agir la session sous un autre compte : session_user et current_user rendent son nom, ses privilèges remplacent ceux du compte de la connexion, et ses rôles par défaut deviennent les rôles actifs. C'est le moyen de vérifier les droits d'un compte sans connaître son mot de passe.

-- session ouverte par root
SET SESSION AUTHORIZATION lecteur;
SELECT session_user, current_user;                 -- lecteur | lecteur
INSERT INTO client (nom, email)
  VALUES ('essai', '[email protected]');           -- 42501 : lecteur ne peut que lire
RESET SESSION AUTHORIZATION;
SELECT session_user;                               -- root

BEGIN;
SET LOCAL SESSION AUTHORIZATION lecteur;
SELECT current_user;                               -- lecteur
COMMIT;
SELECT current_user;                               -- root

Le client est tenu au courant : à chaque changement d'identité (fin de transaction comprise), le serveur renvoie les paramètres session_authorization et is_superuser (ParameterStatus), que SHOW session_authorization, SHOW is_superuser et current_setting(…) rendent aussi. is_superuser vaut on quand le compte sous lequel la session agit a tous les privilèges sur *.*, rôles actifs compris (SET ROLE peut donc le changer).

À connaître : l'instruction est refusée (42501) dans une routine ou un déclencheur exécuté sous son définisseur ; SHOW PROCESSLIST et pg_stat_activity.usename continuent de montrer le compte de la connexion, comme dans PostgreSQL.

38.5 Consulter et auditer les droits#

SHOW GRANTS#

SHOW GRANTS;                                         -- le compte de la session, rôles actifs fondus
SHOW GRANTS FOR app;                                 -- un autre compte (privilège CREATE USER)
SHOW GRANTS FOR compta@localhost USING role_lecture; -- avec les droits de ce rôle

                             Grants for app@%
--------------------------------------------------------------------------
 GRANT USAGE ON *.* TO "app"@"%"
 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON "ventes".* TO "app"@"%"
 GRANT REFERENCES, TRIGGER ON "ventes"."client" TO "app"@"%"
 GRANT INSERT ON "ventes"."ligne_commande" TO "app"@"%" WITH GRANT OPTION
 GRANT SELECT, INSERT ON "ventes"."seq_facture" TO "app"@"%"
(5 lignes)

Les rôles accordés y figurent aussi (GRANT "role_lecture"@"%" TO "compta"@"localhost") ; avec USING rôle, les privilèges du rôle s'ajoutent à ceux du compte. Un compte inconnu rend 42000 (There is no such grant defined for user 'inconnu' on host '%').

Chaque ligne est une instruction qui se rejoue telle quelle : c'est ainsi que l'outil miraj-dump --users exporte comptes et droits (40. Catalogue et outils).

information_schema#

SELECT grantee, table_name, privilege_type, is_grantable
  FROM information_schema.table_privileges
 WHERE table_schema = 'ventes' ORDER BY 1, 2, 3;

       grantee        |   table_name   | privilege_type | is_grantable
----------------------+----------------+----------------+--------------
 'app'@'%'            | client         | REFERENCES     | NO
 'app'@'%'            | client         | TRIGGER        | NO
 'app'@'%'            | ligne_commande | INSERT         | YES
 'app'@'%'            | seq_facture    | INSERT         | NO
 'app'@'%'            | seq_facture    | SELECT         | NO
 'compta'@'localhost' | client         | SELECT         | NO
 'compta'@'localhost' | commande       | SELECT         | NO
 'compta'@'localhost' | commande       | UPDATE         | NO
(8 lignes)

Ces tables ne montrent que les privilèges accordés à ce niveau : un SELECT reçu sur ventes.* figure dans schema_privileges, pas dans table_privileges. Le bénéficiaire y est écrit 'nom'@'hôte'. Un compte ordinaire n'y voit que ce qui le concerne.

Ce qu'un compte voit du catalogue#

Le catalogue est filtré selon les droits : pg_namespace, pg_class, information_schema.tables ou \dt ne montrent à un compte que les schémas et les tables qu'il peut atteindre, sans message d'erreur pour le reste. Le compte support, qui n'a de droits que sur client et v_client_public, ne voit que ces deux objets dans ventes. information_schema.columns suit les privilèges colonne par colonne : avec des privilèges accordés sur certaines colonnes seulement, seules celles-là sont décrites, et privileges rend ceux que le compte détient sur chacune (select,update, par exemple).

Les fonctions has_table_privilege, has_column_privilege, has_any_column_privilege et has_schema_privilege rendent les privilèges réels, ceux que les instructions contrôlent :

SELECT has_table_privilege('client', 'SELECT'),                 -- t
       has_table_privilege('client', 'INSERT, UPDATE'),         -- t si l'un des deux est détenu
       has_column_privilege('client', 'nom', 'UPDATE'),
       has_schema_privilege('ventes', 'CREATE'),
       has_table_privilege('lecteur', 'ventes.client', 'DELETE'); -- un autre compte

has_function_privilege([rôle,] fonction, 'EXECUTE') rend lui aussi le droit réel, celui que l'appel contrôle (sur la routine, son schéma ou au niveau global) ; la fonction s'écrit par son nom, éventuellement qualifié et suivi de ses types ('outils.nb_articles()'), ou par son OID. Une routine que le compte ne voit pas est inconnue (42883) ; une fonction du système (now(), pg_catalog.lower(text)) est exécutable de tous.

has_database_privilege, has_sequence_privilege et has_type_privilege rendent toujours t : la base PostgreSQL est unique, et les séquences sont protégées par les privilèges de leur schéma (has_schema_privilege).

Les rôles, eux, se lisent comme dans PostgreSQL. pg_auth_members porte une ligne par rôle accordé (GRANT rôle TO compte) :

SELECT r.rolname AS role, m.rolname AS membre, a.admin_option, a.inherit_option
  FROM pg_auth_members a
  JOIN pg_roles r ON r.oid = a.roleid
  JOIN pg_roles m ON m.oid = a.member
 ORDER BY 1, 2;

pg_has_role([compte,] rôle, privilège) dit vrai ou faux ; le compte (celui de la session par défaut) et le rôle se donnent par leur nom ou leur OID :

Un superutilisateur a tous les rôles. Un rôle inconnu rend 42704, un privilège inconnu 22023.

38.6 Erreurs de privilège#

Deux points à connaître :

• quand un compte n'a aucun droit sur un schéma, une requête sur une de ses tables rend 42501 même si la table n'existe pas — jamais 42P01, qui révélerait son absence ;
• dans un schéma où le compte a des droits, une table inconnue rend bien 42P01 (relation "inexistante" does not exist).

SET search_path TO archives par un compte sans droit sur archives est accepté sans erreur, mais le schéma courant ne change pas ; USE archives rend 42501.

Un compte ordinaire ne voit dans SHOW PROCESSLIST et pg_stat_activity que ses propres sessions, et ne peut interrompre (KILL, pg_cancel_backend, pg_terminate_backend) que celles-là : pg_terminate_backend d'une autre session rend 42501 (permission denied to terminate process), KILL rend You are not owner of thread 1.

38.7 Le compte root et bonnes pratiques#

Le compte root@localhost est créé avec le dossier de données, doté de tous les privilèges et de l'option GRANT. Son mot de passe initial est tiré au sort et affiché une seule fois au premier démarrage du serveur, ou donné par --initial-root-password (15.5). Comme son hôte est localhost, root ne se connecte que depuis la machine du serveur.

SHOW GRANTS;       -- session de root

 GRANT ALL PRIVILEGES ON *.* TO "root"@"localhost" WITH GRANT OPTION
 GRANT ROLE_ADMIN,CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,BACKUP_ADMIN,ENCRYPTION_KEY_ADMIN ON *.* TO "root"@"localhost" WITH GRANT OPTION
(2 lignes)

Bonnes pratiques :

• changer le mot de passe initial de root dès la première connexion (ALTER USER root@localhost IDENTIFIED BY '…') ;
• créer un compte par usage (un par application, un par personne) plutôt que de partager root : on peut alors tracer, limiter et verrouiller un compte sans toucher aux autres ;
• donner le minimum : SELECT sur les seuls schémas consultés pour un compte de lecture, jamais de privilège global pour une application ;
• restreindre l'hôte quand l'application tourne sur une machine connue (app@'10.0.0.%') ;
• exiger TLS dès que le serveur écoute sur une adresse non locale (38.1).

38.8 Non pris en charge et écarts#

Formes de PostgreSQL qui ne sont pas reconnues, et ce qui les remplace :

Écarts de comportement :

• un compte est une paire nom + hôte ; pg_roles n'en montre que le nom (38.2) ;
• un rôle accordé n'est actif que s'il est un rôle par défaut ou après SET ROLE (38.4) ; pg_auth_members.inherit_option et pg_has_role(…, 'USAGE') le disent (38.5) ;
• pas de propriétaire d'objet : pg_tables.tableowner rend root pour toutes les tables ;
• has_table_privilege, has_schema_privilege, has_column_privilege et has_function_privilege disent la vérité, comme pg_has_role ; un objet que le compte ne voit pas est inconnu pour elles, et les privilèges d'un autre compte sont réservés à qui administre les comptes (38.5) ; has_database_privilege, has_sequence_privilege et has_type_privilege rendent toujours t ;
• les messages de refus sont ceux de MIRAJ pour une colonne ou une routine (… command denied to user …), de SQLSTATE 42501 comme les autres ;
• SHOW CREATE USER et SHOW GRANTS écrivent le compte entre guillemets doubles ("app"@"%") ; l'écriture entre apostrophes ('app'@'%') reste acceptée en entrée.

Voir aussi le chapitre 14, qui décrit le même système de comptes en langage MIRAJ.

Voir aussi#

• 31. Prise en main pour la première connexion.
• 37. Transactions et session pour le schéma courant et les paramètres de session.
• 39. Routines et déclencheurs pour SECURITY DEFINER.
• 15. Administration du serveur, pour le coffre des comptes, le mot de passe initial de root et le certificat TLS.