-- Queries and DML of ordinary roles on tables with the three kinds of sift objects: what each privilege level may read and write, the same -- answers through the index and the plain plan, row-level security, column privileges, views and SECURITY DEFINER functions, prepared -- statements, cursors, COPY and EXPLAIN. Every role has USAGE on the schema sift (PUBLIC has it) except in the one section that takes it back. -- docs (sift and sift_col indexes) and facts (sift_table) have no row-level security; rdocs and rfacts are the same with a policy that -- restricts rq_rls to the rows of g = 1. CREATE EXTENSION pg_sift; \set VERBOSITY terse \set ECHO none SELECT rr_roles_drop('rq_'); rr_roles_drop --------------- (1 row) CREATE ROLE rq_own; CREATE ROLE rq_mem IN ROLE rq_own; CREATE ROLE rq_wr; CREATE ROLE rq_rd; CREATE ROLE rq_col; CREATE ROLE rq_non; CREATE ROLE rq_nos; CREATE ROLE rq_rls; CREATE ROLE rq_byp BYPASSRLS; CREATE ROLE rq_vw; CREATE SCHEMA rq; GRANT USAGE ON SCHEMA rq TO rq_wr, rq_rd, rq_col, rq_non, rq_nos, rq_rls, rq_byp, rq_vw; GRANT USAGE, CREATE ON SCHEMA rq TO rq_own, rq_mem; SET enable_seqscan = off; SELECT * FROM rr_run($$ rq_own | CREATE TABLE rq.docs (id int PRIMARY KEY, body text, g int, v float8) rq_own | INSERT INTO rq.docs SELECT i, 'alpha ' || CASE WHEN i % 5 = 0 THEN 'five ' ELSE '' END || 'beta ' || i, i % 3, i FROM generate_series(1, 300) i rq_own | CREATE INDEX docs_idx ON rq.docs USING sift (body) rq_own | CREATE INDEX docs_col ON rq.docs USING sift_col (id, g, v) rq_own | CREATE TABLE rq.facts (id int, g int, val float8, doc jsonb) USING sift_table rq_own | INSERT INTO rq.facts SELECT i, i % 3, i, jsonb_build_object('a', i % 7) FROM generate_series(1, 300) i rq_own | CREATE TABLE rq.rdocs AS SELECT * FROM rq.docs rq_own | CREATE INDEX rdocs_idx ON rq.rdocs USING sift (body) rq_own | CREATE INDEX rdocs_col ON rq.rdocs USING sift_col (id, g, v) rq_own | CREATE TABLE rq.rfacts (LIKE rq.facts) USING sift_table rq_own | INSERT INTO rq.rfacts SELECT * FROM rq.facts rq_own | GRANT SELECT ON rq.docs, rq.facts, rq.rdocs, rq.rfacts TO rq_rd, rq_nos, rq_rls, rq_byp rq_own | GRANT SELECT, INSERT, UPDATE, DELETE ON rq.docs, rq.facts, rq.rdocs, rq.rfacts TO rq_wr rq_own | GRANT SELECT (id, g) ON rq.docs, rq.facts TO rq_col rq_own | CREATE VIEW rq.facts_v AS SELECT id, g, val FROM rq.facts rq_own | GRANT SELECT ON rq.facts_v TO rq_vw rq_own | CREATE VIEW rq.rdocs_v AS SELECT id, body, g, v FROM rq.rdocs rq_own | GRANT SELECT ON rq.rdocs_v TO rq_vw, rq_rls rq_own | CREATE VIEW rq.docs_v AS SELECT id, body, g, v FROM rq.docs rq_own | GRANT SELECT ON rq.docs_v TO rq_vw rq_own | CREATE FUNCTION rq.count_docs(q text) RETURNS bigint LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS 'SELECT count(*) FROM rq.docs WHERE body @@> q' rq_own | GRANT EXECUTE ON FUNCTION rq.count_docs(text) TO rq_non rq_own | ALTER TABLE rq.rdocs ENABLE ROW LEVEL SECURITY rq_own | ALTER TABLE rq.rfacts ENABLE ROW LEVEL SECURITY rq_own | CREATE POLICY rdocs_all ON rq.rdocs FOR ALL TO PUBLIC USING (true) rq_own | CREATE POLICY rdocs_rls ON rq.rdocs AS RESTRICTIVE FOR SELECT TO rq_rls USING (g = 1) rq_own | CREATE POLICY rfacts_all ON rq.rfacts FOR ALL TO PUBLIC USING (true) rq_own | CREATE POLICY rfacts_rls ON rq.rfacts AS RESTRICTIVE FOR SELECT TO rq_rls USING (g = 1) $$); n | role | stmt | result ----+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------- 1 | rq_own | CREATE TABLE rq.docs (id int PRIMARY KEY, body text, g int, v float8) | ok 2 | rq_own | INSERT INTO rq.docs SELECT i, 'alpha ' || CASE WHEN i % 5 = 0 THEN 'five ' ELSE '' END || 'beta ' || i, i % 3, i FROM generate_series(1, 300) i | ok (300) 3 | rq_own | CREATE INDEX docs_idx ON rq.docs USING sift (body) | ok 4 | rq_own | CREATE INDEX docs_col ON rq.docs USING sift_col (id, g, v) | ok 5 | rq_own | CREATE TABLE rq.facts (id int, g int, val float8, doc jsonb) USING sift_table | ok 6 | rq_own | INSERT INTO rq.facts SELECT i, i % 3, i, jsonb_build_object('a', i % 7) FROM generate_series(1, 300) i | ok (300) 7 | rq_own | CREATE TABLE rq.rdocs AS SELECT * FROM rq.docs | ok 8 | rq_own | CREATE INDEX rdocs_idx ON rq.rdocs USING sift (body) | ok 9 | rq_own | CREATE INDEX rdocs_col ON rq.rdocs USING sift_col (id, g, v) | ok 10 | rq_own | CREATE TABLE rq.rfacts (LIKE rq.facts) USING sift_table | ok 11 | rq_own | INSERT INTO rq.rfacts SELECT * FROM rq.facts | ok (300) 12 | rq_own | GRANT SELECT ON rq.docs, rq.facts, rq.rdocs, rq.rfacts TO rq_rd, rq_nos, rq_rls, rq_byp | ok 13 | rq_own | GRANT SELECT, INSERT, UPDATE, DELETE ON rq.docs, rq.facts, rq.rdocs, rq.rfacts TO rq_wr | ok 14 | rq_own | GRANT SELECT (id, g) ON rq.docs, rq.facts TO rq_col | ok 15 | rq_own | CREATE VIEW rq.facts_v AS SELECT id, g, val FROM rq.facts | ok 16 | rq_own | GRANT SELECT ON rq.facts_v TO rq_vw | ok 17 | rq_own | CREATE VIEW rq.rdocs_v AS SELECT id, body, g, v FROM rq.rdocs | ok 18 | rq_own | GRANT SELECT ON rq.rdocs_v TO rq_vw, rq_rls | ok 19 | rq_own | CREATE VIEW rq.docs_v AS SELECT id, body, g, v FROM rq.docs | ok 20 | rq_own | GRANT SELECT ON rq.docs_v TO rq_vw | ok 21 | rq_own | CREATE FUNCTION rq.count_docs(q text) RETURNS bigint LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS 'SELECT count(*) FROM rq.docs WHERE body @@> q' | ok 22 | rq_own | GRANT EXECUTE ON FUNCTION rq.count_docs(text) TO rq_non | ok 23 | rq_own | ALTER TABLE rq.rdocs ENABLE ROW LEVEL SECURITY | ok 24 | rq_own | ALTER TABLE rq.rfacts ENABLE ROW LEVEL SECURITY | ok 25 | rq_own | CREATE POLICY rdocs_all ON rq.rdocs FOR ALL TO PUBLIC USING (true) | ok 26 | rq_own | CREATE POLICY rdocs_rls ON rq.rdocs AS RESTRICTIVE FOR SELECT TO rq_rls USING (g = 1) | ok 27 | rq_own | CREATE POLICY rfacts_all ON rq.rfacts FOR ALL TO PUBLIC USING (true) | ok 28 | rq_own | CREATE POLICY rfacts_rls ON rq.rfacts AS RESTRICTIVE FOR SELECT TO rq_rls USING (g = 1) | ok (28 rows) ANALYZE rq.docs, rq.facts, rq.rdocs, rq.rfacts; -- the BM25 index: the column privileges must cover the indexed column; row-level security cuts the rows of the policy and refuses the score SELECT * FROM rr_matrix(ARRAY['rq_own', 'rq_mem', 'rq_wr', 'rq_rd', 'rq_nos', 'rq_col', 'rq_non', 'rq_vw'], $$ SELECT count(*) FROM rq.docs WHERE body @@> 'alpha' SELECT count(*) FROM rq.docs WHERE body @@> 'five' AND g = 1 SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 SELECT count(*) FROM rq.docs_v WHERE body @@> 'alpha' SELECT rq.count_docs('five') EXPLAIN SELECT id FROM rq.docs WHERE body @@> 'five' $$); stmt | result | by_role --------------------------------------------------------------------------------------------------------------------+------------------------------------------+------------------------------------------------------------- SELECT count(*) FROM rq.docs WHERE body @@> 'alpha' | ok: (300) | rq_own, rq_mem, rq_wr, rq_rd, rq_nos SELECT count(*) FROM rq.docs WHERE body @@> 'alpha' | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw SELECT count(*) FROM rq.docs WHERE body @@> 'five' AND g = 1 | ok: (20) | rq_own, rq_mem, rq_wr, rq_rd, rq_nos SELECT count(*) FROM rq.docs WHERE body @@> 'five' AND g = 1 | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ok: (5,1.457) ; (10,1.457) ; (15,1.457) | rq_own, rq_mem, rq_wr, rq_rd, rq_nos SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw SELECT count(*) FROM rq.docs_v WHERE body @@> 'alpha' | ok: (300) | rq_own, rq_mem, rq_vw SELECT count(*) FROM rq.docs_v WHERE body @@> 'alpha' | ERROR: permission denied for view docs_v | rq_wr, rq_rd, rq_nos, rq_col, rq_non SELECT rq.count_docs('five') | ok: (60) | rq_own, rq_mem, rq_wr, rq_rd, rq_nos, rq_col, rq_non, rq_vw EXPLAIN SELECT id FROM rq.docs WHERE body @@> 'five' | ok: plan Custom Scan (SiftScan) | rq_own, rq_mem, rq_wr, rq_rd, rq_nos EXPLAIN SELECT id FROM rq.docs WHERE body @@> 'five' | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw (11 rows) SELECT * FROM rr_matrix(ARRAY['rq_own', 'rq_byp', 'rq_rd', 'rq_rls'], $$ SELECT count(*) FROM rq.rdocs WHERE body @@> 'alpha' SELECT count(*) FROM rq.rdocs WHERE body @@> 'five' SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.rdocs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 EXPLAIN SELECT id FROM rq.rdocs WHERE body @@> 'five' $$); stmt | result | by_role ---------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------- SELECT count(*) FROM rq.rdocs WHERE body @@> 'alpha' | ok: (300) | rq_own, rq_byp, rq_rd SELECT count(*) FROM rq.rdocs WHERE body @@> 'alpha' | ok: (100) | rq_rls SELECT count(*) FROM rq.rdocs WHERE body @@> 'five' | ok: (60) | rq_own, rq_byp, rq_rd SELECT count(*) FROM rq.rdocs WHERE body @@> 'five' | ok: (20) | rq_rls SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.rdocs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ok: (5,1.457) ; (10,1.457) ; (15,1.457) | rq_own, rq_byp, rq_rd SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.rdocs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ERROR: sift.score() is not available on "rdocs" while row-level security or a security barrier view applies | rq_rls EXPLAIN SELECT id FROM rq.rdocs WHERE body @@> 'five' | ok: plan Custom Scan (SiftScan) | rq_own, rq_byp, rq_rd EXPLAIN SELECT id FROM rq.rdocs WHERE body @@> 'five' | ok: plan Bitmap Index Scan on rdocs_col | rq_rls (8 rows) -- with USAGE on the schema sift taken back from PUBLIC: the operator needs none, the ranking function does REVOKE USAGE ON SCHEMA sift FROM PUBLIC; GRANT USAGE ON SCHEMA sift TO rq_rd; SELECT * FROM rr_matrix(ARRAY['rq_rd', 'rq_nos'], $$ SELECT count(*) FROM rq.docs WHERE body @@> 'alpha' SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 $$); stmt | result | by_role --------------------------------------------------------------------------------------------------------------------+------------------------------------------+--------------- SELECT count(*) FROM rq.docs WHERE body @@> 'alpha' | ok: (300) | rq_rd, rq_nos SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ok: (5,1.457) ; (10,1.457) ; (15,1.457) | rq_rd SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1 LIMIT 3 | ERROR: permission denied for schema sift | rq_nos (3 rows) REVOKE USAGE ON SCHEMA sift FROM rq_rd; GRANT USAGE ON SCHEMA sift TO PUBLIC; -- the aggregate pushdown on the sift_col index and on the sift_table: PostgreSQL checks the privileges of the role on the columns the query reads -- (as the owner of a view the table is read through), and the scan adds none: the plain plan's answer or refusal for every role SET sift.enable_agg = on; SET seq_page_cost = 10000; SELECT * FROM rr_matrix(ARRAY['rq_own', 'rq_mem', 'rq_wr', 'rq_rd', 'rq_col', 'rq_non', 'rq_vw'], $$ SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g ORDER BY g SELECT g, count(*) FROM rq.docs GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g EXPLAIN SELECT g, count(*) FROM rq.docs GROUP BY g SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g ORDER BY g SELECT g, count(*) FROM rq.facts GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g EXPLAIN SELECT g, count(*) FROM rq.facts GROUP BY g SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g ORDER BY g SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g $$); stmt | result | by_role --------------------------------------------------------------------+---------------------------------------------------+-------------------------------------- SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_mem, rq_wr, rq_rd SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g ORDER BY g | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw SELECT g, count(*) FROM rq.docs GROUP BY g ORDER BY g | ok: (0,100) ; (1,100) ; (2,100) | rq_own, rq_mem, rq_wr, rq_rd, rq_col SELECT g, count(*) FROM rq.docs GROUP BY g ORDER BY g | ERROR: permission denied for table docs | rq_non, rq_vw EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_wr, rq_rd EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g | ERROR: permission denied for table docs | rq_col, rq_non, rq_vw EXPLAIN SELECT g, count(*) FROM rq.docs GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_wr, rq_rd, rq_col EXPLAIN SELECT g, count(*) FROM rq.docs GROUP BY g | ERROR: permission denied for table docs | rq_non, rq_vw SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_mem, rq_wr, rq_rd SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g ORDER BY g | ERROR: permission denied for table facts | rq_col, rq_non, rq_vw SELECT g, count(*) FROM rq.facts GROUP BY g ORDER BY g | ok: (0,100) ; (1,100) ; (2,100) | rq_own, rq_mem, rq_wr, rq_rd, rq_col SELECT g, count(*) FROM rq.facts GROUP BY g ORDER BY g | ERROR: permission denied for table facts | rq_non, rq_vw EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_wr, rq_rd EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g | ERROR: permission denied for table facts | rq_col, rq_non, rq_vw EXPLAIN SELECT g, count(*) FROM rq.facts GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_wr, rq_rd, rq_col EXPLAIN SELECT g, count(*) FROM rq.facts GROUP BY g | ERROR: permission denied for table facts | rq_non, rq_vw SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_mem, rq_vw SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g ORDER BY g | ERROR: permission denied for view docs_v | rq_wr, rq_rd, rq_col, rq_non SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_mem, rq_vw SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g ORDER BY g | ERROR: permission denied for view facts_v | rq_wr, rq_rd, rq_col, rq_non EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_vw EXPLAIN SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g | ERROR: permission denied for view docs_v | rq_wr, rq_rd, rq_col, rq_non EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_mem, rq_vw EXPLAIN SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g | ERROR: permission denied for view facts_v | rq_wr, rq_rd, rq_col, rq_non (24 rows) SELECT * FROM rr_matrix(ARRAY['rq_own', 'rq_byp', 'rq_rd', 'rq_rls'], $$ SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g $$); stmt | result | by_role -------------------------------------------------------------------+---------------------------------------------------+----------------------- SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_byp, rq_rd SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g ORDER BY g | ok: (1,100,14950) | rq_rls EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_byp EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g | ok: plan Seq Scan on rdocs | rq_rd, rq_rls SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_byp, rq_rd SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g ORDER BY g | ok: (1,100,14950) | rq_rls EXPLAIN SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own, rq_byp EXPLAIN SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g | ok: plan Seq Scan on rfacts | rq_rd, rq_rls (8 rows) -- a table with row-level security read through a view of its owner, to whom no policy applies: the rows of the view, whoever reads it SELECT * FROM rr_matrix(ARRAY['rq_own', 'rq_vw', 'rq_rls'], $$ SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g ORDER BY g EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g $$); stmt | result | by_role ------------------------------------------------------------------+---------------------------------------------------+----------------------- SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g ORDER BY g | ok: (0,100,15150) ; (1,100,14950) ; (2,100,15050) | rq_own, rq_vw, rq_rls EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g | ok: plan Custom Scan (SiftAgg) | rq_own EXPLAIN SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g | ok: plan Seq Scan on rdocs | rq_vw, rq_rls (3 rows) -- the pushed-down plan and the plain plan agree for every role, row-level security and BYPASSRLS included SELECT * FROM rr_pushdown_diff(ARRAY['rq_own', 'rq_mem', 'rq_wr', 'rq_rd', 'rq_col', 'rq_non', 'rq_nos', 'rq_vw', 'rq_byp', 'rq_rls'], $$ SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g ORDER BY g SELECT g, count(*) FROM rq.docs GROUP BY g ORDER BY g SELECT count(*) FROM rq.docs SELECT sum(id) FROM rq.docs WHERE g = 1 SELECT g, count(*), sum(val) FROM rq.facts GROUP BY g ORDER BY g SELECT g, count(*) FROM rq.facts GROUP BY g ORDER BY g SELECT g, count(*), sum(v) FROM rq.docs_v GROUP BY g ORDER BY g SELECT g, count(*), sum(val) FROM rq.facts_v GROUP BY g ORDER BY g SELECT g, count(*), sum(v) FROM rq.rdocs GROUP BY g ORDER BY g SELECT g, count(*), sum(val) FROM rq.rfacts GROUP BY g ORDER BY g SELECT g, count(*), sum(v) FROM rq.rdocs_v GROUP BY g ORDER BY g $$); role | stmt | plain | pushed ------+------+-------+-------- (0 rows) RESET sift.enable_agg; RESET seq_page_cost; -- DML: the reader and the column-only role are refused, the writer changes rows and the indexes follow; no TRUNCATE without the privilege SELECT * FROM rr_run($$ rq_rd | INSERT INTO rq.docs VALUES (1000, 'zeta', 0, 0) rq_rd | UPDATE rq.docs SET v = 0 rq_rd | DELETE FROM rq.docs rq_rd | TRUNCATE rq.docs rq_rd | MERGE INTO rq.docs d USING (VALUES (1)) s(id) ON d.id = s.id WHEN MATCHED THEN DELETE rq_rd | INSERT INTO rq.facts VALUES (1000, 0, 0, '{}') rq_col | UPDATE rq.docs SET g = 5 rq_col | DELETE FROM rq.docs rq_non | INSERT INTO rq.docs VALUES (1000, 'zeta', 0, 0) rq_wr | INSERT INTO rq.docs VALUES (1000, 'zeta gamma', 0, 1000) rq_wr | INSERT INTO rq.docs SELECT i + 1000, 'zeta ' || i, i % 3, i FROM generate_series(1, 20) i rq_wr | UPDATE rq.docs SET body = 'zeta moved' WHERE id = 1001 rq_wr | UPDATE rq.docs SET v = v + 1 WHERE g = 1 AND id > 1000 rq_wr | DELETE FROM rq.docs WHERE id > 1010 rq_wr | INSERT INTO rq.docs VALUES (1000, 'zeta again', 0, 0) ON CONFLICT (id) DO UPDATE SET body = excluded.body rq_wr | MERGE INTO rq.docs d USING (VALUES (1, 'merged'), (5000, 'fresh')) s(id, b) ON d.id = s.id WHEN MATCHED THEN UPDATE SET body = s.b WHEN NOT MATCHED THEN INSERT (id, body, g, v) VALUES (s.id, s.b, 0, 0) rq_wr | DELETE FROM rq.docs WHERE id = 5000 RETURNING id rq_wr | SELECT count(*) FILTER (WHERE body @@> 'zeta'), count(*) FILTER (WHERE body @@> 'merged'), count(*) FILTER (WHERE body @@> 'moved') FROM rq.docs rq_wr | TRUNCATE rq.docs rq_wr | INSERT INTO rq.facts SELECT i + 1000, i % 3, i, '{}' FROM generate_series(1, 20) i rq_wr | UPDATE rq.facts SET val = val + 1 WHERE id > 1000 AND g = 1 rq_wr | MERGE INTO rq.facts f USING (VALUES (1, 7), (6000, 8)) s(id, n) ON f.id = s.id WHEN MATCHED THEN UPDATE SET val = s.n WHEN NOT MATCHED THEN INSERT (id, g, val, doc) VALUES (s.id, 0, s.n, '{}') rq_wr | DELETE FROM rq.facts WHERE id > 1010 rq_wr | SELECT count(*), sum(val) FROM rq.facts rq_wr | TRUNCATE rq.facts rq_own | GRANT TRUNCATE ON rq.facts TO rq_wr rq_wr | TRUNCATE rq.facts rq_wr | SELECT count(*) FROM rq.facts $$); n | role | stmt | result ----+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------ 1 | rq_rd | INSERT INTO rq.docs VALUES (1000, 'zeta', 0, 0) | ERROR: permission denied for table docs 2 | rq_rd | UPDATE rq.docs SET v = 0 | ERROR: permission denied for table docs 3 | rq_rd | DELETE FROM rq.docs | ERROR: permission denied for table docs 4 | rq_rd | TRUNCATE rq.docs | ERROR: permission denied for table docs 5 | rq_rd | MERGE INTO rq.docs d USING (VALUES (1)) s(id) ON d.id = s.id WHEN MATCHED THEN DELETE | ERROR: permission denied for table docs 6 | rq_rd | INSERT INTO rq.facts VALUES (1000, 0, 0, '{}') | ERROR: permission denied for table facts 7 | rq_col | UPDATE rq.docs SET g = 5 | ERROR: permission denied for table docs 8 | rq_col | DELETE FROM rq.docs | ERROR: permission denied for table docs 9 | rq_non | INSERT INTO rq.docs VALUES (1000, 'zeta', 0, 0) | ERROR: permission denied for table docs 10 | rq_wr | INSERT INTO rq.docs VALUES (1000, 'zeta gamma', 0, 1000) | ok (1) 11 | rq_wr | INSERT INTO rq.docs SELECT i + 1000, 'zeta ' || i, i % 3, i FROM generate_series(1, 20) i | ok (20) 12 | rq_wr | UPDATE rq.docs SET body = 'zeta moved' WHERE id = 1001 | ok (1) 13 | rq_wr | UPDATE rq.docs SET v = v + 1 WHERE g = 1 AND id > 1000 | ok (7) 14 | rq_wr | DELETE FROM rq.docs WHERE id > 1010 | ok (10) 15 | rq_wr | INSERT INTO rq.docs VALUES (1000, 'zeta again', 0, 0) ON CONFLICT (id) DO UPDATE SET body = excluded.body | ok (1) 16 | rq_wr | MERGE INTO rq.docs d USING (VALUES (1, 'merged'), (5000, 'fresh')) s(id, b) ON d.id = s.id WHEN MATCHED THEN UPDATE SET body = s.b WHEN NOT MATCHED THEN INSERT (id, body, g, v) VALUES (s.id, s.b, 0, 0) | ok (2) 17 | rq_wr | DELETE FROM rq.docs WHERE id = 5000 RETURNING id | ok (1) 18 | rq_wr | SELECT count(*) FILTER (WHERE body @@> 'zeta'), count(*) FILTER (WHERE body @@> 'merged'), count(*) FILTER (WHERE body @@> 'moved') FROM rq.docs | ok: (11,1,1) 19 | rq_wr | TRUNCATE rq.docs | ERROR: permission denied for table docs 20 | rq_wr | INSERT INTO rq.facts SELECT i + 1000, i % 3, i, '{}' FROM generate_series(1, 20) i | ok (20) 21 | rq_wr | UPDATE rq.facts SET val = val + 1 WHERE id > 1000 AND g = 1 | ok (7) 22 | rq_wr | MERGE INTO rq.facts f USING (VALUES (1, 7), (6000, 8)) s(id, n) ON f.id = s.id WHEN MATCHED THEN UPDATE SET val = s.n WHEN NOT MATCHED THEN INSERT (id, g, val, doc) VALUES (s.id, 0, s.n, '{}') | ok (2) 23 | rq_wr | DELETE FROM rq.facts WHERE id > 1010 | ok (11) 24 | rq_wr | SELECT count(*), sum(val) FROM rq.facts | ok: (310,45215) 25 | rq_wr | TRUNCATE rq.facts | ERROR: permission denied for table facts 26 | rq_own | GRANT TRUNCATE ON rq.facts TO rq_wr | ok 27 | rq_wr | TRUNCATE rq.facts | ok 28 | rq_wr | SELECT count(*) FROM rq.facts | ok: (0) (28 rows) INSERT INTO rq.facts SELECT i, i % 3, i, jsonb_build_object('a', i % 7) FROM generate_series(1, 300) i; -- COPY in and out SET SESSION AUTHORIZATION rq_wr; COPY rq.docs (id, body, g, v) FROM STDIN; COPY rq.facts (id, g, val) FROM STDIN; COPY (SELECT id, body FROM rq.docs WHERE body @@> 'copy' ORDER BY id) TO STDOUT; 2001 copy row one 2002 copy row two COPY (SELECT g, count(*) FROM rq.facts GROUP BY g ORDER BY g) TO STDOUT; 0 101 1 101 2 100 RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION rq_rd; COPY (SELECT id, body FROM rq.docs WHERE body @@> 'copy' ORDER BY id) TO STDOUT; 2001 copy row one 2002 copy row two COPY (SELECT id, body FROM rq.docs WHERE id BETWEEN 2 AND 4 ORDER BY id) TO STDOUT; 2 alpha beta 2 3 alpha beta 3 4 alpha beta 4 \copy rq.docs (id, body) from '/dev/null' ERROR: permission denied for table docs \copy rq.facts (id) from '/dev/null' ERROR: permission denied for table facts RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION rq_non; COPY (SELECT id FROM rq.docs WHERE body @@> 'copy') TO STDOUT; ERROR: permission denied for table docs COPY rq.facts (id) TO STDOUT; ERROR: permission denied for table facts RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION rq_col; COPY (SELECT id, g FROM rq.docs WHERE id <= 3 ORDER BY id) TO STDOUT (FORMAT csv, HEADER); id,g 1,1 2,2 3,0 COPY (SELECT id, body FROM rq.docs WHERE id <= 3) TO STDOUT; ERROR: permission denied for table docs RESET SESSION AUTHORIZATION; -- prepared statements are checked and planned for the role that executes them: a plan made for the owner, who sees every row, is not reused -- for the role under a policy (one execution per plan type: custom for the first five, then generic) SELECT * FROM rr_run($$ rq_own | PREPARE q_count(text) AS SELECT count(*) FROM rq.docs WHERE body @@> $1 rq_own | PREPARE q_rank(text) AS SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> $1 ORDER BY 2 DESC, 1 LIMIT 2 rq_own | PREPARE q_row(text) AS SELECT count(*) FROM rq.rdocs WHERE body @@> $1 rq_own | EXECUTE q_count('five') rq_rd | EXECUTE q_count('five') rq_rd | EXECUTE q_rank('alpha five') rq_non | EXECUTE q_count('five') rq_col | EXECUTE q_rank('five') rq_nos | EXECUTE q_rank('alpha five') rq_own | EXECUTE q_row('five') rq_rls | EXECUTE q_row('five') rq_own | EXECUTE q_row('five') rq_rls | EXECUTE q_row('five') rq_byp | EXECUTE q_row('five') $$); n | role | stmt | result ----+--------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------------- 1 | rq_own | PREPARE q_count(text) AS SELECT count(*) FROM rq.docs WHERE body @@> $1 | ok 2 | rq_own | PREPARE q_rank(text) AS SELECT id, round(sift.score(ctid)::numeric, 3) FROM rq.docs WHERE body @@> $1 ORDER BY 2 DESC, 1 LIMIT 2 | ok 3 | rq_own | PREPARE q_row(text) AS SELECT count(*) FROM rq.rdocs WHERE body @@> $1 | ok 4 | rq_own | EXECUTE q_count('five') | ok: (60) 5 | rq_rd | EXECUTE q_count('five') | ok: (60) 6 | rq_rd | EXECUTE q_rank('alpha five') | ok: (5,1.621) ; (10,1.621) 7 | rq_non | EXECUTE q_count('five') | ERROR: permission denied for table docs 8 | rq_col | EXECUTE q_rank('five') | ERROR: permission denied for table docs 9 | rq_nos | EXECUTE q_rank('alpha five') | ok: (5,1.621) ; (10,1.621) 10 | rq_own | EXECUTE q_row('five') | ok: (60) 11 | rq_rls | EXECUTE q_row('five') | ok: (20) 12 | rq_own | EXECUTE q_row('five') | ok: (60) 13 | rq_rls | EXECUTE q_row('five') | ok: (20) 14 | rq_byp | EXECUTE q_row('five') | ok: (60) (14 rows) SET plan_cache_mode = force_generic_plan; SELECT * FROM rr_run($$ rq_own | EXECUTE q_row('five') rq_rls | EXECUTE q_row('five') rq_own | EXECUTE q_row('five') rq_rd | EXECUTE q_count('five') rq_non | EXECUTE q_count('five') rq_rd | EXECUTE q_rank('alpha five') $$); n | role | stmt | result ---+--------+------------------------------+----------------------------------------- 1 | rq_own | EXECUTE q_row('five') | ok: (60) 2 | rq_rls | EXECUTE q_row('five') | ok: (20) 3 | rq_own | EXECUTE q_row('five') | ok: (60) 4 | rq_rd | EXECUTE q_count('five') | ok: (60) 5 | rq_non | EXECUTE q_count('five') | ERROR: permission denied for table docs 6 | rq_rd | EXECUTE q_rank('alpha five') | ok: (5,1.621) ; (10,1.621) (6 rows) RESET plan_cache_mode; DEALLOCATE ALL; -- cursors: scrollable and held, opened by a role and read in the same transaction; a role without the privilege cannot open one BEGIN; SET LOCAL SESSION AUTHORIZATION rq_rd; DECLARE c SCROLL CURSOR FOR SELECT id, round(sift.score(ctid)::numeric, 3) AS s FROM rq.docs WHERE body @@> 'alpha five' ORDER BY 2 DESC, 1; FETCH 2 FROM c; id | s ----+------- 5 | 1.621 10 | 1.621 (2 rows) FETCH BACKWARD 1 FROM c; id | s ----+------- 5 | 1.621 (1 row) MOVE LAST FROM c; FETCH PRIOR FROM c; id | s -----+------- 295 | 1.621 (1 row) CLOSE c; DECLARE h CURSOR WITH HOLD FOR SELECT g, count(*), sum(v) FROM rq.docs GROUP BY g ORDER BY g; COMMIT; FETCH ALL FROM h; g | count | sum ---+-------+------- 0 | 105 | 16169 1 | 105 | 14978 2 | 103 | 15065 (3 rows) CLOSE h; BEGIN; SET LOCAL SESSION AUTHORIZATION rq_non; DECLARE c3 CURSOR FOR SELECT count(*) FROM rq.docs WHERE body @@> 'alpha'; ERROR: permission denied for table docs ROLLBACK; BEGIN; SET LOCAL SESSION AUTHORIZATION rq_rls; DECLARE c4 CURSOR FOR SELECT id, sift.score(ctid) FROM rq.rdocs WHERE body @@> 'alpha five' ORDER BY 2 DESC; ERROR: sift.score() is not available on "rdocs" while row-level security or a security barrier view applies ROLLBACK; BEGIN; SET LOCAL SESSION AUTHORIZATION rq_rls; DECLARE c5 CURSOR FOR SELECT count(*) FROM rq.rdocs WHERE body @@> 'alpha five'; FETCH c5; count ------- 20 (1 row) ROLLBACK; -- EXPLAIN and EXPLAIN ANALYZE: a plan is made and an analyzed statement is run with the privileges of the role, and one that is refused changes nothing SET sift.enable_agg = on; SET seq_page_cost = 10000; SELECT * FROM rr_run($$ rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT id FROM rq.docs WHERE body @@> 'five' rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT g, count(*) FROM rq.facts GROUP BY g rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) UPDATE rq.facts SET val = 0 rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM rq.docs rq_non | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT id FROM rq.docs WHERE body @@> 'five' rq_wr | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) UPDATE rq.facts SET val = val WHERE id = 1 rq_wr | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM rq.docs WHERE id = 2002 rq_own | SELECT count(*), sum(val) FROM rq.facts rq_own | SELECT count(*) FROM rq.docs $$); n | role | stmt | result ---+--------+----------------------------------------------------------------------------------------------------+------------------------------------------ 1 | rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT id FROM rq.docs WHERE body @@> 'five' | ok: plan Bitmap Index Scan on docs_idx 2 | rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT g, count(*) FROM rq.facts GROUP BY g | ok: plan Custom Scan (SiftAgg) 3 | rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) UPDATE rq.facts SET val = 0 | ERROR: permission denied for table facts 4 | rq_rd | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM rq.docs | ERROR: permission denied for table docs 5 | rq_non | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT id FROM rq.docs WHERE body @@> 'five' | ERROR: permission denied for table docs 6 | rq_wr | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) UPDATE rq.facts SET val = val WHERE id = 1 | ok: plan Seq Scan on facts 7 | rq_wr | EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) DELETE FROM rq.docs WHERE id = 2002 | ok: plan Index Scan using docs_pkey 8 | rq_own | SELECT count(*), sum(val) FROM rq.facts | ok: (302,45153) 9 | rq_own | SELECT count(*) FROM rq.docs | ok: (312) (9 rows) RESET sift.enable_agg; RESET seq_page_cost; DROP SCHEMA rq CASCADE; DROP FUNCTION rr_run(text), rr_matrix(text[], text), rr_pushdown_diff(text[], text), rr_script(text[], text), rr_roles_drop(text); DROP OWNED BY rq_own, rq_mem, rq_wr, rq_rd, rq_col, rq_non, rq_nos, rq_rls, rq_byp, rq_vw; DROP ROLE rq_own, rq_mem, rq_wr, rq_rd, rq_col, rq_non, rq_nos, rq_rls, rq_byp, rq_vw; DROP EXTENSION pg_sift;