Difference between revisions of "Row level security example"
Jump to navigation
Jump to search
(Created page with "``` 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...") |
|||
Line 108: | Line 108: | ||
select * from items; | select * from items; | ||
reset role; | reset role; | ||
+ | ``` | ||
+ | |||
+ | # Useful checks | ||
+ | ``` | ||
+ | select * from pg_policies; | ||
+ | SELECT SESSION_USER, CURRENT_USER; | ||
``` | ``` |
Revision as of 20:59, 3 February 2024
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, 'user'); (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, 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; set role user1; SELECT SESSION_USER, CURRENT_USER; reset role; GRANT SELECT ON items 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 = (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;