Row-Level Security: fundamentals

Capsule overview

You already saw the ceiling of the "shared schema with tenant_id and manual discipline" model: a single new query that forgets the filter = a cross-tenant leak. Capsule 03 proposed mitigation patterns (the repository pattern, lint, code review) that reduce the risk but don't eliminate it. This capsule teaches you the mechanism PostgreSQL offers to close that ceiling with a database-level guarantee: Row-Level Security (RLS).

You're going to learn RLS's conceptual and operational fundamentals in PostgreSQL 16+: what exactly a policy is, how it gets turned on with ENABLE ROW LEVEL SECURITY, why FORCE ROW LEVEL SECURITY almost always goes with it, how the role running the query interacts with the policies, and why you need a "session context" mechanism (custom settings like app.tenant_id) so the policies know which tenant the current connection belongs to. Capsule 05 covers the integration with FastAPI and SQLAlchemy async; this one focuses on PostgreSQL's raw concepts.

⚠️ A critical caveat we're going to repeat several times: RLS here is used only for multi-tenancy, not for auth/RBAC. Mixing the two problems is the #1 mistake of teams that discover RLS and it leads to a hell of undebuggable SQL policies. Capsule 05 is going to repeat this caveat. Read it every time it appears — internalizing it saves you months of refactoring.

By the end you'll have working RLS over capsule 03's schema and you'll have verified, with deliberately "malicious" queries in psql, that PostgreSQL really does filter even when you try to read another tenant's data.

Mental model: the policy as an invisible WHERE

The most useful way to think about RLS isn't "an advanced security feature." It's: an invisible WHERE that PostgreSQL automatically adds to every query against the table.

Without RLS:

SELECT * FROM tasks;
-- Returns ALL the rows (from every tenant).

With RLS and a policy USING (tenant_id = current_setting('app.tenant_id')::BIGINT):

SELECT * FROM tasks;
-- PostgreSQL internally turns it into:
-- SELECT * FROM tasks WHERE tenant_id = current_setting('app.tenant_id')::BIGINT;
-- Returns only the rows of the setting's tenant.

The dev doesn't see the WHERE in their code. The dev can't skip the WHERE even by writing raw SQL. The dev can't even know the filtered rows exist — they're invisible to that session. To that session, the table appears to contain only their tenant's rows.

That "invisibility" is the guarantee. It isn't prevention by convention (like capsule 03's repository pattern). It's prevention by mechanism: PostgreSQL doesn't return the rows, end of story. Even if someone writes SELECT * FROM tasks WHERE 1=1 or DELETE FROM tasks, the policy acts first.

RLS's key question is: "where does the value of current_setting('app.tenant_id') come from?". Capsule 05 answers that (the app sets it with SET LOCAL at the start of each transaction from a FastAPI dependency). This capsule focuses on how PostgreSQL applies policies assuming the setting already exists.

Turning RLS on for a table

To turn RLS on for a table you need two steps: declare that the table has RLS and create at least one policy. With no policy, RLS enabled means "reject ALL access" — it effectively blocks the table.

Step 1: turn on RLS

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

After this, any query against tasks from a non-superuser role that is NOT the table's owner returns 0 rows (there are no policies that allow rows).

-- A quick test
SELECT * FROM tasks;
-- Output: 0 rows (no policy yet, RLS blocks everything).

Step 2: create a policy

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

Anatomy of the policy:

• tenant_isolation: the policy's name. Convention: descriptive of the purpose.
• ON tasks: the table it applies to.
• USING (...): the filtering predicate for SELECT, UPDATE, DELETE. The rows that evaluate to TRUE are visible; the ones that evaluate to FALSE or NULL are invisible.
• current_setting('app.tenant_id')::BIGINT: reads the session's setting and casts it to BIGINT. Capsule 05 shows how it gets set from Python.

If you also want to control INSERT, you add WITH CHECK:

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

The difference between USING and WITH CHECK:

• USING: filters which rows are visible for SELECT/UPDATE/DELETE.
• WITH CHECK: validates which values are acceptable on INSERT or UPDATE. Without WITH CHECK, someone could insert a row with tenant_id = 999 even though their session belongs to tenant 1.

For multi-tenancy, always include WITH CHECK. Otherwise a bug in the code could insert rows attributed to another tenant.

Step 3: verification with psql

-- Set tenant 1's context
SET LOCAL app.tenant_id = '1';

