Advanced PostgreSQL Role-Based Access Control: Row-Level Security and Policy Management

PostgreSQL Row-Level Security (RLS) lets your database filter rows based on the current role and session context. If your app serves multiple tenants or mixes admin, manager, and regular user access, RLS can stop one missed WHERE tenant_id = ... from becoming a data leak.

RLS does not replace application authorization. It gives you a database-level safety net for the most sensitive boundary: which rows a connection can read or write.

Why Row-Level Security?

Traditional Approach Problems

Application-layer filtering:

# Every query must remember to filter
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

Issues:

Easy to forget filters (security vulnerabilities)
Code duplication across application
No protection for direct database access
Complex audit requirements

Row-Level Security Benefits

Automatic enforcement: Policies applied transparently
Centralized rules: Security logic in one place
Multi-tenant isolation: Perfect for SaaS applications
Audit compliance: Built-in security guarantees
Performance: Efficient query planning

Basic RLS Setup

Step 1: Enable RLS on 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()
);

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

Step 2: Create Roles

-- Create different user roles
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Grant table access
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;

Step 3: Create Policies

-- Policy: Users can see their own documents
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Policy: Users can insert documents owned by themselves
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Policy: Users can update their own 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);

Step 4: Set Session Variables

In application code:

import psycopg2

# Connect as role
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Set user context
cursor = conn.cursor()
cursor.execute("SELECT set_config('app.user_id', %s, false)", [str(current_user_id)])

# Now queries are automatically filtered
cursor.execute("SELECT * FROM documents")
# Only returns documents where owner_id = current_user_id

Multi-Tenant SaaS Application

Complete Multi-Tenant Setup

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

-- Create users table
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()
);

-- Create application data table
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()
);

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

-- Policy: Users only see orders from their 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);

Application Integration

Django example:

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

Node.js example:

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

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

Advanced Policy Patterns

1. Hierarchical Access Control

-- Users can see documents from their department and public ones
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. Time-Based Access

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: Users can only see active subscriptions
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. Role-Based Access with Complex Logic

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;

-- Complex visibility policy
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. Shared Access Lists

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: Users can see owned or shared documents
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: Users can modify owned documents or those shared with write permission
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'
        )
    );

Administrative Access and RLS

Table owners and superusers can bypass RLS unless you force it. Treat administrative paths as a separate design decision, not an afterthought.

Option 1: BYPASSRLS Role Attribute

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

Use this sparingly. BYPASSRLS is powerful and should not be granted to normal application roles.

Option 2: Permissive Policies for Admins

-- Create permissive policy for admin role
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

Option 3: Force RLS for Table Owners

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY makes table owners follow policies too. That is usually safer for application-owned tables. For maintenance work, use a separate controlled role instead of disabling RLS inside an ad hoc script.

Policy Types: USING vs WITH CHECK

USING Clause

Controls which existing rows are visible/modifiable:

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

-- User can only SELECT rows where owner_id matches

WITH CHECK Clause

Controls which new/updated rows are allowed:

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

-- User can only INSERT rows where owner_id matches

Combined Policies

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

Performance Considerations

1. Index Policy Columns

-- Create indexes for columns used in policies
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. Analyze Query Plans

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

-- Check if policy filter is applied efficiently

3. Be Careful with Subqueries in Policies

Subqueries are sometimes the right model for shared access, but they need indexes and testing. A policy that probes another table for every candidate row can become expensive.

Risky without indexes:

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

Often clearer with supporting indexes:

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

Create indexes such as shares(user_id, document_id) and check the plan with EXPLAIN (ANALYZE, BUFFERS).

Monitoring and Auditing

View Active Policies

-- List all RLS policies
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

Audit Policy Changes

Track policy DDL in migrations and database audit logs. If your environment uses event triggers, test them carefully against your PostgreSQL version and include CREATE POLICY, ALTER POLICY, and DROP POLICY events. Do not rely on an untested trigger snippet for compliance evidence.

Test Policy Effectiveness

-- Create test function
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- Set session variables
    PERFORM set_config('app.user_id', test_user_id::text, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- Return accessible documents
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- Test access
SELECT * FROM test_user_access(123, 5);

Troubleshooting

Issue 1: Policies Not Applied

Check if RLS is enabled:

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

Enable if disabled:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Issue 2: Session Variables Not Set

Debug current settings:

SHOW app.user_id;
SHOW app.tenant_id;

-- Or use current_setting
SELECT current_setting('app.user_id', true);

Issue 3: Performance Degradation

Identify slow policies:

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

-- Look for "Filter" operations with policy conditions

Solution: Simplify policies or add indexes

Issue 4: Unexpected Access Denied

Test as specific user:

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

SELECT * FROM documents;
-- Shows what that user can actually see

RESET ROLE;

Best Practices

Always set session variables: Ensure current_setting() values are populated
Index policy columns: Create indexes on all columns used in USING/WITH CHECK
Test thoroughly: Verify policies with different roles and scenarios
Keep policies simple: Complex logic impacts performance
Use EXPLAIN: Analyze query plans with RLS enabled
Document policies: Comment policy logic for maintainability
Separate concerns: Use different policies for SELECT, INSERT, UPDATE, DELETE
Monitor performance: Set up alerts for slow queries

Takeaway

Start with one simple policy: tenant isolation on your highest-risk tables. Set session context with set_config, index every column used by policies, and test reads and writes as real application roles.

Once the basic boundary works, add ownership, manager, or shared-access policies only where the product needs them. Small, tested policies are easier to reason about than one clever rule that tries to encode your whole permission model.