Row level security example sandbox for testing - Revision history

Busk: Busk moved page Row level security example 2 to Row level security example sandbox for testing without leaving a redirect

2024-02-17T01:06:11Z

2024-02-17T01:00:52Z

2024-02-17T00:56:14Z

2024-02-17T00:48:51Z

2024-02-17T00:47:34Z

2024-02-17T00:45:13Z

2024-02-17T00:42:45Z

2024-02-17T00:38:17Z

2024-02-17T00:34:37Z

2024-02-17T00:28:34Z

@@ Line 106: / Line 106: @@
       (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
+    user_id IN
       (SELECT id FROM users WHERE username = current_user)
 );

@@ Line 142: / Line 142: @@
 set role user1;
 update items set name='item1a' where id=1;
 update items set name='item1' where id=1;
 reset role;
@@ Line 189: / Line 190: @@
 ```
 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 52: / Line 52: @@
 INSERT INTO items VALUES
-  (DEFAULT, 'item1'),
+  (DEFAULT, 'item1', 1),
-  (DEFAULT, 'item2'),
+  (DEFAULT, 'item2', 2),
-(DEFAULT, 'item3');
+(DEFAULT, 'item3', 3);

@@ Line 39: / Line 39: @@
-INSERT INTO users VALUES
+INSERT INTO users (id, username) VALUES
   (DEFAULT, 'user1'),
   (DEFAULT, 'user2'),
 (DEFAULT, 'user3');
@@ Line 50: / Line 55: @@
   (DEFAULT, 'item2'),
 (DEFAULT, 'item3');

@@ Line 17: / Line 17: @@
       id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
       name   varchar(40) NOT NULL,
-      user_id   user_id integer NOT NULL,
+      user_id   bigint NOT NULL,
       created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
 );

← Older revision	Revision as of 01:06, 17 February 2024
(No difference)

@@ Line 24: / 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 32: / 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 166: / Line 166: @@
 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 16: / Line 16: @@
 CREATE TABLE items (
       id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-      name   varchar(40) NOT NULL
+      name   varchar(40) NOT NULL,
 );
@@ Line 142: / Line 144: @@
 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;
+update items set name='item1' where id=1;
 reset role;
 ```
@@ Line 176: / Line 178: @@
 ```
-## Control policy
+# Allow Insert for user
 ```
 GRANT INSERT ON TABLE public.items TO PUBLIC;
@@ Line 183: / Line 185: @@
 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');

@@ Line 133: / Line 133: @@
 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.id=users_x_groups.user_id where users.id = (select id from users where username = current_user)))
     OR
     id IN
-      (SELECT id as id FROM users WHERE username = current_user)
+      (SELECT id FROM users WHERE username = current_user)
 );
 ALTER TABLE items ENABLE ROW LEVEL SECURITY;