You are an expert in PostgreSQL row-level security (RLS) for implementing fine-grained, per-row access control.

## Key Points

- `USING`: filters which existing rows are visible (applied on SELECT, UPDATE, DELETE).
- `WITH CHECK`: validates new or modified rows (applied on INSERT and the new row after UPDATE).
- Use `FORCE ROW LEVEL SECURITY` during development and testing so table owners are also subject to policies.
- Define separate policies per operation (SELECT, INSERT, UPDATE, DELETE) for clarity and least privilege.
- Use restrictive policies for global constraints (e.g., "must be active") and permissive policies for role-specific access.
- Always validate the application-set context variables (e.g., `app.tenant_id`) — a missing or empty setting should deny access, not grant it.
- Create a dedicated application database role that is not the table owner, since table owners bypass RLS by default unless FORCE is used.
- Index columns used in RLS policies (e.g., `tenant_id`, `owner_id`) because the policy conditions are appended to every query.
- Test RLS policies by connecting as the application role and verifying both visible and invisible rows.
- **Table owner bypasses RLS**: By default, the table owner and superusers are exempt from RLS. Use `FORCE ROW LEVEL SECURITY` or ensure the app connects as a non-owner role.
- **No policies means no access**: Once RLS is enabled, if no policies exist for a role, that role sees zero rows. This is secure by default but can cause confusion.
- **Leaking data through functions**: Functions with `SECURITY DEFINER` execute as the function owner and may bypass RLS. Use `SECURITY INVOKER` or set the role explicitly within the function.

## Quick Example

```sql
-- Enable RLS on a table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (important for testing)
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
```

Row-Level Security — PostgreSQL

You are an expert in PostgreSQL row-level security (RLS) for implementing fine-grained, per-row access control.

Core Philosophy

Overview

Row-Level Security (RLS) allows you to define policies that restrict which rows a given database role can see or modify. When enabled on a table, all queries are transparently filtered through the defined policies. RLS is essential for multi-tenant applications, HIPAA-compliant systems, and any scenario requiring per-row authorization enforced at the database level.

Core Concepts

Enabling RLS

-- Enable RLS on a table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (important for testing)
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Basic Policies

-- Allow users to see only their own rows
CREATE POLICY user_isolation ON documents
    FOR ALL
    USING (owner_id = current_setting('app.current_user_id')::bigint);

-- Separate policies for SELECT vs INSERT/UPDATE/DELETE
CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.current_user_id')::bigint);

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

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.current_user_id')::bigint)
    WITH CHECK (owner_id = current_setting('app.current_user_id')::bigint);

CREATE POLICY delete_own ON documents
    FOR DELETE
    USING (owner_id = current_setting('app.current_user_id')::bigint);

USING vs WITH CHECK

• USING: filters which existing rows are visible (applied on SELECT, UPDATE, DELETE).
• WITH CHECK: validates new or modified rows (applied on INSERT and the new row after UPDATE).

-- Users can read all published documents, but only edit their own
CREATE POLICY read_published ON documents
    FOR SELECT
    USING (is_published = true OR owner_id = current_setting('app.current_user_id')::bigint);

CREATE POLICY edit_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.current_user_id')::bigint)
    WITH CHECK (owner_id = current_setting('app.current_user_id')::bigint);

Implementation Patterns

Multi-Tenant Isolation

-- Tenant context is set per transaction by the application
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    tenant_id bigint NOT NULL,
    customer_name text,
    total numeric(12,2)
);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Each tenant sees only their data
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

-- Application sets context before queries
-- (in each transaction or connection setup)
SET app.tenant_id = '42';
SELECT * FROM orders;  -- only sees tenant 42's orders

Role-Based Access with RLS

CREATE TABLE patient_records (
    id bigserial PRIMARY KEY,
    patient_id bigint NOT NULL,
    doctor_id bigint,
    department text,
    diagnosis text,
    created_at timestamptz DEFAULT now()
);

ALTER TABLE patient_records ENABLE ROW LEVEL SECURITY;

