Busk: Created page with "RLS pattern for PostgreSQL that uses the JWT `sub` as the external identity, maps it to an internal UUID `user_id`, and supports per-group CRUD permissions on `todos`. It’s..."

2025-09-18T15:20:13Z

Created page with "RLS pattern for PostgreSQL that uses the JWT `sub` as the external identity, maps it to an internal UUID `user_id`, and supports per-group CRUD permissions on `todos`. It’s..."

New page

RLS pattern for PostgreSQL that uses the JWT `sub` as the external identity, maps it to an internal UUID `user_id`, and supports per-group CRUD permissions on `todos`. It’s PostgREST-friendly (uses `request.jwt.claims`) and keeps trust/authorization entirely in the DB.

# 1) Extensions, schema, roles

```sql
-- One-time setup
create extension if not exists pgcrypto; -- gen_random_uuid()

-- App schemas
create schema if not exists app;
create schema if not exists todo;

-- Typical PostgREST role model:
-- - `api_owner` owns objects
-- - `web_anon` is the run-time role used by PostgREST for authenticated users
do $$
begin
if not exists (select 1 from pg_roles where rolname = 'api_owner') then
create role api_owner login;
end if;
if not exists (select 1 from pg_roles where rolname = 'web_anon') then
create role web_anon nologin;
end if;
end$$;

grant usage on schema app, todo to web_anon;
```

# 2) Identity mapping (internal UUID ⇄ external `sub`)

```sql
-- Internal users table:
create table if not exists app.users (
id uuid primary key default gen_random_uuid(),
external_subject text not null unique, -- the JWT `sub` (opaque string, not always a UUID)
display_name text,
email text,
is_active boolean not null default true,
created_at timestamptz not null default now()
);

-- Resolve the current user's internal UUID from the JWT claims.
-- Optionally auto-provision a user row if first seen.
create or replace function app.current_user_id()
returns uuid
stable
security definer
set search_path = public, pg_temp, app
language plpgsql
as $$
declare
claims jsonb;
sub_txt text;
uid uuid;
begin
claims := nullif(current_setting('request.jwt.claims', true), '')::jsonb;
if claims is null then
return null;
end if;

sub_txt := claims->>'sub';
if sub_txt is null then
return null;
end if;

-- Try to find existing mapping
select u.id into uid
from app.users u
where u.external_subject = sub_txt
and u.is_active;

if uid is not null then
return uid;
end if;

-- Optional "auto-provision on first use"
insert into app.users (external_subject, display_name, email)
values (
sub_txt,
coalesce(claims->>'name', null),
coalesce(claims->>'email', null)
)
returning id into uid;

return uid;
end
$$;

revoke all on function app.current_user_id() from public;
grant execute on function app.current_user_id() to web_anon;
grant select on app.users to web_anon; -- (optional: allow users to read their own profile via RLS below)
```

# 3) Groups & membership

```sql
-- Groups
create table if not exists app.groups (
id uuid primary key default gen_random_uuid(),
name text not null unique,
created_by uuid not null references app.users(id) on delete restrict,
created_at timestamptz not null default now()
);

-- Group membership
create table if not exists app.group_members (
group_id uuid not null references app.groups(id) on delete cascade,
user_id uuid not null references app.users(id) on delete cascade,
role text not null check (role in ('owner','manager','member')),
added_at timestamptz not null default now(),
primary key (group_id, user_id)
);

create index if not exists idx_group_members_user on app.group_members(user_id);
create index if not exists idx_group_members_group on app.group_members(group_id);
```

# 4) Todos & per-group shares

```sql
-- Core todos
create table if not exists todo.todos (
id uuid primary key default gen_random_uuid(),
owner_id uuid not null references app.users(id) on delete restrict,
title text not null,
body text,
is_done boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

create index if not exists idx_todos_owner on todo.todos(owner_id);

-- Shares: grant group access to a todo with simple, explicit action flags
-- (r = read, w = update, d = delete). You can normalize further if preferred.
create table if not exists todo.todo_group_shares (
todo_id uuid not null references todo.todos(id) on delete cascade,
group_id uuid not null references app.groups(id) on delete cascade,
can_read boolean not null default true,
can_write boolean not null default false,
can_del boolean not null default false,
primary key (todo_id, group_id)
);

create index if not exists idx_todo_shares_todo on todo.todo_group_shares(todo_id);
create index if not exists idx_todo_shares_group on todo.todo_group_shares(group_id);
```

