Row level security for groups or Making rows acceb

问题:

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

回答1:

This seems to work:

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;

回答2:

db=# create table rls(i int);
CREATE TABLE
Time: 189.439 ms
db=# alter table rls enable row level security ;
ALTER TABLE
Time: 12.725 ms
db=# insert into rls values(1);
INSERT 0 1
Time: 13.241 ms
db=# create user member;
CREATE ROLE
Time: 11.882 ms
db=# create role rls_r;
CREATE ROLE
Time: 9.378 ms
db=# grant rls_r to member ;
GRANT ROLE
Time: 5.704 ms
db=# CREATE POLICY p ON rls FOR ALL TO PUBLIC USING ((select count(*)=1 from pg_auth_members where member = current_user::regrole));
CREATE POLICY
Time: 32.471 ms

now checking:

db=# set role ro ;
SET
Time: 0.350 ms
db=> select * from rls;
 i
---
(0 rows)

Time: 9.801 ms
db=> set role member;
SET
Time: 0.494 ms
db=> select * from rls;
 i
---
 1
(1 row)

Time: 0.694 ms

seems to work...

why such rule?

remember Member of with array in psql when you du role?.. so just:

MacBook-Air:~ vao$ psql db -E
Timing is on.
Pager usage is off.
psql (9.6.1)
Type "help" for help.

db=# \du ro
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname ~ '^(ro)$'
ORDER BY 1;
**************************

            List of roles
 Role name |  Attributes  | Member of
-----------+--------------+-----------
 ro        | Cannot login | {}

and you see the needed query