Halte là qui va là ? Maitriser les utilisateurs et leurs droits dans PostgreSQL

Introduction

Il est courant dans l’utilisation des bases PostgreSQL de ne pas se soucier de la gestion des droits car quelques utilisateurs accèdent aux données.

Cependant, PostgreSQL permet une gestion particulièrement avancée de l’accès aux données et ce au travers de deux volets : les utilisateurs et les autorisations.

Cet article vous permettra de maitriser la gestion des utilisateurs (rôles) et des droits d’accès aux données.

Gestion des rôles

Un rôle est un objet d’une base PostgreSQL qui peut être propriétaire d’autres objets (des schémas, des tables…) et auquel il est possible d’attribuer des privilèges.

Il est intéressant de noter qu’un rôle est définit au niveau de l’instance de PostgreSQL, tout comme les bases de données. Ainsi, les rôles sont communs aux différentes bases de données de l’instance.

On associe souvent le concept de rôle et la notion d’utilisateur car c’est au travers d’un rôle que l’on va se connecter à l’instance PostgreSQL en utilisant l’identifiant et le mot de passe associés à ce rôle.

Cependant le concept va plus loin car un rôle peut également être considéré comme un groupe d’utilisateur. En effet, il est possible de rendre un rôle membre d’un autre rôle. Ce qui autorise un rôle à être à la fois considéré comme un utilisateur et en même temps comme un groupe.

Notez qu’un rôle peut être membre de plusieurs rôles (= un utilisateur dans plusieurs groupes) et qu’il est possible pour un rôle d’être membre d’un second rôle lui-même membre d’un troisième rôle… (= un utilisateur dans un groupe lui-même dans un groupe…).

Créer un rôle

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

Voici les principales options :

CREATE ROLE
	mon_role
	WITH
		LOGIN
		PASSWORD 'mon_mdp'
		VALID UNTIL '2022-12-24'

		SUPERUSER
		CREATEDB
		CREATEROLE

		IN ROLE role_1, role_2
		INHERIT
		ROLE role_3, role_4
		ADMIN role_5
;

mon_role : identifiant / nom du rôle

• LOGIN : permet de se connecter à l’instance PostgreSQL avec le rôle. L’inverse est NOLOGIN.
                 Par défaut : NOLOGIN
• PASSWORD 'mon_mdp' : permet de définir un mot de passe pour le rôle ce qui permettra de se connecter avec.
                 Par défaut : aucun mot de passe n’est définit ce qui équivaut à indiquer PASSWORD NULL.
• VALID UNTIL '2022-12-24' : le mot de passe n’est valide que jusqu’à la date indiquée, au-delà, il n’est plus valide et il n’est plus possible de se connecter avec ce rôle tant que le mot de passe n’a pas été redéfinit (et une nouvelle date de validité indiquée).
                 Par défaut : aucune date renseignée : le mot de passe n’expire pas.

• SUPERUSER : permet de donner le privilège de super utilisateur au rôle. L’inverse est NOSUPERUSER.
                 Par défaut : NOSUPERUSER
• CREATEDB : permet de donner le privilège de créer des bases de données dans l’instance. L’inverse est NOCREATEDB.
                 Par défaut : NOCREATEDB
• CREATEROLE : permet de donner le privilège de créer des rôles dans l’instance. L’inverse est NOCREATEROLE.
                 Par défaut : NOCREATEROLE

• IN ROLE role_1, role_2 : permet de rendre le rôle créé membre des rôles role_1 et role_2.
• INHERIT : permet d’hériter des droits attribués à un rôle dont le rôle créé est membre. L’inverse est NOINHERIT.
                 Par défaut : INHERIT
• ROLE role_3, role_4 : permet de rendre les rôles role_3 et role_4 membres du rôle créé.
• ADMIN role_5 : permet de rendre le rôle role_5 membre du rôle créé avec les options d’administration (role_5 pourra ajouter d’autres rôles en tant que membre de mon_role).

L’option INHERIT ne permet d’hériter que les droits accordés via la requête GRANT, sur les objets de la base de données. Les options définis sur un rôle (SUPERUSER, CREATEDB, CREATROLE) ne sont pas héritables.

