Skip to main content

Row-Level Security (RLS) Policy Patterns

Version: 1.0 Last Updated: 2025-10-25 Applies To: All versions (MVP.0+)

Table of Contents​

  1. Overview
  2. Multi-Tenancy Isolation Pattern
  3. Common RLS Patterns
  4. Implementation Examples
  5. Testing RLS Policies
  6. Performance Considerations
  7. Troubleshooting

Overview​

What is Row-Level Security (RLS)?​

Row-Level Security (RLS) is a PostgreSQL feature that allows database administrators to define policies that control which rows individual users or applications can access in queries. RLS policies are enforced at the database level, providing defense-in-depth security.

Key Characteristics:

  • Policies are automatically applied to all queries (SELECT, INSERT, UPDATE, DELETE)
  • Enforced regardless of application code
  • Cannot be bypassed by application logic errors
  • Evaluated for each row in the result set

Why Use RLS?​

Security Benefits:

  • Defense in Depth: Database-level enforcement protects against application bugs
  • Data Isolation: Prevents cross-tenant data leaks in multi-tenant systems
  • Audit Compliance: Provides verifiable data access controls
  • Simplified Code: Reduces need for repetitive WHERE clauses in application code

Use Cases:

  • Multi-tenant SaaS applications (org/account isolation)
  • Role-based access control (RBAC)
  • Privacy-sensitive data (user-owned records)
  • Time-based access restrictions (embargo, expiration)
  • Hierarchical data access (parent-child relationships)

When NOT to Use RLS​

Avoid RLS for:

  • High-throughput tables with complex policies (performance overhead)
  • Tables with no sensitive data requiring isolation
  • Read-only reference data (e.g., lookup tables, enums)
  • Background jobs requiring full table access (use bypasses carefully)

Multi-Tenancy Isolation Pattern​

Architecture Overview​

TVL uses a 3-tier multi-tenancy model requiring strict isolation:

Organization (org_id) → Account (account_id) → User (user_id)
        ↓                       ↓                      ↓
    Tenant Boundary      Sub-tenant/Actor        Person Identity

RLS Policy Goals:

  1. Org Isolation: Prevent cross-tenant data leaks (highest priority)
  2. Account Access: Control within-tenant access based on membership
  3. User Context: Apply per-user permissions (role-based)

Org-Level Isolation Policy​

Purpose: Block all cross-tenant access at the database level.

Pattern:

-- Enable RLS on the table
ALTER TABLE spaces ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see rows in their current org
CREATE POLICY org_isolation ON spaces
  USING (org_id = current_setting('app.current_org_id')::uuid);

How It Works:

  1. Application sets app.current_org_id session variable before queries
  2. PostgreSQL evaluates policy for each row
  3. Rows not matching org_id are filtered out automatically
  4. Policy applies to SELECT, UPDATE, DELETE (not INSERT)

Complete Example:

-- Table with org_id
CREATE TABLE spaces (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  account_id UUID NOT NULL REFERENCES accounts(id),
  name VARCHAR(255) NOT NULL,
  status VARCHAR(50) DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now()
);

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

-- Org isolation policy (applies to all operations)
CREATE POLICY org_isolation ON spaces
  USING (org_id = current_setting('app.current_org_id')::uuid);

-- Insert policy (require org_id match)
CREATE POLICY org_insert ON spaces
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

Application Integration:

// Set session context before queries
async function setOrgContext(client, org_id) {
  await client.query(
    `SET LOCAL app.current_org_id = $1`,
    [org_id]
  );
}

// Usage in transaction
async function getSpaces(org_id) {
  return db.transaction(async (client) => {
    await setOrgContext(client, org_id);
    // RLS automatically filters to current org
    const result = await client.query('SELECT * FROM spaces');
    return result.rows;
  });
}

Account-Level Isolation Policy​

Purpose: Control access within an organization based on account membership.

Pattern:

