Gestion des droits – GRANT et REVOKE

Bonne lecture et bon apprentissage !
Junior TSAFACK – 20/08/2026
⏱️ Temps de lecture estimé : 10 minutes

---

Après avoir appris à créer des utilisateurs et des rôles, il est temps de leur attribuer des droits sur les objets de la base de données. PostgreSQL utilise les commandes GRANT et REVOKE pour gérer les permissions. Ce cours vous présente les différents types de droits, leur application sur les bases, schémas, tables et colonnes, ainsi que les bonnes pratiques pour sécuriser votre base.

---

Les droits dans PostgreSQL

Types de droits

Droit	Description	Applicable sur
SELECT	Lire des données	Tables, vues, colonnes, séquences
INSERT	Insérer des données	Tables, colonnes
UPDATE	Modifier des données	Tables, colonnes
DELETE	Supprimer des données	Tables
TRUNCATE	Vider une table	Tables
REFERENCES	Créer des clés étrangères vers une table	Tables, colonnes
TRIGGER	Créer des déclencheurs sur une table	Tables
CREATE	Créer des objets	Bases de données, schémas
CONNECT	Se connecter à une base	Bases de données
TEMPORARY (ou TEMP)	Créer des tables temporaires	Bases de données
EXECUTE	Exécuter des fonctions	Fonctions, procédures
USAGE	Utiliser un objet (schéma, séquence, type)	Schémas, séquences, types
ALL PRIVILEGES	Tous les droits	Tous les objets

💡 Bon à savoir : ALL PRIVILEGES n’est pas toujours équivalent à tous les droits possibles. Il inclut les droits disponibles selon le type d’objet.

---

Syntaxe de GRANT et REVOKE

GRANT

GRANT { droit [,...] | ALL [PRIVILEGES] }
    ON { objet | ALL TABLES IN SCHEMA schema }
    TO { role_specification [,...] | PUBLIC }
    [ WITH GRANT OPTION ];

• WITH GRANT OPTION : permet au bénéficiaire de transmettre le droit à d’autres.

REVOKE

REVOKE [ GRANT OPTION FOR ]
    { droit [,...] | ALL [PRIVILEGES] }
    ON { objet | ALL TABLES IN SCHEMA schema }
    FROM { role_specification [,...] | PUBLIC }
    [ CASCADE | RESTRICT ];

• GRANT OPTION FOR : révoque uniquement la possibilité de transmettre le droit.
• CASCADE : révoque également les droits accordés par ce rôle à d’autres.
• RESTRICT : refuse si d’autres dépendent de ce droit.

---

1. Droits sur les bases de données

Permissions disponibles

• CONNECT : autorise la connexion à la base.
• CREATE : autorise la création de schémas (et d’objets) dans la base.
• TEMPORARY (ou TEMP) : autorise la création de tables temporaires.

Exemple pratique

-- Créer une base de test
CREATE DATABASE xavki;

-- Créer des utilisateurs
CREATE USER xavier LOGIN CREATEDB;
CREATE USER toto LOGIN CREATEDB;

-- Révoquer tous les droits pour PUBLIC (tous les utilisateurs)
REVOKE ALL ON DATABASE xavki FROM PUBLIC;

-- Donner la propriété à xavier
ALTER DATABASE xavki OWNER TO xavier;

-- Tester la connexion avec toto (doit échouer)
\c xavki toto
-- Réponse : FATAL: permission denied for database "xavki"

-- Autoriser toto à se connecter
GRANT CONNECT ON DATABASE xavki TO toto;

-- Tester la connexion (doit réussir)
\c xavki toto
-- Connecté à la base "xavki" en tant que "toto"

-- toto ne peut pas créer de schéma
CREATE SCHEMA test;
-- Réponse : ERROR: permission denied for database xavki

-- Autoriser la création de schémas
GRANT CREATE ON DATABASE xavki TO toto;

-- toto peut maintenant créer des schémas
CREATE SCHEMA test;

---

2. Droits sur les schémas

Permissions disponibles

