1

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

I have several tables and three group roles (group1, group2, and group3). I have several login roles that are members of each group, while "admins" are members of all groups. 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?

bumped to the homepage by Community 11 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.