Viewing data at the intersection between roles

lloydalbin's picture
Submitted by lloydalbin on Tue, 06/12/2018 - 14:24

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.

The con's:

  1. Can view the data if you have partial permissions.

-- 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.

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.

-- 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.

  1. Function must be owned by a super user with execution as security definer

-- 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.

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.

-- 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.

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.
  2. Table owner, does not have the rights to read, write, update, or delete the data.

-- 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."

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.
  2. Must update the TABLE and RULE every time you change the underlying table structure.
  3. Once the RULE is applied, the TABLE turns into a VIEW.
  4. Must use DROP VIEW to get rid of the TABLE with RULE.

-- 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.

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.
  2. Table owner, does not have the rights to read, write, update, or delete the 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;

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.

The con's:

  1. Empty table returned when you don't have all the correct permissions instead of an error message.
  2. Table owner, does not have the rights to read, write, update, or delete the 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.

The con's:

  1. Table owner, does not have the rights to read, write, update, or delete the data.
  2. Once the RULE is applied, the TABLE turns into a VIEW.
  3. Must use DROP VIEW to get rid of the TABLE with RULE.

-- 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.