RLS example

Simple Example

CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group_user TO bob; GRANT group_manager TO carol;

CREATE TABLE confidential_data ( id SERIAL PRIMARY KEY, data TEXT, user_groups TEXT[] ); INSERT INTO confidential_data (data, user_groups) VALUES ('Admin and Manager Data', ARRAY['group_admin', 'group_manager']), ('User Data', ARRAY['group_user']), ('Admin and User Data', ARRAY['group_admin', 'group_user']);

ALTER TABLE confidential_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY group_policy ON confidential_data FOR SELECT USING (EXISTS ( SELECT 1 FROM unnest(user_groups) AS g WHERE g = ANY (SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member')) ));

ALTER TABLE confidential_data FORCE ROW LEVEL SECURITY;

Detailed Example

CREATE ROLE group_admin; CREATE ROLE group_user; CREATE ROLE group_manager; CREATE USER alice PASSWORD 'password'; CREATE USER bob PASSWORD 'password'; CREATE USER carol PASSWORD 'password'; GRANT group_admin TO alice; GRANT group_user TO bob; GRANT group_manager TO carol;

CREATE TABLE confidential_data ( id SERIAL PRIMARY KEY, data TEXT, user_groups TEXT[] ); INSERT INTO confidential_data (data, user_groups) VALUES ('Admin and Manager Data', ARRAY['group_admin', 'group_manager']), ('User Data', ARRAY['group_user']), ('Admin and User Data', ARRAY['group_admin', 'group_user']);

ALTER TABLE confidential_data ENABLE ROW LEVEL SECURITY; CREATE POLICY group_policy ON confidential_data FOR SELECT USING (EXISTS ( SELECT 1 FROM unnest(user_groups) AS g WHERE g = ANY (SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member')) )); ALTER TABLE confidential_data FORCE ROW LEVEL SECURITY;

SET ROLE alice; SELECT * FROM confidential_data;
 Alice should see rows where user_groups contains group_admin.
As Bob (group_user):

SET ROLE bob; SELECT * FROM confidential_data;
 Bob should see rows where user_groups contains group_user.
As Carol (group_manager):

SET ROLE carol; SELECT * FROM confidential_data;
 Carol should see rows where user_groups contains group_manager.