PostgreSQL-在关联政策中检测到无限递归 [英] PostgreSQL - infinite recursion detected in policy for relation

问题描述

数据库中有3个表-部门，员工，客户.一个部门有很多员工.雇员包含列department_id bigint帐户表包含列login varchar，employee_id bigint，用于将Postgres用户(角色)绑定到雇员中的行.

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.

我的目的是让用户仅查看和使用department_id值与用户相同的那些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.

必须有类似的内容

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

但是由于Employee的子查询，它提高了infinite recursion detected in policy for relation employee.

But due to subquery from Employee it's raising 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));

推荐答案

好吧，我不知道它有多不错，但是它对我有用.我在创建current_user所在部门的id所在的视图，然后检查它是否匹配时找到了解决方案:

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

这篇关于PostgreSQL-在关联政策中检测到无限递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！