• USAGE : permet d’accéder aux objets du schéma (lecture, mais pas de création).
• CREATE : permet de créer des objets (tables, vues, fonctions) dans le schéma.

Exemple pratique

-- Se connecter en tant que xavier
\c xavki xavier

-- Créer un schéma
CREATE SCHEMA monschema;

-- Révoquer tous les droits sur le schéma pour PUBLIC
REVOKE ALL ON SCHEMA monschema FROM PUBLIC;

-- Se connecter en tant que toto
\c xavki toto

-- Essayer de créer une table dans monschema (doit échouer)
CREATE TABLE monschema.matable (id int);
-- Réponse : ERROR: permission denied for schema monschema

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner les droits CREATE sur le schéma à toto
GRANT CREATE ON SCHEMA monschema TO toto;

-- Se reconnecter en tant que toto
\c xavki toto

-- toto peut maintenant créer des tables dans monschema
CREATE TABLE monschema.matable (id int);

-- Mais toto ne peut pas lire la table (pas encore)
SELECT * FROM monschema.matable;
-- Réponse : ERROR: permission denied for table matable

Ajouter USAGE

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner USAGE sur le schéma
GRANT USAGE ON SCHEMA monschema TO toto;

-- Se reconnecter en tant que toto
\c xavki toto

-- toto peut maintenant voir les objets du schéma
SELECT * FROM monschema.matable;
-- Réponse : OK (la table existe mais est vide)

---

3. Droits sur les tables

Permissions disponibles

Droit	Description
SELECT	Lire les données de la table.
INSERT	Insérer des lignes.
UPDATE	Modifier des lignes (nécessite aussi SELECT pour certaines colonnes).
DELETE	Supprimer des lignes.
TRUNCATE	Vider la table.
REFERENCES	Permet de créer une clé étrangère vers la table.
TRIGGER	Permet de créer des déclencheurs sur la table.
ALL	Tous les droits ci-dessus.

Exemple pratique

-- Se connecter en tant que xavier
\c xavki xavier

-- Créer une table
CREATE TABLE tbl1 (id int, champs1 varchar);
INSERT INTO tbl1 VALUES (1, 'hello'), (2, 'world'), (3, 'les xavkistes !!!');

-- Révoquer tous les droits sur la table pour toto
REVOKE ALL ON TABLE tbl1 FROM toto;

-- Se connecter en tant que toto
\c xavki toto

-- Essayer d'insérer
INSERT INTO tbl1 VALUES (4, 'test');
-- Réponse : ERROR: permission denied for relation tbl1

-- Essayer de lire
SELECT * FROM tbl1;
-- Réponse : ERROR: permission denied for relation tbl1

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner le droit INSERT à toto
GRANT INSERT ON TABLE tbl1 TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut maintenant insérer
INSERT INTO tbl1 VALUES (4, 'test');
-- OK

-- Mais toto ne peut pas lire
SELECT * FROM tbl1;
-- Réponse : ERROR: permission denied for relation tbl1

Donner SELECT

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner SELECT à toto
GRANT SELECT ON TABLE tbl1 TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut maintenant tout voir
SELECT * FROM tbl1;
-- Résultat : 1, hello | 2, world | 3, les xavkistes !!! | 4, test

---

4. Droits sur les colonnes

Permissions disponibles

• SELECT : lecture de la colonne.
• INSERT : insertion de valeurs dans la colonne.
• UPDATE : modification de la colonne.
• REFERENCES : création de clé étrangère vers la colonne.

Exemple pratique

-- Se connecter en tant que xavier
\c xavki xavier

-- Révoquer tous les droits sur la table
REVOKE ALL ON TABLE tbl1 FROM toto;

-- Donner seulement SELECT sur la colonne champs1
GRANT SELECT (champs1) ON TABLE tbl1 TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut lire champs1
SELECT champs1 FROM tbl1;
-- Résultat : hello, world, les xavkistes !!!, test

-- toto ne peut pas lire id
SELECT id FROM tbl1;
-- Réponse : ERROR: permission denied for column id

-- toto ne peut pas faire SELECT *
SELECT * FROM tbl1;
-- Réponse : ERROR: permission denied for column id

