Skip to content

04.3-default-privileges.md

Latest commit

 

History

History
1006 lines (735 loc) · 31.4 KB

04.3-default-privileges.md

File metadata and controls

1006 lines (735 loc) · 31.4 KB

🔝 Retour au Sommaire

16.4.3. Default Privileges (ALTER DEFAULT PRIVILEGES)

Introduction

Imaginez cette situation frustrante : vous accordez soigneusement des permissions à un utilisateur sur toutes les tables existantes. Tout fonctionne parfaitement. Puis, quelques jours plus tard, une nouvelle table est créée dans la base de données. Soudain, votre utilisateur ne peut plus y accéder car il n'a pas de permissions sur cette nouvelle table !

C'est exactement le problème que résout ALTER DEFAULT PRIVILEGES : cette commande permet de définir des permissions par défaut qui seront automatiquement appliquées à tous les nouveaux objets créés dans le futur.

Cette section explique comment utiliser cette fonctionnalité puissante pour automatiser la gestion des permissions et éviter les oublis.

1. Le Problème : Permissions sur les Nouveaux Objets

1.1. Scénario Problématique

Voici une situation typique qui cause des problèmes :

-- Étape 1 : Configuration initiale
CREATE SCHEMA app_data;

CREATE TABLE app_data.users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

CREATE ROLE app_backend LOGIN PASSWORD 'secure_pass';

-- Étape 2 : Accorder les permissions
GRANT USAGE ON SCHEMA app_data TO app_backend;  
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_data TO app_backend;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO app_backend;  

-- ✅ Tout fonctionne : app_backend peut accéder à la table users

Quelques jours plus tard, un développeur crée une nouvelle table :

-- Nouvelle table créée par postgres (superuser)
CREATE TABLE app_data.orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    total NUMERIC
);

Et maintenant, l'application rencontre une erreur :

-- L'application essaie d'accéder à la nouvelle table
SELECT * FROM app_data.orders;
-- ❌ Erreur : permission denied for table orders

Pourquoi ? Parce que les permissions accordées avec GRANT ... ON ALL TABLES ne s'appliquent qu'aux tables existantes au moment de la commande. Les nouvelles tables n'héritent pas automatiquement de ces permissions.

1.2. Solution Naïve (et Mauvaise)

Une solution serait de ré-exécuter les GRANT à chaque fois qu'une nouvelle table est créée :

-- Après chaque CREATE TABLE...
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_data TO app_backend;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO app_backend;

❌ Problèmes :

  • Fastidieux et répétitif
  • Facile à oublier
  • Erreurs garanties en production
  • Ne s'intègre pas bien dans les pipelines d'automatisation

1.3. La Vraie Solution : ALTER DEFAULT PRIVILEGES

-- Configuration des permissions par défaut
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;

Maintenant, toutes les nouvelles tables et séquences créées dans app_data auront automatiquement ces permissions accordées à app_backend ! 🎉

2. Syntaxe et Fonctionnement

2.1. Syntaxe Générale

ALTER DEFAULT PRIVILEGES
    [ FOR ROLE role_name ]
    [ IN SCHEMA schema_name ]
    abbreviated_grant_or_revoke;

Composants :

  • FOR ROLE : Spécifie le rôle qui créera les objets (optionnel)
  • IN SCHEMA : Limite aux objets créés dans un schéma spécifique (optionnel)
  • abbreviated_grant_or_revoke : GRANT ou REVOKE habituel, mais sans nommer d'objet spécifique

2.2. Syntaxe Détaillée pour GRANT

ALTER DEFAULT PRIVILEGES [ FOR ROLE creating_role ] [ IN SCHEMA schema_name ]  
GRANT { privilege [, ...] | ALL [ PRIVILEGES ] }  
    ON { TABLES | SEQUENCES | FUNCTIONS | TYPES | SCHEMAS }
    TO { role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

2.3. Types d'Objets Supportés

ALTER DEFAULT PRIVILEGES fonctionne pour :

Type d'Objet Permissions Applicables Exemple d'Usage
TABLES SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALL Tables et vues
SEQUENCES USAGE, SELECT, UPDATE, ALL Séquences (SERIAL, IDENTITY)
FUNCTIONS EXECUTE Fonctions et procédures stockées
TYPES USAGE Types personnalisés
SCHEMAS USAGE, CREATE Schémas (rarement utilisé)

3. Exemples Pratiques

3.1. Exemple de Base : Application Simple

Configuration complète pour une application avec lecture/écriture :

-- 1. Créer le schéma
CREATE SCHEMA app_data;

-- 2. Créer le rôle
CREATE ROLE app_backend LOGIN PASSWORD 'secure_password';

-- 3. Permissions sur le schéma
GRANT USAGE ON SCHEMA app_data TO app_backend;

-- 4. Permissions sur les objets EXISTANTS
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_backend;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO app_backend;  

-- 5. 🌟 Permissions par DÉFAUT sur les FUTURS objets
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;

Test :

-- Créer une nouvelle table
CREATE TABLE app_data.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- app_backend a AUTOMATIQUEMENT les permissions !
-- (se connecter en tant que app_backend pour tester)
SELECT * FROM app_data.products;  -- ✅ Fonctionne !  
INSERT INTO app_data.products (name) VALUES ('Test');  -- ✅ Fonctionne !

3.2. Exemple : Utilisateur en Lecture Seule

Pour un utilisateur qui doit uniquement consulter les données :

-- Créer le rôle
CREATE ROLE app_readonly LOGIN PASSWORD 'secure_password';

-- Permission sur le schéma
GRANT USAGE ON SCHEMA app_data TO app_readonly;

-- Permissions sur les tables existantes
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_readonly;

-- 🌟 Permissions par défaut sur les futures tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_readonly;

Maintenant, chaque nouvelle table sera automatiquement accessible en lecture à app_readonly.

3.3. Exemple : FOR ROLE (Propriétaire Spécifique)

Par défaut, ALTER DEFAULT PRIVILEGES s'applique aux objets créés par l'utilisateur qui exécute la commande. Pour spécifier un autre créateur, utilisez FOR ROLE :

-- Imaginons que "developpeur" crée les tables
CREATE ROLE developpeur LOGIN PASSWORD 'pass';  
GRANT CREATE ON SCHEMA app_data TO developpeur;  

-- Configuration des permissions par défaut pour les objets créés par "developpeur"
ALTER DEFAULT PRIVILEGES FOR ROLE developpeur IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE developpeur IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_readonly;

Explication :

  • Quand developpeur crée une table dans app_data, elle aura automatiquement :
    • SELECT, INSERT, UPDATE pour app_backend
    • SELECT pour app_readonly

3.4. Exemple : Plusieurs Schémas

Pour appliquer les mêmes permissions à plusieurs schémas, il faut répéter la commande :

-- Configuration pour le schéma app_data
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_backend;  

-- Configuration pour le schéma app_logs
ALTER DEFAULT PRIVILEGES IN SCHEMA app_logs  
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_backend;  

-- Configuration pour le schéma app_cache
ALTER DEFAULT PRIVILEGES IN SCHEMA app_cache  
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO app_backend;

3.5. Exemple : Fonctions et Procédures

Les fonctions ont un comportement par défaut particulier : elles sont exécutables par PUBLIC. Pour le changer :

-- Révoquer l'accès public par défaut sur les nouvelles fonctions
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;  

-- Accorder uniquement à app_backend
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT EXECUTE ON FUNCTIONS TO app_backend;

Maintenant, les nouvelles fonctions créées dans app_data ne seront exécutables que par app_backend.

3.6. Exemple : ALL PRIVILEGES

Pour accorder toutes les permissions possibles :

-- Administrateur avec tous les droits sur les futures tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT ALL PRIVILEGES ON TABLES TO admin_role;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT ALL PRIVILEGES ON SEQUENCES TO admin_role;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT ALL PRIVILEGES ON FUNCTIONS TO admin_role;

4. REVOKE avec DEFAULT PRIVILEGES

Tout comme on peut GRANT par défaut, on peut aussi REVOKE par défaut.

4.1. Révoquer des Permissions par Défaut

-- Révoquer SELECT par défaut
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
REVOKE SELECT ON TABLES FROM app_readonly;  

-- Révoquer EXECUTE sur les fonctions de PUBLIC
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

4.2. Exemple : Sécuriser les Fonctions

Par défaut, PostgreSQL accorde EXECUTE sur les nouvelles fonctions à PUBLIC. Pour une sécurité maximale :

-- Révoquer l'accès public par défaut
ALTER DEFAULT PRIVILEGES  
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;  

-- Dans chaque schéma aussi
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;  

-- Puis accorder explicitement selon les besoins
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT EXECUTE ON FUNCTIONS TO app_backend;

5. Vérifier les Default Privileges

5.1. Via psql : \ddp

La commande \ddp affiche les default privileges configurés :

\ddp

Résultat exemple :

          Default access privileges
 Owner  | Schema   | Type     | Access privileges
--------+----------+----------+-------------------
 postgres | app_data | table    | app_backend=arwd/postgres
                                 app_readonly=r/postgres
 postgres | app_data | sequence | app_backend=rU/postgres

5.2. Avec un Filtre sur un Schéma

\ddp app_data

5.3. Via SQL : pg_default_acl

Pour une analyse plus détaillée, interrogez la table système pg_default_acl :

SELECT
    pg_get_userbyid(d.defaclrole) AS owner,
    n.nspname AS schema,
    CASE d.defaclobjtype
        WHEN 'r' THEN 'table'
        WHEN 'S' THEN 'sequence'
        WHEN 'f' THEN 'function'
        WHEN 'T' THEN 'type'
        WHEN 'n' THEN 'schema'
    END AS object_type,
    d.defaclacl AS default_acl
FROM pg_default_acl d  
LEFT JOIN pg_namespace n ON d.defaclnamespace = n.oid  
WHERE n.nspname = 'app_data'  -- Filtrer par schéma  
   OR d.defaclnamespace = 0;   -- ou global (pas de schéma spécifique)

5.4. Décoder les Permissions (Plus Lisible)

SELECT
    pg_get_userbyid(d.defaclrole) AS "Owner/Creator",
    COALESCE(n.nspname, 'ALL SCHEMAS') AS "Schema",
    CASE d.defaclobjtype
        WHEN 'r' THEN 'Tables'
        WHEN 'S' THEN 'Sequences'
        WHEN 'f' THEN 'Functions'
        WHEN 'T' THEN 'Types'
    END AS "Object Type",
    (aclexplode(d.defaclacl)).grantee::regrole AS "Grantee",
    (aclexplode(d.defaclacl)).privilege_type AS "Privilege"
FROM pg_default_acl d  
LEFT JOIN pg_namespace n ON d.defaclnamespace = n.oid  
ORDER BY 1, 2, 3, 4;

Résultat exemple :

 Owner/Creator | Schema   | Object Type | Grantee      | Privilege
---------------+----------+-------------+--------------+-----------
 postgres      | app_data | Tables      | app_backend  | INSERT
 postgres      | app_data | Tables      | app_backend  | SELECT
 postgres      | app_data | Tables      | app_backend  | UPDATE
 postgres      | app_data | Tables      | app_readonly | SELECT
 postgres      | app_data | Sequences   | app_backend  | USAGE

6. Cas d'Usage Avancés

6.1. Environnement de Développement vs Production

Développement : Permissif

-- En dev, les développeurs ont tous les droits
ALTER DEFAULT PRIVILEGES IN SCHEMA public  
GRANT ALL PRIVILEGES ON TABLES TO developpeurs;  

ALTER DEFAULT PRIVILEGES IN SCHEMA public  
GRANT ALL PRIVILEGES ON SEQUENCES TO developpeurs;  

ALTER DEFAULT PRIVILEGES IN SCHEMA public  
GRANT ALL PRIVILEGES ON FUNCTIONS TO developpeurs;

Production : Restrictif

-- En prod, séparation stricte des rôles
-- Application : seulement ce qui est nécessaire
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_production;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_production;  

-- Lecture seule : analystes et reporting
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO readonly_group;  

-- Admin : tous les droits pour maintenance
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT ALL PRIVILEGES ON TABLES TO admin_production;

6.2. Architecture Multi-Tenant par Schéma

Chaque client a son propre schéma, avec des permissions automatiques :

-- Fonction pour créer un nouveau tenant avec default privileges
CREATE OR REPLACE FUNCTION create_tenant(tenant_name TEXT)  
RETURNS VOID AS $$  
DECLARE  
    schema_name TEXT;
    app_role TEXT;
    admin_role TEXT;
BEGIN
    schema_name := 'tenant_' || tenant_name;
    app_role := 'app_' || tenant_name;
    admin_role := 'admin_' || tenant_name;

    -- Créer le schéma
    EXECUTE format('CREATE SCHEMA %I', schema_name);

    -- Créer les rôles
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD ''changeme''', app_role);
    EXECUTE format('CREATE ROLE %I LOGIN PASSWORD ''changeme''', admin_role);

    -- Permissions sur le schéma
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schema_name, app_role);
    EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', schema_name, admin_role);

    -- 🌟 Default privileges pour l'application
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT, INSERT, UPDATE ON TABLES TO %I',
                   schema_name, app_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON SEQUENCES TO %I',
                   schema_name, app_role);

    -- 🌟 Default privileges pour l'admin
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON TABLES TO %I',
                   schema_name, admin_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON SEQUENCES TO %I',
                   schema_name, admin_role);

    RAISE NOTICE 'Tenant % créé avec succès', tenant_name;
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT create_tenant('acme_corp');  
SELECT create_tenant('globex');

Maintenant, chaque fois qu'une nouvelle table est créée dans le schéma d'un tenant, les permissions sont automatiquement configurées !

6.3. Pipeline CI/CD avec Migrations

Dans un pipeline CI/CD moderne avec des migrations de base de données (Flyway, Liquibase, Alembic) :

-- Script d'initialisation (exécuté une fois)
-- init_permissions.sql

-- Rôle qui exécute les migrations
CREATE ROLE migration_runner LOGIN PASSWORD 'secure_pass';  
GRANT CREATE ON SCHEMA app_data TO migration_runner;  

-- Rôle de l'application
CREATE ROLE app_backend LOGIN PASSWORD 'secure_pass';  
GRANT USAGE ON SCHEMA app_data TO app_backend;  

-- 🌟 Default privileges : les objets créés par migration_runner
-- seront automatiquement accessibles à app_backend
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_data  
GRANT EXECUTE ON FUNCTIONS TO app_backend;

Workflow :

  1. Le pipeline CI/CD se connecte avec migration_runner
  2. Il crée des tables, fonctions, etc.
  3. app_backend a automatiquement accès à tout ce qui est créé
  4. Pas besoin de GRANT manuels dans chaque migration !

6.4. Équipes Multiples avec Créateurs Différents

Dans une grande organisation avec plusieurs équipes :

-- Équipe Backend crée des tables applicatives
CREATE ROLE equipe_backend;

ALTER DEFAULT PRIVILEGES FOR ROLE equipe_backend IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_production;  

-- Équipe Analytics crée des tables de reporting
CREATE ROLE equipe_analytics;

ALTER DEFAULT PRIVILEGES FOR ROLE equipe_analytics IN SCHEMA reporting  
GRANT SELECT ON TABLES TO data_scientists;  

-- Équipe DevOps crée des tables de monitoring
CREATE ROLE equipe_devops;

ALTER DEFAULT PRIVILEGES FOR ROLE equipe_devops IN SCHEMA monitoring  
GRANT SELECT ON TABLES TO grafana_user;

7. Pièges et Erreurs Courantes

7.1. Piège #1 : Oublier FOR ROLE

❌ Erreur courante :

-- En tant que postgres (superuser)
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;  

-- Un développeur crée une table
-- (connecté en tant que developpeur, pas postgres)
CREATE TABLE app_data.new_table (id INTEGER);

-- app_backend n'a PAS les permissions ! 😱

Pourquoi ? Les default privileges s'appliquent au rôle qui exécute ALTER DEFAULT PRIVILEGES, pas à tous les créateurs potentiels.

✅ Solution :

-- Spécifier explicitement FOR ROLE
ALTER DEFAULT PRIVILEGES FOR ROLE developpeur IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;  

-- Ou, en tant que postgres, pour soi-même
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;

7.2. Piège #2 : Objets Existants Non Couverts

ALTER DEFAULT PRIVILEGES ne s'applique qu'aux futurs objets, pas aux existants.

❌ Erreur :

-- Des tables existent déjà
CREATE TABLE app_data.old_table (id INTEGER);

-- Configuration des default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;  

-- app_backend ne peut PAS accéder à old_table ! 😱

✅ Solution : Toujours combiner avec GRANT pour les objets existants :

-- Permissions sur les objets EXISTANTS
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_backend;

-- Permissions sur les FUTURS objets
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;

7.3. Piège #3 : Oublier les Séquences

Les colonnes SERIAL/IDENTITY créent des séquences. Il faut configurer les default privileges pour les séquences aussi :

❌ Erreur :

-- Seulement les tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT ON TABLES TO app_backend;  

-- Nouvelle table avec SERIAL
CREATE TABLE app_data.orders (
    id SERIAL PRIMARY KEY,  -- Crée une séquence !
    total NUMERIC
);

-- INSERT échoue !
INSERT INTO app_data.orders (total) VALUES (100);
-- ❌ Erreur : permission denied for sequence orders_id_seq

✅ Solution :

-- Toujours inclure les séquences
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;  -- ✅

7.4. Piège #4 : Schémas Multiples

ALTER DEFAULT PRIVILEGES s'applique par schéma. Si vous utilisez plusieurs schémas, répétez la configuration :

-- Configuration pour CHAQUE schéma
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1  
GRANT SELECT ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA schema2  
GRANT SELECT ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA schema3  
GRANT SELECT ON TABLES TO app_backend;  

-- Ou créer une fonction helper pour éviter la répétition
CREATE OR REPLACE FUNCTION setup_default_privileges(schema_name TEXT, role_name TEXT)  
RETURNS VOID AS $$  
BEGIN  
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT, INSERT, UPDATE ON TABLES TO %I',
                   schema_name, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON SEQUENCES TO %I',
                   schema_name, role_name);
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT setup_default_privileges('schema1', 'app_backend');  
SELECT setup_default_privileges('schema2', 'app_backend');

8. Bonnes Pratiques

8.1. Configurer les Default Privileges Dès le Début

Meilleur moment : Lors de la création de la base de données et des schémas.

-- Template de configuration initiale
CREATE DATABASE nouvelle_app;
\c nouvelle_app

-- Sécurisation
REVOKE ALL ON DATABASE nouvelle_app FROM PUBLIC;  
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  

-- Schémas
CREATE SCHEMA app_data;  
CREATE SCHEMA app_logs;  

-- Rôles
CREATE ROLE app_backend LOGIN PASSWORD 'secure_pass';  
CREATE ROLE app_readonly LOGIN PASSWORD 'secure_pass';  

-- Permissions schéma
GRANT USAGE ON SCHEMA app_data, app_logs TO app_backend, app_readonly;

-- 🌟 Default privileges (dès le début !)
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_readonly;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_logs  
GRANT SELECT, INSERT ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES IN SCHEMA app_logs  
GRANT USAGE ON SEQUENCES TO app_backend;

8.2. Documenter les Default Privileges

Commentez vos configurations :

-- ==================================================
-- DEFAULT PRIVILEGES CONFIGURATION
-- Appliqué par: DBA Team
-- Date: 2024-01-15
-- Raison: Automatiser les permissions pour CI/CD
-- ==================================================

-- Application backend : lecture/écriture complète
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

-- Analystes : lecture seule
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_data  
GRANT SELECT ON TABLES TO analytics_team;  

-- ==================================================

8.3. Utiliser FOR ROLE Systématiquement

Spécifiez toujours FOR ROLE pour être explicite, même si c'est pour votre propre rôle :

-- ✅ Explicite et clair
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;  

-- ❌ Ambigu (s'applique au rôle courant, mais lequel ?)
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data  
GRANT SELECT ON TABLES TO app_backend;

8.4. Script de Vérification

Créez un script pour vérifier que les default privileges sont correctement configurés :

-- verify_default_privileges.sql
DO $$  
DECLARE  
    expected_count INTEGER;
    actual_count INTEGER;
BEGIN
    RAISE NOTICE '=== VERIFICATION DEFAULT PRIVILEGES ===';

    -- Vérifier les default privileges sur les tables dans app_data
    SELECT COUNT(*) INTO actual_count
    FROM pg_default_acl d
    JOIN pg_namespace n ON d.defaclnamespace = n.oid
    WHERE n.nspname = 'app_data'
    AND d.defaclobjtype = 'r';  -- 'r' = tables

    expected_count := 1;  -- On attend 1 configuration

    IF actual_count >= expected_count THEN
        RAISE NOTICE '✓ Default privileges configurés sur tables: % configurations', actual_count;
    ELSE
        RAISE WARNING '✗ Default privileges MANQUANTS sur tables ! Trouvé: %, Attendu: au moins %',
                      actual_count, expected_count;
    END IF;

    -- Vérifier les séquences
    SELECT COUNT(*) INTO actual_count
    FROM pg_default_acl d
    JOIN pg_namespace n ON d.defaclnamespace = n.oid
    WHERE n.nspname = 'app_data'
    AND d.defaclobjtype = 'S';  -- 'S' = sequences

    IF actual_count >= 1 THEN
        RAISE NOTICE '✓ Default privileges configurés sur séquences';
    ELSE
        RAISE WARNING '✗ Default privileges MANQUANTS sur séquences !';
    END IF;
END $$;

8.5. Inclure dans les Scripts de Migration

Intégrez ALTER DEFAULT PRIVILEGES dans vos scripts d'infrastructure as code :

-- migrations/V001__init_database.sql (Flyway)

-- Création des structures
CREATE SCHEMA app_data;  
CREATE ROLE app_backend LOGIN PASSWORD '${APP_PASSWORD}';  

-- Permissions de base
GRANT USAGE ON SCHEMA app_data TO app_backend;

-- Default privileges pour le futur
ALTER DEFAULT PRIVILEGES FOR ROLE ${MIGRATION_USER} IN SCHEMA app_data  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE ${MIGRATION_USER} IN SCHEMA app_data  
GRANT USAGE ON SEQUENCES TO app_backend;

8.6. Audit Régulier

Vérifiez régulièrement que les default privileges sont toujours pertinents :

-- Lister tous les default privileges configurés
SELECT
    pg_get_userbyid(d.defaclrole) AS creator,
    COALESCE(n.nspname, 'ALL') AS schema,
    CASE d.defaclobjtype
        WHEN 'r' THEN 'tables'
        WHEN 'S' THEN 'sequences'
        WHEN 'f' THEN 'functions'
    END AS type,
    d.defaclacl AS acl
FROM pg_default_acl d  
LEFT JOIN pg_namespace n ON d.defaclnamespace = n.oid  
ORDER BY 1, 2, 3;

9. Supprimer les Default Privileges

Si vous souhaitez supprimer complètement une configuration de default privileges :

9.1. Révoquer Toutes les Permissions Par Défaut

-- Révoquer tous les default privileges d'un rôle sur les tables
ALTER DEFAULT PRIVILEGES FOR ROLE developpeur IN SCHEMA app_data  
REVOKE ALL PRIVILEGES ON TABLES FROM app_backend;  

-- Révoquer pour les séquences
ALTER DEFAULT PRIVILEGES FOR ROLE developpeur IN SCHEMA app_data  
REVOKE ALL PRIVILEGES ON SEQUENCES FROM app_backend;

9.2. Vérifier la Suppression

-- Vérifier qu'il n'y a plus de default privileges
\ddp app_data

10. Comparaison : GRANT vs ALTER DEFAULT PRIVILEGES

Aspect GRANT ALTER DEFAULT PRIVILEGES
Cible Objets existants Futurs objets
Moment d'application Immédiat À la création de l'objet
Durée Permanent (jusqu'à REVOKE) Permanent (règle pour le futur)
Modification rétroactive Oui (affecte immédiatement) Non (pas d'effet sur l'existant)
Cas d'usage Corriger les permissions Automatiser les permissions

Conclusion : Utilisez LES DEUX :

  • GRANT pour les objets qui existent déjà
  • ALTER DEFAULT PRIVILEGES pour les objets qui seront créés

11. Exemple Complet : Configuration Production

Voici un exemple complet de configuration pour un environnement de production :

-- ========================================
-- CONFIGURATION PRODUCTION
-- Database: app_production
-- ========================================

-- Connexion
\c app_production

-- ----------------------------------------
-- 1. SÉCURISATION DE BASE
-- ----------------------------------------
REVOKE ALL ON DATABASE app_production FROM PUBLIC;  
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  

-- ----------------------------------------
-- 2. CRÉATION DES SCHÉMAS
-- ----------------------------------------
CREATE SCHEMA app_core;       -- Tables métier principales  
CREATE SCHEMA app_analytics;  -- Tables analytiques  
CREATE SCHEMA app_audit;      -- Logs d'audit  

-- ----------------------------------------
-- 3. CRÉATION DES RÔLES
-- ----------------------------------------
-- Application principale
CREATE ROLE app_backend LOGIN PASSWORD 'CHANGE_ME_IN_PROD';

-- Service de reporting
CREATE ROLE app_reporting LOGIN PASSWORD 'CHANGE_ME_IN_PROD';

-- Équipe data science
CREATE ROLE data_scientists;

-- Rôle pour les migrations (utilisé par CI/CD)
CREATE ROLE migration_runner LOGIN PASSWORD 'CHANGE_ME_IN_PROD';

-- ----------------------------------------
-- 4. PERMISSIONS SUR LES SCHÉMAS
-- ----------------------------------------
GRANT USAGE ON SCHEMA app_core TO app_backend, app_reporting, data_scientists;  
GRANT CREATE ON SCHEMA app_core TO migration_runner;  

GRANT USAGE ON SCHEMA app_analytics TO app_reporting, data_scientists;  
GRANT CREATE ON SCHEMA app_analytics TO migration_runner;  

GRANT USAGE ON SCHEMA app_audit TO app_backend;  
GRANT CREATE ON SCHEMA app_audit TO app_backend;  

-- ----------------------------------------
-- 5. PERMISSIONS SUR OBJETS EXISTANTS
-- ----------------------------------------
-- (Au cas où des tables existent déjà)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_core TO app_backend;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_core TO app_backend;  

GRANT SELECT ON ALL TABLES IN SCHEMA app_core TO app_reporting;  
GRANT SELECT ON ALL TABLES IN SCHEMA app_analytics TO app_reporting, data_scientists;  

-- ----------------------------------------
-- 6. 🌟 DEFAULT PRIVILEGES (Le cœur !)
-- ----------------------------------------

-- Tables créées par migration_runner dans app_core
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_core  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_core  
GRANT SELECT ON TABLES TO app_reporting;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_core  
GRANT USAGE ON SEQUENCES TO app_backend;  

-- Tables créées par migration_runner dans app_analytics
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_analytics  
GRANT SELECT ON TABLES TO app_reporting, data_scientists;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_analytics  
GRANT USAGE ON SEQUENCES TO app_reporting;  

-- Tables d'audit créées par l'application elle-même
ALTER DEFAULT PRIVILEGES FOR ROLE app_backend IN SCHEMA app_audit  
GRANT SELECT, INSERT ON TABLES TO app_backend;  

ALTER DEFAULT PRIVILEGES FOR ROLE app_backend IN SCHEMA app_audit  
GRANT USAGE ON SEQUENCES TO app_backend;  

-- Fonctions : sécurité maximale
ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_core  
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;  

ALTER DEFAULT PRIVILEGES FOR ROLE migration_runner IN SCHEMA app_core  
GRANT EXECUTE ON FUNCTIONS TO app_backend;  

-- ----------------------------------------
-- 7. VÉRIFICATION
-- ----------------------------------------
\echo '=== Vérification Default Privileges ==='
\ddp app_core
\ddp app_analytics
\ddp app_audit

\echo '=== Configuration terminée ==='

Conclusion

ALTER DEFAULT PRIVILEGES est une fonctionnalité essentielle pour :

  • ✅ Automatiser : Plus besoin de GRANT à chaque création d'objet
  • ✅ Sécuriser : Les permissions sont consistantes et prédictibles
  • ✅ Simplifier : Moins d'erreurs humaines et de permissions manquantes
  • ✅ Intégrer : S'intègre parfaitement dans les pipelines CI/CD
  • ✅ Maintenir : Configuration centralisée et documentée

Points Clés à Retenir

  1. Utilisez TOUJOURS ALTER DEFAULT PRIVILEGES dans vos bases de production
  2. Combinez avec GRANT pour couvrir les objets existants ET futurs
  3. Spécifiez FOR ROLE pour être explicite sur qui crée les objets
  4. N'oubliez pas les séquences (SERIAL/IDENTITY)
  5. Configurez dès le début de votre projet
  6. Documentez vos choix de configuration
  7. Vérifiez régulièrement avec \ddp ou des requêtes SQL

ALTER DEFAULT PRIVILEGES transforme la gestion des permissions d'un processus manuel et source d'erreurs en un système automatisé et fiable. C'est un investissement minimal pour un gain énorme en robustesse et en sérénité ! 🎯

⏭️ Rôles, Groupes et principe du moindre privilège