# 5) RLS policies

Enable RLS:

```sql
alter table app.users enable row level security;
alter table app.groups enable row level security;
alter table app.group_members enable row level security;
alter table todo.todos enable row level security;
alter table todo.todo_group_shares enable row level security;
```

## `app.users` (each user sees themselves; optional admin bypass if you have one)

```sql
-- SELECT: only yourself
create policy users_select_self on app.users
for select
to web_anon
using (id = app.current_user_id());

-- UPDATE: let users edit their own profile (optional)
create policy users_update_self on app.users
for update
to web_anon
using (id = app.current_user_id())
with check (id = app.current_user_id());
```

## `app.groups`

```sql
-- CREATE a group: creator becomes owner via trigger or API-side insert
create policy groups_insert_any on app.groups
for insert
to web_anon
with check (created_by = app.current_user_id());

-- SELECT groups if you're a member
create policy groups_select_member on app.groups
for select
to web_anon
using (
exists (
select 1
from app.group_members gm
where gm.group_id = app.groups.id
and gm.user_id = app.current_user_id()
)
);

-- UPDATE/DELETE groups only if you're an owner
create policy groups_update_owner on app.groups
for update
to web_anon
using (
exists (
select 1 from app.group_members gm
where gm.group_id = app.groups.id
and gm.user_id = app.current_user_id()
and gm.role = 'owner'
)
)
with check (
exists (
select 1 from app.group_members gm
where gm.group_id = app.groups.id
and gm.user_id = app.current_user_id()
and gm.role = 'owner'
)
);

create policy groups_delete_owner on app.groups
for delete
to web_anon
using (
exists (
select 1 from app.group_members gm
where gm.group_id = app.groups.id
and gm.user_id = app.current_user_id()
and gm.role = 'owner'
)
);
```

## `app.group_members`

```sql
-- Anyone can see members of their groups
create policy gm_select_member on app.group_members
for select
to web_anon
using (
exists (
select 1 from app.group_members gm2
where gm2.group_id = group_members.group_id
and gm2.user_id = app.current_user_id()
)
);

-- Add members only if you're owner/manager of that group
create policy gm_insert_mgr on app.group_members
for insert
to web_anon
with check (
exists (
select 1 from app.group_members gm2
where gm2.group_id = group_members.group_id
and gm2.user_id = app.current_user_id()
and gm2.role in ('owner','manager')
)
);

-- Update member roles (promote/demote) only if owner/manager
create policy gm_update_mgr on app.group_members
for update
to web_anon
using (
exists (
select 1 from app.group_members gm2
where gm2.group_id = group_members.group_id
and gm2.user_id = app.current_user_id()
and gm2.role in ('owner','manager')
)
)
with check (
exists (
select 1 from app.group_members gm2
where gm2.group_id = group_members.group_id
and gm2.user_id = app.current_user_id()
and gm2.role in ('owner','manager')
)
);

-- Remove members only if owner/manager (or yourself to leave)
create policy gm_delete_mgr_or_self on app.group_members
for delete
to web_anon
using (
(user_id = app.current_user_id()) or
exists (
select 1 from app.group_members gm2
where gm2.group_id = group_members.group_id
and gm2.user_id = app.current_user_id()
and gm2.role in ('owner','manager')
)
);
```

## `todo.todos`

