Contrôle d'accès basé sur les rôles (RBAC) avancé dans PostgreSQL : sécurité au niveau des lignes et gestion des politiques

Introduction

La sécurité au niveau des lignes (Row-Level Security - RLS) de PostgreSQL offre un contrôle d'accès granulaire au niveau de la ligne, vous permettant d'appliquer des règles d'autorisation complexes directement dans la base de données. Cela élimine la nécessité d'un filtrage au niveau de la couche applicative et garantit la sécurité des données, quelle que soit la manière dont les applications accèdent à la base de données.

Pourquoi la sécurité au niveau des lignes ?

Problèmes de l'approche traditionnelle

Filtrage au niveau de la couche applicative :

# Chaque requête doit penser à filtrer
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

Problèmes :
- Oubli facile des filtres (vulnérabilités de sécurité)
- Duplication du code dans toute l'application
- Aucune protection pour l'accès direct à la base de données
- Exigences d'audit complexes

Avantages de la sécurité au niveau des lignes

• Application automatique : Politiques appliquées de manière transparente
• Règles centralisées : Logique de sécurité regroupée en un seul endroit
• Isolation multi-locataire (Multi-tenant) : Idéal pour les applications SaaS
• Conformité de l'audit : Garanties de sécurité intégrées
• Performance : Planification efficace des requêtes

Configuration de base de la RLS

Étape 1 : Activer la RLS sur la table

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner_id INTEGER NOT NULL,
    department VARCHAR(50),
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Activer la sécurité au niveau des lignes
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Étape 2 : Créer des rôles

-- Créer différents rôles d'utilisateur
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Accorder l'accès à la table
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO regular_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO department_manager;
GRANT ALL ON documents TO admin;

Étape 3 : Créer des politiques (Policies)

-- Politique : Les utilisateurs peuvent voir leurs propres documents
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Politique : Les utilisateurs peuvent insérer des documents qui leur appartiennent
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Politique : Les utilisateurs peuvent mettre à jour leurs propres documents
CREATE POLICY user_update_own ON documents
    FOR UPDATE
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Étape 4 : Définir les variables de session

Dans le code de l'application :

import psycopg2

# Connexion en tant que rôle
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Définir le contexte utilisateur
cursor = conn.cursor()
cursor.execute("SET app.user_id = %s", [current_user_id])

# Maintenant, les requêtes sont automatiquement filtrées
cursor.execute("SELECT * FROM documents")
# Retourne uniquement les documents où owner_id = current_user_id

Application SaaS Multi-Tenant

Configuration Multi-Tenant complète

-- Créer la table des locataires (tenants)
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Créer la table des utilisateurs
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    tenant_id INTEGER REFERENCES tenants(id),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Créer la table des données d'application
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Activer la RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Politique : Les utilisateurs ne voient que les commandes de leur locataire
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    TO public
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

Intégration Applicative

Exemple Django :

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if request.user.is_authenticated:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SET app.tenant_id = %s",
                    [request.user.tenant_id]
                )

        response = self.get_response(request)
        return response

Exemple Node.js :

const { Pool } = require('pg');
const pool = new Pool();

async function setTenantContext(tenantId) {
    const client = await pool.connect();
    try {
        await client.query('SET app.tenant_id = $1', [tenantId]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

// Utilisation
app.use(async (req, res, next) => {
    if (req.user) {
        req.dbClient = await setTenantContext(req.user.tenantId);
    }
    next();
});

Modèles de politiques avancés

1. Contrôle d'accès hiérarchique

-- Les utilisateurs peuvent voir les documents de leur département et ceux publics
CREATE POLICY department_access ON documents
    FOR SELECT
    TO regular_user
    USING (
        department = current_setting('app.user_department', true)
        OR is_public = true
        OR owner_id = current_setting('app.user_id')::INTEGER
    );

2. Accès basé sur le temps

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- Politique : Les utilisateurs ne voient que les abonnements actifs
CREATE POLICY active_subscriptions ON subscriptions
    FOR SELECT
    TO public
    USING (
        user_id = current_setting('app.user_id')::INTEGER
        AND NOW() BETWEEN valid_from AND valid_until
    );

3. Accès basé sur les rôles avec logique complexe

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    owner_id INTEGER,
    team_id INTEGER,
    visibility VARCHAR(20) -- 'private', 'team', 'public'
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Politique de visibilité complexe
CREATE POLICY project_visibility ON projects
    FOR SELECT
    TO public
    USING (
        CASE current_setting('app.user_role', true)
            WHEN 'admin' THEN true
            WHEN 'manager' THEN (
                team_id = current_setting('app.user_team_id')::INTEGER
                OR visibility = 'public'
            )
            ELSE (
                owner_id = current_setting('app.user_id')::INTEGER
                OR visibility = 'public'
            )
        END
    );

4. Listes d'accès partagé

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    owner_id INTEGER NOT NULL
);

CREATE TABLE document_shares (
    document_id INTEGER REFERENCES documents(id),
    shared_with_user_id INTEGER,
    permission VARCHAR(20), -- 'read', 'write'
    PRIMARY KEY (document_id, shared_with_user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Politique : Les utilisateurs voient les documents possédés ou partagés
CREATE POLICY document_access ON documents
    FOR SELECT
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
        )
    );

-- Politique : Les utilisateurs modifient les documents possédés ou partagés avec permission d'écriture
CREATE POLICY document_modify ON documents
    FOR UPDATE
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
              AND permission = 'write'
        )
    );