-- A query that looks like it "reads everything" actually filters via the policy
SELECT id, tenant_id, title FROM tasks;
-- Expected output: only rows with tenant_id = 1.

Change the tenant and run it again:

SET LOCAL app.tenant_id = '2';
SELECT id, tenant_id, title FROM tasks;
-- Expected output: only rows with tenant_id = 2.

Here you've already seen the magic. The query is the same, the SQL is the same, but PostgreSQL filters automatically according to the setting. That's what closes the ceiling of the "manual discipline" model.

A critical trap: FORCE ROW LEVEL SECURITY

By default, RLS does NOT apply to the table's owner role. This is documented but it's the most common surprise.

-- If you connect as the role that CREATED the table (typically postgres in simple setups)
SELECT * FROM tasks;
-- Returns ALL the rows (RLS ignored for the owner).

In development setups where the app connects as postgres, RLS seems "not to work." That's the cause.

The solution: FORCE ROW LEVEL SECURITY.

ALTER TABLE tasks FORCE ROW LEVEL SECURITY;

After FORCE, RLS applies to the owner too. Now even if you connect as postgres, the policies filter.

Recommendation: always turn on ENABLE and FORCE together in multi-tenancy. The legitimate exception is when you deliberately want a specific role to bypass RLS (admin jobs, internal dashboards, cross-tenant ETLs). For that case, see the bypass pattern with a BYPASSRLS role below.

-- The complete pattern
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

⚠️ Critical caveat: RLS for multi-tenancy, NOT for auth/RBAC

This caveat appears several times in this module on purpose. It's the most expensive mistake to reverse.

Multi-tenancy with RLS:

• The isolated unit is the tenant (an organization/customer company).
• The tenant is stable throughout the whole session/request: an Acme user never switches to Globex in the middle of a request.
• A single policy per table: USING (tenant_id = current_setting('app.tenant_id')).
• The setting gets set once per request (SET LOCAL from a dependency).

Auth/RBAC with RLS (an anti-pattern):

• The "isolated" unit is the individual user or the role with granular permissions.
• The permissions change: user X can read projects A, B and edit only A; user Y can read A, C and edit B.
• You need N policies per table, one per permission type (SELECT, INSERT, UPDATE, DELETE).
• Each policy contains complex logic for "does this user have permission over this row for this action?".
• Every query becomes a debugging session over impossible SQL policies.

Why the anti-pattern shows up: a dev discovers RLS and thinks "I can express all my permissions as policies." It sounds elegant. Six months later they have 80 policies across 20 tables, a permission matrix where nobody knows what happens when an admin user tries to edit an archived project, and every new query means "first understand the 8 policies that apply to this table." Productivity collapses.

The rule: RLS for multi-tenancy (a stable tenant_id, one policy per table, a simple session setting). For per-user permissions, use an RBAC system in the application layer (FastAPI dependencies with explicit checks, or libraries like Casbin). Auth/RBAC is covered in guide #9 of the path, not here.

How to tell whether what you're writing is multi-tenancy or RBAC:

• If the question is "which tenant sees this row?": multi-tenancy. RLS is the tool.
• If the question is "can this user perform this action on this row?": RBAC. RLS is NOT the tool.

How PostgreSQL runs a query with RLS

It's worth understanding what happens internally. When you run:

SELECT id, title FROM tasks WHERE status = 'open';

And a policy USING (tenant_id = current_setting('app.tenant_id')::BIGINT) exists, PostgreSQL internally runs:

SELECT id, title FROM tasks
WHERE (tenant_id = current_setting('app.tenant_id')::BIGINT)  -- from the policy
  AND status = 'open';                                          -- from the original query

The policy's predicate gets added as an AND to the original query's WHERE. EXPLAIN shows that predicate:

EXPLAIN ANALYZE
SELECT id, title FROM tasks WHERE status = 'open';

Typical output (with the right index):

Index Scan using ix_tasks_tenant_status on tasks
  Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::bigint)
              AND (status = 'open'::text))

Performance implications:

• The policy's predicate gets evaluated on EVERY query. If your policy is simple (column = setting), the cost is marginal (~5%).
• If the policy contains a complex subquery (tenant_id IN (SELECT id FROM tenants WHERE owner = ...)), the cost can be high. This happens in the RBAC anti-pattern, not in well-done multi-tenancy.
• The indexes you designed for tenant_id queries (capsule 03) keep working with RLS. PostgreSQL uses them for the policy's predicate exactly as it used them for the manual WHERE.

