Consider the following two tables in a PostgreSQL database:
create table if not exists public.users ( id text primary key, is_staff boolean ); create table if not exists public.issues ( id int primary key, issue_owner text not null references public.users on delete restrict ); alter table public.issues enable row level security;
I want to create policies that will ensure only the owner of an issue and staff members can access the row.
Here's my current attempt — the only way I have been able to make it work is with a subquery:
create policy "Issues are accessible to their owners." on public.issues using ( current_user = issue_owner ); create policy "Issues are accessible to staff members." on public.issues using ( exists ( select 1 from public.users where current_user = id and is_staff = true ) );
This works exactly as intended but I am wondering if there is a more efficient way to do this. By "efficient", I am considering both performance and readability.