Роли и таблицы

postgres=# CREATE ROLE alice LOGIN;
CREATE ROLE
postgres=# CREATE ROLE bob LOGIN;
CREATE ROLE
postgres=# CREATE ROLE charlie LOGIN;
CREATE ROLE
postgres=# CREATE TABLE users_depts(
    login text,
    department text);
CREATE TABLE
postgres=# INSERT INTO users_depts VALUES 
    ('alice',  'PR'),
    ('bob',    'Sales'),
    ('charlie','PR'),
    ('charlie','Sales');
INSERT 0 4
postgres=# CREATE TABLE revenue(
    department text,
    amount numeric(10,2));
CREATE TABLE
postgres=# INSERT INTO revenue SELECT 'PR',   -random()* 100.00 FROM generate_series(1,100000);
INSERT 0 100000
postgres=# INSERT INTO revenue SELECT 'Sales', random()*1000.00 FROM generate_series(1,10000);
INSERT 0 10000

Политики и привилегии

postgres=# CREATE POLICY departments ON revenue
    USING (department IN (SELECT department FROM users_depts WHERE login = current_user));
CREATE POLICY
postgres=# CREATE POLICY amount ON revenue AS RESTRICTIVE
    USING (true)
    WITH CHECK ((SELECT count(*) FROM users_depts WHERE login = current_user) > 1
                OR abs(amount) <= 100.00);
CREATE POLICY
postgres=# ALTER TABLE revenue ENABLE ROW LEVEL SECURITY;
ALTER TABLE
postgres=# GRANT SELECT, INSERT ON users_depts, revenue TO alice, bob, charlie;
GRANT

Проверка

Алиса:

postgres=# \c - alice
You are now connected to database "access_rls" as user "alice".
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4977917.73
(1 row)

alice=> INSERT INTO revenue VALUES ('PR', 100.00);
INSERT 0 1
alice=> INSERT INTO revenue VALUES ('PR', 101.00);
ERROR:  new row violates row-level security policy "amount" for table "revenue"

Боб:

alice=> \c - bob
You are now connected to database "access_rls" as user "bob".
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |    sum     
------------+------------
 Sales      | 4943078.38
(1 row)

alice=> INSERT INTO revenue VALUES ('Sales', 100.00);
INSERT 0 1
alice=> INSERT INTO revenue VALUES ('Sales', 101.00);
ERROR:  new row violates row-level security policy "amount" for table "revenue"

Чарли:

alice=> \c - charlie
You are now connected to database "access_rls" as user "charlie".
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4977817.73
 Sales      |  4943178.38
(2 rows)

alice=> INSERT INTO revenue VALUES ('PR', 1000.00);
INSERT 0 1
alice=> INSERT INTO revenue VALUES ('Sales', 1000.00);
INSERT 0 1

Накладные расходы

Выполним запрос несколько раз, чтобы оценить среднее значение времени выполнения.

alice=> \timing on
Timing is on.
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 58,388 ms
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 65,298 ms
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 58,351 ms
alice=> \c - postgres
You are now connected to database "access_rls" as user "postgres".
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 40,407 ms
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 39,340 ms
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -4976817.73
 Sales      |  4944178.38
(2 rows)

Time: 39,263 ms

В данном конкретном случае накладные расходы не драматичны, хотя и вполне ощутимы.