We ran across a use case where we needed to restrict people's access to the data by requiring them to belong to 2 or more groups. PostgreSQL by default does not support this. I have worked up several way to be able to perform this action, but as always, there are pro's and con's to each method. In our use case, we wanted the user to SELECT on a table and return an error if the user did not have the correct rights instead of an empty table. Ideally, a user without the correct rights would see an error even if there was no data in the table. This is because, if you have a table containing adverse events, it could jeopardize human safety if users thought there were no adverse events, when, in fact, it's just that they didn't have permission to see the adverse events. The "Feature Chart" will help you pick the version that meet's your specific needs.
Our Criteria | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Style | User SELECT's on | Object owned by table_owner | Empty Table / With Data | ||||||||||
Un-Privileged User | Partial Privileged User | Privileged User | Superuser | Table Owner | |||||||||
CRITERIA | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||||||
Feature Chart | |||||||||||||
Style | User SELECT's on | Object owned by table_owner | Empty Table / With Data | ||||||||||
Un-Privileged user_c | Partial Privileged user_b | Privileged user_a | Superuser | Table Owner | |||||||||
TABLE | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
||
VIEW | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||||
FUNCTION | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||||
TABLE with POLICY 9.5+ | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||
TABLE with POLICY (FORCE'd) 9.5+ | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
||||
TABLE with RULE | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||||
VIEW with FUNCTION | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
|||||
TABLE with POLICY and FUNCTION | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
||
TABLE with RULE and FUNCTION | ![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
What we need to find out is, does this user belong to these roles/groups. As normal, there are several ways to ask this question. While style 1 works, the more groups that you need to check against the uglier the code becomes. While I am told two groups right now, I can see a possibility of more than two in the future.
SELECT CASE WHEN pg_has_role(current_user, 'group_a', 'MEMBER') IS TRUE AND pg_has_role(current_user, 'group_b', 'MEMBER') IS TRUE THEN TRUE ELSE FALSE END AS check;
Style 2 is cleaner code and handles more than two roles/groups easily.
SELECT (array_agg(role_name::text) @> ARRAY['group_a', 'group_b']) AS check FROM information_schema.applicable_roles WHERE grantee = current_user
I run into some issues along the way, such as using the WHERE clause in the views/rules using the functions. This does not work, because the WHERE clause does not get evaluated when the source table is empty. This is why I moved the function to act as a source table within the views/rules so that it would get evaluated every time and throw errors when there was partial permissions.
Setup for all examples
When I show each sample below, you will need to start with an empty database and run this generic setup for each of the examples.
-- Create a new database and then login as the superuser -- This allows us to switch roles easily for testing -- Create the accounts needed for this test CREATE ROLE group_a; CREATE ROLE group_b; CREATE ROLE user_a WITH INHERIT IN ROLE group_a, group_b; CREATE ROLE user_b WITH INHERIT IN ROLE group_a; CREATE ROLE user_c; CREATE ROLE table_owner; -- Change to the table owner to create the table and insert the data SET ROLE table_owner; CREATE TABLE public.test ( id INTEGER, string TEXT ); INSERT INTO public.test VALUES (1, 'testing'), (2, 'more testing'), (3, 'even more testing');
Example: Table
The problem with the plain TABLE is that if you only belong to one of the two group, you can view the data. We want to enforce that you must belong to both groups. This means that a plain table with normal permissions will not work for what we want.
-- In an empty database run the "Setup for all examples" -- first then run the code below -- Grant both groups SELECT permissions on the table GRANT SELECT ON public.test TO group_a; GRANT SELECT ON public.test TO group_b; SET ROLE user_a; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 31 ms) SET ROLE user_b; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 16 ms) SET ROLE table_owner; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 16 ms) SET ROLE postgres; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 15 ms)
Example: View
For this method, no SELECT permissions are granted on the table. Instead, there's a view to the table and each role is granted separately. The role intersection check is implemented in a LEFT JOIN. The problem with the VIEW is that if you only belong to one of the two groups, you see an empty dataset instead of knowing that you did not have the correct permissions.
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE table_owner; CREATE OR REPLACE VIEW public.test_view AS SELECT a.* FROM public.test a LEFT JOIN ( SELECT (array_agg(role_name::text) @> ARRAY['group_a', 'group_b']) AS check FROM information_schema.applicable_roles WHERE grantee = current_user ) b ON (TRUE) WHERE b.check = TRUE; GRANT SELECT ON public.test_view TO group_a; GRANT SELECT ON public.test_view TO group_b; SET ROLE user_a; SELECT * FROM public.test_view; -- 3 rows returned (execution time: 0 ms; total time: 16 ms) SET ROLE user_b; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 32 ms) SET ROLE user_c; SELECT * FROM public.test_view; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE group_b; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE table_owner; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE postgres; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 15 ms) -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE user_b; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE user_c; SELECT * FROM public.test_view; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 0 ms)
Example: Function
For this method, no SELECT permissions are granted on the table. Instead, there's a function that reads table and each role is granted separately on the function. The role intersection check is implemented inside the function. While a function may seem like a simple fix, it is not. Normally you would set the function to be owned by the table_owner and run as security definer. The problem with this is that the table_owner can't see the results from information_schema.applicable_roles for any other user but table_owner. This means that the function must be owned by a superuser and run as security definer for it to work properly. This is normally a bad practice, so I do not recommend this style.
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE postgres; CREATE OR REPLACE FUNCTION public.test () RETURNS SETOF public.test AS $body$ DECLARE r RECORD; check_roles NAME[]; BEGIN check_roles = ARRAY['group_a'::name, 'group_b'::name]; -- Check to see if the current_user is a direct member of the check_roles SELECT ((SELECT array_agg(role_name::name) FROM information_schema.applicable_roles WHERE grantee = session_user) @> check_roles) AS security_check INTO r; IF r.security_check = TRUE THEN -- current_user was found to be a member of all check_roles RETURN QUERY SELECT * FROM public.test; ELSE -- current_user was NOT found to be a member of all check_roles RAISE EXCEPTION 'test(): User: % is required to be a member of all of these groups: %', session_user, check_roles; END IF; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY DEFINER; GRANT EXECUTE ON FUNCTION public.test() TO group_a, group_b; REVOKE EXECUTE ON FUNCTION public.test() FROM PUBLIC; SET SESSION AUTHORIZATION user_a; SELECT * FROM public.test(); -- 3 rows returned (execution time: 0 ms; total time: 15 ms) SET SESSION AUTHORIZATION user_b; SELECT * FROM public.test(); -- ERROR: test(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION user_c; SELECT * FROM public.test(); -- ERROR: permission denied for function test SET SESSION AUTHORIZATION group_a; SELECT * FROM public.test(); -- ERROR: test(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION group_b; SELECT * FROM public.test(); -- ERROR: test(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION table_owner; SELECT * FROM public.test(); -- ERROR: permission denied for function test SET SESSION AUTHORIZATION postgres; SELECT * FROM public.test(); -- ERROR: test(): User: postgres is required to be a member of all of these groups: {group_a,group_b} -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET SESSION AUTHORIZATION user_a; SELECT * FROM public.test(); -- Empty set (execution time: 0 ms; total time: 0 ms SET SESSION AUTHORIZATION user_b; SELECT * FROM public.test(); -- ERROR: test(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION user_c; SELECT * FROM public.test(); -- ERROR: permission denied for relation test SET SESSION AUTHORIZATION group_a; SELECT * FROM public.test(); -- ERROR: test(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION group_b; SELECT * FROM public.test(); -- EERROR: test(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET SESSION AUTHORIZATION table_owner; SELECT * FROM public.test(); -- ERROR: permission denied for function test SET SESSION AUTHORIZATION postgres; SELECT * FROM public.test(); -- ERROR: test(): User: postgres is required to be a member of all of these groups: {group_a,group_b}
Example: Table with Policy
For this method, each role is granted SELECT permissions on the table. The role intersection check is implemented in the policy. This is sort of an unconventional way of using Row Level Security. We can perform the test we want using a Policy which requires PostgreSQL 9.5+. The problem with this method is that it is a per row evaluation. This means that if the table is empty, the evaluation does not happen.
-- In an empty database run the "Setup for all examples" -- first then run the code below -- Grant both groups SELECT permissions on the table GRANT SELECT ON public.test TO group_a; GRANT SELECT ON public.test TO group_b; CREATE POLICY test_policy ON public.test USING ((SELECT array_agg(role_name::text) FROM information_schema.applicable_roles WHERE grantee = current_user) @> ARRAY['group_a', 'group_b']); ALTER TABLE public.test ENABLE ROW LEVEL SECURITY; SET ROLE user_a; SELECT * FROM public.test; -- 3 rows returned (execution time: 15 ms; total time: 31 ms) SET ROLE user_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE group_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 15 ms) SET ROLE table_owner; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 16 ms) -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms)
Example: Table with Policy using forced Row Level Security
For this method, each role is granted SELECT permissions on the table. The role intersection check is implemented in the policy and also enforced against the table owner. This is sort of an unconventional way of using Row Level Security. We can perform the test we want using a Policy which requires PostgreSQL 9.5+ and enforce the policy against the table owner.
-- In an empty database run the "Setup for all examples" -- first then run the code below -- Grant both groups SELECT permissions on the table GRANT SELECT ON public.test TO group_a; GRANT SELECT ON public.test TO group_b; CREATE POLICY test_policy ON public.test USING ((SELECT array_agg(role_name::text) FROM information_schema.applicable_roles WHERE grantee = current_user) @> ARRAY['group_a', 'group_b']); ALTER TABLE public.test ENABLE ROW LEVEL SECURITY; ALTER TABLE public.test FORCE ROW LEVEL SECURITY; SET ROLE user_a; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms)
Example: Table with Rule
For this method, no SELECT permissions are granted on the table. Instead, there's a rule/view to the table and each role is granted separately. The role intersection check is implemented in a WHERE clause of the rule. The problem with the RULE is that if you only belong to one of the two group, you get an empty dataset instead of an error message. Rules are how PostgreSQL implements views internally. Per the PostgreSQL Docs: "It is considered better style to write a CREATE VIEW command than to create a real table and define an ON SELECT rule for it."
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE table_owner; CREATE TABLE public.test_rule (LIKE public.test); CREATE RULE "_RETURN" AS ON SELECT TO public.test_rule DO INSTEAD SELECT * FROM public.test WHERE (SELECT array_agg(role_name::text) FROM information_schema.applicable_roles WHERE grantee = current_user) @> ARRAY['group_a', 'group_b']; GRANT SELECT ON public.test_rule TO group_a; GRANT SELECT ON public.test_rule TO group_b; SET ROLE user_a; SELECT * FROM public.test_rule; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE user_c; SELECT * FROM public.test_rule; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 15 ms) SET ROLE user_b; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 15 ms) SET ROLE user_c; SELECT * FROM public.test_rule; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 0 ms)
Example: View with Function
For this method, no SELECT permissions are granted on the table. Instead, there's a view to the table and each role is granted separately. The role intersection check is implemented just before the LEFT JOIN. This causes the security_check function to always be checked even in the table is empty. This does enforce most of what we want. Just wish it appeared as a table.
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE table_owner; CREATE OR REPLACE FUNCTION public.security_check ( check_roles name [] ) RETURNS boolean AS $body$ DECLARE r RECORD; BEGIN -- Checks to see if NULL was passed IF array_length(check_roles,1) IS NULL THEN RAISE EXCEPTION 'security_check(): Must specify roles to be checked.'; END IF; -- Checks to see if any value in the array is NULL IF check_roles @> ARRAY[NULL::name] THEN RAISE EXCEPTION 'security_check(): NULL roles are not allowed in check_roles: %', check_roles; END IF; -- Check to see if the current_user is a direct member of the check_roles SELECT ((SELECT array_agg(role_name::name) FROM information_schema.applicable_roles WHERE grantee = current_user) @> check_roles) AS security_check INTO r; IF r.security_check = TRUE THEN -- current_user was found to be a member of all check_roles RETURN TRUE; ELSE -- current_user was NOT found to be a member of all check_roles RAISE EXCEPTION 'security_check(): User: % is required to be a member of all of these groups: %', current_user, check_roles; END IF; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; GRANT EXECUTE ON FUNCTION public.security_check(check_roles name []) TO PUBLIC; CREATE OR REPLACE VIEW public.test_view AS SELECT a.* FROM public.security_check(ARRAY['group_a', 'group_b']) LEFT JOIN public.test a ON (TRUE) WHERE a.id IS NOT NULL; GRANT SELECT ON public.test_view TO group_a; GRANT SELECT ON public.test_view TO group_b; SET ROLE user_a; SELECT * FROM public.test_view; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test_view; -- ERROR: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE user_c; SELECT * FROM public.test_view; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_view; -- ERROR: security_check(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET ROLE group_b; SELECT * FROM public.test_view; -- ERROR: security_check(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE table_owner; SELECT * FROM public.test_view; -- ERROR: security_check(): User: table_owner is required to be a member of all of these groups: {group_a,group_b} SET ROLE postgres; SELECT * FROM public.test_view; -- ERROR: security_check(): User: postgres is required to be a member of all of these groups: {group_a,group_b} -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test_view; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test_view; -- ERROR: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE user_c; SELECT * FROM public.test_view; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_view; -- ERROR: security_check(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET ROLE group_b; SELECT * FROM public.test_view; -- ERROR: security_check(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE table_owner; SELECT * FROM public.test_view; -- ERROR: security_check(): User: table_owner is required to be a member of all of these groups: {group_a,group_b} SET ROLE postgres; SELECT * FROM public.test_view; -- ERROR: security_check(): User: postgres is required to be a member of all of these groups: {group_a,group_b}
Example: Table with Policy and Function
For this method, each role is granted SELECT permissions on the table. The role intersection check is implemented in the policy as a call to the security_check function. This is better than just having a table and policy because this method can throw errors for partial privileged users if there is data.
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE table_owner; CREATE OR REPLACE FUNCTION public.security_check ( check_roles name [] ) RETURNS boolean AS $body$ DECLARE r RECORD; BEGIN -- Checks to see if NULL was passed IF array_length(check_roles,1) IS NULL THEN RAISE EXCEPTION 'security_check(): Must specify roles to be checked.'; END IF; -- Checks to see if any value in the array is NULL IF check_roles @> ARRAY[NULL::name] THEN RAISE EXCEPTION 'security_check(): NULL roles are not allowed in check_roles: %', check_roles; END IF; -- Check to see if the current_user is a direct member of the check_roles SELECT ((SELECT array_agg(role_name::name) FROM information_schema.applicable_roles WHERE grantee = current_user) @> check_roles) AS security_check INTO r; IF r.security_check = TRUE THEN -- current_user was found to be a member of all check_roles RETURN TRUE; ELSE -- current_user was NOT found to be a member of all check_roles RAISE EXCEPTION 'security_check(): User: % is required to be a member of all of these groups: %', current_user, check_roles; END IF; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; GRANT EXECUTE ON FUNCTION public.security_check(check_roles name []) TO PUBLIC; -- Grant both groups SELECT permissions on the table GRANT SELECT ON public.test TO group_a; GRANT SELECT ON public.test TO group_b; CREATE POLICY test_policy ON public.test USING (public.security_check(ARRAY['group_a'::name, 'group_b'::name])); ALTER TABLE public.test ENABLE ROW LEVEL SECURITY; SET ROLE user_a; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 15 ms) SET ROLE user_b; SELECT * FROM public.test; -- ERROR: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- ERROR: security_check(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET ROLE group_b; SELECT * FROM public.test; -- ERROR: security_check(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE table_owner; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- 3 rows returned (execution time: 0 ms; total time: 16 ms) -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE user_c; SELECT * FROM public.test; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE group_b; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE table_owner; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms) SET ROLE postgres; SELECT * FROM public.test; -- Empty set (execution time: 0 ms; total time: 0 ms)
Table with Policy and Function - Speed Test
The table policy is a per row item, you will want to understand how this affects performance. In this test we will add 10 millions rows of data and then test with and without using a policy several times. We can that the row level security adds about 3-5 seconds for 10 million rows. Because the function is set as STABLE, this also means that it will only be evaluated once, thereby saving some time over having to be run for every row.
-- Continuing from the above test. SET ROLE table_owner; WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 10000000 ) INSERT INTO public.test SELECT n, 'testing' FROM t; SET ROLE user_a; CREATE TABLE test2 AS SELECT * FROM public.test; -- Query OK, 10000000 rows affected (execution time: 11.014 sec; total time: 11.014 sec) SET ROLE table_owner; ALTER TABLE public.test DISABLE ROW LEVEL SECURITY; SET ROLE user_a; CREATE TABLE test3 AS SELECT * FROM public.test; -- Query OK, 10000000 rows affected (execution time: 8.159 sec; total time: 8.159 sec) SET ROLE table_owner; ALTER TABLE public.test ENABLE ROW LEVEL SECURITY; SET ROLE user_a; CREATE TABLE test4 AS SELECT * FROM public.test; -- Query OK, 10000000 rows affected (execution time: 13.104 sec; total time: 13.104 sec) SET ROLE table_owner; ALTER TABLE public.test DISABLE ROW LEVEL SECURITY; SET ROLE user_a; CREATE TABLE test5 AS SELECT * FROM public.test; -- Query OK, 10000000 rows affected (execution time: 8.315 sec; total time: 8.315 sec)
Example: Table with Rule and Function
For this method, no SELECT permissions are granted on the table. Instead, there's a view to the table and each role is granted separately. The role intersection check is implemented just before the LEFT JOIN. This causes the security_check function to always be checked even in the table is empty. The problem with this method is that it is a per row evaluation.
-- In an empty database run the "Setup for all examples" -- first then run the code below SET ROLE table_owner; CREATE OR REPLACE FUNCTION public.security_check ( check_roles name [] ) RETURNS boolean AS $body$ DECLARE r RECORD; BEGIN -- Checks to see if NULL was passed IF array_length(check_roles,1) IS NULL THEN RAISE EXCEPTION 'security_check(): Must specify roles to be checked.'; END IF; -- Checks to see if any value in the array is NULL IF check_roles @> ARRAY[NULL::name] THEN RAISE EXCEPTION 'security_check(): NULL roles are not allowed in check_roles: %', check_roles; END IF; -- Check to see if the current_user is a direct member of the check_roles SELECT ((SELECT array_agg(role_name::name) FROM information_schema.applicable_roles WHERE grantee = current_user) @> check_roles) AS security_check INTO r; IF r.security_check = TRUE THEN -- current_user was found to be a member of all check_roles RETURN TRUE; ELSE -- current_user was NOT found to be a member of all check_roles RAISE EXCEPTION 'security_check(): User: % is required to be a member of all of these groups: %', current_user, check_roles; END IF; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; GRANT EXECUTE ON FUNCTION public.security_check(check_roles name []) TO PUBLIC; DROP VIEW public.test_rule; CREATE TABLE public.test_rule (LIKE public.test); SELECT relname, relkind FROM pg_catalog.pg_class WHERE relname = 'test_rule'; /* "relname" "relkind" "test_rule" "r" */ CREATE RULE "_RETURN" AS ON SELECT TO public.test_rule DO INSTEAD SELECT test.* FROM public.security_check(ARRAY['group_a'::name, 'group_b'::name]) LEFT JOIN public.test ON (TRUE) -- Where clause needed to prevent 1 row from being returned when test contains 0 rows WHERE test.id IS NOT NULL; SELECT relname, relkind FROM pg_catalog.pg_class WHERE relname = 'test_rule'; /* "relname" "relkind" "test_rule" "v" */ GRANT SELECT ON public.test_rule TO group_a; GRANT SELECT ON public.test_rule TO group_b; SET ROLE user_a; SELECT * FROM public.test_rule; -- 3 rows returned (execution time: 0 ms; total time: 15 ms) SET ROLE user_b; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE user_c; SELECT * FROM public.test_rule; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET ROLE group_b; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE table_owner; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: table_owner is required to be a member of all of these groups: {group_a,group_b} SET ROLE postgres; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: postgres is required to be a member of all of these groups: {group_a,group_b} -- Check to see what happens with an empty dataset SET ROLE table_owner; TRUNCATE TABLE public.test; SET ROLE user_a; SELECT * FROM public.test_rule; -- Empty set (execution time: 0 ms; total time: 16 ms) SET ROLE user_b; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE user_c; SELECT * FROM public.test_rule; -- ERROR: permission denied for relation test SET ROLE group_a; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: group_a is required to be a member of all of these groups: {group_a,group_b} SET ROLE group_b; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: group_b is required to be a member of all of these groups: {group_a,group_b} SET ROLE table_owner; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: table_owner is required to be a member of all of these groups: {group_a,group_b} SET ROLE postgres; SELECT * FROM public.test_rule; -- ERROR: security_check(): User: postgres is required to be a member of all of these groups: {group_a,group_b}
Upgrading the Function:
While we are only dealing with users that are directly granted access to the group, you may wish to deal with inherited roles. We can create a simple recursive function to lookup this information. Because the user is a member of these group, we can use the set role function to switch to that group to look up what groups that group belongs to, we just need to set the user back afterwards.
CREATE OR REPLACE FUNCTION public.applicable_roles ( lookup name ) RETURNS SETOF information_schema.applicable_roles AS $body$ DECLARE original_user NAME; r RECORD; BEGIN original_user = current_user; EXECUTE 'SET ROLE ' || quote_ident(lookup); FOR r IN SELECT * FROM information_schema.applicable_roles WHERE grantee = current_user LOOP RETURN NEXT r; RETURN QUERY SELECT * FROM public.applicable_roles(r.role_name); END LOOP; EXECUTE 'SET ROLE ' || quote_ident(original_user); END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
We want to have the same permission denied errors that an unprivileged user would receive on a normal table. This can be achieved by passing in the relation, aka table or view name, into the function and then updating our error messages.
SET ROLE table_owner; CREATE OR REPLACE FUNCTION public.security_check ( check_roles name [], relation name ) RETURNS boolean AS $body$ DECLARE r RECORD; BEGIN -- Checks to see if NULL was passed IF array_length(check_roles,1) IS NULL THEN RAISE EXCEPTION 'check_roles argument of security_check() is null' USING ERRCODE = 'null_value_not_allowed', HINT = 'security_check(): Must specify roles to be checked.'; END IF; IF relation IS NULL THEN RAISE EXCEPTION 'relation argument of security_check() is null' USING ERRCODE = 'null_value_not_allowed', HINT = 'security_check(): Must specify relation being checked.'; END IF; -- Check to see if the current_user is a direct member of the check_roles -- SELECT ((SELECT array_agg(role_name::name) FROM information_schema.applicable_roles WHERE grantee = current_user) SELECT ((SELECT array_agg(role_name::name) FROM public.applicable_roles(current_user)) @> check_roles) AS security_check INTO r; IF r.security_check = TRUE THEN -- current_user was found to be a member of all check_roles RETURN TRUE; ELSE -- current_user was NOT found to be a member of all check_roles RAISE EXCEPTION 'permission denied for relation %', relation USING ERRCODE = 'insufficient_privilege', HINT = 'security_check(): User: ' || current_user || ' is required to be a member of all of these groups: {' || array_to_string(check_roles, ',', 'NULL') || '}'; END IF; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; GRANT EXECUTE ON FUNCTION public.security_check(check_roles name [], relation name) TO PUBLIC; SET ROLE user_b; SELECT public.security_check(null, null); -- ERROR: check_roles argument of security_check() is null -- HINT: security_check(): Must specify roles to be checked. SELECT public.security_check(ARRAY['group_a'::name, 'group_b'::name], null); -- ERROR: relation argument of security_check() is null -- HINT: security_check(): Must specify roles to be checked. SELECT public.security_check(ARRAY[null::name], 'test_rule'::name); -- ERROR: permission denied for relation test_rule -- HINT: security_check(): User: user_b is required to be a member of all of these groups: {NULL} SELECT public.security_check(ARRAY['group_a'::name, 'group_b'::name], 'test_rule'::name) -- ERROR: permission denied for relation test_rule -- HINT: security_check(): User: user_b is required to be a member of all of these groups: {group_a,group_b}
Take Away:
There is nothing that meets our full criteria. We ended up going with the View with Function due to the tighter security over the Table with Policy and Function. This is due to that when querying as a partially privileged user, aka user_b, the Table with Policy and Function, we can get different results depending on the PostgreSQL version. Setting the volatility of the function to stable in PostgreSQL 9.6 will throw a permission denied error where as in PostgreSQL 10 you will get an empty table returned.
Thanks to David Costanzo for proofreading and testing.