3

Given the following snippet from my schema:

create table users (
  id   serial primary key,
  name text not null
);

create table user_groups (
  id   serial primary key,
  name text not null
);

create table user_user_group (
  user_id       integer not null references users(id),
  user_group_id integer not null references user_groups(id)
);

grant all on users to staff;
grant all on user_groups to staff;
grant all on user_user_group to staff;

create function can_access_user_group(id integer) returns boolean as $$
  select exists(
    select 1
    from user_user_group
    where user_group_id = id
    and user_id = current_user_id()
  );
$$ language sql stable security invoker;

create function can_access_user(id integer) returns boolean as $$
  select exists(
    select 1
    from user_user_group
    where user_id = id
    and can_access_user_group(user_group_id)
  );
$$ language sql stable security invoker;

alter table users enable row level security;
create policy staff_users_policy
  on users
  to staff
  using (
    can_access_user(id)
  );

Please assume the staff role, and current_user_id() function are tested and working correctly. I'm hoping to allow the "staff" role to create users in user groups they can access via the user_user_group table. The following statement fails the staff_users_policy:

begin;
set local role staff;

with new_user as (
  insert into users (
    name
  ) values (
    'Some name'
  )
  returning id
) 
insert into user_user_group (
  user_id,
  user_group_id
) 
select 
  new_user.id, 
  1 as user_group_id
from new_user;
          
commit;

I can add a staff_insert_users_policy like this:

create policy staff_insert_users_policy
  on users
  for insert
  to staff
  with check (
    true
  );

Which allows me to insert the user but fails on returning id, and I need the new user id in order to add the row to the user_user_group table.

I understand why it fails, but conceptually how can I avoid this problem? I could create a "definer" function, or a new role with it's own policy just for this but I'm hoping there's a more straightforward approach.

daviestar
  • 4,531
  • 3
  • 29
  • 47

1 Answers1

1

I just came around this problem too and solved it by generating the uuid before inserting it:

create or replace function insert_review_with_reviewer(
  v_review_input public.review,
  v_reviewer_input public.reviewer
)
  returns void
  language plpgsql
  security invoker
as
$$
declare
  v_review_id uuid := gen_random_uuid();
begin
  insert into public.review
  (id,
   organisation_id,
   review_channel_id,
   external_id,
   star_rating,
   comment)
  values (v_review_id,
          v_review_input.organisation_id,
          v_review_input.review_channel_id,
          v_review_input.external_id,
          v_review_input.star_rating,
          v_review_input.comment);

  insert into public.reviewer
    (organisation_id, profile_photo_url, display_name, is_anonymous, review_id)
    values (v_reviewer_input.organisation_id, v_reviewer_input.profile_photo_url, v_reviewer_input.display_name,
            v_reviewer_input.is_anonymous, v_review_id);
  end if;
end
$$;
psteinroe
  • 493
  • 1
  • 6
  • 18