```sql
-- READ if you own it OR you are in a group that has read share
create policy todos_select_owner_or_shared on todo.todos
for select
to web_anon
using (
owner_id = app.current_user_id()
or exists (
select 1
from todo.todo_group_shares s
join app.group_members gm
on gm.group_id = s.group_id
and gm.user_id = app.current_user_id()
where s.todo_id = todos.id
and s.can_read
)
);

-- CREATE: only as yourself (owner_id must equal current user)
create policy todos_insert_self on todo.todos
for insert
to web_anon
with check (owner_id = app.current_user_id());

-- UPDATE if owner OR group share allows write
create policy todos_update_owner_or_shared on todo.todos
for update
to web_anon
using (
owner_id = app.current_user_id()
or exists (
select 1
from todo.todo_group_shares s
join app.group_members gm
on gm.group_id = s.group_id
and gm.user_id = app.current_user_id()
where s.todo_id = todos.id
and s.can_write
)
)
with check (
owner_id = app.current_user_id()
or exists (
select 1
from todo.todo_group_shares s
join app.group_members gm
on gm.group_id = s.group_id
and gm.user_id = app.current_user_id()
where s.todo_id = todos.id
and s.can_write
)
);

-- DELETE if owner OR group share allows delete
create policy todos_delete_owner_or_shared on todo.todos
for delete
to web_anon
using (
owner_id = app.current_user_id()
or exists (
select 1
from todo.todo_group_shares s
join app.group_members gm
on gm.group_id = s.group_id
and gm.user_id = app.current_user_id()
where s.todo_id = todos.id
and s.can_del
)
);
```

## `todo.todo_group_shares`

```sql
-- Visible if you are the todo owner OR member of the shared group
create policy shares_select_visible on todo.todo_group_shares
for select
to web_anon
using (
exists (select 1 from todo.todos t where t.id = todo_group_shares.todo_id and t.owner_id = app.current_user_id())
or exists (select 1 from app.group_members gm where gm.group_id = todo_group_shares.group_id and gm.user_id = app.current_user_id())
);

-- Only the todo owner (or group owner/manager, if you prefer) can create/update/delete shares
create policy shares_cud_todo_owner on todo.todo_group_shares
for insert
to web_anon
with check (
exists (select 1 from todo.todos t where t.id = todo_group_shares.todo_id and t.owner_id = app.current_user_id())
);

create policy shares_update_todo_owner on todo.todo_group_shares
for update
to web_anon
using (
exists (select 1 from todo.todos t where t.id = todo_group_shares.todo_id and t.owner_id = app.current_user_id())
)
with check (
exists (select 1 from todo.todos t where t.id = todo_group_shares.todo_id and t.owner_id = app.current_user_id())
);

create policy shares_delete_todo_owner on todo.todo_group_shares
for delete
to web_anon
using (
exists (select 1 from todo.todos t where t.id = todo_group_shares.todo_id and t.owner_id = app.current_user_id())
);
```

# 6) Grants

```sql
-- Base privileges (RLS still applies)
grant select, insert, update, delete on app.users to web_anon;
grant select, insert, update, delete on app.groups to web_anon;
grant select, insert, update, delete on app.group_members to web_anon;
grant select, insert, update, delete on todo.todos to web_anon;
grant select, insert, update, delete on todo.todo_group_shares to web_anon;

-- Sequences (none; we use UUIDs)
```

# 7) (Nice to have) Triggers & helpers

Auto-maintain `updated_at` on todos:

```sql
create or replace function todo.bump_updated_at()
returns trigger language plpgsql as $$
begin
new.updated_at := now();
return new;
end$$;

drop trigger if exists trg_todos_updated_at on todo.todos;
create trigger trg_todos_updated_at
before update on todo.todos
for each row execute function todo.bump_updated_at();
```

# 8) How to use (PostgREST / JWT)

* PostgREST forwards JWT claims to `current_setting('request.jwt.claims', true)`.
* Your token must include a `sub`. Example payload:

```json
{
"sub": "c2d1ae8a-...-external-id-or-opaque",
"email": "alice@example.com",
"name": "Alice Example",
"exp": 1900000000
}
```
* First request from a `sub` will auto-provision a row in `app.users` and return a stable internal `id` UUID for joins.

# 9) Notes & options

* **Don’t use `sub` as PK**: different IdPs may change/format it differently. Keep `users.id` (UUID) as canonical, store `external_subject` (TEXT) as the mapping.
* **Group authority in DB**: we intentionally ignore any `groups` claim for authorization. JWT can lie or drift; DB is the source of truth.
* **Granularity**: the `todo_group_shares` flags (`can_read`, `can_write`, `can_del`) are easy to reason about. If you prefer, model a `perm` enum (`read`, `write`, `delete`, `share`) or a bitmask.

---

RLS example 3 - Revision history

Busk: Created page with "RLS pattern for PostgreSQL that uses the JWT `sub` as the external identity, maps it to an internal UUID `user_id`, and supports per-group CRUD permissions on `todos`. It’s..."