-- Policy: Users see rows for their account OR org-wide access
CREATE POLICY account_access ON spaces
  USING (
    -- Exact account match
    account_id = current_setting('app.current_account_id')::uuid
    OR
    -- Org-wide membership (account_id IS NULL)
    EXISTS (
      SELECT 1 FROM memberships m
      WHERE m.user_id = current_setting('app.current_user_id')::uuid
        AND m.org_id = spaces.org_id
        AND m.account_id IS NULL  -- Org-wide flag
        AND m.status = 'active'
    )
  );

Complete Multi-Tier Example:

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

-- Policy 1: Org isolation (mandatory)
CREATE POLICY org_isolation ON spaces
  USING (org_id = current_setting('app.current_org_id')::uuid);

-- Policy 2: Account access (within org)
CREATE POLICY account_access ON spaces
  USING (
    account_id = current_setting('app.current_account_id')::uuid
    OR EXISTS (
      SELECT 1 FROM memberships m
      WHERE m.user_id = current_setting('app.current_user_id')::uuid
        AND m.org_id = spaces.org_id
        AND m.account_id IS NULL
        AND m.status = 'active'
    )
  );

-- Policy 3: Insert requires account match
CREATE POLICY account_insert ON spaces
  FOR INSERT
  WITH CHECK (
    org_id = current_setting('app.current_org_id')::uuid
    AND account_id = current_setting('app.current_account_id')::uuid
  );

-- Policy 4: Update requires ownership
CREATE POLICY account_update ON spaces
  FOR UPDATE
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND (
      account_id = current_setting('app.current_account_id')::uuid
      OR EXISTS (
        SELECT 1 FROM memberships m
        WHERE m.user_id = current_setting('app.current_user_id')::uuid
          AND m.org_id = spaces.org_id
          AND m.account_id IS NULL
          AND m.status = 'active'
      )
    )
  );

Application Context Setup:

async function setSessionContext(client, session) {
  const { org_id, account_id, user_id } = session;

  await client.query(
    `SET LOCAL app.current_org_id = $1;
     SET LOCAL app.current_account_id = $2;
     SET LOCAL app.current_user_id = $3;`,
    [org_id, account_id, user_id]
  );
}

// Middleware to inject context
async function withRLSContext(req, res, next) {
  req.dbContext = async (callback) => {
    return db.transaction(async (client) => {
      await setSessionContext(client, req.session);
      return callback(client);
    });
  };
  next();
}

// Usage in route handler
app.get('/api/spaces', async (req, res) => {
  const spaces = await req.dbContext(async (client) => {
    const result = await client.query('SELECT * FROM spaces');
    return result.rows;
  });
  res.json(spaces);
});

Common RLS Patterns​

Pattern 1: Owner-Only Access​

Use Case: User-owned resources (profiles, preferences, private notes)

CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  display_name VARCHAR(255),
  bio TEXT,
  avatar_url TEXT
);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- Users can only see/edit their own profile
CREATE POLICY owner_only ON user_profiles
  USING (user_id = current_setting('app.current_user_id')::uuid)
  WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

With Org Context:

-- Owner-only within org boundary
CREATE POLICY owner_only_in_org ON user_settings
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND user_id = current_setting('app.current_user_id')::uuid
  );

Pattern 2: Team Member Access​

Use Case: Shared team resources, collaborative documents

CREATE TABLE team_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  team_id UUID NOT NULL REFERENCES teams(id),
  title VARCHAR(255),
  content TEXT,
  created_by UUID REFERENCES users(id)
);

ALTER TABLE team_documents ENABLE ROW LEVEL SECURITY;

-- Access for team members
CREATE POLICY team_member_access ON team_documents
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND EXISTS (
      SELECT 1 FROM team_memberships tm
      WHERE tm.team_id = team_documents.team_id
        AND tm.user_id = current_setting('app.current_user_id')::uuid
        AND tm.status = 'active'
    )
  );

Pattern 3: Public Read, Authenticated Write​

Use Case: Public listings, published content with authenticated editing

CREATE TABLE property_listings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  account_id UUID NOT NULL REFERENCES accounts(id),
  title VARCHAR(255),
  description TEXT,
  is_published BOOLEAN DEFAULT false,
  published_at TIMESTAMPTZ
);

