Lesson 10 · Row-Level Security & the whole chain

Location-aware RLS policies

RLS enforces more than the tenant — some tables push the whole permission-on-a-location check (Lesson 8) into the database.

Your win: read a policy that combines tenant + permission + location, explain how Postgres combines permissive and restrictive policies, and see why this is Lesson 8's app-layer check re-enforced in the database.

Beyond the tenant: permission on a location, in SQL

Lesson 9's policies checked one thing: resource_path = your tenant. But the most sensitive tables — users, locations, user_access_paths — go further. Their policies check whether you (app.user_id) hold the right permission on the row's location — the exact question Lesson 8 answered in Go, now answered by the database. Here's the read policy for the users table:

CREATE POLICY rls_users_select_location ON users AS PERMISSIVE FOR select USING (
  EXISTS (
    SELECT 1 FROM granted_permissions p
      JOIN user_access_paths usp ON usp.location_id = p.location_id
    WHERE p.user_id = current_setting('app.user_id')                  -- ← ME (the caller)
      AND p.permission_id = (SELECT permission_id FROM permission
                            WHERE permission_name = 'user.user.read'       -- ← the permission
                              AND resource_path = current_setting('permission.resource_path'))
      AND usp.user_id = users.user_id ))                            -- ← the row's user, at that location

Read it in English: "this users row is visible if I hold user.user.read on a location that the row's user belongs to." That's granted_permissions (Lesson 6) joined to user_access_paths (Lesson 7), keyed by app.user_id and permission.resource_path — the session variables setPostgres set in Lesson 9. Every piece of the last five lessons meets here.

The self-visibility policy

There's a second policy on the same table, and it's a lovely detail:

CREATE POLICY rls_users_permission_v4 ON users AS PERMISSIVE FOR ALL
  USING      ( current_setting('app.user_id') = user_id )
  WITH CHECK ( current_setting('app.user_id') = user_id );

"You can always see (and edit) your own user row." Without this, a user with no user.user.read permission couldn't even read their own profile. So a users row is visible if you have permission on its location OR it's you. That "OR" is where Postgres policy combination matters.

The key mechanic: permissive OR, restrictive AND

A table can have many policies. Postgres combines them by a precise rule:¹

So the users table composes like this:

The locations table follows the same shape: a permissive rls_locations_location (you hold master.location.read on it) AND a restrictive rls_locations_restrictive (tenant match) (migrations/auth/1001:544,559). Permission opens the door; the tenant is the wall that always stands.

Check yourself (from memory)

1. Postgres RLS — permissive vs restrictive. In-repo: migrations/bob/1421_migrate.up.sql:11, migrations/bob/1422_migrate.up.sql:4, migrations/auth/1001_migrate.up.sql:544,559,608.