Ten analysts need the same privileges on the same five tables. Fifty GRANTs is too many, and easy to get wrong when a person joins or leaves.
Group roles
Make one role for the job, give it the privileges, then make people members of it:
sql
CREATE ROLE analysts; -- no LOGIN: a group
GRANT SELECT ON orders, order_items TO analysts;
GRANT analysts TO ana, ben; -- ana and ben are membersanalystsSELECT on orders
member
anainherits SELECT on orders
GRANT role TO rolemakes the second role a member of the first. It has noON: you grant a role, not a privilege on a table.- A member inherits the privileges of the group.
anacan readorderswith no grant of her own. REVOKE analysts FROM ben;removes ben from the group and takes every group privilege away from him.
Check membership and privileges
has_table_privilege counts inherited privileges:
sql
SELECT has_table_privilege('ana', 'orders', 'SELECT'); -- truepg_has_role(member, group, 'MEMBER') is true when the first role is a member of the second.