ALTER TABLE property_listings ENABLE ROW LEVEL SECURITY;

-- Policy 1: Anyone can read published listings
CREATE POLICY public_read ON property_listings
  FOR SELECT
  USING (is_published = true);

-- Policy 2: Owners can read all their listings
CREATE POLICY owner_read_all ON property_listings
  FOR SELECT
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND account_id = current_setting('app.current_account_id')::uuid
  );

-- Policy 3: Only owners can insert
CREATE POLICY owner_insert ON property_listings
  FOR INSERT
  WITH CHECK (
    org_id = current_setting('app.current_org_id')::uuid
    AND account_id = current_setting('app.current_account_id')::uuid
  );

-- Policy 4: Only owners can update
CREATE POLICY owner_update ON property_listings
  FOR UPDATE
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND account_id = current_setting('app.current_account_id')::uuid
  );

Pattern 4: Time-Based Access​

Use Case: Embargoed content, scheduled releases, expired subscriptions

CREATE TABLE subscription_content (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  title VARCHAR(255),
  content TEXT,
  available_from TIMESTAMPTZ NOT NULL,
  available_until TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE subscription_content ENABLE ROW LEVEL SECURITY;

-- Policy: Content visible only within time window
CREATE POLICY time_based_access ON subscription_content
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND available_from <= now()
    AND (available_until IS NULL OR available_until > now())
  );

Pattern 5: Hierarchical Access​

Use Case: Manager access to subordinate data, parent-child relationships

CREATE TABLE employee_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  employee_user_id UUID NOT NULL REFERENCES users(id),
  manager_user_id UUID REFERENCES users(id),
  title VARCHAR(255),
  salary_info JSONB
);

ALTER TABLE employee_records ENABLE ROW LEVEL SECURITY;

-- Policy: Employees see own records + manager sees team
CREATE POLICY hierarchical_access ON employee_records
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND (
      -- Own record
      employee_user_id = current_setting('app.current_user_id')::uuid
      OR
      -- Manager's direct reports
      manager_user_id = current_setting('app.current_user_id')::uuid
    )
  );

Pattern 6: Role-Based Access​

Use Case: Admin overrides, role-specific visibility

CREATE TABLE sensitive_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  log_level VARCHAR(50),
  message TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE sensitive_logs ENABLE ROW LEVEL SECURITY;

-- Policy: Admins see all, users see only warnings/errors
CREATE POLICY role_based_logs ON sensitive_logs
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND (
      -- Admins see everything
      EXISTS (
        SELECT 1 FROM memberships m
        JOIN roles r ON r.id = m.role_id
        WHERE m.user_id = current_setting('app.current_user_id')::uuid
          AND m.org_id = sensitive_logs.org_id
          AND r.name = 'admin'
          AND m.status = 'active'
      )
      OR
      -- Non-admins see only warnings/errors
      log_level IN ('warning', 'error', 'critical')
    )
  );

Implementation Examples​

PostgreSQL CREATE POLICY Syntax​

Basic Syntax:

