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

La sécurité au niveau des lignes (RLS) de PostgreSQL permet à votre base de données de filtrer les lignes en fonction du rôle actuel et du contexte de session. Si votre application dessert plusieurs locataires ou mélange des accès administrateur, gestionnaire et utilisateur régulier, la RLS peut empêcher qu'un WHERE tenant_id = ... oublié ne devienne une fuite de données.

La RLS ne remplace pas l'autorisation applicative. Elle vous offre un filet de sécurité au niveau de la base de données pour la frontière la plus sensible : les lignes qu'une connexion peut lire ou écrire.

Pourquoi la sécurité au niveau des lignes ?

Problèmes de l'approche traditionnelle

Filtrage au niveau applicatif :

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

Problèmes :

Filtres faciles à oublier (vulnérabilités de sécurité)
Duplication de 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 : Les politiques sont appliquées de manière transparente
Règles centralisées : La logique de sécurité en un seul endroit
Isolation multi-locataire : Parfait pour les applications SaaS
Conformité d'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 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

-- 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 leur appartenant
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 des variables de session

Dans le code applicatif :

import psycopg2

# Se connecter 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("SELECT set_config('app.user_id', %s, false)", [str(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-locataire

Configuration multi-locataire complète

-- Créer la table des locataires
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 de données applicatives
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 voient uniquement 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(
                    "SELECT set_config('app.tenant_id', %s, false)",
                    [str(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('SELECT set_config($1, $2, false)', ['app.tenant_id', String(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 les documents 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 peuvent voir uniquement 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ées

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 peuvent voir 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 peuvent modifier 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'
        )
    );

Accès administratif et RLS

Les propriétaires de tables et les superutilisateurs peuvent contourner la RLS sauf si vous la forcez. Traitez les chemins administratifs comme une décision de conception distincte, pas une réflexion après coup.

Option 1 : Attribut de rôle BYPASSRLS

CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

Utilisez-le avec parcimonie. BYPASSRLS est puissant et ne devrait pas être accordé aux rôles applicatifs normaux.

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 : Forcer la RLS pour les propriétaires de tables

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY oblige les propriétaires de tables à suivre également les politiques. C'est généralement plus sûr pour les tables appartenant à l'application. Pour les travaux de maintenance, utilisez un rôle contrôlé séparé au lieu de désactiver la RLS dans un script ad hoc.

Types de politiques : USING vs WITH CHECK

Clause USING

Contrôle quelles lignes existantes sont visibles/modifiables :

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- L'utilisateur peut uniquement SELECT les lignes où owner_id correspond

Clause WITH CHECK

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

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- L'utilisateur peut uniquement INSERT les lignes où owner_id correspond

Politiques combinées

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Considérations de performance

1. Indexer les colonnes des politiques

-- 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êtes

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

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

3. Faire attention aux sous-requêtes dans les politiques

Les sous-requêtes sont parfois le bon modèle pour l'accès partagé, mais elles nécessitent des index et des tests. Une politique qui interroge une autre table pour chaque ligne candidate peut devenir coûteuse.

Risqué sans index :

CREATE POLICY slow_policy ON documents
    USING (
        id IN (
            SELECT document_id
            FROM shares
            WHERE user_id = current_setting('app.user_id')::INTEGER
        )
    );

Souvent plus clair avec des index de support :

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_setting('app.user_id')::INTEGER
        )
    );

Créez des index tels que shares(user_id, document_id) et vérifiez le plan avec EXPLAIN (ANALYZE, BUFFERS).

Surveillance et audit

Voir 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 politiques

Suivez le DDL des politiques dans les migrations et les journaux d'audit de la base de données. Si votre environnement utilise des déclencheurs d'événements, testez-les soigneusement par rapport à votre version de PostgreSQL et incluez les événements CREATE POLICY, ALTER POLICY et DROP POLICY. Ne vous fiez pas à un extrait de déclencheur non testé pour une preuve de conformité.

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, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- Retourner les documents accessibles
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- 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';

-- Rechercher les opérations "Filter" avec des conditions de politique

Solution : Simplifier les politiques ou ajouter des index

Problème 4 : Accès refusé inattendu

Tester en tant qu'utilisateur spécifique :

SET ROLE regular_user;
SELECT set_config('app.user_id', '123', false);

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

RESET ROLE;

Meilleures pratiques

Toujours définir les variables de session : Assurez-vous que les valeurs current_setting() sont renseignées
Indexer les colonnes des politiques : Créez des index sur toutes les colonnes utilisées dans USING/WITH CHECK
Tester minutieusement : Vérifiez les politiques avec différents rôles et scénarios
Garder les politiques simples : Une logique complexe impacte les performances
Utiliser EXPLAIN : Analysez les plans de requêtes avec la RLS activée
Documenter les politiques : Commentez la logique des politiques pour la maintenabilité
Séparer les préoccupations : Utilisez différentes politiques pour SELECT, INSERT, UPDATE, DELETE
Surveiller les performances : Configurez des alertes pour les requêtes lentes

À retenir

Commencez par une politique simple : l'isolation des locataires sur vos tables les plus risquées. Définissez le contexte de session avec set_config, indexez chaque colonne utilisée par les politiques, et testez les lectures et écritures en tant que rôles applicatifs réels.

Une fois la frontière de base fonctionnelle, ajoutez des politiques de propriété, de gestionnaire ou d'accès partagé uniquement là où le produit en a besoin. Les politiques petites et testées sont plus faciles à raisonner qu'une règle astucieuse qui essaie d'encoder tout votre modèle de permissions.