1

I have an employee table which has data for all region and countries. As an admin, I want to enable RLS on this table so that the end user can only view the data he is allowed to. The access permissions for a user are defined in access_ctl table. Tried implementing RLS as below but when end user queries the table, he gets error.

As Admin/Superuser:

CREATE SCHEMA IF NOT EXISTS rls_demo;


CREATE TABLE rls_demo.ep (
  id INT,
  division VARCHAR(255),
  region VARCHAR(255),
  work_location_country VARCHAR(256)
);


INSERT INTO rls_demo.ep VALUES
  (1,'SALES',NULL,'USA'),
  (2,NULL,'North America','CAN'),
  (3,'SERVICE','North America','USA');
  
 
CREATE TABLE rls_demo.access_ctl (
  rule_id INT IDENTITY(1,1),
  username VARCHAR(100),
  division VARCHAR(255),
  region VARCHAR(255),
  work_location_country VARCHAR(256),
  full_access BOOLEAN DEFAULT FALSE
);


INSERT INTO rls_demo.access_ctl (username, division, region, work_location_country, full_access)
VALUES ('ta1', NULL, NULL, 'CAN', FALSE);


CREATE RLS POLICY policy_ep_demo
WITH (division VARCHAR(255), region VARCHAR(255), work_location_country VARCHAR(256)) AS ep
USING (
  EXISTS (
    SELECT 1
    FROM rls_demo.access_ctl ac
    WHERE ac.username = current_user
      AND (
        ac.full_access = TRUE
        OR (
          (ac.division IS NULL OR ac.division = ep.division)
          AND (ac.region  IS NULL OR ac.region  = ep.region)
          AND (ac.work_location_country IS NULL OR ac.work_location_country = ep.work_location_country)
        )
      )
  )
);


ALTER TABLE rls_demo.ep ROW LEVEL SECURITY ON;
ATTACH RLS POLICY policy_ep_demo ON rls_demo.ep TO ROLE rls_demo_role;


CREATE ROLE  rls_demo_role;
GRANT ROLE rls_demo_role TO ta1;


GRANT USAGE ON SCHEMA rls_demo TO role rls_demo_role;
GRANT SELECT ON rls_demo.ep           TO role rls_demo_role;   
GRANT SELECT ON rls_demo.access_ctl   TO role rls_demo_role;   

As TA1 User:

Now when TA1 user runs the SELECT DISTINCT work_location_country FROM rls_demo.ep query, he gets following error:

SQL Error [42501]: ERROR: permission denied to rls policy for relation access_ctl

2
  • Have you tried setting the role before the query to make sure it running under the right context; SET ROLE rls_demo_role; Commented Aug 19 at 20:15
  • @JeffA "SET ROLE rls_demo_role" doesn't work in Redshift. Commented Aug 26 at 18:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.