CREATE POLICY policy_name ON table_name
  [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
  [ TO { role_name | PUBLIC } ]
  [ USING ( using_expression ) ]
  [ WITH CHECK ( check_expression ) ];

Key Components:

  1. FOR clause: Operation type

    • ALL (default): All operations
    • SELECT: Read-only policy
    • INSERT: Insert policy (uses WITH CHECK only)
    • UPDATE: Update policy (uses both USING and WITH CHECK)
    • DELETE: Delete policy (uses USING only)

  2. USING clause: Filter which rows are visible

    • Applied to SELECT, UPDATE, DELETE
    • Boolean expression evaluated per row
    • Return true = row visible, false = filtered out

  3. WITH CHECK clause: Validate new/updated rows

    • Applied to INSERT, UPDATE
    • Boolean expression evaluated for new values
    • Return true = allowed, false = rejected with error

Examples:

-- SELECT only
CREATE POLICY select_published ON articles
  FOR SELECT
  USING (published = true);

-- INSERT only (no USING needed)
CREATE POLICY insert_own_org ON articles
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

-- UPDATE (both USING and WITH CHECK)
CREATE POLICY update_own_articles ON articles
  FOR UPDATE
  USING (author_id = current_setting('app.current_user_id')::uuid)
  WITH CHECK (author_id = current_setting('app.current_user_id')::uuid);

-- DELETE only
CREATE POLICY delete_own_comments ON comments
  FOR DELETE
  USING (user_id = current_setting('app.current_user_id')::uuid);

Setting Session Variables (SET LOCAL)​

Session Variables:

  • Stored per-connection or per-transaction
  • Accessed via current_setting('var_name')
  • Must be cast to appropriate type (::uuid, ::integer, etc.)

SET LOCAL vs SET:

-- SET: Persists for entire connection
SET app.current_org_id = '123e4567-e89b-12d3-a456-426614174000';

-- SET LOCAL: Reverts after transaction ends (SAFER)
SET LOCAL app.current_org_id = '123e4567-e89b-12d3-a456-426614174000';

Best Practice: Always use SET LOCAL in transactions

async function executeWithContext(session, callback) {
  return db.transaction(async (client) => {
    // Set context variables (transaction-scoped)
    await client.query(
      `SET LOCAL app.current_org_id = $1;
       SET LOCAL app.current_account_id = $2;
       SET LOCAL app.current_user_id = $3;`,
      [session.org_id, session.account_id, session.user_id]
    );

    // Execute queries with RLS enforced
    return callback(client);
  });
  // Context variables automatically cleared after transaction
}

Application Middleware Integration​

Express.js Middleware:

const { Pool } = require('pg');
const pool = new Pool(/* config */);

// Middleware to inject RLS context
function withRLSContext(req, res, next) {
  // Extract session data (from JWT, session store, etc.)
  const { org_id, account_id, user_id } = req.user || {};

  if (!org_id || !user_id) {
    return res.status(401).json({ error: 'Unauthorized' });
  }

  // Provide helper method for RLS-aware queries
  req.dbQuery = async (query, params = []) => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');

      // Set RLS context
      await client.query(
        `SET LOCAL app.current_org_id = $1;
         SET LOCAL app.current_account_id = $2;
         SET LOCAL app.current_user_id = $3;`,
        [org_id, account_id, user_id]
      );

      // Execute query
      const result = await client.query(query, params);

      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  };

  next();
}

// Apply middleware
app.use(withRLSContext);

// Route handler (RLS automatically applied)
app.get('/api/spaces', async (req, res) => {
  const result = await req.dbQuery('SELECT * FROM spaces WHERE status = $1', ['active']);
  res.json(result.rows);
});

NestJS Injectable Service:

import { Injectable } from '@nestjs/common';
import { Pool, PoolClient } from 'pg';

interface SessionContext {
  org_id: string;
  account_id: string;
  user_id: string;
}

@Injectable()
export class DatabaseService {
  constructor(private pool: Pool) {}

  async withRLS<T>(
    context: SessionContext,
    callback: (client: PoolClient) => Promise<T>
  ): Promise<T> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');

      // Set RLS context
      await client.query(
        `SET LOCAL app.current_org_id = $1;
         SET LOCAL app.current_account_id = $2;
         SET LOCAL app.current_user_id = $3;`,
        [context.org_id, context.account_id, context.user_id]
      );