Colonnes multiples

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner SELECT sur plusieurs colonnes
GRANT SELECT (id, champs1) ON TABLE tbl1 TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut maintenant lire les deux colonnes
SELECT id, champs1 FROM tbl1;
-- OK

---

5. Droits sur les séquences

Permissions disponibles

• USAGE : utiliser la séquence (nextval, currval, setval).
• SELECT : lire la valeur courante de la séquence.
• UPDATE : modifier la séquence (via setval).

Exemple pratique

-- Créer une table avec une séquence
CREATE TABLE avec_sequence (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(50)
);

-- Révoquer les droits sur la séquence
REVOKE ALL ON SEQUENCE avec_sequence_id_seq FROM toto;

-- Se connecter en tant que toto
\c xavki toto

-- Essayer d'insérer (doit échouer car la séquence est nécessaire)
INSERT INTO avec_sequence (nom) VALUES ('test');
-- Réponse : ERROR: permission denied for sequence avec_sequence_id_seq

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner USAGE sur la séquence à toto
GRANT USAGE ON SEQUENCE avec_sequence_id_seq TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- L'insertion fonctionne maintenant
INSERT INTO avec_sequence (nom) VALUES ('test');
-- OK

---

6. Droits sur les fonctions et procédures

Permissions disponibles

• EXECUTE : exécuter la fonction ou la procédure.

Exemple pratique

-- Créer une fonction
CREATE OR REPLACE FUNCTION nb_employes() RETURNS INTEGER AS $$
    SELECT COUNT(*) FROM employes;
$$ LANGUAGE SQL;

-- Révoquer tous les droits sur la fonction
REVOKE ALL ON FUNCTION nb_employes() FROM toto;

-- Se connecter en tant que toto
\c xavki toto

-- Essayer d'exécuter (doit échouer)
SELECT nb_employes();
-- Réponse : ERROR: permission denied for function nb_employes

-- Se reconnecter en tant que xavier
\c xavki xavier

-- Donner EXECUTE à toto
GRANT EXECUTE ON FUNCTION nb_employes() TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut maintenant exécuter la fonction
SELECT nb_employes();
-- OK

---

7. Le rôle PUBLIC et la sécurité par défaut

Qu’est-ce que PUBLIC ?

PUBLIC est un groupe spécial qui représente tous les utilisateurs de la base. Par défaut, PostgreSQL accorde certains droits à PUBLIC sur les objets créés.

Exemple de l’effet de PUBLIC

-- Créer une table dans une nouvelle base
CREATE DATABASE testdb;
\c testdb postgres
CREATE TABLE secret (data TEXT);

-- Sans précautions, tous les utilisateurs peuvent lire cette table ?
-- Vérifions les droits
SELECT * FROM information_schema.table_privileges
WHERE table_name = 'secret' AND grantee = 'PUBLIC';
-- Résultat : PUBLIC a SELECT sur la table !

Révoquer les droits de PUBLIC

-- Révoquer tous les droits par défaut de PUBLIC
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- Révoquer les droits sur les séquences
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

-- Révoquer les droits sur les fonctions
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;

Modifier les droits par défaut (DDL)

-- Définir les droits par défaut pour les futures tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT ON TABLES TO role_applicatif;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE ALL ON TABLES FROM PUBLIC;

---

8. WITH GRANT OPTION et transfert de droits

Principe

WITH GRANT OPTION permet au bénéficiaire de transmettre les droits à d’autres utilisateurs.

Exemple

-- Se connecter en tant que postgres
\c xavki postgres

-- Créer la table
CREATE TABLE donnees (id int, valeur text);

-- Donner SELECT à xavier avec possibilité de transmettre
GRANT SELECT ON donnees TO xavier WITH GRANT OPTION;

-- Se connecter en tant que xavier
\c xavki xavier

-- xavier peut maintenant donner SELECT à toto
GRANT SELECT ON donnees TO toto;

-- Se connecter en tant que toto
\c xavki toto

-- toto peut lire la table
SELECT * FROM donnees;

