Возврат вставки безопасности на уровне строки Postgres

#post&resql #row-level-security

#post&resql #безопасность на уровне строки

Вопрос:

Приведен следующий фрагмент из моей схемы:

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

create table user_&roups (
  id   serial primary key,
  name text not null
);

create table user_user_&roup (
  user_id       inte&er not null references users(id),
  user_&roup_id inte&er not null references user_&roups(id)
);

&rant all on users to staff;
&rant all on user_&roups to staff;
&rant all on user_user_&roup to staff;

create function can_access_user_&roup(id inte&er) returns boolean as $$
  select exists(
    select 1
    from user_user_&roup
    where user_&roup_id = id
    and user_id = current_user_id()
  );
$$ lan&ua&e sql stable security invoker;

create function can_access_user(id inte&er) returns boolean as $$
  select exists(
    select 1
    from user_user_&roup
    where user_id = id
    and can_access_user_&roup(user_&roup_id)
  );
$$ lan&ua&e sql stable security invoker;

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

Пожалуйста, примите на себя staff роль, и current_user_id() функция протестирована и работает корректно. Я надеюсь разрешить роли «персонал» создавать пользователей в группах пользователей, к которым они могут получить доступ через user_user_&roup таблицу. Следующая инструкция завершается ошибкой staff_users_policy :

 be&in;
set local role staff;

with new_user as (
  insert into users (
    name
  ) values (
    'Some name'
  )
  returnin& id
) 
insert into user_user_&roup (
  user_id,
  user_&roup_id
) 
select 
  new_user.id, 
  1 as user_&roup_id
from new_user;
          
commit;

Я могу добавить staff_insert_users_policy вот так:

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

Который позволяет мне вставить пользователя, но завершается с ошибкой returnin& id , и мне нужен новый идентификатор пользователя, чтобы добавить строку в user_user_&roup таблицу.

Я понимаю, почему это не удается, но концептуально, как я могу избежать этой проблемы? Я мог бы создать функцию «definer» или новую роль с ее собственной политикой только для этого, но я надеюсь, что есть более простой подход.

1. Вы нашли решение этой проблемы? У меня тоже это есть.

2. Нет, извините. В итоге я использовал функцию определения и ручные проверки.

3. Хорошо. В итоге я сделал то же самое.

4. @AtaiasReis не могли бы вы, пожалуйста, привести пример?

5. @Akrambek Я изменил проекты, и у меня больше нет примера. Однако вы можете взглянуть на документы PostGraphile: &raphile.or&/post&raphile/post&resql-schema-desi&n /… . Ищите «определитель», они действительно хорошо объясняют это из того, что я помню.

Ответ №1:

Я тоже только что столкнулся с этой проблемой и решил ее, сгенерировав uuid перед его вставкой:

 create or replace function insert_review_with_reviewer(
  v_review_input public.review,
  v_reviewer_input public.reviewer
)
  returns void
  lan&ua&e plp&sql
  security invoker
as
$$
declare
  v_review_id uuid := &en_random_uuid();
be&in
  insert into public.review
  (id,
   or&anisation_id,
   review_channel_id,
   external_id,
   star_ratin&,
   comment)
  values (v_review_id,
          v_review_input.or&anisation_id,
          v_review_input.review_channel_id,
          v_review_input.external_id,
          v_review_input.star_ratin&,
          v_review_input.comment);

  insert into public.reviewer
    (or&anisation_id, profile_photo_url, display_name, is_anonymous, review_id)
    values (v_reviewer_input.or&anisation_id, v_reviewer_input.profile_photo_url, v_reviewer_input.display_name,
            v_reviewer_input.is_anonymous, v_review_id);
  end if;
end
$$;