PostgreSQL - infinite recursion detected in policy for relation

Question

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

please provide relations ddl o reproduce. also if you create policy on employee, `SELECT department_id FROM employee ` looks excessive - why not `USING (id = (SELECT employee_id FROM account WHERE login = CURRENT_USER)` ?.. would `department_id` have one-to-many relation with id?.. — Vao Tsun, Jan 13 '18 at 16:14
@VaoTsun, I edited post, did you mean that? Many employees can have the same `department_id` and that is the point of policy - to pick out employees with coincident departments — Powercoder, Jan 13 '18 at 18:11

Powercoder · Accepted Answer · 2018-01-17T23:46:21.603

3

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

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

edited Jan 17 '18 at 23:46

answered Jan 15 '18 at 06:39

Powercoder

695
1
5
25

1

i believe the reason this works is because the VIEW is owned by whatever user is executing the CREATE VIEW command (in this case, not an "employee"). So the VIEW is not subject to the policy. – seveibar Sep 15 '19 at 04:24

score 0 · Answer 2 · answered Jan 13 '18 at 19:34

alas rexter does not allow to create a role.. 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;

cant reproduce. this is not an answer - just a formatted script. I will erase it when resolved

score 0 · Answer 3 · answered Jul 13 '23 at 23:42

Here is a more succinct solution I used. I'm using a separate users table from the auth table to manage users. From that table, I use a custom enum type and use a helper function on the RLS policy.

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 statement is used to execute a query without returning any result data. In this case, it checks if a row exists in the public.users table.

PostgreSQL - infinite recursion detected in policy for relation

3 Answers3

Linked