Home PostgreSQL - infinite recursion detected in policy for relation
Reply: 0

PostgreSQL - infinite recursion detected in policy for relation

user1284
1#
user1284 Published in April 24, 2018, 6:23 am

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));
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309511 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO