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.