Row-Level Security Implementation for Multi-Tenant Web Application

Row-Level Security for Multi-Tenant Applications

Row-Level Security (RLS) — PostgreSQL mechanism allowing you to restrict row access directly at the DBMS level. Even if the application makes an error and doesn't pass WHERE tenant_id = ?, PostgreSQL automatically applies the policy. RLS — second layer of tenant data protection, independent from ORM.

How RLS Works

-- Enable RLS for table
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- By default table owner (superuser) bypasses RLS
-- To enforce policies even for owner:
ALTER TABLE articles FORCE ROW LEVEL SECURITY;

-- Policy: row visible if tenant_id matches context
CREATE POLICY tenant_isolation_select ON articles
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Policy INSERT: cannot insert row with another tenant's id
CREATE POLICY tenant_isolation_insert ON articles
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Combine SELECT/INSERT/UPDATE/DELETE
CREATE POLICY tenant_isolation ON articles
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

current_setting('app.current_tenant_id') — session parameter that application sets before queries.

Setting Context in Application

// Laravel — set tenant context in middleware
class SetTenantContext
{
    public function handle(Request $request, Closure $next): Response
    {
        $tenant = app('tenant'); // set earlier

        // Set PostgreSQL session variable
        DB::statement(
            "SELECT set_config('app.current_tenant_id', ?, false)",
            [$tenant->id]
        );

        return $next($request);
    }
}

false in third parameter of set_config — value applies only in current transaction. true — for entire session. With connection pooling (PgBouncer) false is safer — value resets when connection returns to pool.

PgBouncer and RLS

PgBouncer in transaction mode resets session-level variables between transactions — good for security, but requires setting app.current_tenant_id at start of each transaction:

DB::transaction(function () use ($tenant) {
    DB::statement(
        "SELECT set_config('app.current_tenant_id', ?, true)",
        [$tenant->id]
    );

    // All queries inside transaction are protected by RLS
    Article::create([...]);
    Comment::create([...]);
});

Different Policies for Roles

-- Superadmin sees all rows
CREATE POLICY superadmin_all ON articles
    FOR ALL
    USING (current_setting('app.is_superadmin', true) = 'true');

-- Users see only theirs and published articles of their tenant
CREATE POLICY user_select ON articles
    FOR SELECT
    USING (
        tenant_id = current_setting('app.current_tenant_id')::uuid
        AND (
            author_id = current_setting('app.current_user_id')::uuid
            OR status = 'published'
        )
    );

-- Editors can see drafts in their tenant
CREATE POLICY editor_select ON articles
    FOR SELECT
    USING (
        tenant_id = current_setting('app.current_tenant_id')::uuid
        AND current_setting('app.current_role', true) = 'editor'
    );

Multiple policies for one command (SELECT) combine via OR (permissive) or AND (restrictive).

Restrictive Policies

Permissive (default): access allowed if ANY policy matches. Restrictive: access allowed if ALL restrictive policies match.

-- Hard limit: deleted accounts see nothing regardless of other policies
CREATE POLICY no_deleted_tenant ON articles
    AS RESTRICTIVE
    USING (
        NOT EXISTS (
            SELECT 1 FROM tenants
            WHERE id = current_setting('app.current_tenant_id')::uuid
            AND deleted_at IS NOT NULL
        )
    );

Bypassing RLS for System Operations

-- Special role without RLS (for migrations, analytics)
CREATE ROLE app_migrations BYPASSRLS;
CREATE ROLE app_analytics BYPASSRLS;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_analytics;

-- In application: two connection pools
-- app_user — normal role with RLS
-- app_analytics — role with BYPASSRLS for analytical queries

// Laravel: separate connection for analytics
DB::connection('analytics')->select('SELECT COUNT(*) FROM articles GROUP BY tenant_id');

Testing RLS Policies

-- Test as tenant_a
SET app.current_tenant_id = 'tenant-a-uuid';
SELECT count(*) FROM articles; -- should see only tenant_a articles

-- Test insert attempt into another tenant
SET app.current_tenant_id = 'tenant-a-uuid';
INSERT INTO articles (tenant_id, title)
VALUES ('tenant-b-uuid', 'Hack attempt'); -- ERROR: new row violates row-level security policy

// PHPUnit — test data leak between tenants
public function test_tenant_isolation(): void
{
    $tenantA = Tenant::factory()->create();
    $tenantB = Tenant::factory()->create();

    Article::factory()->count(5)->create(['tenant_id' => $tenantA->id]);
    Article::factory()->count(3)->create(['tenant_id' => $tenantB->id]);

    // Auth as tenant A
    DB::statement("SELECT set_config('app.current_tenant_id', ?, false)", [$tenantA->id]);

    $articles = Article::all();

    $this->assertCount(5, $articles);
    $this->assertTrue($articles->every(fn($a) => $a->tenant_id === $tenantA->id));
}

Performance

RLS adds condition to every query — index on tenant_id is mandatory:

-- Composite index for typical queries
CREATE INDEX articles_tenant_status_idx ON articles(tenant_id, status);
CREATE INDEX articles_tenant_created_idx ON articles(tenant_id, created_at DESC);

-- Partial index for active records
CREATE INDEX articles_active_idx ON articles(tenant_id, created_at DESC)
    WHERE deleted_at IS NULL;

EXPLAIN ANALYZE shows RLS filter application — ensure Index Scan is used, not Seq Scan.

Timeline

RLS policies on all tables, middleware context setup, isolation tests, bypass role for migrations: 1 week. With restrictive policies for deleted accounts, BYPASSRLS for analytics, performance load testing: 2 weeks.