The operational implication: well-done RLS does NOT require rewriting your indexes. The composite (tenant_id, ...) indexes you already had are still optimal.

Worked example: the complete setup in psql

Let's do the complete setup from scratch with two tenants and verify the isolation. You need an accessible PostgreSQL 16+ (Docker works perfectly).

1. Create the DB and connect

psql -U postgres

CREATE DATABASE rls_demo;
\c rls_demo

2. Create the base schema

CREATE TABLE tenants (
    id BIGSERIAL PRIMARY KEY,
    slug VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ix_tasks_tenant_created ON tasks (tenant_id, created_at DESC);

-- Insert two tenants and data
INSERT INTO tenants (slug, name) VALUES ('acme', 'Acme Corp'), ('globex', 'Globex');

INSERT INTO tasks (tenant_id, title) VALUES
    (1, 'Acme task 1'),
    (1, 'Acme task 2'),
    (2, 'Globex task 1'),
    (2, 'Globex task 2'),
    (2, 'Globex task 3');

3. Create a dedicated role for the app (important)

This step is key. If the app connects as postgres (the owner), you need FORCE ROW LEVEL SECURITY. Even so, the recommended practice is to create a separate role dedicated to the app:

CREATE ROLE app_user LOGIN PASSWORD 'app_password';
GRANT CONNECT ON DATABASE rls_demo TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON tasks TO app_user;
GRANT SELECT ON tenants TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

4. Turn on RLS and create the policy

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

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

5. Testing the isolation from psql

Exit psql and reconnect as app_user:

psql -U app_user -d rls_demo

-- With no context: the query fails because current_setting raises an error
SELECT * FROM tasks;
-- ERROR: unrecognized configuration parameter "app.tenant_id"

To avoid the error with no setting, you can use the "tolerant" version of current_setting:

-- A version that doesn't fail if the setting doesn't exist (it returns NULL).
-- Useful when you want NOTHING to be visible with no context instead of an error.
DROP POLICY tenant_isolation ON tasks;
CREATE POLICY tenant_isolation ON tasks
    USING (
        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    )
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    );

The second argument true in current_setting('app.tenant_id', true) means "missing_ok": it returns NULL instead of an error if the setting doesn't exist. The additional NULLIF(..., '') converts an empty string to NULL (some drivers set an empty string instead of setting nothing).

-- With no context: 0 rows (because the predicate evaluates against NULL)
SELECT * FROM tasks;
-- Output: 0 rows.

-- Set tenant 1's context
SET LOCAL app.tenant_id = '1';

SELECT * FROM tasks;

Expected output:

 id | tenant_id |    title    | status |          created_at
----+-----------+-------------+--------+-------------------------------
  1 |         1 | Acme task 1 | open   | 2026-05-02 10:00:00.000+00
  2 |         1 | Acme task 2 | open   | 2026-05-02 10:00:01.000+00
(2 rows)

-- Switch to tenant 2
SET LOCAL app.tenant_id = '2';

SELECT * FROM tasks;

Expected output:

 id | tenant_id |     title     | status |          created_at
----+-----------+---------------+--------+-------------------------------
  3 |         2 | Globex task 1 | open   | 2026-05-02 10:00:02.000+00
  4 |         2 | Globex task 2 | open   | 2026-05-02 10:00:03.000+00
  5 |         2 | Globex task 3 | open   | 2026-05-02 10:00:04.000+00
(3 rows)

This is DB-level guaranteed isolation. The same SQL, the same role, a different setting → different results. And nothing in the SQL mentions tenant_id.

6. The "malicious" test: trying to bypass the isolation

SET LOCAL app.tenant_id = '1';

-- Try to read tenant 2's tasks with an explicit WHERE
SELECT * FROM tasks WHERE tenant_id = 2;

Expected output:

 id | tenant_id | title | status | created_at
----+-----------+-------+--------+------------
(0 rows)

PostgreSQL filtered by the policy (tenant_id = 1) BEFORE the manual WHERE. The condition tenant_id = 2 AND tenant_id = 1 evaluates to FALSE for every row. Result: 0 rows. The malicious attempt failed.

7. "Malicious" test 2: trying to insert into another tenant

SET LOCAL app.tenant_id = '1';

-- Try to insert a row into tenant 2
INSERT INTO tasks (tenant_id, title) VALUES (2, 'INTRUDER');