Contourner la RLS pour les tâches administratives

Option 1 : Attribut de rôle BYPASSRLS

-- Accorder un accès de type super-utilisateur
CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

-- L'administrateur peut voir toutes les lignes quelles que soient les politiques

Option 2 : Politiques permissives pour les administrateurs

-- Créer une politique permissive pour le rôle admin
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

Option 3 : Désactiver temporairement la RLS

-- Pour les scripts de maintenance
BEGIN;
    ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
    -- Effectuer les opérations administratives
    UPDATE documents SET archived = true WHERE created_at < NOW() - INTERVAL '1 year';
    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
COMMIT;

Types de politiques : USING vs WITH CHECK

Clause USING

Contrôle les lignes existantes qui sont visibles/modifiables :

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_user_id());

-- L'utilisateur ne peut SÉLECTIONNER que les lignes où l'owner_id correspond

Clause WITH CHECK

Contrôle les nouvelles lignes ou les lignes mises à jour qui sont autorisées :

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_user_id());

-- L'utilisateur ne peut INSÉRER que des lignes où l'owner_id correspond

Politiques combinées

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_user_id())       -- Ne peut mettre à jour que ses propres lignes
    WITH CHECK (owner_id = current_user_id()); -- Ne peut pas changer le propriétaire

Considérations de performance

1. Indexer les colonnes de politique

-- Créer des index pour les colonnes utilisées dans les politiques
CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_department ON documents(department);

2. Analyser les plans de requête

EXPLAIN ANALYZE
SELECT * FROM documents
WHERE title LIKE '%report%';

-- Vérifier si le filtre de politique est appliqué efficacement

3. Éviter les sous-requêtes dans les politiques

Mauvais - Sous-requête exécutée pour chaque ligne :

CREATE POLICY slow_policy ON documents
    USING (
        id IN (SELECT document_id FROM shares WHERE user_id = current_user_id())
    );

Mieux - Utiliser EXISTS :

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_user_id()
        )
    );

Idéal - Utiliser une JOIN dans l'application ou une vue matérialisée :

CREATE MATERIALIZED VIEW user_accessible_documents AS
SELECT d.*, s.user_id AS shared_with
FROM documents d
LEFT JOIN shares s ON d.id = s.document_id;

CREATE INDEX ON user_accessible_documents(shared_with);

Surveillance et Audit

Afficher les politiques actives

-- Lister toutes les politiques RLS
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

Auditer les changements de politique

-- Créer un journal d'audit pour les changements de politique
CREATE TABLE policy_audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    policy_name VARCHAR(100),
    action VARCHAR(20),
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Fonction de déclenchement (trigger) pour enregistrer les changements
CREATE OR REPLACE FUNCTION log_policy_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO policy_audit_log (table_name, policy_name, action, changed_by)
    SELECT 
        objid::regclass::text,
        object_identity,
        'CREATED',
        current_user
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE POLICY';
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_policy_create
    ON ddl_command_end
    WHEN TAG IN ('CREATE POLICY')
    EXECUTE FUNCTION log_policy_changes();

Tester l'efficacité des politiques

-- Créer une fonction de test
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- Définir les variables de session
    PERFORM set_config('app.user_id', test_user_id::text, true);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, true);

    -- Retourner les documents accessibles
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Tester l'accès
SELECT * FROM test_user_access(123, 5);

Dépannage

Problème 1 : Politiques non appliquées

Vérifier si la RLS est activée :

SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'documents';

Activer si désactivée :

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Problème 2 : Variables de session non définies

Déboguer les paramètres actuels :

SHOW app.user_id;
SHOW app.tenant_id;

-- Ou utiliser current_setting
SELECT current_setting('app.user_id', true);

Problème 3 : Dégradation des performances

Identifier les politiques lentes :

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM documents WHERE title = 'test';

-- Chercher les opérations "Filter" avec les conditions de politique

Solution : Simplifier les politiques ou ajouter des index

Problème 4 : Refus d'accès inattendu

Tester en tant qu'utilisateur spécifique :

SET ROLE regular_user;
SET app.user_id = '123';

SELECT * FROM documents;
-- Affiche ce que cet utilisateur peut réellement voir

RESET ROLE;

Bonnes pratiques

1. Toujours définir les variables de session : S'assurer que les valeurs current_setting() sont renseignées
2. Indexer les colonnes de politique : Créer des index sur toutes les colonnes utilisées dans USING/WITH CHECK
3. Tester minutieusement : Vérifier les politiques avec différents rôles et scénarios
4. Garder les politiques simples : Une logique complexe impacte les performances
5. Utiliser EXPLAIN : Analyser les plans de requête avec la RLS activée
6. Documenter les politiques : Commenter la logique des politiques pour la maintenance
7. Séparer les préoccupations : Utiliser des politiques différentes pour SELECT, INSERT, UPDATE, DELETE
8. Surveiller les performances : Mettre en place des alertes pour les requêtes lentes

Conclusion

La sécurité au niveau des lignes de PostgreSQL offre un contrôle d'accès robuste, appliqué directement par la base de données :

• Filtrage automatique : Sécurité appliquée de manière transparente
• Isolation multi-locataire : Parfait pour les applications SaaS
• Politiques flexibles : Prise en charge de règles d'autorisation complexes
• Sécurité centralisée : Logique dans la base de données, pas dispersée dans le code

Commencez par une isolation simple des locataires (tenants), testez les impacts sur les performances, puis étendez vers des modèles d'autorisation plus sophistiqués selon les besoins.