-- toto ne PEUT PAS transmettre les droits (il n'a pas GRANT OPTION)
GRANT SELECT ON donnees TO autre_user;
-- Réponse : ERROR: permission denied for relation donnees

Révocation avec CASCADE

-- Se connecter en tant que postgres
\c xavki postgres

-- Révoquer le droit SELECT de xavier avec CASCADE
REVOKE SELECT ON donnees FROM xavier CASCADE;

-- toto perd également le droit SELECT (car il l'avait reçu de xavier)
-- Vérifier
\c xavki toto
SELECT * FROM donnees;
-- Réponse : ERROR: permission denied for relation donnees

---

9. Rôles prédéfinis utiles

PostgreSQL fournit des rôles système pratiques :

Rôle	Privilèges accordés
pg_monitor	Lecture des vues de statistiques (pg_stat_activity, pg_stat_database, etc.)
pg_signal_backend	Annulation de requêtes (pg_cancel_backend) et de sessions (pg_terminate_backend)
pg_read_server_files	Lecture de fichiers sur le serveur (pg_read_file, pg_ls_dir)
pg_write_server_files	Écriture de fichiers sur le serveur
pg_execute_server_program	Exécution de programmes système via COPY ... PROGRAM

Exemple d’utilisation

-- Donner à un analyste les droits de monitoring
GRANT pg_monitor TO xavier;

-- Se connecter en tant que xavier
\c xavki xavier

-- Consulter les sessions actives
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity;

-- Donner à un DBA le droit d'annuler des requêtes
GRANT pg_signal_backend TO dba_junior;

-- Se connecter en tant que dba_junior
\c xavki dba_junior

-- Annuler une requête (nécessite le PID)
SELECT pg_cancel_backend(12345);

---

Tableau récapitulatif des droits par objet

Objet	Droits disponibles
Base de données	CONNECT, CREATE, TEMPORARY
Schéma	USAGE, CREATE
Table	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
Colonne	SELECT, INSERT, UPDATE, REFERENCES
Séquence	USAGE, SELECT, UPDATE
Fonction / Procédure	EXECUTE
Vue	SELECT, INSERT, UPDATE, DELETE (si mise à jour possible)
Vue matérialisée	SELECT

---

Bonnes pratiques

Pratique	Description
Principe du moindre privilège	Accordez le minimum de droits nécessaires.
Utiliser des rôles de groupe	Créez des rôles NOLOGIN pour les groupes de droits.
Éviter PUBLIC	Révoquez les droits de PUBLIC sur les objets sensibles.
Utiliser DEFAULT PRIVILEGES	Définissez les droits par défaut pour éviter les oublis.
Audit régulier	Consultez information_schema.table_privileges pour vérifier les droits.
CASCADE avec précaution	CASCADE peut révoquer des droits inattendus.
Utiliser des schémas	Isolez les données par schéma pour simplifier la gestion des droits.
Documenter	Documentez les attributions de droits dans la documentation de la base.

---

Commandes récapitulatives

Action	Commande
Donner un droit sur une table	GRANT SELECT ON table TO role;
Donner un droit sur un schéma	GRANT USAGE ON SCHEMA schema TO role;
Donner un droit sur une base	GRANT CONNECT ON DATABASE db TO role;
Donner un droit sur une colonne	GRANT SELECT (col) ON table TO role;
Révoquer un droit	REVOKE SELECT ON table FROM role;
Révoquer avec cascade	REVOKE SELECT ON table FROM role CASCADE;
Modifier les droits par défaut	ALTER DEFAULT PRIVILEGES ...;
Voir les droits d’une table	\dp table
Voir les droits d’un schéma	\dn+ schema
Voir les droits d’une base	\l+

---

Prochain chapitre

Vous maîtrisez maintenant la gestion des droits avec GRANT et REVOKE. Dans le prochain cours, nous aborderons les logs et le monitoring, pour surveiller et diagnostiquer votre base de données PostgreSQL.

👉 Cours 10 : Logs et monitoring

---

Junior TSAFACK – 20/08/2026