Row level security in PostgreSQL for different groups

Question

Need a little help with row-level security in Postgres.

I have several tables and three group roles:

• group1
• group2
• group3

I have several login roles (users) that are members of each group, while "admins" are members of all groups.

i.e.

• group1
  • user1_1
  • user1_2
• group2
  • user2_1
  • user2_2
• group3
  • user3_1
  • user3_2

I would like to enable row-level security such that members of group1 can only see rows that were created by them or another user in group1 and same for group2 and group3.

I can easily do this for individual users by creating a row that contains current_user and using something like:

CREATE POLICY row_policy 
    ON table1 
    USING (true) 
    WITH CHECK (created_by = current_user);

But how does one enable this for different group roles so that every member of that group has the same row-level privileges?

Answer 1

So, if user1 has group in common with user2, user2 should be able to see that row, yes?

One way would be to enumerate all the groups that user1 is in, and all the groups user2 is in, and see if there's a union

See How to get all roles that a user is a member of (including inherited roles)? to get all groups a user belongs to

See also pg_has_role https://www.postgresql.org/docs/9.6/static/functions-info.html