-- Doctors see only their own patients
CREATE POLICY doctor_access ON patient_records
    FOR SELECT
    TO doctor_role
    USING (doctor_id = current_setting('app.user_id')::bigint);

-- Department heads see all records in their department
CREATE POLICY dept_head_access ON patient_records
    FOR SELECT
    TO dept_head_role
    USING (department = current_setting('app.department'));

-- Admins see everything
CREATE POLICY admin_access ON patient_records
    FOR ALL
    TO admin_role
    USING (true)
    WITH CHECK (true);

Permissive vs Restrictive Policies

-- Permissive (default): any matching policy grants access (OR logic)
CREATE POLICY allow_own ON documents
    AS PERMISSIVE
    FOR SELECT
    USING (owner_id = current_setting('app.current_user_id')::bigint);

CREATE POLICY allow_public ON documents
    AS PERMISSIVE
    FOR SELECT
    USING (is_public = true);
-- Result: user sees own docs OR public docs

-- Restrictive: must pass ALL restrictive policies (AND logic)
CREATE POLICY require_active ON documents
    AS RESTRICTIVE
    FOR SELECT
    USING (is_active = true);
-- Result: user sees (own docs OR public docs) AND active docs

Setting Application Context

-- Option 1: SET per transaction
BEGIN;
SELECT set_config('app.tenant_id', '42', true);  -- true = local to transaction
-- ... queries ...
COMMIT;

-- Option 2: SET on connection (session-level)
SET app.current_user_id = '123';

-- Option 3: Using a function for validated context
CREATE OR REPLACE FUNCTION app_tenant_id() RETURNS bigint AS $$
BEGIN
    RETURN NULLIF(current_setting('app.tenant_id', true), '')::bigint;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE POLICY tenant_policy ON orders
    FOR ALL
    USING (tenant_id = app_tenant_id());

Best Practices

• Use FORCE ROW LEVEL SECURITY during development and testing so table owners are also subject to policies.
• Define separate policies per operation (SELECT, INSERT, UPDATE, DELETE) for clarity and least privilege.
• Use restrictive policies for global constraints (e.g., "must be active") and permissive policies for role-specific access.
• Always validate the application-set context variables (e.g., app.tenant_id) — a missing or empty setting should deny access, not grant it.
• Create a dedicated application database role that is not the table owner, since table owners bypass RLS by default unless FORCE is used.
• Index columns used in RLS policies (e.g., tenant_id, owner_id) because the policy conditions are appended to every query.
• Test RLS policies by connecting as the application role and verifying both visible and invisible rows.

Common Pitfalls

• Table owner bypasses RLS: By default, the table owner and superusers are exempt from RLS. Use FORCE ROW LEVEL SECURITY or ensure the app connects as a non-owner role.
• No policies means no access: Once RLS is enabled, if no policies exist for a role, that role sees zero rows. This is secure by default but can cause confusion.
• Leaking data through functions: Functions with SECURITY DEFINER execute as the function owner and may bypass RLS. Use SECURITY INVOKER or set the role explicitly within the function.
• Performance of complex policies: Policies with subqueries or joins are evaluated for every row. Keep policies simple and ensure supporting indexes exist.
• Dump and restore issues: pg_dump runs as a superuser and bypasses RLS, so all rows are included. But restoring into a database with RLS enabled may cause unexpected behavior if the restoring role is subject to policies.
• Forgetting WITH CHECK on mutations: Without WITH CHECK, a user could INSERT or UPDATE a row to have a tenant_id they do not own, potentially leaking it to another tenant.

Anti-Patterns

Over-engineering for hypothetical scale. Building for millions of users when you have hundreds adds complexity without value. Solve today's problems first.

Ignoring the existing ecosystem. Reinventing functionality that mature libraries already provide well wastes time and introduces unnecessary risk.

Premature abstraction. Creating elaborate frameworks and utilities before you have enough concrete cases to know what the abstraction should look like produces the wrong abstraction.

Neglecting error handling at boundaries. Internal code can trust its inputs, but system boundaries (user input, APIs, file I/O) require defensive validation.

Skipping documentation for obvious code. What is obvious to you today will not be obvious to your colleague next month or to you next year.