PostgreSQL - обнаружена бесконечная рекурсия в политике для отношений

Question

В базе данных находятся 3 таблицы - отдел, сотрудник, учетная запись. В одном отделе много сотрудников. Сотрудник содержит столбец department_id bigint Таблица счетов содержит столбцы login varchar, employee_id bigint и используется для привязки пользователей (ролей) Postgres к строкам в Employee.

Моя цель - позволить пользователям видеть и работать только с теми строками Сотрудника, для которых значение department_id такой же, как для пользователя.

Там должно быть что-то вроде:

CREATE POLICY locale_policy ON employee
TO justuser, operator
USING (department_id =
    (SELECT department_id FROM employee WHERE id =
        (SELECT employee_id FROM account WHERE login = CURRENT_USER)
    )
)

Но из-за подзапроса от Сотрудника это поднимает infinite recursion detected in policy for relation employee,

РЕДАКТИРОВАТЬ: отношения определяются:

create table department(
    id serial primary key);
create table employee(
    id serial primary key,
    department_id int8 not null references department(id));
create table account(
    id serial primary key,
    login varchar(100) not null unique,
    employee_id int8 not null unique references employee(id));

Answer 1

Ну, я не знаю, насколько это прилично, но у меня это работает. Я нашел решение в создании представления где id отдела current_user, а затем проверил, соответствует ли оно:

CREATE VIEW curr_department AS
    (SELECT department_id as id FROM employee WHERE id =
        (SELECT employee_id FROM account WHERE login = current_user)
    );

CREATE POLICY locale_policy ON employee
    TO justuser, operator
    USING (department_id =
        (SELECT id FROM curr_department)
    );

Answer 2

Увы рекстер не позволяет создавать роли.. http://rextester.com/QDYC6798

create table department(
    id serial primary key);
create table employee(
    id serial primary key,
    department_id int8 not null references department(id));
create table account(
    id serial primary key,
    login varchar(100) not null unique,
    employee_id int8 not null unique references employee(id));
insert into department default values;
insert into department default values;
insert into employee (department_id ) select 1;
insert into employee (department_id ) select 2;
insert into account (login,employee_id) select 'justuser',1;
insert into account (login,employee_id) select 'operator',2;
create role justuser;
create role operator;
set role justuser;
select * from employee;

не могу воспроизвести. это не ответ - просто отформатированный скрипт. Я сотру это когда решено

Answer 3

Вот более краткое решение, которое я использовал. Я использую отдельную таблицу пользователей изauthтаблица для управления пользователями. Из этой таблицы я использую собственный тип перечисления и вспомогательную функцию в политике RLS.

      CREATE TYPE user_role AS enum ('USER', 'ADMIN');

CREATE TABLE users (
  id uuid PRIMARY KEY NOT NULL REFERENCES auth.users(id),
  name text NOT NULL,
  role user_role NOT NULL DEFAULT 'USER'::user_role,
  email text UNIQUE NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now())
);


CREATE OR REPLACE FUNCTION is_admin (user_id UUID)
RETURNS BOOL AS $$
BEGIN
  PERFORM
  FROM public.users
  WHERE id = user_id AND role = 'ADMIN'::user_role;
  RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY definer;

CREATE POLICY "Admins can view all users data" ON public.users
  FOR SELECT
  TO authenticated
  USING (is_admin(auth.uid()));

The PERFORMОператор используется для выполнения запроса без возврата каких-либо данных результата. В этом случае он проверяет, существует ли строка вpublic.usersстол.