PecHub/backend/alembic/versions/0006_rls_complete.py

"""RLS completa su tutte le tabelle applicative + ruolo pechub_app

Revision ID: 0006
Revises: 0005
Create Date: 2026-03-19 00:00:00.000000

Aggiunge:
  - ENABLE ROW LEVEL SECURITY + policy USING (tenant_id = ...) su tutte le
    tabelle applicative che mancavano (attachments, send_jobs, archival_batches,
    archival_dips, labels, mailbox_permissions, virtual_boxes,
    notification_channels, notification_rules, notification_log, tenant_settings)
  - Crea il ruolo PostgreSQL applicativo 'pechub_app' (non-superuser) con
    GRANT SELECT/INSERT/UPDATE/DELETE su tutte le tabelle.
    Il ruolo viene usato dalla connessione applicativa (DATABASE_URL) mentre
    Alembic continua ad usare l'utente superuser (DATABASE_URL_SYNC).

Nota: la migrazione usa DO $$ BEGIN ... EXCEPTION WHEN ... END $$ per
rendere idempotente la creazione del ruolo e delle policy.
"""

from alembic import op

revision = "0006"
down_revision = "0005"
branch_labels = None
depends_on = None

# Tabelle con tenant_id che devono avere RLS (escluse quelle già gestite in 0001)
_RLS_TABLES = [
    "attachments",
    "send_jobs",
    "archival_batches",
    "archival_dips",
    "labels",
    "mailbox_permissions",
    "virtual_boxes",
    "notification_channels",
    "notification_rules",
    "notification_log",
    "tenant_settings",
]

# Tutte le tabelle su cui pechub_app ha SELECT/INSERT/UPDATE/DELETE
_ALL_APP_TABLES = [
    "tenants",
    "users",
    "refresh_tokens",
    "mailboxes",
    "messages",
    "attachments",
    "send_jobs",
    "archival_batches",
    "archival_batch_messages",
    "archival_dips",
    "labels",
    "message_labels",
    "mailbox_permissions",
    "virtual_boxes",
    "virtual_box_rules",
    "virtual_box_assignments",
    "virtual_box_mailboxes",
    "notification_channels",
    "notification_rules",
    "notification_log",
    "audit_log",
    "tenant_settings",
]


def upgrade() -> None:
    # ── 1. Abilita RLS + policy su tabelle mancanti ───────────────────────────
    for table in _RLS_TABLES:
        op.execute(f"ALTER TABLE {table} ENABLE ROW LEVEL SECURITY")
        # Politica idempotente: elimina la policy se esiste e la ricrea
        op.execute(
            f"""
            DO $$
            BEGIN
                -- Elimina policy esistente (se presente da un run precedente)
                DROP POLICY IF EXISTS {table}_tenant_isolation ON {table};
                -- Crea la nuova policy
                CREATE POLICY {table}_tenant_isolation ON {table}
                USING (
                    tenant_id = NULLIF(
                        current_setting('app.current_tenant_id', TRUE), ''
                    )::UUID
                );
            END
            $$;
            """
        )

    # ── 2. Crea ruolo applicativo pechub_app (non-superuser) ──────────────────
    op.execute(
        """
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM pg_roles WHERE rolname = 'pechub_app'
            ) THEN
                CREATE USER pechub_app WITH PASSWORD 'pechub_app_password'
                    NOSUPERUSER NOCREATEDB NOCREATEROLE;
                RAISE NOTICE 'Ruolo pechub_app creato';
            ELSE
                RAISE NOTICE 'Ruolo pechub_app già esistente – skip';
            END IF;
        END
        $$;
        """
    )

    # ── 3. Permessi al ruolo applicativo ──────────────────────────────────────
    op.execute("GRANT CONNECT ON DATABASE pechub TO pechub_app")
    op.execute("GRANT USAGE ON SCHEMA public TO pechub_app")

    for table in _ALL_APP_TABLES:
        op.execute(
            f"GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE {table} TO pechub_app"
        )

    # Sequenze (per BIGSERIAL come audit_log.id)
    op.execute("GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pechub_app")

    # Default privileges: le tabelle create nelle migrazioni future ricevono
    # automaticamente i permessi per pechub_app
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES FOR ROLE pechub IN SCHEMA public
        GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pechub_app;
        """
    )
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES FOR ROLE pechub IN SCHEMA public
        GRANT USAGE, SELECT ON SEQUENCES TO pechub_app;
        """
    )


def downgrade() -> None:
    # Rimuovi policy e disabilita RLS
    for table in _RLS_TABLES:
        op.execute(
            f"DROP POLICY IF EXISTS {table}_tenant_isolation ON {table}"
        )
        op.execute(f"ALTER TABLE {table} DISABLE ROW LEVEL SECURITY")

    # Revoca permessi e rimuovi ruolo
    op.execute("REVOKE ALL ON ALL TABLES IN SCHEMA public FROM pechub_app")
    op.execute("REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM pechub_app")
    op.execute("REVOKE USAGE ON SCHEMA public FROM pechub_app")
    op.execute("REVOKE CONNECT ON DATABASE pechub FROM pechub_app")
    op.execute("DROP USER IF EXISTS pechub_app")