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

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         | -5008636.30
(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".
bob=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |    sum     
------------+------------
 Sales      | 5028638.31
(1 row)

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

Чарли:

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

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

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

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

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

Time: 60,651 ms
charlie=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -5007536.30
 Sales      |  5029738.31
(2 rows)

Time: 60,182 ms
charlie=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -5007536.30
 Sales      |  5029738.31
(2 rows)

Time: 64,437 ms
charlie=> \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         | -5007536.30
 Sales      |  5029738.31
(2 rows)

Time: 41,806 ms
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
 department |     sum     
------------+-------------
 PR         | -5007536.30
 Sales      |  5029738.31
(2 rows)

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

Time: 40,048 ms

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