Expected output:

ERROR:  new row violates row-level security policy for table "tasks"

The WITH CHECK rejected the INSERT because the tenant_id = 2 doesn't match the setting (app.tenant_id = 1). The cross-tenant write attempt also failed.

The bypass pattern for admin/jobs

There are legitimate cases where you need to read/write cross-tenant: nightly jobs, admin dashboards, maintenance scripts, ETLs. For those cases there are two patterns:

Pattern A: a role with BYPASSRLS

You create a specific role for administrative jobs that has BYPASSRLS. That role ignores all the policies.

CREATE ROLE admin_job LOGIN PASSWORD 'admin_password' BYPASSRLS;
GRANT SELECT ON tasks TO admin_job;

When the nightly job connects as admin_job, the policies get ignored and it can read every table cross-tenant.

Pro: explicit and separate from the main app. Con: you have to manage a second role's credentials.

Pattern B: a conditional bypass inside the policy

Another option is to add a bypass to the policy's predicate:

DROP POLICY tenant_isolation ON tasks;
CREATE POLICY tenant_isolation ON tasks
    USING (
        current_setting('app.bypass_rls', true) = 'on'
        OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    );

When the admin job needs cross-tenant access, it sets:

SET LOCAL app.bypass_rls = 'on';
SELECT * FROM tasks;  -- now it sees everything.

Pro: a single role for everything, granular control in code. Con: a dev could add SET LOCAL app.bypass_rls = 'on' "by mistake" and open a hole. Risky.

Recommendation: Pattern A (a dedicated role with BYPASSRLS) is more operationally defensible. Pattern B is useful when you CAN'T create separate roles.

Why does this matter in real work?

1. It's the answer to "how do you guarantee isolation?" that closes enterprise deals. Serious buyers ask. Your answer with RLS ("PostgreSQL enforces policies at the DB level, no query can cross tenants even with a bug in the code") is defensible. With a manual WHERE tenant_id it isn't.

2. It's the safety net that protects against human bugs. Your team is going to grow. New devs are going to open PRs. Some PR is going to forget the filter. With RLS, that lapse doesn't become a breach — the DB prevents it.

3. It's the PostgreSQL feature with the most "trending" in modern product. Supabase built it as the core of its product. Hasura exposes it as a native mechanism. AWS Aurora recommends it in its SaaS whitepapers. Knowing it deeply is an advantage.

4. Misunderstood it's debugging hell. Mistake #1 (using RLS for auth) destroys productivity. Knowing the rule "RLS for multi-tenancy, NOT for RBAC" anticipates the problem before you commit it.

Traps and common mistakes

Mistake 1 (conceptual and MOST IMPORTANT): using RLS for auth/RBAC

Symptom: a team discovers RLS and starts expressing per-user permissions as policies. "This policy allows only the project's owner to edit it, this other one allows only admins to delete it, this one for reviewers..." Three months later they have 50 policies, queries that take longer because of the policies, and debugging that requires understanding advanced policy SQL.

Why it happens: RLS sounds like "row-level access control," which naturally gets interpreted as "per-user permissions." The trap is that RLS is designed for an isolation unit that's stable during the session (the tenant), not for granular permissions that change per action.

How to tell: ask "does my policy's predicate depend on the individual user or on the tenant?". If it depends on the user, it is NOT multi-tenancy — it's RBAC and RLS isn't the right tool.

How to fix it: RLS only for tenant_id (or an equivalent: org_id, workspace_id). For granular user permissions, use explicit checks in the application layer. Auth is covered in guide #9 of the path.

Mistake 2 (operational): forgetting FORCE ROW LEVEL SECURITY

Symptom: the team turns on RLS, writes correct policies, tests in dev and "it doesn't work" — every query still returns every row. After hours of debugging they discover the app connects as the tables' owner and RLS doesn't apply to the owner by default.

Why it happens: the documentation mentions it but it isn't obvious. In simple setups (local Docker with the postgres user), the app owns everything and RLS seems not to work.

How to tell: check with \d+ table_name in psql. If it says "Row security: enabled" without "(forced)", the FORCE is missing.

How to fix it: always turn on ENABLE and FORCE together. Or create a separate role for the app that is NOT the owner. The recommended practice combines both (a separate role + FORCE) for defense in depth.

Mistake 3 (operational): a policy with no WITH CHECK

