PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.20 » Langage SQL » Définition des données » Droits

5.7. Droits

Quand un objet est créé, il se voit affecter un propriétaire. Le propriétaire est normalement le rôle qui a exécuté la requête de création. Pour la plupart des objets, l'état initial est que seul le propriétaire (et les superutilisateurs) peut faire quelque chose avec cet objet. Pour permettre aux autres rôles de l'utiliser, des droits doivent être donnés.

Il existe un certain nombre de droits différents : SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE et USAGE. Les droits applicables à un objet particulier varient selon le type d'objet (table, fonction...). Plus de détails sur la signification de ces droits sont donnés ci-dessous. La section et les chapitres suivants présentent l'utilisation de ces droits.

Le droit de modifier ou de détruire un objet est le privilège du seul propriétaire.

Un objet peut se voir affecter un nouveau propriétaire avec la commande ALTER correspondant à l'objet, par exemple

ALTER TABLE nom_table OWNER TO nouveau_propriétaire;
        

Les superutilisateurs peuvent toujours le faire. Les rôles ordinaires ne peuvent le faire que s'ils sont le propriétaire actuel de l'objet (ou un membre du rôle propriétaire) et un membre du nouveau rôle propriétaire.

La commande GRANT est utilisée pour accorder des privilèges. Par exemple, si joe est un rôle et comptes une table, le privilège d'actualiser la table comptes peut être accordé à joe avec :

GRANT UPDATE ON comptes TO joe;

Écrire ALL à la place d'un droit spécifique accorde tous les droits applicables à ce type d'objet.

Le nom de « rôle » spécial PUBLIC peut être utilisé pour donner un privilège à tous les rôles du système. De plus, les rôles de type « group » peuvent être configurés pour aider à la gestion des droits quand il y a beaucoup d'utilisateurs dans une base -- pour les détails, voir Chapitre 21.

Pour révoquer un privilège, on utilise la commande bien nommée REVOKE, comme dans l'exemple ci-dessous :

REVOKE ALL ON comptes FROM PUBLIC;

Les privilèges spéciaux du propriétaire de l'objet (c'est-à-dire le droit d'exécuter DROP, GRANT, REVOKE, etc.) appartiennent toujours implicitement au propriétaire. Ils ne peuvent être ni accordés ni révoqués. Mais le propriétaire de l'objet peut choisir de révoquer ses propres droits ordinaires pour, par exemple, mettre une table en lecture seule pour lui-même et pour les autres.

Habituellement, seul le propriétaire de l'objet (ou un superutilisateur) peut accorder ou révoquer les droits sur un objet. Néanmoins, il est possible de donner un privilège « avec possibilité de transmission » (« with grant option »), qui donne à celui qui le reçoit la permission de le donner à d'autres. Si cette option est ensuite révoquée, alors tous ceux qui ont reçu ce privilège par cet utilisateur (directement ou indirectement via la chaîne des dons) perdent ce privilège. Pour les détails, voir les pages de références GRANT et REVOKE.

Les privilèges disponibles sont :

SELECT

Autorise SELECT de n'importe quelle colonne, ou colonnes, désignée(s) d'une table, vue, vue matérialisée ou tout autre objet utilisable comme une table. Permet aussi l'utilisation de COPY TO. Ce privilège est aussi nécessaire pour référencer les valeurs actuelles d'une colonne dans UPDATE ou DELETE. Pour les séquences, ce privilège permet aussi d'utiliser la fonction currval. Pour les large objects, ce privilège permet de lire l'objet.

INSERT

Permet l'INSERT d'une nouvelle ligne dans une table, vue, etc. Peut être accordé sur des colonnes spécifiques, auquel cas seules ces colonnes pourront être affectées dans l'ordre INSERT (les autres commandes recevront alors les valeurs par défaut). Permet aussi d'utiliser COPY FROM.

UPDATE

Autorise l'UPDATE de n'importe quelle colonne, ou colonnes, désignée(s) d'une table, vue, etc. (En pratique, toute commande UPDATE non triviale requerra aussi le privilège SELECT puisqu'il faut se référer aux colonnes de la table pour déterminer quelles lignes mettre à jour et/ou calculer les nouvelles valeurs des colonnes.) SELECT ... FOR UPDATE et SELECT ... FOR SHARE requièrent aussi ce privilège sur au moins une colonne, en plus du privilège SELECT. Pour les séquences, ce privilège autorise l'usage des fonctions nextval et setval. Pour les large objects, ce privilège permet d'écrire dans l'objet ou de le tronquer.

