Row level security example sandbox for testing

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

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


drop table if exists items;
CREATE TABLE items (
     id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL,
     user_id   bigint NOT NULL,
     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


drop table if exists users_x_groups;
CREATE TABLE users_x_groups (
     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 (
     id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     item_id int NOT NULL,
     group_id int NOT NULL
);



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

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


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


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


INSERT INTO users_x_groups (id, user_id, group_id) 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_select ON items;
CREATE POLICY s_items_select ON items AS PERMISSIVE FOR SELECT
 USING (
   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.id=users_x_groups.user_id where users.id = (select id from users where username = current_user)))
   OR
   user_id IN
     (SELECT 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;
set role user3;
select * from items;
reset role;

Allow Updates to Items if owned by group user belongs to

GRANT UPDATE ON TABLE public.items TO PUBLIC;

DROP POLICY IF EXISTS s_items_update ON items;
CREATE POLICY s_items_update ON items AS PERMISSIVE FOR UPDATE
 USING (
   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.id=users_x_groups.user_id where users.id = (select id from users where username = current_user)))
   OR
   id IN
     (SELECT id FROM users WHERE username = current_user)
);
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

set role user1;
update items set name='item1a' where id=1;
select * from items;
update items set name='item1' where id=1;
reset role;

Check items permissions policy & grants

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='items';

\z items;

Allow Insert by user but we need to use a function to add

Function to add user_id

CREATE OR REPLACE FUNCTION set_user_id()
RETURNS TRIGGER AS $$
BEGIN
    NEW.user_id := (select id from users where username=current_user);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER before_insert_set_user_id
BEFORE INSERT ON items
FOR EACH ROW
EXECUTE FUNCTION set_user_id();

Allow Insert for user

GRANT INSERT ON TABLE public.items TO PUBLIC;

drop POLICY IF EXISTS s_items_insert ON items;
CREATE POLICY s_items_insert ON items AS PERMISSIVE FOR INSERT
  with check (
    exists (select id from users where username = current_user)
);

Test insert

set role user1;
INSERT INTO items (id, name) VALUES (DEFAULT, 'new-item-by-user1a');
reset role;

Useful checks

select * from pg_policies;
SELECT SESSION_USER, CURRENT_USER;

WIP - Work in Process - don't work yet

check_insert_permission function for policy

CREATE OR REPLACE FUNCTION check_insert_permission()
  RETURNS BOOLEAN AS $$
BEGIN
  -- Return true if the id column matches the current user's ID
  RETURN current_setting('user.id')::INTEGER = (SELECT user_id FROM items WHERE items.id = NEW.id);
END;
$$ LANGUAGE plpgsql;


CREATE POLICY insert_policy
  ON items
  FOR INSERT
  USING (check_insert_permission());

Trash

Grant Select

drop POLICY IF EXISTS user_items_select ON items;
CREATE POLICY user_items_select ON items AS PERMISSIVE FOR SELECT
  USING (user_id IN 
    (SELECT user_id FROM users WHERE username = current_user)
);