Les super-utilisateurs (possédant l’option SUPERUSER) peuvent tout faire dans la base et ne possèdent aucune restriction.

Attention, l’option CREATEROLE permet au rôle possédant cette option de créer des rôles avec des options différentes de lui-même (sauf l’option SUPERUSER). Ainsi, un rôle n’ayant pas l’option CREATEDB mais possédant l’option CREATEROLE peut tout à fait créer un rôle possédant l’option CREATEDB pour contourner l’impossibilité de créer de nouvelles bases de données.

Modifier un rôle

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

La modification d’un rôle ne peut être faite que par :

• lui-même,
• un super utilisateur,
• un rôle possédant l’option CREATEROLE.

Voici la syntaxe de modification d’un rôle :

ALTER ROLE mon_role
	WITH
		mon_option
;

mon_option reprend la même liste d’option que lors de la création d’un rôle.

Pour renommer un rôle, la syntaxe suivante peut être utilisée :

ALTER ROLE mon_role 
	RENAME TO mon_nouveau_nom
;

Supprimer un rôle

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

La suppression d’un rôle ne peut être faite que par :

• lui-même,
• un super utilisateur,
• un rôle possédant l’option CREATEROLE.

Voici la syntaxe de modification d’un rôle :

DROP ROLE IF EXISTS mon_role
;

Changer de rôle

Lors de l’accès à une instance PostgreSQL, la connexion s’initialise avec le rôle utilisé pour la connexion et deux variables sont créées :

• session_user : l’utilisateur de session : qui communique entre le logiciel client et l’instance PostgreSQL.
• current_user : l’utilisateur actuel : qui permet de déterminer les droits appliqués.

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

La syntaxe suivante permet de changer la variable current_user et donc les droits qui s’appliquent dans la session en cours.

SET ROLE role_1
;

Les droits qui s’appliquent après la requête précédente sont identiques à ceux qui se serait appliqués si la connexion avait été initialisée avec le rôle role_1.

SET ROLE NONE
;

Cette requête permet de revenir dans l’état initial, lors de la connexion à l’instance.

Il n’est possible d’obtenir les droits que de rôles dont l’utilisateur est membre.

Notez bien que l’utilisation de cette commande fonctionne de la façon suivante selon les options définis sur les rôles :

• Un super utilisateur choisissant de prendre les droits d’un rôle non super utilisateur perdra donc ses droits de super utilisateur.
• Un rôle possédant l’option INHERIT hérite des droits de tous les rôles dont il est membre. Le changement de rôle (qui s’effectue obligatoirement vers un rôle dont il est membre) impliquera donc la suppression des droits autrefois récupérés par héritage et non disponibles pour le rôle dont on souhaite récupérer les droits.
• Un rôle possédant l’option NOINHERIT n’hérite d’aucun droit des rôles dont il est membre. Le changement de rôle impliquera donc la suppression des droits assignés directement au rôle initial et l’ajout des droits du rôle pour lequel on souhaite acquérir les droits.

Adhésion des rôles

Les rôles peuvent devenir membre d’autres rôles. Ceci permet de constituer des groupes de rôles et ainsi de simplifier l’attribution des droits.

Pour associer un (ou plusieurs) rôle(s) à un (ou plusieurs) autre(s) voici la syntaxe à utiliser :

GRANT 
	role_groupe_1, role_groupe_2 
TO 
	role_user_1, role_user_2
;

Pour associer un rôle à un autre, il faut être connecté avec le rôle dont on souhaite faire devenir membre (comme role_groupe_1 dans mon exemple). Un rôle est administrateur des adhésions de lui-même en quelque sorte.

Autrement, il faut posséder les droits d’administration des adhésions au rôle, droits acquis via la syntaxe suivante :

GRANT 
	role_groupe_1 
TO 
	role_user_1
WITH ADMIN OPTION
;

Ce qui permettra à role_user_1 d’être membre de role_groupe_1 et d’y ajouter d’autres membres.

La révocation des adhésions utilise la syntaxe suivante :

REVOKE
	role_groupe_1, role_groupe_2 
FROM
	role_user_1, role_user_2
;

