Centro di Conoscenza DevOps
Centro di Conoscenza DevOps

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

Master PostgreSQL Row-Level Security (RLS) per il controllo degli accessi a granularità fine, l'isolamento multi-tenant e l'autorizzazione basata su policy. Guida completa con modelli di implementazione nel mondo reale.

30 visualizzazioni

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

Introduzione

La Sicurezza a Livello di Riga (RLS) di PostgreSQL fornisce un controllo di accesso granulare a livello di riga, consentendo di applicare regole di autorizzazione complesse direttamente nel database. Questo elimina la necessità di filtrare a livello di applicazione e garantisce la sicurezza dei dati indipendentemente da come le applicazioni accedono al database.

Perché la Sicurezza a Livello di Riga?

Problemi dell'approccio tradizionale

Filtraggio a livello di applicazione:

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

Problemi:
- Filtri facili da dimenticare (vulnerabilità di sicurezza)
- Duplicazione del codice in tutta l'applicazione
- Nessuna protezione per l'accesso diretto al database
- Requisiti di audit complessi

Vantaggi della Sicurezza a Livello di Riga

  • Enforcement automatico: le policy vengono applicate in modo trasparente
  • Regole centralizzate: la logica di sicurezza in un unico punto
  • Isolamento multi-tenant: ideale per applicazioni SaaS
  • Conformità all'audit: garanzie di sicurezza integrate
  • Performance: ottimizzazione efficiente delle query

Configurazione di RLS di Base

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

-- Enable row-level security
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Passo 2: Creare ruoli

-- Creare ruoli utente differenti
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Concedere 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 policy

-- Policy: gli utenti possono vedere solo 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 loro proprietà
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 variabili di sessione

Nel codice dell'applicazione:

import psycopg2

# Connessione con il ruolo
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Impostare il contesto utente
cursor = conn.cursor()
cursor.execute("SET app.user_id = %s", [current_user_id])

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

Applicazione SaaS a più tenant

Configurazione completa a più tenant

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

-- Creare tabella utenti
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()
);

-- Creare tabella dati di esempio
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()
);

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

-- Policy: gli utenti vedono solo gli ordini del proprio 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 con l'applicazione

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(
                    "SET app.tenant_id = %s",
                    [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('SET app.tenant_id = $1', [tenantId]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

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

Modelli avanzati di policy

1. Controllo di accesso gerarchico

-- Gli utenti possono vedere 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 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. Accesso tramite elenchi condivisi

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: utenti possono vedere 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: utenti possono modificare 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'
        )
    );

Screenshot per operazioni amministrative bypassando RLS

Opzione 1: attributo BYPASSRLS

-- Concedere accesso simile a superuser
CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

-- L'admin può vedere tutte le righe indipendentemente dalle policy

Opzione 2: policy permissive per gli admin

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

Opzione 3: disabilitare RLS temporaneamente

-- Per script di manutenzione
BEGIN;
    ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
    -- Operazioni amministrative
    UPDATE documents SET archived = true WHERE created_at < NOW() - INTERVAL '1 year';
    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
COMMIT;

Tipi di policy: clausole 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_user_id());

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

Clausola WITH CHECK

Controlla quali nuove/aggiornate righe sono ammesse:

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

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

Policy combinate

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_user_id())       -- può aggiornare solo le proprie righe
    WITH CHECK (owner_id = current_user_id()); -- non può cambiare la proprietà

Considerazioni prestazionali

1. Indici delle colonne delle policy

-- Creare indici sulle colonne usate 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 query

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

-- Verificare se il filtro di policy è applicato efficacemente

3. Evitare sottoselezioni nelle policy

Scarso - sottoselezione eseguita per ogni riga:

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

Migliore - usare EXISTS:

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

Migliore ancora - usare JOIN in applicazione o vista materializzata:

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

Monitoraggio e audit

Visualizzare le policy attive

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

Registrare le modifiche alle policy

-- Creare tabella di log per modifiche alle policy
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()
);

-- Funzione trigger per loggare modifiche
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();

Testare l'efficacia delle policy

-- Funzione di test per accesso utente
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- Impostare variabili di sessione
    PERFORM set_config('app.user_id', test_user_id::text, true);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, true);

    -- Restituire i documenti accessibili
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Eseguire test
SELECT * FROM test_user_access(123, 5);

Risoluzione dei problemi

Problema 1: le policy non vengono applicate

Verificare se RLS è abilitato:

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

Abilitare 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 usare current_setting
SELECT current_setting('app.user_id', true);

Problema 3: degradazione delle prestazioni

Identificare policy lente:

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

-- Cercare operazioni "Filter" con condizioni di policy

Soluzione: semplificare le policy o aggiungere indici

Problema 4: accesso negato inaspettato

Testare come utente specifico:

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

SELECT * FROM documents;
-- Mostra ciò che quell'utente può realmente vedere

RESET ROLE;

Best practice

  1. Impostare sempre le variabili di sessione: assicurarsi che i valori di current_setting() siano popolati
  2. Individuare le colonne delle policy: creare indici su tutte le colonne usate in USING/WITH CHECK
  3. Testare approfonditamente: verificare le policy con ruoli e scenari differenti
  4. Mantenere le policy semplici: logica complessa influisce sulle prestazioni
  5. Usare EXPLAIN: analizzare i piani di query con RLS abilitato
  6. Documentare le policy: commentare la logica per manutenzione
  7. Separare le preoccupazioni: usare policy diverse per SELECT, INSERT, UPDATE, DELETE
  8. Monitorare le prestazioni: impostare allarmi per query lente

Conclusione

PostgreSQL Row-Level Security fornisce un meccanismo di controllo degli accessi robusto e applicato a livello di database:

  • Filtraggio automatico: la sicurezza viene applicata in modo trasparente
  • Isolamento multi-tenant: perfetto per applicazioni SaaS
  • Policy flessibili: supportano regole di autorizzazione complesse
  • Sicurezza centralizzata: la logica è nel database, non dispersa nel codice

Iniziare con un semplice isolamento tenant, testare l'impatto sulle prestazioni, quindi espandere a modelli di autorizzazione più sofisticati se necessario.