组的行级安全性或使行适合组 [英] Row level security for groups or Making rows accebile to groups

问题描述

我希望表中的行仅可由组成员访问。我创建用户并通过以下方法将其添加到组中，

I want the rows in a table accessible to only members of groups. I create users and add them to group by following method,

CREATE USER abc LOGIN PASSWORD 'securedpassword1';
CREATE USER xyz LOGIN PASSWORD 'securedpassword2';

ALTER GROUP permanent ADD USER abc;

然后我编写的策略仅对当前用户开放。但是我需要整个小组来访问它。

Then the policy I write makes it accessible to only current user. But I need whole group to access it.

CREATE TABLE table_Workers
(
    worID INT
    ,worName CHARACTER VARYING
    ,pgUser CHARACTER VARYING
);
INSERT INTO table_Workers VALUES 
(1,'Jason','abc'),(2,'Roy','abc'),(3,'Johny','abc')
,(4,'Jane','xyz'),(5,'Kane','xyz'),(6,'Stuart','xyz');


CREATE POLICY policy_employee_user ON table_Workers FOR ALL
TO PUBLIC USING (pgUser = current_user);

ALTER TABLE table_Workers ENABLE ROW LEVEL SECURITY;

pgUser命名可以访问该行的用户。我希望用pgRole替换pgUser列，其中提到了组的名称，其成员可以访问该特定行。使行对整个组都可访问的任何提示或方法都值得赞赏。

pgUser names the user who can access the row. I wish to replace column pgUser with pgRole, where name of the group is mentioned whose members can access that particular row. Any hint or method is appreciated for making rows accessible to whole group.

推荐答案

这似乎可行：

CREATE TABLE workers
(
    worid   int,
    worname text,
    pgrole text[]
);

INSERT INTO workers 
VALUES 
  (1,'Jason','{group1}'),
  (2,'Roy','{group1,group2}'),
  (3,'Johny','{group1}');

CREATE POLICY policy_employee_user ON workers FOR ALL
TO PUBLIC 
   USING ( (select count(*) 
            from unnest(pgrole) r 
            where pg_has_role(current_user, r, 'MEMBER')) > 0 );

ALTER TABLE workers ENABLE ROW LEVEL SECURITY;

这篇关于组的行级安全性或使行适合组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！