DELETE

Permet le DELETE d'une ligne dans une table, vue, etc. (En pratique, toute commande DELETE non triviale requerra aussi le privilège SELECT, puisqu'il faut lire les colonnes de la table pour déterminer quelles lignes supprimer.)

TRUNCATE

Permet TRUNCATE sur une table.

REFERENCES

Permet la création de clés étrangères référençant une table, ou des colonnes spécifiques d'une table.

TRIGGER

Permet la création d'un trigger sur une table, vue, etc.

CREATE

Pour les bases de données, autorise la création de nouveaux schémas et publications dans la base.

Pour les schémas, autorise la création de nouveaux objets dans le schéma. Pour renommer un objet existant, vous devez posséder l'objet et posséder ce privilège pour le schéma de l'objet.

Pour les tablespaces, permet de créer des tables, index et fichiers temporaires dans le tablespace, et de créer des bases de données ayant ce tablespace comme tablespace par défaut. (Notez que révoquer ce privilège ne modifiera pas l'emplacement des objets.)

CONNECT

Permet au bénéficiaire de ce privilège de se connecter à la base de données. Ce privilège est vérifié au démarrage de la connexion (en plus de la vérification de toute restriction imposée par pg_hba.conf).

TEMPORARY

Permet aux tables temporaires d'être créées dans la base de données.

EXECUTE

Permet d'appeler une fonction ou procédure, y compris en utilisant des opérateurs implémentés par-dessus la fonction. C'est le seul type de privilège applicable aux fonctions et procédures.

USAGE

Pour les langages procéduraux, permet d'utiliser le langage pour la création de fonctions. C'est le seul type de privilège applicable aux langages procéduraux.

Pour les schémas, permet l'accès aux objets contenus dans le schéma (à supposer que les privilèges d'accès requis par les objets soient aussi respectés). Cela autorise essentiellement le bénéficiaire à « rechercher » des objets dans le schéma. Sans cette permission, il reste possible de voir les noms des objets, par exemple en consultant les catalogues système. Après révocation de ce privilège, les sessions existantes peuvent avoir des ordres ayant précédemment effectué cette recherche, donc ce n'est pas une manière totalement sûre d'interdire l'accès à un objet.

Pour les séquences, permet l'utilisation des fonctions currval et nextval.

Pour les types et domaines, permet l'utilisation du type ou domaine dans la création de tables, fonctions et autres objets. (Notez que ce privilège ne contrôle pas l'« utilisation » de ce type, comme les valeurs apparaissant dans les requêtes. Il se limite à interdire la création d'objets qui dépendent de ce type. Le but principal de ce privilège est de contrôler quels utilisateurs peuvent créer des dépendances envers un type, qui pourraient empêcher le propriétaire de modifier le type plus tard.)

Pour les foreign data wrappers, permet la création de nouveaux serveurs avec ce wrapper.

Pour les serveurs distants (foreign servers), permet la création de tables distantes utilisant le serveur. Les bénéficiaires du privilège peuvent créer, modifier, supprimer leurs propres correspondances d'utilisateurs (user mappings) associées à ce serveur.

Les privilèges requis par d'autres commandes sont listés sur la page de référence de la commande.

Par défaut, PostgreSQL accorde des privilèges sur certains types d'objets à PUBLIC dès la création des objets. Aucun privilège n'est accordé à PUBLIC par défaut sur les tables, colonnes de table, séquences, foreign data wrappers, serveurs distants, large objects, schémas et tablespaces. Pour les autres types d'objets, les privilèges par défaut accordés à PUBLIC sont les suivants : sur les bases de données : les privilèges CONNECT et TEMPORARY (création de table temporaire) ; sur les fonctions et procédures : le privilège EXECUTE  ; et sur les langages et types de données (y compris les domaines) : le privilège USAGE. Bien sûr, le propriétaire de l'objet peut révoquer, à l'aide de la commande REVOKE, les privilèges par défaut comme ceux expressément accordés. (Pour une sécurité maximum, ordonnez REVOKE dans la même transaction que celle qui crée l'objet ; il n'y a alors aucune fenêtre où un autre utilisateur peut utiliser l'objet.) Ces privilèges par défaut peuvent aussi être remplacés avec la commande ALTER DEFAULT PRIVILEGES.

