postgres policy

postgres-policy.sql

SET SESSION app.id TO DEFAULT;
SET ROLE postgres;
SET search_path = 'public';

DROP TABLE IF EXISTS expenses CASCADE;
DROP TABLE IF EXISTS projects CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
	user_name text PRIMARY KEY NOT NULL,
	real_name text NOT NULL
);

CREATE TABLE projects (
	user_name text NOT NULL REFERENCES users(user_name),
	project_id int NOT NULL,
	CONSTRAINT pk_projects PRIMARY KEY (user_name, project_id)
);

CREATE TABLE expenses (
	expense_id SERIAL PRIMARY KEY,
	user_name text NOT NULL,
	project_id int NOT NULL,
	amount MONEY NOT NULL,
	FOREIGN KEY (user_name, project_id) REFERENCES projects(user_name, project_id)
);

DROP ROLE other;
CREATE ROLE other;
DROP ROLE bob;
CREATE ROLE bob;
DROP ROLE alice;
CREATE ROLE alice;

INSERT INTO users (user_name, real_name) VALUES
	('admin','Admin'),
	('bob','Bob'),
	('alice','Alice');

INSERT INTO projects(user_name, project_id)
SELECT 'alice', project.id
FROM generate_series(0, 10) project(id)
UNION
SELECT 'bob', project.id
FROM generate_series(0, 10) project(id);

INSERT INTO expenses(user_name, project_id, amount)
SELECT 'alice', project.id, expense.amount
FROM
	generate_series(0, 10) project(id),
	generate_series(0, 1000) expense(amount)
UNION
SELECT 'bob', project.id, expense.amount
FROM
	generate_series(0, 10) project(id),
	generate_series(0, 1000) expense(amount);

ANALYZE users, projects, expenses;

CREATE INDEX ix_expenses ON expenses (user_name, project_id);

GRANT SELECT, DELETE, UPDATE ON users, expenses, projects TO public;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE expenses ENABLE ROW LEVEL SECURITY;

CREATE POLICY pol_users ON users USING (current_setting('app.id') = user_name);
CREATE POLICY pol_projects ON projects USING (current_setting('app.id') = user_name);
CREATE POLICY pol_expenses ON expenses USING (current_setting('app.id') = user_name);

SET ROLE other;
SET SESSION app.id TO 'bob';
explain analyze select amount from expenses where user_name = 'bob' AND project_id = 2;

-- Result  (cost=26.55..182.57 rows=1001 width=8) (actual time=0.099..1.608 rows=1001 loops=1)
--   One-Time Filter: (current_setting('app.id'::text) = 'bob'::text)
--   ->  Bitmap Heap Scan on expenses  (cost=26.55..182.57 rows=1001 width=8) (actual time=0.094..0.761 rows=1001 loops=1)
--         Recheck Cond: ((user_name = 'bob'::text) AND (project_id = 2))
--         Heap Blocks: exact=141
--         ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (actual time=0.083..0.083 rows=1001 loops=1)
--               Index Cond: ((user_name = 'bob'::text) AND (project_id = 2))
-- Planning Time: 0.148 ms
-- Execution Time: 1.998 ms

explain analyze select amount from expenses where project_id = 2;

-- Bitmap Heap Scan on expenses  (cost=26.55..185.07 rows=1001 width=8) (actual time=0.098..0.991 rows=1001 loops=1)
--   Recheck Cond: (project_id = 2)
--   Filter: (current_setting('app.id'::text) = user_name)
--   Heap Blocks: exact=141
--   ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (actual time=0.084..0.084 rows=1001 loops=1)
--         Index Cond: ((user_name = current_setting('app.id'::text)) AND (project_id = 2))
-- Planning Time: 0.066 ms
-- Execution Time: 1.341 ms

explain analyze select amount from expenses where user_name = 'alice' AND project_id = 2;

-- Result  (cost=26.55..182.57 rows=1001 width=8) (actual time=0.004..0.004 rows=0 loops=1)
--   One-Time Filter: (current_setting('app.id'::text) = 'alice'::text)
--   ->  Bitmap Heap Scan on expenses  (cost=26.55..182.57 rows=1001 width=8) (never executed)
--         Recheck Cond: ((user_name = 'alice'::text) AND (project_id = 2))
--         ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (never executed)
--               Index Cond: ((user_name = 'alice'::text) AND (project_id = 2))
-- Planning Time: 0.083 ms
-- Execution Time: 0.017 ms

