PostgreSQL -在关系策略中检测到无限递归

qfe3c7zg 于 2023-01-30 发布在 PostgreSQL

关注(0)|答案(2)|浏览(120)

在数据库中有3个表-部门，雇员，帐户。一个部门有许多雇员。雇员包含列department_id bigint帐户表包含列login varchar，employee_id bigint和用于绑定Postgres用户（角色）到雇员中的行。
我的目标是让用户只看到和使用Employee中department_id值与用户相同的那些行。
必须有这样的东西：

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。

EDIT：关系定义方式：

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

postgresql

来源：https://stackoverflow.com/questions/48238936/postgresql-infinite-recursion-detected-in-policy-for-relation

2条答案

按热度按时间

pes8fvy91#

我不知道它有多好，但它对我很有效。我找到了创建视图的解决方案，其中是current_user的部门ID，然后检查是否匹配：

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

赞(0）回复(0）举报 2023-01-30

u91tlkcl2#

alas rexter不允许创建角色.. 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;

无法重现。这不是答案-只是一个格式化的脚本。解决后我将删除它

赞(0）回复(0）举报 2023-01-30

我来回答

PostgreSQL -在关系策略中检测到无限递归

2条答案

相关问题

热门标签

最新问答