      const result = await callback(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

// Usage
@Injectable()
export class SpacesService {
  constructor(private db: DatabaseService) {}

  async getSpaces(context: SessionContext): Promise<Space[]> {
    return this.db.withRLS(context, async (client) => {
      const result = await client.query('SELECT * FROM spaces');
      return result.rows;
    });
  }
}

Testing RLS Policies​

Unit Tests for Policies​

Test Structure:

  1. Create test data across multiple orgs/accounts
  2. Set session context for different users
  3. Verify data visibility/invisibility
  4. Assert correct error handling

Example Test Suite (Jest + pg):

const { Pool } = require('pg');
const pool = new Pool(/* test database config */);

describe('RLS Policies - Spaces Table', () => {
  let org1, org2, account1, account2, user1, user2;

  beforeAll(async () => {
    // Setup test data
    org1 = await createOrg('Org 1');
    org2 = await createOrg('Org 2');
    account1 = await createAccount(org1.id, 'Account 1');
    account2 = await createAccount(org2.id, 'Account 2');
    user1 = await createUser('user1@test.com');
    user2 = await createUser('user2@test.com');

    await createMembership(user1.id, org1.id, account1.id);
    await createMembership(user2.id, org2.id, account2.id);
  });

  afterAll(async () => {
    await cleanupTestData();
    await pool.end();
  });

  test('org isolation: user cannot see other org spaces', async () => {
    // Create space in org1
    const space1 = await createSpace(org1.id, account1.id, 'Villa A');

    // Query as user2 (org2 context)
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(`SET LOCAL app.current_org_id = $1`, [org2.id]);
      await client.query(`SET LOCAL app.current_account_id = $1`, [account2.id]);
      await client.query(`SET LOCAL app.current_user_id = $1`, [user2.id]);

      const result = await client.query('SELECT * FROM spaces WHERE id = $1', [space1.id]);

      // Should return 0 rows (RLS filtered)
      expect(result.rows.length).toBe(0);

      await client.query('COMMIT');
    } finally {
      client.release();
    }
  });

  test('account isolation: user sees only their account spaces', async () => {
    const space1 = await createSpace(org1.id, account1.id, 'Villa A');
    const account3 = await createAccount(org1.id, 'Account 3');
    const space2 = await createSpace(org1.id, account3.id, 'Villa B');

    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(`SET LOCAL app.current_org_id = $1`, [org1.id]);
      await client.query(`SET LOCAL app.current_account_id = $1`, [account1.id]);
      await client.query(`SET LOCAL app.current_user_id = $1`, [user1.id]);

      const result = await client.query('SELECT * FROM spaces');

      // Should only see space1 (same account)
      expect(result.rows.length).toBe(1);
      expect(result.rows[0].id).toBe(space1.id);

      await client.query('COMMIT');
    } finally {
      client.release();
    }
  });

  test('org-wide access: admin sees all accounts', async () => {
    const adminUser = await createUser('admin@test.com');
    await createMembership(adminUser.id, org1.id, null); // account_id = NULL

    const space1 = await createSpace(org1.id, account1.id, 'Villa A');
    const account3 = await createAccount(org1.id, 'Account 3');
    const space2 = await createSpace(org1.id, account3.id, 'Villa B');

    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(`SET LOCAL app.current_org_id = $1`, [org1.id]);
      await client.query(`SET LOCAL app.current_account_id = $1`, [account1.id]);
      await client.query(`SET LOCAL app.current_user_id = $1`, [adminUser.id]);

      const result = await client.query('SELECT * FROM spaces ORDER BY name');

      // Should see both spaces (org-wide access)
      expect(result.rows.length).toBe(2);

      await client.query('COMMIT');
    } finally {
      client.release();
    }
  });

  test('insert policy: cannot insert into other org', async () => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(`SET LOCAL app.current_org_id = $1`, [org2.id]);
      await client.query(`SET LOCAL app.current_account_id = $1`, [account2.id]);
      await client.query(`SET LOCAL app.current_user_id = $1`, [user2.id]);

      // Attempt to insert into org1 (should fail)
      await expect(
        client.query(
          `INSERT INTO spaces (org_id, account_id, name)
           VALUES ($1, $2, $3)`,
          [org1.id, account1.id, 'Malicious Space']
        )
      ).rejects.toThrow(/new row violates row-level security policy/);

      await client.query('ROLLBACK');
    } finally {
      client.release();
    }
  });
});

Testing with Different Roles​

Role-Based Test Matrix:

const roles = ['admin', 'editor', 'viewer'];

