schema.sql

application/sql

Download

-- add a simple user table.
create table users (
  id serial primary key,
  first_name varchar,
  last_name varchar,
  display_name varchar,
  is_admin boolean
);

-- this function returns the current (application) user
create function my_account()
returns users
as $$
  select *
  from users
  where cast(id as varchar) = current_setting('jwt.claims.sub', true)
  -- The application will set the current user id at the begin of each transaction.
  -- (See https://postgrest.org or https://graphile.org for example use cases.)
$$
language sql
security definer
stable;

-- enable row level security
alter table users enable row level security;

-- allow all users to edit and delete their own accounts
create policy manage_my_account
on users
using (
  id in (select id from my_account())
);

-- allow admins to edit and delete all accounts
create policy administrate_accounts
on users
using (
  exists(select from my_account() where is_admin)
);

-- The bug will show up, when you pg_dump the schema, after schema modifications to the user table.
alter table users
  drop column first_name,
  drop column last_name;

-- Now, please pg_dump the schema and watch out for the dumped policies.