Pour ne révoquer que les droit d’administration des adhésions, c’est la syntaxe suivante qu’il faut utiliser :

REVOKE ADMIN OPTION FOR
	role_groupe_1 
FROM
	role_user_1
;

Gestion des droits

Introduction

Par défaut dans PostgreSQL, le rôle propriétaire d’un objet en est le gestionnaire, il peut modifier la structure de l’objet et son contenu. Pour que d’autres rôles puissent intervenir sur ces objets, il faut leur accorder des droits.

Attention, les droits ne permettent que d’intervenir sur le contenu de l’objet, pas sur l’objet en lui-même (suppression, renommage, redéfinition). Ces actions ne peuvent être faites que par le propriétaire de l’objet, par exemple seul le propriétaire d’une table peut en modifier le nom ou la structure.

Le propriétaire d’un objet peut choisir de révoquer certains droits pour lui-même pour des raisons de sécurité.

L’arborescence d’une base de donnée (objets contenus et structure de la base et des objets) est visible dans des vues créées dans les schémas information_schema et pg_catalog. Ce sont ces vues qui permettent aux logiciels clients avec interface graphiques (DBeaver, pgAdmin) d’afficher le contenu d’une base de données.

Ces vues sont toujours accessibles à tous, il n’est donc pas possible de restreindre la visibilité du contenu d’une base de données (par exemple cacher un schéma, une table ou la structuration d’une table).

Un utilisateur pourra donc toujours regarder le contenu des différents schémas sans pour autant avoir accès aux données contenues dans les tables et autres objets.

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

La syntaxe est toujours la même :

GRANT mon_action_1, mon action_2
ON mon_objet_1, mon_objet_2
TO mon_role_a, mon_role_b
;

Le détail des actions, par ailleurs détaillées plus bas dans cet article, est disponible sur cette page.

Droit d’accorder des permissions :

Si besoin, il est possible de donner le droit d’accorder des permissions avec la syntaxe suivante :

GRANT mon_action_1, mon_action_2
ON mon_objet_1, mon_objet_2
TO mon_role_a, mon_role_b
WITH GRANT OPTION
;

Attention, ce droit d’accorder des permissions ne vaut que pour les actions décrites dans la requête pour le ou les objets mentionnés. Il est donc possible d’accorder des droits à sur une table à un rôle mais dont seuls quelques-uns peuvent être accordés par ce même rôle à d’autres rôle.

Il n’est pas possible d’accorder des droits d’accorder des permissions pour le rôle public (décrit plus loin).

Toutes les actions possibles

Si toutes les actions possibles sont à accorder, la liste peut être remplacée par ALL ou ALL PRIVILEGES. Par exemple :

GRANT ALL PRIVILEGES
ON mon_objet_1, mon_objet_2
TO mon_role_a, mon_role_b
;

Tous les objets possibles

Il est également possible d’accorder des privilèges à tous les objets d’in schéma en remplaçant le nom des objets par ON ALL type_objetS IN SCHEMA mon_schema. Par exemple :

GRANT mon_action_1, mon action_2
ON ALL TABLES IN SCHEMA mon_schema
TO mon_role_a, mon_role_b
;

Tout le monde

Lors de la définition des droits, il est possible de définir des droits pour tout le monde en utilisant le rôle public. Il s’agit d’un rôle particulier qui veut dire « n’importe quel rôle ».

Révocation

Les droits peuvent être retirés, le détail de la syntaxe et des options disponibles se trouve sur cette page.

La suppression des permissions utilise la syntaxe suivante :

REVOKE mon_action
ON mon_objet
FROM mon_role
;

La suppression des droits d’accorder des permissions utilise la syntaxe suivante :

REVOKE GRANT OPTION FOR mon_action
ON mon_objet
FROM mon_role
;

Droits des objets

Voici une liste non exhaustive des différents droits qu’il est possible d’accorder :

Niveau instance

Base de donnée

GRANT CREATE, CONNECT, TEMPORARY
ON DATABASE ma_bdd
TO mon_role
;

Actions :

• CREATE : créer des objets dans la base de données (des schémas par exemple).
• CONNECT : se connecter à la base de données.
• TEMPORARY : créer des objets temporaires dans la base.