describe('Role-Based RLS', () => {
  roles.forEach(roleName => {
    describe(`As ${roleName}`, () => {
      let user, org, account;

      beforeEach(async () => {
        org = await createOrg('Test Org');
        account = await createAccount(org.id, 'Test Account');
        user = await createUser(`${roleName}@test.com`);
        await assignRole(user.id, org.id, account.id, roleName);
      });

      test('can read spaces', async () => {
        const space = await createSpace(org.id, account.id, 'Villa');
        const result = await queryAsUser(user, 'SELECT * FROM spaces');
        expect(result.rows.length).toBeGreaterThan(0);
      });

      test(`can ${canWrite(roleName) ? '' : 'NOT '}update spaces`, async () => {
        const space = await createSpace(org.id, account.id, 'Villa');

        if (canWrite(roleName)) {
          await expect(
            queryAsUser(user, 'UPDATE spaces SET name = $1 WHERE id = $2', ['New Name', space.id])
          ).resolves.not.toThrow();
        } else {
          await expect(
            queryAsUser(user, 'UPDATE spaces SET name = $1 WHERE id = $2', ['New Name', space.id])
          ).rejects.toThrow();
        }
      });
    });
  });
});

function canWrite(role) {
  return ['admin', 'editor'].includes(role);
}

Performance Considerations​

Index Requirements​

Rule: Always index columns used in RLS policies.

Critical Indexes:

-- Org-level policies: Index org_id first
CREATE INDEX idx_spaces_org_id ON spaces(org_id);

-- Multi-column policies: Composite index
CREATE INDEX idx_spaces_org_account ON spaces(org_id, account_id);

-- Membership lookups in policies
CREATE INDEX idx_memberships_user_org ON memberships(user_id, org_id);
CREATE INDEX idx_memberships_user_org_account ON memberships(user_id, org_id, account_id);

-- Status filters in policies
CREATE INDEX idx_memberships_status ON memberships(user_id, org_id) WHERE status = 'active';

Index Strategy:

  1. Index all foreign keys used in session variable comparisons
  2. Create composite indexes matching policy filter order
  3. Use partial indexes for common WHERE conditions
  4. Monitor query plans with EXPLAIN ANALYZE

Query Plan Analysis​

EXPLAIN with RLS:

-- Set context as RLS would
SET LOCAL app.current_org_id = '123e4567-e89b-12d3-a456-426614174000';
SET LOCAL app.current_account_id = '987e6543-e89b-12d3-a456-426614174000';
SET LOCAL app.current_user_id = '456e7890-e89b-12d3-a456-426614174000';

-- Analyze query with RLS policies
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM spaces WHERE status = 'active';

Good Plan:

Index Scan using idx_spaces_org_account on spaces
  Index Cond: (org_id = '...'::uuid AND account_id = '...'::uuid)
  Filter: (status = 'active'::text)
  Rows: 15 (actual)

Bad Plan:

Seq Scan on spaces  ← Sequential scan = missing index
  Filter: (org_id = '...'::uuid AND status = 'active'::text)
  Rows Removed by Filter: 50000  ← Scanning all orgs!

Performance Benchmarks:

ScenarioWithout IndexWith IndexImprovement
Single-org query (1M rows)1200ms8ms150x
Account filter (100K rows)450ms3ms150x
Membership EXISTS (50K rows)2300ms45ms51x

Query Optimization Tips​

  1. Avoid Complex Subqueries in Policies:

    -- BAD: Nested subqueries
    CREATE POLICY complex_policy ON spaces
      USING (
        EXISTS (
          SELECT 1 FROM memberships m
          WHERE m.user_id = current_setting('app.current_user_id')::uuid
            AND EXISTS (
              SELECT 1 FROM teams t WHERE t.id = m.team_id
            )
        )
      );

    -- GOOD: Simplified join
    CREATE POLICY simple_policy ON spaces
      USING (
        EXISTS (
          SELECT 1 FROM memberships m
          WHERE m.user_id = current_setting('app.current_user_id')::uuid
            AND m.org_id = spaces.org_id
        )
      );

  2. Cache Membership Checks:

    // Cache user's org membership in session
    req.session.has_org_access = await checkOrgAccess(user_id, org_id);

    // Use cached value in policy
    await client.query(`SET LOCAL app.has_org_access = $1`, [req.session.has_org_access]);

  3. Use Partial Indexes for Active Records:

    CREATE INDEX idx_active_spaces ON spaces(org_id, account_id)
      WHERE status = 'active';