Symptom: the team creates a policy with only USING. The SELECT/UPDATE/DELETE queries filter correctly. But the INSERTs accept rows with any tenant_id. A bug in the code ends up inserting rows attributed to other tenants.

Why it happens: people assume USING covers everything. The documentation mentions WITH CHECK but it doesn't get interpreted as "indispensable for multi-tenancy."

How to tell: try inserting a row with a tenant_id different from the setting. If it goes through, WITH CHECK is missing.

How to fix it: always include a WITH CHECK equal to the USING (or stricter if you want to control INSERT separately).

Mistake 4 (operational): current_setting without missing_ok

Symptom: if a request forgets to set app.tenant_id, the queries don't fail with "0 rows returned" but with ERROR: unrecognized configuration parameter "app.tenant_id". The app responds with a 500 instead of a 200 with empty data.

Why it happens: current_setting('foo') (with no second argument) raises an error if the setting doesn't exist. Capsule 05 shows how to make sure the setting always exists, but as defense in depth it's worth using the tolerant version in the policy.

How to tell: run a query without having set app.tenant_id. If you see an error instead of 0 rows, the , true is missing from current_setting.

How to fix it: use current_setting('app.tenant_id', true) (the second argument true = missing_ok). Combined with NULLIF(..., '') to handle the empty string.

Mistake 5 (conceptual): assuming RLS replaces filtering in the code

Symptom: the team turns on RLS and removes all the WHERE tenant_ids from the code "because they're no longer necessary." Later a dev writes a query that runs outside the normal request (a standalone script, a data migration, a debugging REPL) and since there's no session setting, the query returns data from ALL the tenants.

Why it happens: RLS only protects when the setting exists. If a session doesn't set the context, the effects vary depending on how the policy was configured (an error, or 0 rows with missing_ok).

How to tell: review standalone scripts, custom data migrations, code that runs outside FastAPI. Do they have a session setting? Should they?

How to fix it: keep the WHERE tenant_id in the code as defense in depth. RLS is the safety net for the cases where human code fails, not a replacement for human code. Capsule 05 shows the complete pattern.

Mistake 6 (operational): not testing the isolation explicitly

Symptom: the team trusts that "we have RLS, it's covered." They don't write specific tests. After a PostgreSQL upgrade or a change in how the connection pool connects, the policies stop applying correctly and nobody notices until the first customer reports seeing someone else's data.

Why it happens: RLS is invisible to code review (the policies live in the DB, not in the code repo). If there are no tests, there's no continuous guarantee.

How to tell: does your test suite include explicit isolation tests? Do they pass in CI?

How to fix it: write tests that: (1) insert data into two tenants, (2) set the first tenant's context, (3) verify the second's data does NOT appear. Covered in capsule 05 with SQLAlchemy.

Exercises

Exercise 1: identify whether RLS is the right tool

For each of these requirements, decide whether RLS is the right tool and justify it:

a) "Each customer company has its own tasks. One company's tasks must never be visible to another."

b) "Administrators can edit any project. Regular members can only edit projects where they're owners. Viewers can only read."

c) "A country's invoices can only be seen by users from the same country (privacy regulation)."

d) "Employees can see salaries from their own department, but not from other departments."

Exercise 2: write the policies for a new table

You have a multi-tenant notes table:

CREATE TABLE notes (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id),
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    is_pinned BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Write the SQL commands to:

1. Turn on RLS and FORCE.
2. Create the per-tenant isolation policy (tolerant of a missing setting).
3. Verify the setup with \d+ notes in psql.

-- 1. Turn on RLS
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;

-- 2. A policy tolerant of a missing setting
CREATE POLICY tenant_isolation ON notes
    USING (
        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    )
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    );

-- 3. Verify the setup
\d+ notes

Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(id)
Policies (forced row security enabled):
    POLICY "tenant_isolation"
      USING ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::bigint))
      WITH CHECK ((tenant_id = (NULLIF(current_setting('app.tenant_id'::text, true), ''::text))::bigint))

Exercise 3: detect and diagnose policies that don't work

You connect to a teammate's DB. This is the output of \d+ projects:

 Column     | Type    | Nullable | Default
 -----------+---------+----------+---------
 id         | bigint  | not null |
 tenant_id  | bigint  | not null |
 name       | varchar | not null |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
Policies:
    POLICY "tenant_isolation"
      USING ((tenant_id = (current_setting('app.tenant_id'::text))::bigint))