Niveau base de donnée

Schéma

GRANT CREATE, USAGE
ON SCHEMA mon_schema
TO mon_role
;

Actions :

• CREATE : créer des objets dans le schéma (des tables par exemple).
• USAGE : accéder aux données des objets du schéma (il faut en plus que les droit d’accès soit donnés sur les objets du schéma).

Espace de stockage

GRANT CREATE
ON TABLESPACE mon_tablespace
TO mon_role
;

Actions :

• CREATE : autoriser des objets créés par le rôle à utiliser l’espace de stockage (des tables par exemple).

Niveau schéma

Tables

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
ON TABLE mon_schema.ma_table
TO mon_role 
;

Actions :

• SELECT : lire les données.
• INSERT : insérer des données.
• UPDATE : mettre à jour les données.
• DELETE : supprimer des données.
• TRUNCATE : vider la table (attention, il est également possible de vider une table avec DELETE).
• REFERENCE : utiliser la table comme référence dans une clé étrangère.
• TRIGGER : ajouter des triggers sur la table.

Fonction

GRANT EXECUTE
ON FUNCTION mon_schema.ma_fonction
TO mon_role 
;

Actions :

• EXECUTE : utiliser la fonction.

Procédure

GRANT EXECUTE
ON PROCEDURE mon_schema.ma_procedure
TO mon_role
;

Actions :

• EXECUTE : utiliser la procédure.

Séquence

GRANT USAGE, SELECT, UPDATE
ON SEQUENCE mon_schema.ma_sequence
TO mon_role
;

Actions :

• USAGE : afficher la valeur actuelle de la séquence et mettre à jour la séquence avec nextval().
• SELECT : afficher la valeur actuelle de la séquence.
• UPDATE : mettre à jour la séquence avec nextval() et/ou définir une nouvelle valeur avec setval().

Niveau table

Colonne

GRANT SELECT, INSERT, UPDATE, REFERENCES (
	colonne_1,
	colonne_2
)
ON TABLE mon_schema.ma_table
TO mon_role
;

Actions :

• SELECT : lire les données.
• INSERT : insérer des données.
• UPDATE : mettre à jour les données.
• REFERENCE : utiliser la colonne comme référence dans une clé étrangère.

Droits par défaut

Pour éviter d’avoir à mettre à jour les droits dès qu’un objet est créé, il est possible de définir des droits par défaut.

Ces droits par défaut ne peuvent être définit que pour un seul schéma à la fois et uniquement pour les éléments contenus dans un schéma (tables, séquences, fonctions, types).

Le détail de la syntaxe et des options disponibles se trouve sur cette page.

Voici la syntaxe utilisée :

ALTER DEFAULT PRIVILEGES
FOR ROLE 
	role_createur
IN SCHEMA
	mon_schema
-- Syntaxe habituel d’un GRANT ou d’un REVOKE --
;

Notez qu’il faut indiquer le rôle qui créera les objets. En effet, c’est le rôle qui créé l’objet qui attribut les droits. Il est donc possible d’avoir des droits par défaut différents selon le rôle créant un objet. Vous pouvez utiliser le rôle public pour cibler « n’importe quel rôle ».

Il n’est possible d’indiquer qu’un rôle dont le rôle courant (qui lance la requête) est membre sauf si le rôle courant est super utilisateur, dans ce cas n’importe quel rôle peut être indiqué.

Si FOR ROLE role_createur n’est pas mentionné, alors le rôle courant est utilisé.

Voici un exemple pour les tables :

ALTER DEFAULT PRIVILEGES
FOR ROLE 
	public
IN SCHEMA
	mon_schema
GRANT SELECT, REFERENCES
ON TABLES
TO mon_role_1
;

L’objet sur lequel s’appliqueront les droits n’est pas indiqué (car par encore créé). On spécifie simple le type d’objet (au pluriel) : TABLES, SEQUENCES, FUNCTIONS, ROUTINES, TYPES.

Ici aussi, il est possible d’utiliser ALL pour indiquer « toutes les actions » et de donner le droit d’accorder des permissions avec WITH GRANT OPTION.

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQL autorisationdroitdroitsgrouperoleutilisateur