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 | -5003821.69 (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 | 4952520.95 (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 | -5003721.69 Sales | 4952620.95 (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 | -5002721.69 Sales | 4953620.95 (2 rows) Time: 68,249 ms
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
department | sum ------------+------------- PR | -5002721.69 Sales | 4953620.95 (2 rows) Time: 66,771 ms
alice=> SELECT department, SUM(amount) FROM revenue GROUP BY department;
department | sum ------------+------------- PR | -5002721.69 Sales | 4953620.95 (2 rows) Time: 69,433 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 | -5002721.69 Sales | 4953620.95 (2 rows) Time: 44,692 ms
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
department | sum ------------+------------- PR | -5002721.69 Sales | 4953620.95 (2 rows) Time: 41,790 ms
postgres=# SELECT department, SUM(amount) FROM revenue GROUP BY department;
department | sum ------------+------------- PR | -5002721.69 Sales | 4953620.95 (2 rows) Time: 41,526 ms
В данном конкретном случае накладные расходы не драматичны, хотя и вполне ощутимы.