sqlpostgresqlpolicyrow-level-security

PostgreSQL - infinite recursion detected in policy for relation


In database are 3 tables - Department, Employee, Account. One department has many employees. Employee contain column department_id bigint Account table contain columns login varchar, employee_id bigint and used for binding Postgres users (roles) to rows in Employee.

My aim is to let users see and work with only those rows of Employee for which the value of department_id is the same as for the user.

There must be something like:

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)
    )
)

But due to subquery from Employee it's raising infinite recursion detected in policy for relation employee.

EDIT: relations are defined by:

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));

Solution

  • Well I don't know how decent is it, but it works for me. I found a solution in creating a view where is id of current_user's department and then checking if it matches:

    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)
        );