The teammate reports: "We turned on RLS but it doesn't seem to protect — every query returns data from every tenant." Identify the TWO problems you see in this configuration.

ALTER TABLE projects FORCE ROW LEVEL SECURITY;

DROP POLICY tenant_isolation ON projects;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

Exercise 4: predict the result of queries with RLS enabled

Assume this configuration:

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

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- The data in the table:
-- (id=1, tenant_id=1, title='A1')
-- (id=2, tenant_id=1, title='A2')
-- (id=3, tenant_id=2, title='B1')
-- (id=4, tenant_id=2, title='B2')

Predict the result of each query (assume they run in a session as app_user, not as the owner):

a) With no prior SET LOCAL: SELECT * FROM tasks; b) SET LOCAL app.tenant_id = '1'; SELECT COUNT(*) FROM tasks; c) SET LOCAL app.tenant_id = '1'; SELECT * FROM tasks WHERE tenant_id = 2; d) SET LOCAL app.tenant_id = '2'; UPDATE tasks SET title = 'updated' WHERE id = 1; e) SET LOCAL app.tenant_id = '1'; INSERT INTO tasks (tenant_id, title) VALUES (2, 'INTRUDER'); f) SET LOCAL app.tenant_id = '1'; DELETE FROM tasks;

Exercise 5: write a "malicious" test in pure SQL

Write a sequence of SQL in psql that:

1. Sets tenant 1's context.
2. Tries to read tenant 2's data with FIVE different techniques.
3. Verifies that all five fail or return 0 rows.

-- Setup: set tenant 1's context
BEGIN;
SET LOCAL app.tenant_id = '1';

-- Technique 1: a WHERE with the other tenant's explicit tenant_id
SELECT 'Technique 1' AS test, COUNT(*) AS leaked FROM tasks WHERE tenant_id = 2;
-- Expected: leaked = 0 (the policy filters first to tenant_id=1, then WHERE 2 → 0 rows)

-- Technique 2: an ORDER BY that looks like it "reads everything"
SELECT 'Technique 2' AS test, COUNT(*) AS leaked FROM (
    SELECT * FROM tasks ORDER BY id DESC LIMIT 1000
) sub WHERE tenant_id = 2;
-- Expected: leaked = 0

-- Technique 3: a subquery with an attempted bypass
SELECT 'Technique 3' AS test, COUNT(*) AS leaked FROM tasks
WHERE id IN (SELECT id FROM tasks WHERE tenant_id = 2);
-- Expected: leaked = 0 (the subquery is also under the policy)

-- Technique 4: a JOIN with tenants
SELECT 'Technique 4' AS test, COUNT(*) AS leaked
FROM tasks t JOIN tenants te ON t.tenant_id = te.id
WHERE te.slug = 'globex';
-- Expected: leaked = 0

-- Technique 5: an aggregation that looks like it "counts everything"
SELECT 'Technique 5' AS test, COUNT(DISTINCT tenant_id) AS distinct_tenants_visible FROM tasks;
-- Expected: distinct_tenants_visible = 1 (only tenant 1, the rest are invisible)

ROLLBACK;

    test     | leaked
-------------+--------
 Technique 1 |      0
    test     | leaked
-------------+--------
 Technique 2 |      0
    test     | leaked
-------------+--------
 Technique 3 |      0
    test     | leaked
-------------+--------
 Technique 4 |      0
    test     | distinct_tenants_visible
-------------+--------------------------
 Technique 5 |                        1

Exercise 6: design the bypass for an admin job

Your team needs a daily cron that computes global platform metrics: how many tasks were created in the last 24 hours across all tenants. The query is:

SELECT COUNT(*), tenant_id
FROM tasks
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY tenant_id;

Design the necessary DB changes and describe how the cron job would connect:

a) Using a role with BYPASSRLS. b) Using a bypass setting inside the policy.

Which would you recommend and why?

-- Create the dedicated role
CREATE ROLE admin_metrics LOGIN PASSWORD 'metrics_secret' BYPASSRLS;
GRANT CONNECT ON DATABASE rls_demo TO admin_metrics;
GRANT USAGE ON SCHEMA public TO admin_metrics;
GRANT SELECT ON tasks TO admin_metrics;
GRANT SELECT ON tenants TO admin_metrics;

# scripts/daily_metrics.py
import asyncpg

