Controllo degli Accessi Avanzato Basato sui Ruoli in PostgreSQL: Sicurezza a Livello di Riga e Gestione delle Policy

La Sicurezza a Livello di Riga (RLS) di PostgreSQL consente al tuo database di filtrare le righe in base al ruolo corrente e al contesto della sessione. Se la tua applicazione serve più tenant o mescola accessi di amministratori, manager e utenti regolari, RLS può impedire che un singolo WHERE tenant_id = ... dimenticato diventi una fuga di dati.

RLS non sostituisce l'autorizzazione applicativa. Ti fornisce una rete di sicurezza a livello di database per il confine più sensibile: quali righe una connessione può leggere o scrivere.

Perché la Sicurezza a Livello di Riga?

Problemi dell'Approccio Tradizionale

Filtraggio a livello applicativo:

# Ogni query deve ricordarsi di filtrare
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

Problemi:

Facile dimenticare i filtri (vulnerabilità di sicurezza)
Duplicazione del codice nell'applicazione
Nessuna protezione per l'accesso diretto al database
Requisiti di audit complessi

Vantaggi della Sicurezza a Livello di Riga

Applicazione automatica: Le policy vengono applicate in modo trasparente
Regole centralizzate: La logica di sicurezza in un unico posto
Isolamento multi-tenant: Perfetto per applicazioni SaaS
Conformità agli audit: Garanzie di sicurezza integrate
Prestazioni: Pianificazione efficiente delle query

Configurazione Base di RLS

Passo 1: Abilitare RLS sulla Tabella

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()
);

-- Abilita la sicurezza a livello di riga
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Passo 2: Creare i Ruoli

-- Crea diversi ruoli utente
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Concedi accesso alla tabella
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;

Passo 3: Creare le Policy

-- Policy: Gli utenti possono vedere i propri documenti
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Policy: Gli utenti possono inserire documenti di cui sono proprietari
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Policy: Gli utenti possono aggiornare i propri documenti
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);

Passo 4: Impostare le Variabili di Sessione

Nel codice applicativo:

import psycopg2

# Connettiti come ruolo
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Imposta il contesto utente
cursor = conn.cursor()
cursor.execute("SELECT set_config('app.user_id', %s, false)", [str(current_user_id)])

# Ora le query vengono filtrate automaticamente
cursor.execute("SELECT * FROM documents")
# Restituisce solo i documenti dove owner_id = current_user_id

Applicazione SaaS Multi-Tenant

Configurazione Multi-Tenant Completa

-- Crea tabella tenants
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Crea tabella users
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()
);

-- Crea tabella dati applicativi
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()
);

-- Abilita RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: Gli utenti vedono solo gli ordini del loro tenant
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);

Integrazione Applicativa

Esempio 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

Esempio 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;
    }
}

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

Pattern Avanzati di Policy

1. Controllo degli Accessi Gerarchico

-- Gli utenti possono vedere i documenti del loro dipartimento e quelli pubblici
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. Accesso Basato sul Tempo

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;

-- Policy: Gli utenti possono vedere solo le sottoscrizioni attive
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. Accesso Basato sui Ruoli con Logica Complessa

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;

-- Policy di visibilità complessa
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. Liste di Accesso Condivise

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;

-- Policy: Gli utenti possono vedere i documenti di proprietà o condivisi
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
        )
    );

-- Policy: Gli utenti possono modificare i documenti di proprietà o condivisi con permesso di scrittura
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'
        )
    );

Accesso Amministrativo e RLS

I proprietari delle tabelle e i superutenti possono bypassare RLS a meno che non lo si forzi. Tratta i percorsi amministrativi come una decisione di progettazione separata, non un ripensamento.

Opzione 1: Attributo di Ruolo BYPASSRLS

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

Usalo con parsimonia. BYPASSRLS è potente e non dovrebbe essere concesso ai ruoli applicativi normali.

Opzione 2: Policy Permissive per gli Amministratori

-- Crea policy permissiva per il ruolo admin
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