Tableau 5.1 liste les abréviations à une lettre utilisées pour les types de privilèges dans les valeurs des ACL (listes de contrôle d'accès). Vous verrez ces lettres dans la sortie des commandes psql plus bas, ou en consultant les colonnes ACL des catalogues système.

Tableau 5.1. Abréviations des privilèges dans les ACL

PrivilègeAbréviationTypes d'objets concernés
SELECTr (« read ») LARGE OBJECT, SEQUENCE, TABLE (et objets similaires à des tables), colonne de table
INSERTa (« append »)TABLE, colonne de table
UPDATEw (« write ») LARGE OBJECT, SEQUENCE, TABLE, colonne de table
DELETEdTABLE
TRUNCATEDTABLE
REFERENCESxTABLE, colonne de table
TRIGGERtTABLE
CREATEC DATABASE, SCHEMA, TABLESPACE
CONNECTcDATABASE
TEMPORARYTDATABASE
EXECUTEXFUNCTION, PROCEDURE
USAGEU DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

Tableau 5.2 résume les privilèges disponibles pour chaque objet SQL, avec les abréviations ci-dessus. Il affiche aussi la commande psql à utiliser pour consulter les valeurs des privilèges de chaque type d'objet.

Tableau 5.2. Résumé des privilèges d'accès

Type d'objetTous les privilègesPrivilèges par défaut pour PUBLICCommande psql
DATABASECTcTc\l
DOMAINUU\dD+
FUNCTION ou PROCEDUREXX\df+
FOREIGN DATA WRAPPERUaucun\dew+
FOREIGN SERVERUaucun\des+
LANGUAGEUU\dL+
LARGE OBJECTrwaucun 
SCHEMAUCaucun\dn+
SEQUENCErwUaucun\dp
TABLE (et objets similaires)arwdDxtaucun\dp
Colonne de tablearwxaucun\dp
TABLESPACECaucun\db+
TYPEUU\dT+

Les privilèges accordés à un objet particulier sont affichés comme une liste d'entrées aclitem, chacun ayant le format :

grantee=privilege-abbreviation[*].../grantor

Chaque aclitem liste tous les droits d'un bénéficiaire qui ont été donnés par un rôle particulier. Les droits spécifiques sont représentés par des abréviations sur une lettre à partir de Tableau 5.1, avec un * ajouté si le droit a été donné avec l'option grant. Par exemple, calvin=r*w/hobbes indique que le rôle calvin a le privilège SELECT (r) avec possibilité de transmission (« with grant option ») ainsi que le privilège UPDATE (w), sans retransmission possible ; privilèges accordés tous les deux par le rôle hobbes. Si calvin a aussi des privilèges sur le même objet accordés par un autre utilisateur, ils apparaîtraient comme une entrée aclitem séparée. Un champ vide dans un aclitem indique PUBLIC.

Par exemple, supposons que l'utilisateur miriam crée une table mytable et fasse :

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
        

alors dans psql la commande \dp afficherait :

=> \dp mytable
                                        Droits d'accès
 Schéma |   Nom   | Type  |     Droits d'accès    | Droits d'accès à la colonne | Politiques
--------+---------+-------+-----------------------+-----------------------------+------------
 public | mytable | table | miriam=arwdDxt/miriam+| col1:                      +|
        |         |       | =r/miriam            +|   miriam_rw=rw/miriam       |
        |         |       | admin=arw/miriam      |                             |
(1 ligne)
        

Si la colonne « Droits d'accès » est vide pour un objet donné, cela signifie que l'objet a les privilèges par défaut (c'est-à-dire que l'entrée des privilèges dans les catalogues système est NULL). Les privilèges par défaut incluent toujours tous les privilèges pour le propriétaire, et peuvent inclure certains privilèges pour PUBLIC en fonction du type d'objet comme expliqué plus haut. La première commande GRANT ou REVOKE sur un objet va instancier les privilèges par défaut (en produisant, par exemple, miriam=arwdDxt/miriam) puis les modifier selon la requête. De manière similaire, des entrées ne sont affichées dans « Droits d'accès à la colonne » que pour les colonnes qui n'ont pas les privilèges par défaut. (Note : dans ce contexte, on entend par « privilèges par défaut » les privilèges par défaut intégrés à PostgreSQL pour le type d'objet. Un objet dont les privilèges auront été affectés par une commande ALTER DEFAULT PRIVILEGES montrera toujours une entrée de privilèges explicite qui inclue les effets du ALTER.)

Notez que les droits de transmission (grant option) implicites ne sont pas indiqués dans l'affichage des droits d'accès. Un * n'apparaîtra que si la transmission a été explicitement accordée à quelqu'un.