Row level security hands-on

version: '3.1'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432
    volumes:
      - ./data/db:/var/lib/postgresql/data

~/go/projects/github.com/kbwo/demo-rls
❮ psql -U postgres --password --host 127.0.0.1 --port 5432
Password:
psql (16.1)
Type "help" for help.

postgres=# CREATE DATABASE demo_rls;
CREATE DATABASE
postgres=# \c demo_rls;
Password:
You are now connected to database "demo_rls" as user "postgres".
demo_rls=# CREATE TABLE users (
    id         INT  NOT NULL,
    name       TEXT NOT NULL,
    email      TEXT,
    company_id TEXT NOT NULL,
    PRIMARY KEY (company_id, id)
);
CREATE TABLE
demo_rls=# INSERT INTO users VALUES (1, 'yamada', 'yamada@001.example.com', '001');
INSERT INTO users VALUES (2, 'murata', 'murata@001.example.com', '001');
INSERT INTO users VALUES (1, 'tanaka', 'tanaka@002.example.com', '002');
INSERT 0 1
INSERT 0 1
INSERT 0 1
demo_rls=# CREATE ROLE sales_company;
GRANT SELECT,INSERT,UPDATE,DELETE ON users TO sales_company;

CREATE USER sales_company_001;
CREATE USER sales_company_002;
GRANT sales_company TO sales_company_001;
GRANT sales_company TO sales_company_002;

CREATE USER presiding_company;
GRANT SELECT,INSERT,UPDATE,DELETE ON users TO presiding_company;
CREATE ROLE
GRANT
CREATE ROLE
CREATE ROLE
GRANT ROLE
GRANT ROLE
CREATE ROLE
GRANT
demo_rls=# ALTER USER sales_company_001 WITH PASSWORD 'example';
ALTER ROLE
demo_rls=# ALTER USER sales_company_002 WITH PASSWORD 'example';
ALTER ROLE
demo_rls=# exit

~/go/projects/github.com/kbwo/demo-rls 1m 39s
❮ psql --username=sales_company_001 --password --host 127.0.0.1 --port 5432 demo_rls
Password:
psql (16.1)
Type "help" for help.

demo_rls=> \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

demo_rls=> \d users
                 Table "public.users"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 id         | integer |           | not null |
 name       | text    |           | not null |
 email      | text    |           |          |
 company_id | text    |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (company_id, id)

demo_rls=> exit

~/go/projects/github.com/kbwo/demo-rls 38s
❯ psql -U postgres --password --host 127.0.0.1 --port 5432
Password:
psql (16.1)
Type "help" for help.

postgres=# \c demo_rls
Password:
You are now connected to database "demo_rls" as user "postgres".
demo_rls=# ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE
demo_rls=# CREATE POLICY users_for_sales_company ON users TO sales_company
    USING (CONCAT('sales_company_', company_id) = CURRENT_USER);
CREATE POLICY
demo_rls=# CREATE POLICY users_for_presiding_company ON users TO presiding_company
    USING (true) WITH CHECK (true);
CREATE POLICY
demo_rls=# \dp;
                                                              Access privileges
 Schema | Name  | Type  |        Access privileges        | Column privileges |                           Policies
--------+-------+-------+---------------------------------+-------------------+--------------------------------------------------------------
 public | users | table | postgres=arwdDxt/postgres      +|                   | users_for_sales_company:                                    +
        |       |       | sales_company=arwd/postgres    +|                   |   (u): (concat('sales_company_', company_id) = CURRENT_USER)+
        |       |       | presiding_company=arwd/postgres |                   |   to: sales_company                                         +
        |       |       |                                 |                   | users_for_presiding_company:                                +
        |       |       |                                 |                   |   (u): true                                                 +
        |       |       |                                 |                   |   (c): true                                                 +
        |       |       |                                 |                   |   to: presiding_company
(1 row)

demo_rls=# \d users;
                 Table "public.users"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 id         | integer |           | not null |
 name       | text    |           | not null |
 email      | text    |           |          |
 company_id | text    |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (company_id, id)
Policies:
    POLICY "users_for_presiding_company"
      TO presiding_company
      USING (true)
      WITH CHECK (true)
    POLICY "users_for_sales_company"
      TO sales_company
      USING ((concat('sales_company_', company_id) = CURRENT_USER))

demo_rls=# exit

~/go/projects/github.com/kbwo/demo-rls 1m 38s
❯ psql --username=sales_company_001 --password --host 127.0.0.1 --port 5432 demo_rls
Password:
psql (16.1)
Type "help" for help.

demo_rls=> SELECT * FROM users;
 id |  name  |         email          | company_id
----+--------+------------------------+------------
  1 | yamada | yamada@001.example.com | 001
  2 | murata | murata@001.example.com | 001
(2 rows)

demo_rls=> INSERT INTO users VALUES
    (3, 'kotani', 'kotani@001.example.com', '001'),
    (2, 'watabe', 'watabe@002.example.com', '002');
ERROR:  new row violates row-level security policy for table "users"
demo_rls=> UPDATE users SET name = 'xxx' WHERE id = 1;
UPDATE 1
demo_rls=> SELECT * FROM users;
 id |  name  |         email          | company_id
----+--------+------------------------+------------
  2 | murata | murata@001.example.com | 001
  1 | xxx    | yamada@001.example.com | 001
(2 rows)

demo_rls=> DELETE FROM users;
DELETE 2
demo_rls=>