Troubleshooting​

Common Mistakes​

Mistake 1: Forgetting to Set Session Context​

Symptom: Queries return 0 rows or error "unrecognized configuration parameter"

// ❌ WRONG: No context set
const result = await client.query('SELECT * FROM spaces');
// Returns 0 rows (RLS blocks everything)

// ✅ CORRECT: Set context first
await client.query(`SET LOCAL app.current_org_id = $1`, [org_id]);
await client.query(`SET LOCAL app.current_account_id = $1`, [account_id]);
const result = await client.query('SELECT * FROM spaces');

Mistake 2: Using SET Instead of SET LOCAL​

Symptom: Context leaks between transactions/requests

// ❌ WRONG: Persists across transactions
await client.query(`SET app.current_org_id = $1`, [org_id]);

// ✅ CORRECT: Transaction-scoped
await client.query('BEGIN');
await client.query(`SET LOCAL app.current_org_id = $1`, [org_id]);
await client.query('COMMIT');
// Context automatically cleared

Mistake 3: Missing WITH CHECK Policy​

Symptom: Can SELECT but not INSERT/UPDATE

-- ❌ WRONG: Only USING clause
CREATE POLICY spaces_policy ON spaces
  USING (org_id = current_setting('app.current_org_id')::uuid);

-- ✅ CORRECT: Add WITH CHECK for inserts
CREATE POLICY spaces_select ON spaces
  FOR SELECT
  USING (org_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY spaces_insert ON spaces
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

Mistake 4: RLS Not Enabled on Table​

Symptom: Policies exist but not enforced

-- Check if RLS is enabled
SELECT tablename, rowsecurity FROM pg_tables
WHERE schemaname = 'public' AND tablename = 'spaces';

-- Enable if needed
ALTER TABLE spaces ENABLE ROW LEVEL SECURITY;

Debugging RLS Issues​

Debug Step 1: Check Policy Definitions​

-- View all policies on a table
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'spaces';

Debug Step 2: Test Policy in Isolation​

-- Manually test policy expression
SET LOCAL app.current_org_id = '123e4567-e89b-12d3-a456-426614174000';

-- Test USING clause
SELECT id, org_id,
  (org_id = current_setting('app.current_org_id')::uuid) AS passes_policy
FROM spaces;

Debug Step 3: Bypass RLS for Debugging​

-- Temporarily disable RLS (testing only!)
ALTER TABLE spaces DISABLE ROW LEVEL SECURITY;

-- Run query
SELECT * FROM spaces;

-- Re-enable
ALTER TABLE spaces ENABLE ROW LEVEL SECURITY;

WARNING: Never disable RLS in production!

Debug Step 4: Check Session Variables​

-- View current session variables
SHOW app.current_org_id;
SHOW app.current_account_id;
SHOW app.current_user_id;

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

Debug Step 5: Log Policy Violations​

// Wrap queries with error logging
async function queryWithLogging(client, query, params) {
  try {
    return await client.query(query, params);
  } catch (err) {
    if (err.message.includes('row-level security policy')) {
      console.error('RLS Policy Violation:', {
        query,
        params,
        context: {
          org_id: await client.query(`SELECT current_setting('app.current_org_id', true)`),
          account_id: await client.query(`SELECT current_setting('app.current_account_id', true)`),
          user_id: await client.query(`SELECT current_setting('app.current_user_id', true)`)
        }
      });
    }
    throw err;
  }
}

Error Messages​

ErrorCauseSolution
new row violates row-level security policyINSERT/UPDATE blocked by WITH CHECKVerify session context matches row values
unrecognized configuration parameter: "app.current_org_id"Session variable not setCall SET LOCAL before queries
invalid input syntax for type uuid: ""Empty string passed instead of NULLUse current_setting('var', true) to return NULL if unset
permission denied for tableUser lacks table-level permissionsGRANT SELECT/INSERT/UPDATE on table

End of Document