Row level security for groups or Making rows accebile to groups

Question

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.

Neoplasm

not sure about the syntax, but something like using (pg_has_role(current_user, 'permanent')) maybe? – Zoolatry

pgUser here is the column where name of user is mentioned who can access the row. If current_user and pgUser both have same value than row can be accessed. Now there is be column named as pgRole or pgGroup. I want to use it to check if current user is in that group which is mentioned in pgGroup column. So the user can access the respective row. This way different users can access different rows of their need. – Neoplasm

You included the complete create table statement of the table table_workers in your question (edit your question do not post code in comments). But it seems you should be able to use pg_has_role(pguser, pgrole) then – Zoolatry

okay. I have edited the question accordingly. but if I use column pgRole than I have to remove column pgUser. I hope you meant pg_has_role(current_user, pgrole) here. pgRole will have two groups including 'permanent' and 'temp' – Neoplasm

Thanks for the prompt replies. I mentioned I need to replace that pgUser column to pgRole. Where group name will be entered. And my aim is to make the members of those groups able to access the respective rows. – Neoplasm

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

Zoolatry

Great! Thanks alot. This works like a gem. Although I dont understand the complete logic in the commands of the Policy, but it is working. I need to give some time to understand it properly. info for people, Ofcourse we need to create groups group 1 and group 2 here with proper users. And then grant select or insert rights accordingly to the respective groups. – Neoplasm

Answer 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

Constriction

Thank you for the answer. I need users from different groups to be able to access thier respective rows. Therefore, I have a seperate column in table which will be having name of the group that can access it. Than group of the user is compared with the value of that column. If the name of group is same than it is accessible by the member of this group. I couldn't compare that group column in your suggested policy. Thats the only way I feel I can define the accessibility of particular rows by particular groups. – Neoplasm