kers, or admin tools from leaking data.

ALTER TABLE workspace_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE workspace_reports FORCE ROW LEVEL SECURITY;

Rationale: FORCE ensures that even the schema owner cannot read rows outside the policy. This is critical when your application uses a single database role for both DDL migrations and runtime queries.

Step 2: Define Policies Using Namespaced Session Variables

PostgreSQL policies cannot accept direct parameters. They must read from session-level configuration. Variable names must contain a period to distinguish them from built-in GUCs.

CREATE POLICY workspace_isolation ON workspace_reports
  FOR SELECT
  USING (
    workspace_id = current_setting('ctx.workspace_id')::uuid
    AND (
      current_setting('ctx.user_role') = 'admin'
      OR reporter_id = current_setting('ctx.user_id')::uuid
    )
  );

Rationale: The policy combines workspace scoping with role-based visibility. Admins see all reports in the workspace; standard users only see their own. The current_setting() calls are evaluated once per query execution, not per row, provided the policy predicate is indexable.

Step 3: Inject Context at Request Time

Your backend must establish the security context before executing any dashboard query. This requires an explicit transaction with SET LOCAL to guarantee automatic cleanup.

import { Pool, PoolClient } from 'pg';

const pool = new Pool({ /* connection config */ });

async function executeDashboardQuery(
  client: PoolClient,
  workspaceId: string,
  userId: string,
  userRole: string,
  sql: string
) {
  await client.query('BEGIN');
  
  try {
    // Inject context into the current transaction scope
    await client.query(
      `SELECT set_config('ctx.workspace_id', $1, true)`,
      [workspaceId]
    );
    await client.query(
      `SELECT set_config('ctx.user_id', $1, true)`,
      [userId]
    );
    await client.query(
      `SELECT set_config('ctx.user_role', $1, true)`,
      [userRole]
    );

    // Execute the dashboard query. RLS enforces isolation automatically.
    const result = await client.query(sql);
    await client.query('COMMIT');
    return result.rows;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  }
}

Rationale: set_config(..., true) is the programmatic equivalent of SET LOCAL. The third argument forces transaction-local scope, meaning the variables are automatically cleared when the transaction ends. This prevents context leakage across requests, which is critical when using connection poolers.

Step 4: Optimize for the Query Planner

RLS policies become part of the query plan. If the policy references a function that isn't marked STABLE, PostgreSQL may re-evaluate it for every row, destroying performance.

CREATE FUNCTION get_workspace_id() RETURNS uuid
LANGUAGE sql STABLE PARALLEL SAFE AS
$$ SELECT current_setting('ctx.workspace_id')::uuid $$;

CREATE FUNCTION get_user_role() RETURNS text
LANGUAGE sql STABLE PARALLEL SAFE AS
$$ SELECT current_setting('ctx.user_role') $$;

-- Rewrite policy to use the optimized functions
DROP POLICY workspace_isolation ON workspace_reports;

CREATE POLICY workspace_isolation ON workspace_reports
  FOR SELECT
  USING (
    workspace_id = get_workspace_id()
    AND (
      get_user_role() = 'admin'
      OR reporter_id = get_user_id()
    )
  );

Rationale: Marking functions as STABLE and PARALLEL SAFE signals to the planner that the result is constant within a single query execution and can be safely used in parallel workers. This enables index-only scans and prevents sequential table scans on large datasets.

Pitfall Guide

1. Connection Pooler Context Bleed

Explanation: PgBouncer in transaction or statement mode reuses backend connections across different client requests. If you use SET (without LOCAL) or set_config(..., false), the session variable persists on the backend connection and leaks into the next user's query. Fix: Always use SET LOCAL or set_config(..., true) inside an explicit BEGIN/COMMIT block. Verify your pooler configuration matches your transaction scope strategy.

2. Table Owner Bypass

Explanation: PostgreSQL grants implicit RLS bypass to table owners and roles with BYPASSRLS. If your migration runner or background worker connects as the table owner, it will see all rows regardless of policies. Fix: Create a dedicated application role without superuser or BYPASSRLS privileges. Use FORCE ROW LEVEL SECURITY on all analytics tables. Test policies using the exact role your backend uses in production.

3. Asymmetric Read/Write Policies

Explanation: Defining only a USING clause protects reads but leaves writes unprotected. A user could insert or update rows tagged with another workspace's ID, polluting your data. Fix: Always pair USING with WITH CHECK for INSERT, UPDATE, and DELETE operations. WITH CHECK validates the new row state before it's written.

4. View Privilege Inheritance

Explanation: Views execute with the privileges of their creator by default. If you create a masking view as a superuser, queries against it can bypass RLS on the underlying table. Fix: In PostgreSQL 15+, set security_invoker = true on the view. Alternatively, create views under a restricted application role that lacks superuser privileges.

5. Unoptimized Policy Functions

Explanation: Using current_setting() directly in a policy without wrapping it in a STABLE function can cause per-row evaluation, especially in complex joins or window functions. Fix: Wrap session variable reads in STABLE PARALLEL SAFE SQL functions. Verify execution plans with EXPLAIN ANALYZE to ensure the policy predicate is evaluated once, not per row.

