Skip to content

04.2-permissions-schema-database.md

Latest commit

 

History

History
916 lines (664 loc) · 25.7 KB

04.2-permissions-schema-database.md

File metadata and controls

916 lines (664 loc) · 25.7 KB

🔝 Retour au Sommaire

16.4.2. Permissions de Schéma et Database

Introduction

Dans PostgreSQL, la hiérarchie des objets suit une structure à plusieurs niveaux : Instance → Database → Schema → Objets (Tables, Fonctions, etc.). Avant même de pouvoir accéder aux tables ou fonctions, un utilisateur doit avoir les permissions appropriées au niveau de la base de données et du schéma.

Cette section explique comment gérer les permissions à ces deux niveaux essentiels, qui sont souvent négligés par les débutants mais cruciaux pour une configuration sécurisée.

1. Comprendre la Hiérarchie PostgreSQL

1.1. Structure Logique

Instance PostgreSQL (Cluster)
    ├── Database 1 (ex: production)
    │   ├── Schema public
    │   │   ├── Table clients
    │   │   ├── Table commandes
    │   │   └── Function calculer_tva()
    │   ├── Schema marketing
    │   │   └── Table campagnes
    │   └── Schema finance
    │       └── Table factures
    ├── Database 2 (ex: dev)
    └── Database 3 (ex: test)

1.2. Pourquoi Cette Hiérarchie Importe ?

Pour qu'un utilisateur puisse accéder à une table, il doit avoir :

  1. ✅ Permission CONNECT sur la database
  2. ✅ Permission USAGE sur le schema
  3. ✅ Permission appropriée sur l'objet (SELECT, INSERT, etc.)

Si l'un de ces trois niveaux est manquant, l'accès sera refusé, même si les autres permissions sont accordées.

2. Permissions sur les DATABASES

2.1. Les Différents Types de Permissions

Permission Description Cas d'usage
CONNECT Se connecter à la base de données Nécessaire pour tout accès
CREATE Créer de nouveaux schémas dans la database Développeurs, DBAs
TEMP/TEMPORARY Créer des tables temporaires Sessions de travail, analyses
ALL PRIVILEGES Toutes les permissions ci-dessus Propriétaire ou administrateur

2.2. La Permission CONNECT

C'est la permission la plus fondamentale. Sans elle, impossible de se connecter à la base de données.

Comportement par Défaut

Par défaut, PostgreSQL accorde CONNECT à PUBLIC sur les nouvelles bases de données. Cela signifie que tous les utilisateurs peuvent se connecter à toutes les bases.

-- Vérifier les permissions par défaut
\l+

-- Ou via SQL
SELECT datname, datacl  
FROM pg_database  
WHERE datname = 'production';

Résultat typique :

  datname    |          datacl
-------------+---------------------------
 production  | {=Tc/postgres,postgres=CTc/postgres}

Le =Tc signifie que PUBLIC a les permissions TEMP et CONNECT.

Exemple 1 : Problème Courant - Utilisateur Sans CONNECT

Créons un utilisateur qui ne peut pas se connecter :

-- Créer un utilisateur
CREATE ROLE user_bloque LOGIN PASSWORD 'mot_de_passe';

-- Révoquer CONNECT pour tout le monde sur la database
REVOKE CONNECT ON DATABASE production FROM PUBLIC;

-- L'utilisateur essaie de se connecter
psql -U user_bloque -d production
-- ❌ Erreur : FATAL: permission denied for database "production"

Exemple 2 : Solution - Accorder CONNECT

-- Accorder explicitement CONNECT
GRANT CONNECT ON DATABASE production TO user_bloque;

-- Maintenant la connexion fonctionne
psql -U user_bloque -d production  -- ✅ OK

Exemple 3 : Sécuriser une Base de Données

Pour une base de données de production, il est recommandé de révoquer l'accès public et d'accorder explicitement les permissions :

-- Révoquer l'accès public
REVOKE ALL ON DATABASE production FROM PUBLIC;