Opzione 3: Forzare RLS per i Proprietari delle Tabelle

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY fa sì che anche i proprietari delle tabelle seguano le policy. Di solito è più sicuro per le tabelle di proprietà dell'applicazione. Per i lavori di manutenzione, usa un ruolo controllato separato invece di disabilitare RLS all'interno di uno script ad hoc.

Tipi di Policy: USING vs WITH CHECK

Clausola USING

Controlla quali righe esistenti sono visibili/modificabili:

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

-- L'utente può solo SELEZIONARE le righe dove owner_id corrisponde

Clausola WITH CHECK

Controlla quali nuove righe/righe aggiornate sono consentite:

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

-- L'utente può solo INSERIRE righe dove owner_id corrisponde

Policy Combinate

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

Considerazioni sulle Prestazioni

1. Indicizzare le Colonne delle Policy

-- Crea indici per le colonne utilizzate nelle policy
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. Analizzare i Piani di Esecuzione

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

-- Verifica se il filtro della policy viene applicato in modo efficiente

3. Fare Attenzione alle Sottoquery nelle Policy

Le sottoquery sono talvolta il modello giusto per l'accesso condiviso, ma necessitano di indici e test. Una policy che interroga un'altra tabella per ogni riga candidata può diventare costosa.

Rischioso senza indici:

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

Spesso più chiaro con indici di supporto:

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

Crea indici come shares(user_id, document_id) e controlla il piano con EXPLAIN (ANALYZE, BUFFERS).

Monitoraggio e Audit

Visualizzare le Policy Attive

-- Elenca tutte le policy RLS
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

Tracciare le Modifiche alle Policy

Tieni traccia del DDL delle policy nelle migrazioni e nei log di audit del database. Se il tuo ambiente utilizza trigger di evento, testali attentamente rispetto alla tua versione di PostgreSQL e includi gli eventi CREATE POLICY, ALTER POLICY e DROP POLICY. Non fare affidamento su uno snippet di trigger non testato per prove di conformità.

Testare l'Efficacia delle Policy

-- Crea funzione di 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
    -- Imposta le variabili di sessione
    PERFORM set_config('app.user_id', test_user_id::text, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- Restituisci i documenti accessibili
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- Testa l'accesso
SELECT * FROM test_user_access(123, 5);

Risoluzione dei Problemi

Problema 1: Policy Non Applicate

Verifica se RLS è abilitato:

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

Abilita se disabilitato:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Problema 2: Variabili di Sessione Non Impostate

Debug delle impostazioni correnti:

SHOW app.user_id;
SHOW app.tenant_id;

-- Oppure usa current_setting
SELECT current_setting('app.user_id', true);

Problema 3: Degrado delle Prestazioni

Identifica le policy lente:

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

-- Cerca operazioni "Filter" con condizioni delle policy

Soluzione: Semplifica le policy o aggiungi indici

Problema 4: Accesso Negato Inaspettato

Testa come utente specifico:

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

SELECT * FROM documents;
-- Mostra cosa quell'utente può effettivamente vedere

RESET ROLE;

Best Practices

Imposta sempre le variabili di sessione: Assicurati che i valori di current_setting() siano popolati
Indicizza le colonne delle policy: Crea indici su tutte le colonne utilizzate in USING/WITH CHECK
Testa accuratamente: Verifica le policy con diversi ruoli e scenari
Mantieni le policy semplici: La logica complessa influisce sulle prestazioni
Usa EXPLAIN: Analizza i piani di esecuzione con RLS abilitato
Documenta le policy: Commenta la logica delle policy per la manutenibilità
Separa le preoccupazioni: Usa policy diverse per SELECT, INSERT, UPDATE, DELETE
Monitora le prestazioni: Imposta avvisi per query lente

Conclusione

Inizia con una policy semplice: l'isolamento dei tenant sulle tue tabelle a più alto rischio. Imposta il contesto di sessione con set_config, indicizza ogni colonna utilizzata dalle policy e testa letture e scritture come ruoli applicativi reali.

Una volta che il confine di base funziona, aggiungi policy di proprietà, manager o accesso condiviso solo dove il prodotto ne ha bisogno. Le policy piccole e testate sono più facili da comprendere rispetto a una regola intelligente che cerca di codificare l'intero modello di autorizzazione.