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

PostgreSQL - infinite recursion detected in policy for relation

Powercoder
1#
Powercoder Published in 2018-01-13 10:21:38Z

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));
Vao Tsun
2#
Vao Tsun Reply to 2018-01-13 19:34:02Z

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

Powercoder
3#
Powercoder Reply to 2018-01-17 23:46:21Z

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

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

© 2016 Powered by mzan.com design MATCHINFO