-- Créer des rôles spécifiques
CREATE ROLE app_production LOGIN PASSWORD 'secure_password';  
CREATE ROLE lecteur_production LOGIN PASSWORD 'secure_password';  

-- Accorder CONNECT uniquement aux rôles autorisés
GRANT CONNECT ON DATABASE production TO app_production;  
GRANT CONNECT ON DATABASE production TO lecteur_production;

2.3. La Permission CREATE

Cette permission permet de créer de nouveaux schémas dans la base de données.

Exemple 1 : Créer un Schéma

-- Accorder CREATE sur la database
GRANT CREATE ON DATABASE production TO developpeur;

-- Le développeur peut maintenant créer des schémas
CREATE SCHEMA mon_nouveau_schema;  -- ✅ OK

⚠️ Attention : Cette permission est puissante. Un utilisateur avec CREATE peut créer des schémas et y placer des objets. Réservez-la aux développeurs et DBAs.

Exemple 2 : Environnement de Développement

Dans un environnement de développement, vous pouvez être plus permissif :

-- Base de données de dev
CREATE DATABASE dev;

-- Créer un rôle développeur
CREATE ROLE developpeurs;

-- Accorder CREATE pour expérimenter
GRANT CREATE ON DATABASE dev TO developpeurs;

-- Ajouter des utilisateurs au groupe
CREATE ROLE dev1 LOGIN PASSWORD 'pass';  
GRANT developpeurs TO dev1;

2.4. La Permission TEMPORARY

Cette permission permet de créer des tables temporaires durant une session.

Utilité des Tables Temporaires

Les tables temporaires sont utiles pour :

  • Stocker des résultats intermédiaires
  • Analyser des données sans modifier la base
  • Effectuer des calculs complexes

Exemple

-- Accorder la permission TEMP
GRANT TEMP ON DATABASE production TO analyste;

-- L'analyste peut créer des tables temporaires
CREATE TEMP TABLE calculs_intermediaires (
    id INTEGER,
    resultat NUMERIC
);

-- La table est automatiquement supprimée à la fin de la session

2.5. Révoquer des Permissions sur Database

-- Révoquer CONNECT
REVOKE CONNECT ON DATABASE production FROM utilisateur;

-- Révoquer CREATE
REVOKE CREATE ON DATABASE production FROM developpeur;

-- Révoquer toutes les permissions
REVOKE ALL PRIVILEGES ON DATABASE production FROM utilisateur;

2.6. Vérifier les Permissions sur Database

Via psql

-- Lister les databases et leurs permissions
\l

-- Avec détails
\l+

Via SQL

-- Permissions sur une database spécifique
SELECT
    datname AS database,
    datacl AS permissions
FROM pg_database  
WHERE datname = 'production';  

-- Décoder les permissions (plus lisible)
SELECT
    datname,
    (aclexplode(datacl)).grantee::regrole AS role,
    (aclexplode(datacl)).privilege_type AS privilege
FROM pg_database  
WHERE datname = 'production';

3. Permissions sur les SCHEMAS

3.1. Qu'est-ce qu'un Schéma ?

Un schéma est un namespace (espace de noms) qui contient des objets de base de données (tables, vues, fonctions, etc.). Il sert à :

  • Organiser les objets logiquement
  • Isoler différentes applications ou modules
  • Gérer les permissions de manière granulaire
  • Éviter les conflits de noms

Le Schéma "public"

Par défaut, PostgreSQL crée un schéma nommé public dans chaque base de données. Si vous ne spécifiez pas de schéma, les objets sont créés dans public.

-- Ces deux commandes sont équivalentes
CREATE TABLE clients (id SERIAL);  
CREATE TABLE public.clients (id SERIAL);

3.2. Le Concept de search_path

Le search_path détermine l'ordre dans lequel PostgreSQL cherche les objets lorsqu'ils ne sont pas qualifiés par un schéma.

-- Voir le search_path actuel
SHOW search_path;
-- Résultat typique : "$user", public

