Skip to content

Instantly share code, notes, and snippets.

@tswaters
Last active February 7, 2020 05:08
Show Gist options
  • Save tswaters/7ceb2e6ab0b460ff09a427668fcadcdb to your computer and use it in GitHub Desktop.
Save tswaters/7ceb2e6ab0b460ff09a427668fcadcdb to your computer and use it in GitHub Desktop.
postgres policy
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment