Row level security example - Revision history

Busk at 01:05, 17 February 2024

2024-02-17T01:05:42Z

Busk at 22:09, 16 February 2024

2024-02-16T22:09:12Z

Busk at 21:59, 16 February 2024

2024-02-16T21:59:35Z

Busk at 21:47, 16 February 2024

2024-02-16T21:47:52Z

Busk at 21:43, 16 February 2024

2024-02-16T21:43:23Z

Busk at 21:26, 16 February 2024

2024-02-16T21:26:42Z

Busk at 19:40, 16 February 2024

2024-02-16T19:40:45Z

Busk at 19:27, 16 February 2024

2024-02-16T19:27:49Z

Busk at 19:24, 16 February 2024

2024-02-16T19:24:31Z

Busk at 19:19, 16 February 2024

2024-02-16T19:19:56Z

@@ Line 2: / Line 2: @@
 drop table if exists users;
 CREATE TABLE users (
-      user_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+      id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
       username   varchar(40) NOT NULL
 );
@@ Line 8: / Line 8: @@
 drop table if exists groups;
 CREATE TABLE groups (
-      group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+      id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
       groupname   varchar(40) NOT NULL
 );
@@ Line 15: / Line 15: @@
 drop table if exists items;
 CREATE TABLE items (
-      item_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+      id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-      name   varchar(40) NOT NULL
+      name   varchar(40) NOT NULL,
 );
@@ Line 22: / Line 24: @@
 drop table if exists users_x_groups;
 CREATE TABLE users_x_groups (
-      user_x_group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+      id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
       user_id int NOT NULL,
       group_id int NOT NULL
@@ Line 30: / Line 32: @@
 drop table if exists items_x_groups;
 CREATE TABLE items_x_groups (
-      item_x_group_id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+      id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
       item_id int NOT NULL,
       group_id int NOT NULL
@@ Line 37: / Line 39: @@
-INSERT INTO users VALUES
+INSERT INTO users (id, username) VALUES
   (DEFAULT, 'user1'),
   (DEFAULT, 'user2'),
 (DEFAULT, 'user3');
+INSERT INTO groups (id, groupname) VALUES
   (DEFAULT, 'group1'),
   (DEFAULT, 'group11'),
@@ Line 58: / Line 51: @@
-INSERT INTO items_x_groups VALUES
+INSERT INTO items VALUES
   (DEFAULT, 1, 1),
 (DEFAULT, 1, 11),
@@ Line 67: / Line 66: @@
-INSERT INTO users_x_groups VALUES
+INSERT INTO users_x_groups (id, user_id, group_id) VALUES
   (DEFAULT, 1, 1),
 (DEFAULT, 1, 11),
@@ Line 104: / Line 103: @@
 CREATE POLICY s_items_select ON items AS PERMISSIVE FOR SELECT
   USING (
-    item_id IN
+    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)))
+      (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 user_id FROM users WHERE username = current_user)
+      (SELECT id FROM users WHERE username = current_user)
 );
 ALTER TABLE items ENABLE ROW LEVEL SECURITY;
@@ Line 133: / Line 132: @@
 CREATE POLICY s_items_update ON items AS PERMISSIVE FOR UPDATE
   USING (
-    item_id IN
+    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)))
+      (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
+    id IN
-      (SELECT user_id FROM users WHERE username = current_user)
+      (SELECT id FROM users WHERE username = current_user)
 );
 ALTER TABLE items ENABLE ROW LEVEL SECURITY;
 set role user1;
-update items set name='item1a' where item_id=1;
+update items set name='item1a' where id=1;
-update items set name='item1' where item_id=1;
+select * from items;
 reset role;
 ```
@@ Line 164: / Line 164: @@
 RETURNS TRIGGER AS $$
 BEGIN
-     NEW.user_id := (select user_id from users where username=current_user);
+     NEW.user_id := (select id from users where username=current_user);
      RETURN NEW;
 END;
@@ Line 176: / Line 176: @@
 ```
-## Control policy
+# Allow Insert for user
 ```
 GRANT INSERT ON TABLE public.items TO PUBLIC;
@@ Line 183: / Line 183: @@
 CREATE POLICY s_items_insert ON items AS PERMISSIVE FOR INSERT
    with check (
-     exists (select user_id from users where username = current_user)
+     exists (select id from users where username = current_user)
 );
 set role user1;
-INSERT INTO items VALUES (DEFAULT, 'new-item-by-user1');
+INSERT INTO items (id, name) VALUES (DEFAULT, 'new-item-by-user1a');
 reset role;
 ```

@@ Line 189: / Line 189: @@
 reset role;
 ```
 # Useful checks
@@ Line 224: / Line 214: @@
    USING (check_insert_permission());
 ```

@@ Line 101: / Line 101: @@
 Apply policy
 ```
-drop POLICY IF EXISTS s_items_select ON items;
+DROP POLICY IF EXISTS s_items_select ON items;
- CREATE POLICY s_items_select ON items AS PERMISSIVE FOR SELECT
+CREATE POLICY s_items_select ON items AS PERMISSIVE FOR SELECT
-  USING ( item_id IN
+  USING (
-(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)))
+   item_id IN
 );
 ALTER TABLE items ENABLE ROW LEVEL SECURITY;
@@ Line 126: / Line 130: @@
 GRANT UPDATE ON TABLE public.items TO PUBLIC;
-drop POLICY IF EXISTS s_items_update ON items;
+DROP POLICY IF EXISTS s_items_update ON items;
- CREATE POLICY s_items_update ON items AS PERMISSIVE FOR UPDATE
+CREATE POLICY s_items_update ON items AS PERMISSIVE FOR UPDATE
-  USING ( item_id IN
+  USING (
-(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)))
+   item_id IN
 );
 ALTER TABLE items ENABLE ROW LEVEL SECURITY;

@@ Line 186: / Line 186: @@
 drop POLICY IF EXISTS user_items_select ON items;
 CREATE POLICY user_items_select ON items AS PERMISSIVE FOR SELECT
-   USING (user_id <= (SELECT user_id FROM users WHERE username = current_user)
+   USING (user_id IN
 );
 ```

@@ Line 180: / Line 180: @@
 INSERT INTO items VALUES (DEFAULT, 'new-item-by-user1');
 reset role;
 ```

@@ Line 150: / Line 150: @@
 # Allow Insert by user but we need to use a function to add
 ```
 GRANT INSERT ON TABLE public.items TO PUBLIC;

@@ Line 175: / Line 175: @@
 BEGIN
    -- Return true if the id column matches the current user's ID
-   RETURN current_setting('user.id')::INTEGER = (SELECT id FROM items WHERE items.id = NEW.id);
+   RETURN current_setting('user.id')::INTEGER = (SELECT user_id FROM items WHERE items.id = NEW.id);
 END;
 $$ LANGUAGE plpgsql;

@@ Line 165: / Line 165: @@
 select * from pg_policies;
 SELECT SESSION_USER, CURRENT_USER;
 ```

← Older revision		Revision as of 19:19, 16 February 2024
Line 147:		Line 147:
	\z items;		\z items;

		+	```
		+
		+	# Allow Insert by user but we need to use a function to add
		+	```
		+	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 user_id from users where username = current_user)
		+	);
	```		```