-- Modifier le search_path
SET search_path TO mon_schema, public;

Explication :

  • PostgreSQL cherche d'abord dans mon_schema
  • Puis dans public si l'objet n'est pas trouvé dans mon_schema

3.3. Les Différents Types de Permissions

Permission Description Cas d'usage
USAGE Accéder aux objets du schéma Nécessaire pour tout accès aux objets
CREATE Créer de nouveaux objets dans le schéma Développeurs qui doivent créer tables/fonctions
ALL PRIVILEGES USAGE + CREATE Propriétaire ou administrateur du schéma

3.4. La Permission USAGE

C'est la permission essentielle pour accéder aux objets d'un schéma. Sans elle, même si vous avez SELECT sur une table, vous ne pourrez pas y accéder.

Exemple 1 : Problème Courant - USAGE Manquant

Créons un schéma et une table :

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

-- Créer une table dans ce schéma
CREATE TABLE marketing.campagnes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    budget NUMERIC
);

-- Créer un utilisateur
CREATE ROLE utilisateur_marketing LOGIN PASSWORD 'pass';

-- Accorder SELECT sur la table
GRANT SELECT ON TABLE marketing.campagnes TO utilisateur_marketing;

-- L'utilisateur essaie d'accéder
SELECT * FROM marketing.campagnes;
-- ❌ Erreur : permission denied for schema marketing

Pourquoi ? L'utilisateur a SELECT sur la table, mais pas USAGE sur le schéma.

Exemple 2 : Solution - Accorder USAGE

-- Accorder USAGE sur le schéma
GRANT USAGE ON SCHEMA marketing TO utilisateur_marketing;

-- Maintenant ça fonctionne
SELECT * FROM marketing.campagnes;  -- ✅ OK

Exemple 3 : Configuration Standard pour une Application

-- Créer des schémas pour différents modules
CREATE SCHEMA app_public;   -- Données publiques  
CREATE SCHEMA app_internal; -- Données internes  
CREATE SCHEMA app_admin;    -- Données d'administration  

-- Créer un rôle pour l'application
CREATE ROLE app_backend LOGIN PASSWORD 'secure_password';

-- Accorder USAGE sur les schémas nécessaires
GRANT USAGE ON SCHEMA app_public TO app_backend;  
GRANT USAGE ON SCHEMA app_internal TO app_backend;  
-- Pas d'accès à app_admin

-- Puis accorder les permissions sur les objets
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_public TO app_backend;  
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_internal TO app_backend;

3.5. La Permission CREATE

Cette permission permet de créer de nouveaux objets (tables, fonctions, etc.) dans le schéma.

Exemple 1 : Développeur qui Crée des Tables

-- Créer un schéma de développement
CREATE SCHEMA dev_workspace;

-- Accorder USAGE et CREATE
GRANT USAGE, CREATE ON SCHEMA dev_workspace TO developpeur;

-- Le développeur peut maintenant créer des objets
CREATE TABLE dev_workspace.test_table (id INTEGER);  -- ✅ OK  
CREATE FUNCTION dev_workspace.test_func() RETURNS INTEGER AS $$ ... $$;  -- ✅ OK

Exemple 2 : Séparation Développement / Production

-- Schéma de production (restrictif)
CREATE SCHEMA production;  
GRANT USAGE ON SCHEMA production TO app_backend;  
-- Pas de CREATE : les objets sont créés par migration

-- Schéma de développement (permissif)
CREATE SCHEMA dev;  
GRANT USAGE, CREATE ON SCHEMA dev TO developpeur;  
-- Les développeurs peuvent expérimenter librement

3.6. Comportement par Défaut du Schéma "public"

⚠️ Attention : Par défaut, PostgreSQL accorde CREATE sur le schéma public à PUBLIC (tous les utilisateurs). C'est un risque de sécurité !

-- Vérifier les permissions par défaut
\dn+ public

Résultat typique :

  Name  |  Owner   |  Access privileges
--------+----------+---------------------
 public | postgres | postgres=UC/postgres+
        |          | =UC/postgres

Le =UC signifie que PUBLIC a USAGE et CREATE sur le schéma public.

Sécuriser le Schéma Public

Bonne pratique : Révoquer CREATE du schéma public pour éviter que n'importe qui puisse créer des objets :

-- Révoquer CREATE sur public pour tout le monde
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Garder USAGE pour que les utilisateurs puissent accéder aux objets existants
-- (USAGE est déjà accordé par défaut, mais on peut le rendre explicite)
GRANT USAGE ON SCHEMA public TO PUBLIC;

Maintenant :

  • Tous les utilisateurs peuvent accéder aux objets dans public (USAGE)
  • Mais seuls les utilisateurs autorisés peuvent créer de nouveaux objets

3.7. Révoquer des Permissions sur Schéma

-- Révoquer USAGE
REVOKE USAGE ON SCHEMA marketing FROM utilisateur;

-- Révoquer CREATE
REVOKE CREATE ON SCHEMA dev FROM developpeur;

-- Révoquer toutes les permissions
REVOKE ALL PRIVILEGES ON SCHEMA marketing FROM utilisateur;

3.8. Vérifier les Permissions sur Schéma

Via psql

-- Lister les schémas et leurs permissions
\dn+

Via SQL

-- Permissions sur un schéma spécifique
SELECT
    nspname AS schema_name,
    nspacl AS permissions
FROM pg_namespace  
WHERE nspname = 'marketing';  

-- Décoder les permissions (plus lisible)
SELECT
    n.nspname AS schema_name,
    r.rolname AS grantee,
    p.privilege_type
FROM pg_namespace n  
CROSS JOIN LATERAL aclexplode(n.nspacl) AS acl  
JOIN pg_roles r ON acl.grantee = r.oid  
CROSS JOIN LATERAL (  
    SELECT
        CASE acl.privilege_type
            WHEN 'CREATE' THEN 'CREATE'
            WHEN 'USAGE' THEN 'USAGE'
        END AS privilege_type
) p
WHERE n.nspname = 'marketing';

4. Workflow Complet : Database → Schema → Objets

Pour qu'un utilisateur puisse réellement travailler avec des données, voici le workflow complet des permissions à accorder.

4.1. Exemple Complet : Application Web

-- 1. Créer la base de données
CREATE DATABASE app_production;

-- 2. Se connecter à la base
\c app_production

-- 3. Sécuriser la base de données
REVOKE ALL ON DATABASE app_production FROM PUBLIC;  
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  

-- 4. Créer des schémas
CREATE SCHEMA app_data;  
CREATE SCHEMA app_logs;  

-- 5. Créer des tables
CREATE TABLE app_data.users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE app_logs.access_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    timestamp TIMESTAMPTZ,
    action VARCHAR(50)
);

-- 6. Créer un rôle pour l'application
CREATE ROLE app_backend LOGIN PASSWORD 'secure_password_here';