6. Silent Policy Absence

Explanation: If no policy matches a query, PostgreSQL defaults to denying access. In development, this often manifests as empty result sets rather than explicit errors, making debugging difficult. Fix: Implement a catch-all policy that logs a warning or raises a notice when context is missing. Use environment-specific policies that fail loudly in non-production environments.

7. Missing Composite Index Alignment

Explanation: RLS policies filter on specific columns. If your indexes don't lead with those columns, the planner cannot push the policy predicate down, resulting in full table scans. Fix: Ensure composite indexes start with the RLS filter columns. For example: CREATE INDEX idx_reports_workspace_user ON workspace_reports (workspace_id, reporter_id, created_at DESC);

Production Bundle

Action Checklist

• Enable RLS and force enforcement on all analytics tables
• Create namespaced session variables (ctx.*) for workspace, user, and role context
• Implement transaction-scoped context injection using SET LOCAL or set_config(..., true)
• Wrap current_setting() calls in STABLE PARALLEL SAFE functions for query planner optimization
• Align composite indexes to lead with RLS policy columns
• Configure PgBouncer to match transaction scope and verify no context leakage occurs
• Test all policies using the exact application role, never as postgres or superuser
• Add WITH CHECK clauses to all write policies to prevent cross-tenant data pollution

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Single-tenant SaaS with embedded BI	RLS with workspace_id + FORCE	Eliminates need for query rewriting; guarantees isolation regardless of SQL origin	Low (one-time schema change, minimal runtime overhead)
Multi-tier internal tool with role-based views	RLS + security_invoker = true views	Separates row filtering from column masking; prevents privilege escalation via views	Medium (requires view maintenance, but reduces app-layer complexity)
AI-assisted query builder or ad-hoc console	RLS + STABLE policy functions + strict index alignment	LLM-generated SQL cannot bypass storage-level enforcement; planner optimizes around policy	Low (index alignment is standard practice; RLS adds negligible CPU)

Configuration Template

-- 1. Schema preparation
ALTER TABLE workspace_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE workspace_reports FORCE ROW LEVEL SECURITY;

-- 2. Optimized context functions
CREATE FUNCTION get_workspace_id() RETURNS uuid
LANGUAGE sql STABLE PARALLEL SAFE AS
$$ SELECT current_setting('ctx.workspace_id')::uuid $$;

CREATE FUNCTION get_user_id() RETURNS uuid
LANGUAGE sql STABLE PARALLEL SAFE AS
$$ SELECT current_setting('ctx.user_id')::uuid $$;

CREATE FUNCTION get_user_role() RETURNS text
LANGUAGE sql STABLE PARALLEL SAFE AS
$$ SELECT current_setting('ctx.user_role') $$;

-- 3. Read policy
CREATE POLICY workspace_read ON workspace_reports
  FOR SELECT
  USING (
    workspace_id = get_workspace_id()
    AND (
      get_user_role() = 'admin'
      OR reporter_id = get_user_id()
    )
  );

-- 4. Write policy
CREATE POLICY workspace_write ON workspace_reports
  FOR INSERT
  WITH CHECK (
    workspace_id = get_workspace_id()
    AND reporter_id = get_user_id()
  );

-- 5. Index alignment
CREATE INDEX idx_reports_workspace_user_time 
  ON workspace_reports (workspace_id, reporter_id, created_at DESC);

// TypeScript context injection wrapper
import { Pool, PoolClient } from 'pg';

export class SecureQueryExecutor {
  constructor(private pool: Pool) {}

  async run<T>(
    workspaceId: string,
    userId: string,
    userRole: string,
    query: string,
    params?: any[]
  ): Promise<T[]> {
    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      
      await client.query(`SELECT set_config('ctx.workspace_id', $1, true)`, [workspaceId]);
      await client.query(`SELECT set_config('ctx.user_id', $1, true)`, [userId]);
      await client.query(`SELECT set_config('ctx.user_role', $1, true)`, [userRole]);

      const result = await client.query(query, params);
      await client.query('COMMIT');
      return result.rows as T[];
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  }
}

Quick Start Guide

1. Identify your isolation boundary: Determine whether your primary filter is tenant_id, workspace_id, or organization_id. Add this column to all analytics tables if missing.
2. Enable and force RLS: Run ALTER TABLE <table> ENABLE ROW LEVEL SECURITY; followed by FORCE ROW LEVEL SECURITY; on every table exposed to embedded queries.
3. Create context functions: Define STABLE PARALLEL SAFE SQL functions that read from namespaced session variables (ctx.*). This prevents per-row evaluation and enables parallel query execution.
4. Inject context in transactions: Wrap every dashboard query in a BEGIN/COMMIT block. Use set_config(..., true) to set workspace, user, and role variables before execution.
5. Validate with EXPLAIN: Run EXPLAIN ANALYZE on representative dashboard queries. Verify that the policy predicate appears in the plan and that index scans are used instead of sequential scans.