SET SESSION app.id TO 'invalid';
explain analyze select amount from expenses where project_id = 2;

-- Index Scan using ix_expenses on expenses  (cost=0.29..7.75 rows=1 width=8) (actual time=0.067..0.067 rows=0 loops=1)
--   Index Cond: ((user_name = current_setting('app.id'::text)) AND (project_id = 2))
-- Planning Time: 0.101 ms
-- Execution Time: 0.094 ms

SET SESSION app.id TO DEFAULT;

SET ROLE postgres;
DROP POLICY pol_users ON users;
DROP POLICY pol_projects ON projects;
DROP POLICY pol_expenses ON expenses;
CREATE POLICY pol_users ON users USING (current_user = user_name);
CREATE POLICY pol_projects ON projects USING (current_user = user_name);
CREATE POLICY pol_expenses ON expenses USING (current_user = user_name);

SET ROLE bob;
explain analyze select amount from expenses where user_name = 'bob' AND project_id = 2;

-- Bitmap Heap Scan on expenses  (cost=26.42..187.44 rows=501 width=8) (actual time=0.153..1.080 rows=1001 loops=1)
--   Recheck Cond: ((user_name = 'bob'::text) AND (project_id = 2))
--   Filter: (CURRENT_USER = user_name)
--   Heap Blocks: exact=141
--   ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (actual time=0.126..0.126 rows=1001 loops=1)
--         Index Cond: ((user_name = 'bob'::text) AND (project_id = 2))
-- Planning Time: 0.073 ms
-- Execution Time: 1.514 ms

explain analyze select amount from expenses where project_id = 2;

-- Seq Scan on expenses  (cost=0.00..526.38 rows=1001 width=8) (actual time=0.009..1.968 rows=1001 loops=1)
--   Filter: ((project_id = 2) AND (CURRENT_USER = user_name))
--   Rows Removed by Filter: 21021
-- Planning Time: 0.048 ms
-- Execution Time: 2.291 ms

explain analyze select amount from expenses where user_name = 'alice' AND project_id = 2;

-- Bitmap Heap Scan on expenses  (cost=26.42..187.44 rows=501 width=8) (actual time=0.603..0.604 rows=0 loops=1)
--   Recheck Cond: ((user_name = 'alice'::text) AND (project_id = 2))
--   Filter: (CURRENT_USER = user_name)
--   Rows Removed by Filter: 1001
--   Heap Blocks: exact=141
--   ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (actual time=0.102..0.102 rows=1001 loops=1)
--         Index Cond: ((user_name = 'alice'::text) AND (project_id = 2))
-- Planning Time: 0.051 ms
-- Execution Time: 0.642 ms

explain analyze select amount from expenses where project_id = 2;

-- Seq Scan on expenses  (cost=0.00..526.38 rows=1001 width=8) (actual time=0.010..2.521 rows=1001 loops=1)
--   Filter: ((project_id = 2) AND (CURRENT_USER = user_name))
--   Rows Removed by Filter: 21021
-- Planning Time: 0.065 ms
-- Execution Time: 2.885 ms

SET ROLE other;
explain analyze select amount from expenses where user_name = 'bob' AND project_id = 2;

-- Bitmap Heap Scan on expenses  (cost=26.42..187.44 rows=501 width=8) (actual time=0.107..0.807 rows=1001 loops=1)
--   Recheck Cond: ((user_name = 'bob'::text) AND (project_id = 2))
--   Filter: (CURRENT_USER = user_name)
--   Heap Blocks: exact=141
--   ->  Bitmap Index Scan on ix_expenses  (cost=0.00..26.30 rows=1001 width=0) (actual time=0.093..0.093 rows=1001 loops=1)
--         Index Cond: ((user_name = 'bob'::text) AND (project_id = 2))
-- Planning Time: 0.056 ms
-- Execution Time: 1.147 ms

explain analyze select amount from expenses where project_id = 2;

-- Seq Scan on expenses  (cost=0.00..526.38 rows=1001 width=8) (actual time=0.010..2.186 rows=1001 loops=1)
--   Filter: ((project_id = 2) AND (CURRENT_USER = user_name))
--   Rows Removed by Filter: 21021
-- Planning Time: 0.048 ms
-- Execution Time: 2.536 ms