Row level security example

drop table if exists users;
CREATE TABLE users (
     user_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     username   varchar(40) NOT NULL
);

drop table if exists groups;
CREATE TABLE groups (
     group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     groupname   varchar(40) NOT NULL
);


drop table if exists items;
CREATE TABLE items (
     item_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL
);


drop table if exists users_x_groups;
CREATE TABLE users_x_groups (
     user_x_group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     user_id int NOT NULL,
     group_id int NOT NULL
);


drop table if exists items_x_groups;
CREATE TABLE items_x_groups (
     item_x_group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     item_id int NOT NULL,
     group_id int NOT NULL
);



INSERT INTO users VALUES
 (DEFAULT, 'user1'),
 (DEFAULT, 'user2'),
(DEFAULT, 'user3');



INSERT INTO items VALUES
 (DEFAULT, 'item1'),
 (DEFAULT, 'item2'),
(DEFAULT, 'item3');



INSERT INTO groups VALUES
 (DEFAULT, 'group1'),
 (DEFAULT, 'group11'),
 (DEFAULT, 'group2'),
 (DEFAULT, 'group22');


INSERT INTO items_x_groups VALUES
 (DEFAULT, 1, 1),
(DEFAULT, 1, 11),
(DEFAULT, 11, 11),
 (DEFAULT, 2, 2),
 (DEFAULT, 2, 22),
(DEFAULT, 3, 33);


INSERT INTO users_x_groups VALUES
 (DEFAULT, 1, 1),
(DEFAULT, 1, 11),
(DEFAULT, 2, 2),
 (DEFAULT, 2, 22);




ALTER TABLE items ENABLE ROW LEVEL SECURITY;


create role user1;
create role user2;
create role user3;


set role user1;
SELECT SESSION_USER, CURRENT_USER;

reset role;

Grants

GRANT SELECT ON TABLE public.items TO PUBLIC;
GRANT SELECT ON TABLE public.items_x_groups TO PUBLIC;
GRANT SELECT ON TABLE public.users TO PUBLIC;
GRANT SELECT ON TABLE public.users_x_groups TO PUBLIC;

Apply policy

drop POLICY IF EXISTS s_items_select3 ON items;
 CREATE POLICY s_items_select3 ON items AS PERMISSIVE FOR SELECT
 USING ( item_id IN
(select distinct item_id from items_x_groups where group_id in (SELECT users_x_groups.group_id FROM USERS INNER JOIN users_x_groups ON users.user_id=users_x_groups.user_id where users.user_id = (select user_id from users where username = current_user)))
);
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

test

set role user1;
select * from items;
set role user2;
select * from items;
reset role;

Useful checks

select * from pg_policies;
SELECT SESSION_USER, CURRENT_USER;