RLS example 3

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

-- 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)

-- 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

-- 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

-- 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:

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)

-- 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

-- 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

-- 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

-- 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

-- 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

-- 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:

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:

  {
  "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.

---