01-init.sql

CREATE SCHEMA demo;

CREATE TABLE demo.private_rows (
  value varchar(255),
  policy varchar(255),
  "group" varchar(255)
);

INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value1', 'FREE', '');
INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value2', 'FREE', '');
INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value3', 'PRIV', 'VLKB.group1');
INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value4', 'PRIV', 'VLKB.group1');
INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value5', 'PRIV', 'VLKB.group2');
INSERT INTO demo.private_rows (value, policy, "group") VALUES ('value6', 'PRIV', 'VLKB.group2');

-- WARNING: always create a new role because Row Level Security doesn't work for table owner
CREATE ROLE tap WITH LOGIN PASSWORD 'demo';

GRANT USAGE ON SCHEMA demo TO tap;
GRANT SELECT ON demo.private_rows TO tap;

ALTER TABLE demo.private_rows ENABLE ROW LEVEL SECURITY;

CREATE POLICY filter_rls_policy ON demo.private_rows FOR ALL TO PUBLIC USING (policy = 'FREE' OR "group" = ANY(current_setting('my.tap_groups', true)::varchar[]));