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