-- 7. Accorder les permissions (dans l'ordre !)

-- 7a. Niveau DATABASE
GRANT CONNECT ON DATABASE app_production TO app_backend;

-- 7b. Niveau SCHEMA
GRANT USAGE ON SCHEMA app_data TO app_backend;  
GRANT USAGE ON SCHEMA app_logs TO app_backend;  

-- 7c. Niveau OBJETS
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app_data.users TO app_backend;  
GRANT INSERT ON TABLE app_logs.access_logs TO app_backend;  

-- 7d. Niveau SEQUENCES (pour les SERIAL)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO app_backend;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_logs TO app_backend;

Maintenant, app_backend peut :

  • ✅ Se connecter à app_production
  • ✅ Accéder aux schémas app_data et app_logs
  • ✅ Lire/modifier les données dans users
  • ✅ Insérer des logs dans access_logs

4.2. Exemple : Utilisateur en Lecture Seule

-- Créer un rôle pour les analystes
CREATE ROLE analyste LOGIN PASSWORD 'secure_password';

-- Permissions minimales pour lecture seule
GRANT CONNECT ON DATABASE app_production TO analyste;  
GRANT USAGE ON SCHEMA app_data TO analyste;  
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO analyste;

4.3. Exemple : Plusieurs Équipes, Plusieurs Schémas

-- Équipe Marketing
CREATE SCHEMA marketing;  
CREATE ROLE equipe_marketing;  
GRANT USAGE, CREATE ON SCHEMA marketing TO equipe_marketing;  
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA marketing TO equipe_marketing;  

-- Équipe Finance
CREATE SCHEMA finance;  
CREATE ROLE equipe_finance;  
GRANT USAGE, CREATE ON SCHEMA finance TO equipe_finance;  
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA finance TO equipe_finance;  

-- Équipe Marketing ne peut PAS accéder au schéma Finance, et vice versa
-- (pas de USAGE accordé sur le schéma de l'autre équipe)

5. Bonnes Pratiques

5.1. Toujours Sécuriser les Nouvelles Databases

Lors de la création d'une nouvelle base de données :

-- Template pour nouvelle database sécurisée
CREATE DATABASE nouvelle_base;
\c nouvelle_base

-- Sécurisation immédiate
REVOKE ALL ON DATABASE nouvelle_base FROM PUBLIC;  
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  

-- Puis accorder explicitement selon les besoins

5.2. Utiliser des Schémas pour Isoler

Au lieu de tout mettre dans public, créez des schémas logiques :

-- Mauvaise pratique : tout dans public
CREATE TABLE public.users (...);  
CREATE TABLE public.products (...);  
CREATE TABLE public.orders (...);  
CREATE TABLE public.admin_settings (...);  

-- Bonne pratique : schémas logiques
CREATE SCHEMA app;  
CREATE TABLE app.users (...);  
CREATE TABLE app.products (...);  
CREATE TABLE app.orders (...);  

CREATE SCHEMA admin;  
CREATE TABLE admin.settings (...);  
CREATE TABLE admin.audit_logs (...);

Avantages :

  • 🔒 Isolation des permissions
  • 📁 Organisation claire
  • 🎯 Contrôle granulaire

5.3. Ne Pas Oublier les Séquences

Lorsque vous accordez des permissions sur des schémas, n'oubliez pas les séquences :

-- Accorder les permissions sur le schéma
GRANT USAGE ON SCHEMA app_data TO app_backend;

-- Accorder sur les tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_data TO app_backend;

-- ⚠️ NE PAS OUBLIER les séquences !
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO app_backend;

5.4. Ordre Logique des Permissions

Accordez toujours les permissions dans cet ordre :

  1. Database (CONNECT)
  2. Schema (USAGE)
  3. Objets (SELECT, INSERT, etc.)
  4. Séquences (USAGE)

Ne sautez pas d'étapes !

5.5. Documenter l'Architecture des Schémas

-- Commentaires sur les schémas pour documentation
COMMENT ON SCHEMA app_data IS 'Données de l''application principale';  
COMMENT ON SCHEMA app_logs IS 'Logs d''audit et de traçabilité';  
COMMENT ON SCHEMA app_cache IS 'Tables de cache temporaire';  

-- Consulter les commentaires
SELECT
    schemaname,
    obj_description(oid, 'pg_namespace') AS description
FROM pg_namespace n  
JOIN pg_class c ON n.oid = c.relnamespace  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
GROUP BY schemaname, n.oid;

5.6. Tester les Permissions

Après avoir configuré les permissions, testez avec chaque rôle :

-- Se connecter en tant que l'utilisateur
\c app_production app_backend

-- Tester l'accès
SELECT * FROM app_data.users LIMIT 1;  
INSERT INTO app_logs.access_logs (user_id, action) VALUES (1, 'test');  

-- Si erreur, vérifier les permissions à chaque niveau

5.7. Automatiser avec des Scripts

Créez des scripts de gestion des permissions :

-- Script : grant_app_permissions.sql
-- Usage : Accorder les permissions standard pour l'application

BEGIN;

GRANT CONNECT ON DATABASE app_production TO :username;  
GRANT USAGE ON SCHEMA app_data TO :username;  
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO :username;  
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app_data TO :username;  

COMMIT;

-- Utilisation :
-- psql -v username=nouveau_user -f grant_app_permissions.sql

6. Scénarios Pratiques

Scénario 1 : Migration d'une Application Monolithique

Vous avez une application monolithique avec tout dans public. Vous souhaitez migrer vers une architecture multi-schémas.

-- État initial : tout dans public
-- public.users, public.orders, public.products, etc.

-- 1. Créer les nouveaux schémas
CREATE SCHEMA app_users;  
CREATE SCHEMA app_commerce;  
CREATE SCHEMA app_reports;  

-- 2. Migrer les tables (exemple simplifié)
ALTER TABLE public.users SET SCHEMA app_users;  
ALTER TABLE public.orders SET SCHEMA app_commerce;  
ALTER TABLE public.products SET SCHEMA app_commerce;  

-- 3. Mettre à jour les permissions
GRANT USAGE ON SCHEMA app_users TO app_backend;  
GRANT USAGE ON SCHEMA app_commerce TO app_backend;  
GRANT USAGE ON SCHEMA app_reports TO app_reporting;  

-- 4. Mettre à jour le search_path pour compatibilité temporaire
ALTER ROLE app_backend SET search_path TO app_users, app_commerce, public;

Scénario 2 : Base de Données Partagée entre Plusieurs Applications

Plusieurs applications partagent la même instance PostgreSQL mais doivent être isolées.

-- Database partagée
CREATE DATABASE shared_db;
\c shared_db

-- Sécurisation
REVOKE ALL ON DATABASE shared_db FROM PUBLIC;

-- Application 1
CREATE SCHEMA app1;  
CREATE ROLE app1_user LOGIN PASSWORD 'pass1';  
GRANT CONNECT ON DATABASE shared_db TO app1_user;  
GRANT USAGE, CREATE ON SCHEMA app1 TO app1_user;  
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app1 TO app1_user;  

-- Application 2
CREATE SCHEMA app2;  
CREATE ROLE app2_user LOGIN PASSWORD 'pass2';  
GRANT CONNECT ON DATABASE shared_db TO app2_user;  
GRANT USAGE, CREATE ON SCHEMA app2 TO app2_user;  
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app2 TO app2_user;  

-- Résultat : Isolation complète
-- app1_user ne peut PAS accéder au schéma app2
-- app2_user ne peut PAS accéder au schéma app1

Scénario 3 : Environnement Multi-Tenant

Chaque client (tenant) a son propre schéma avec une isolation totale.

-- Fonction pour créer un nouveau tenant
CREATE OR REPLACE FUNCTION creer_tenant(nom_tenant TEXT)  
RETURNS VOID AS $$  
DECLARE  
    schema_name TEXT;
    role_name TEXT;
BEGIN
    schema_name := 'tenant_' || nom_tenant;
    role_name := 'user_' || nom_tenant;

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

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

    -- Accorder les permissions
    EXECUTE format('GRANT USAGE, CREATE ON SCHEMA %I TO %I', schema_name, role_name);

    -- Créer les tables standard
    EXECUTE format('CREATE TABLE %I.users (id SERIAL PRIMARY KEY, name VARCHAR(100))', schema_name);

    -- Accorder les permissions sur les tables
    EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO %I', schema_name, role_name);
    EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA %I TO %I', schema_name, role_name);
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT creer_tenant('client_a');  
SELECT creer_tenant('client_b');  

-- Résultat :
-- Schéma tenant_client_a avec user_client_a
-- Schéma tenant_client_b avec user_client_b
-- Isolation totale entre les clients

7. Diagnostic et Troubleshooting

7.1. Erreur : "permission denied for database"

Cause : L'utilisateur n'a pas CONNECT sur la database.

Solution :

GRANT CONNECT ON DATABASE ma_base TO mon_utilisateur;

7.2. Erreur : "permission denied for schema"

Cause : L'utilisateur n'a pas USAGE sur le schéma.

Solution :

GRANT USAGE ON SCHEMA mon_schema TO mon_utilisateur;

7.3. Erreur : "permission denied for table"

Mais l'utilisateur a SELECT sur la table !

Cause probable : USAGE manquant sur le schéma.

Vérification :

-- Vérifier les permissions sur le schéma
SELECT
    nspname,
    nspacl
FROM pg_namespace  
WHERE nspname = 'mon_schema';  

-- Si NULL ou pas le bon rôle, accorder USAGE
GRANT USAGE ON SCHEMA mon_schema TO mon_utilisateur;

7.4. Script de Diagnostic Complet

-- Vérifier toutes les permissions pour un utilisateur
DO $$  
DECLARE  
    v_role TEXT := 'mon_utilisateur';  -- Remplacer par le nom du rôle
BEGIN
    RAISE NOTICE '=== DIAGNOSTIC PERMISSIONS POUR % ===', v_role;

    -- Database
    RAISE NOTICE 'Database: %', current_database();
    PERFORM 1 FROM pg_database d
    WHERE d.datname = current_database()
    AND has_database_privilege(v_role, d.datname, 'CONNECT');
    IF FOUND THEN
        RAISE NOTICE '  ✓ CONNECT: OUI';
    ELSE
        RAISE NOTICE '  ✗ CONNECT: NON';
    END IF;

    -- Schemas
    RAISE NOTICE 'Schemas avec USAGE:';
    FOR rec IN
        SELECT nspname
        FROM pg_namespace
        WHERE has_schema_privilege(v_role, nspname, 'USAGE')
        AND nspname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        RAISE NOTICE '  ✓ %', rec.nspname;
    END LOOP;
END $$;

8. Résumé et Checklist

8.1. Checklist de Configuration

Pour configurer correctement les permissions pour un nouvel utilisateur :

✅ Niveau Database

  • CONNECT accordé
  • TEMP accordé si nécessaire
  • CREATE refusé (sauf si développeur)

✅ Niveau Schema

  • USAGE accordé sur tous les schémas nécessaires
  • CREATE accordé uniquement si nécessaire
  • Schéma public sécurisé (CREATE révoqué de PUBLIC)

✅ Niveau Objets

  • Permissions accordées sur les tables
  • Permissions accordées sur les fonctions
  • USAGE accordé sur les séquences

✅ Vérification

  • Tester la connexion avec le rôle
  • Tester l'accès aux objets
  • Vérifier qu'aucun accès non souhaité n'existe

8.2. Points Clés à Retenir

  1. Hiérarchie stricte : Database → Schema → Objets
  2. CONNECT est obligatoire pour accéder à une database
  3. USAGE est obligatoire pour accéder aux objets d'un schéma
  4. Sécuriser le schéma public en révoquant CREATE de PUBLIC
  5. Utiliser des schémas pour isoler et organiser
  6. Ne pas oublier les séquences lors de l'attribution des permissions
  7. Tester systématiquement avec chaque rôle après configuration

Conclusion

La gestion des permissions au niveau database et schema est fondamentale pour la sécurité de PostgreSQL. Elle constitue les deux premiers niveaux de contrôle d'accès, avant même d'arriver aux objets.

Une configuration correcte à ces niveaux permet de :

  • 🔒 Sécuriser : Isolation entre applications et utilisateurs
  • 🎯 Organiser : Structure logique claire
  • 🛡️ Contrôler : Accès granulaire et traçable
  • 🚀 Scalabilité : Architecture multi-tenant possible

En maîtrisant ces concepts, vous posez les bases d'une architecture PostgreSQL robuste et sécurisée.

⏭️ Default privileges (ALTER DEFAULT PRIVILEGES)