async def main():
    conn = await asyncpg.connect(
        "postgresql://admin_metrics:metrics_secret@localhost/rls_demo"
    )
    rows = await conn.fetch("""
        SELECT COUNT(*) AS task_count, tenant_id
        FROM tasks
        WHERE created_at > NOW() - INTERVAL '24 hours'
        GROUP BY tenant_id
    """)
    for row in rows:
        print(f"Tenant {row['tenant_id']}: {row['task_count']} tasks")
    await conn.close()

-- The modified policy
DROP POLICY tenant_isolation ON tasks;
CREATE POLICY tenant_isolation ON tasks
    USING (
        current_setting('app.bypass_rls', true) = 'on'
        OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    )
    WITH CHECK (
        current_setting('app.bypass_rls', true) = 'on'
        OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::BIGINT
    );

# scripts/daily_metrics.py
import asyncpg

async def main():
    conn = await asyncpg.connect(
        "postgresql://app_user:app_password@localhost/rls_demo"
    )
    async with conn.transaction():
        await conn.execute("SET LOCAL app.bypass_rls = 'on'")
        rows = await conn.fetch("""
            SELECT COUNT(*) AS task_count, tenant_id
            FROM tasks
            WHERE created_at > NOW() - INTERVAL '24 hours'
            GROUP BY tenant_id
        """)
        for row in rows:
            print(f"Tenant {row['tenant_id']}: {row['task_count']} tasks")
    await conn.close()

Summary and next step

In this capsule you learned:

• RLS is an invisible WHERE that PostgreSQL automatically applies to every query against a table with policies.
• Correct activation: ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY + at least one policy.
• Policies have USING (filters SELECT/UPDATE/DELETE) and WITH CHECK (validates INSERT/UPDATE). For multi-tenancy, both go with the same predicate.
• The typical predicate is tenant_id = current_setting('app.tenant_id')::BIGINT, read from a session setting the app sets with SET LOCAL at the start of each transaction.
• The critical caveat repeated: RLS here is only for multi-tenancy, NOT for auth/RBAC. Mixing them leads to debugging hell.
• The bypass pattern for admin/jobs: a separate role with BYPASSRLS (recommended) or a bypass setting in the policy (the alternative).
• "Malicious" tests in psql demonstrate the isolation works: queries with WHERE tenant_id = another return 0 rows, INSERTs with someone else's tenant get rejected.
• RLS keeps using the indexes you already had for tenant_id queries — there's no need to redesign the indexing.

Before moving on you should be able to:

• Turn on RLS and FORCE on a new table with the right syntax.
• Write a policy for multi-tenancy with USING and WITH CHECK.
• Distinguish when RLS is the right tool (multi-tenancy) vs when it is NOT (RBAC).
• Diagnose policies that don't work (missing FORCE, missing WITH CHECK, connecting as the owner).
• Design the bypass pattern for admin jobs without weakening the isolation.
• Predict the result of queries with RLS enabled, including "malicious" cases.

Next capsule — RLS with FastAPI and SQLAlchemy async. You already have RLS's fundamentals in pure SQL. Now you're going to integrate them into a real FastAPI app: how to write the dependency that runs SET LOCAL app.tenant_id at the start of each request, how to handle asyncpg's critical gotcha with cached prepared statements (which can make one tenant see another's data intermittently), how to configure PgBouncer so it doesn't break the session context, and how to write automated isolation tests that run in CI. You're going to repeat the "RLS is NOT for auth" caveat because it's the #1 mistake of teams that get here. It's the capsule that turns RLS theory into a production implementation.

Resources

1. PostgreSQL 16 — Row Security Policies — the official reference. Required reading, especially the sections on USING vs WITH CHECK and FORCE.
2. PostgreSQL 16 — CREATE POLICY — the complete policy syntax.
3. PostgreSQL 16 — current_setting — the reference for the session settings mechanism RLS uses.
4. Supabase — Row Level Security guide — the most widely used RLS model in modern production. Applicable patterns even if you don't use Supabase.
5. AWS — SaaS Tenant Isolation Strategies (whitepaper) — the section on RLS as an isolation mechanism.
6. Crunchy Data — Multi-Tenancy and Row-Level Security — a pragmatic analysis with real code.
7. PostgreSQL Wiki — RLS examples — community examples including real use cases.

Module 4 — SQL Patterns for Production APIs Guide

Next capsule: RLS with FastAPI and SQLAlchemy async — the production implementation with asyncpg's prepared statements gotcha.