0
\$\begingroup\$

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.

\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.