-- The contract every reader of a lion index keeps (DESIGN.md ยง9, "Direct -- readers"), run against each one, so a new reader gets a line here: -- - a function handed an index by name refuses a table where row-level -- security applies to the caller, and an unpopulated materialized view -- (lion_reader_vet()); one that reads only statistics refuses row-level -- security too, since they count the hidden rows; -- - it refuses an index that is not valid, and checks EXECUTE on the -- functions its answer stands for (the key type's equality, a -- filter's operators); -- - the planner's paths answer under a policy exactly what seq scan does. -- Snapshot eligibility and predicate locks need two sessions: -- test/isolation/reader_checkxmin.spec and reader_serializable.spec. \set VERBOSITY terse SET client_min_messages = warning; CREATE EXTENSION IF NOT EXISTS pg_lion; CREATE TABLE rcon (id int, k int NOT NULL, j int NOT NULL, d tsvector) WITH (autovacuum_enabled = off); INSERT INTO rcon SELECT g, g % 10, g % 7, to_tsvector('simple', 'w' || (g % 10) || CASE WHEN g % 3 = 0 THEN ' secret' ELSE '' END) FROM generate_series(1, 3000) g; CREATE INDEX rcon_k ON rcon USING lion (k); CREATE INDEX rcon_j ON rcon USING lion (j) WITH (slices = on); CREATE INDEX rcon_d ON rcon USING lion (d) WITH (store_positions = true); VACUUM ANALYZE rcon; -- every direct reader, each as one statement; a new one goes here CREATE TABLE rcon_readers (name text, stmt text); INSERT INTO rcon_readers VALUES ('lion_index_count', $$SELECT lion_index_count('%1$s_k', 3)$$), ('lion_index_count, two keys', $$SELECT lion_index_count('%1$s_k', 3, '%1$s_j', 3)$$), ('lion_index_count_any', $$SELECT lion_index_count_any('%1$s_k', ARRAY[3, 6])$$), ('lion_index_count_stats', $$SELECT count FROM lion_index_count_stats('%1$s_k', 3)$$), ('lion_index_count_group_stats', $$SELECT count(*) FROM lion_index_count_group_stats('%1$s_k')$$), ('lion_bm25', $$SELECT count(*) FROM lion_bm25('%1$s_d', 'secret', 10)$$), ('lion_bm25, a named column', $$SELECT count(*) FROM lion_bm25('%1$s_d', 'secret', 10, col => 'd')$$), ('lion_bm25_score', $$SELECT lion_bm25_score('secret'::tsvector, 'secret', '%1$s_d') > 0$$), ('lion_bm25_score, a named column', $$SELECT lion_bm25_score('secret'::tsvector, 'secret', '%1$s_d', col => 'd') > 0$$), ('lion_index_facets', $$SELECT string_agg(key || ':' || count, ' ') FROM lion_index_facets('%1$s_d', 3)$$), ('lion_index_facets, filtered', $$SELECT string_agg(key || ':' || count, ' ') FROM lion_index_facets('%1$s_j', 3, 'k = 3 AND d @@ $q$secret$q$')$$), ('lion_index_slice_agg', $$SELECT count || ' ' || sum || ' ' || min || ' ' || max FROM lion_index_slice_agg('%1$s_j', 'k = 3')$$); -- what each reader says for the relation rel: its answer, or its error CREATE FUNCTION rcon_try(stmt text, rel text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE r text; BEGIN EXECUTE format(stmt, rel) INTO r; RETURN r; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END $$; SELECT name, rcon_try(stmt, 'rcon') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+--------------------------- lion_bm25 | 10 lion_bm25, a named column | 10 lion_bm25_score | true lion_bm25_score, a named column | true lion_index_count | 300 lion_index_count, two keys | 43 lion_index_count_any | 600 lion_index_count_group_stats | 1 lion_index_count_stats | 300 lion_index_facets | secret:1000 w0:300 w1:300 lion_index_facets, filtered | 3:15 5:15 0:14 lion_index_slice_agg | 300 903 0 6 (12 rows) -- sql's rows with lion's paths allowed and with all of them off CREATE FUNCTION rcon_same(sql text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE a text; b text; BEGIN EXECUTE 'SELECT string_agg(x::text, '','') FROM (' || sql || ') x' INTO a; SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL pg_lion.enable_count_pushdown = off; SET LOCAL pg_lion.enable_bm25_scan = off; SET LOCAL pg_lion.enable_ordered_scan = off; EXECUTE 'SELECT string_agg(x::text, '','') FROM (' || sql || ') x' INTO b; RETURN CASE WHEN a IS NOT DISTINCT FROM b THEN 'same' ELSE a || ' <> ' || b END; END $$; -- row-level security applying to the caller: refused CREATE ROLE rcon_reader; GRANT SELECT ON rcon, rcon_readers TO rcon_reader; ALTER TABLE rcon ENABLE ROW LEVEL SECURITY; CREATE POLICY rcon_hide ON rcon FOR SELECT USING (NOT d @@ 'secret'); SET ROLE rcon_reader; SELECT name, rcon_try(stmt, 'rcon') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+------------------------------------------------------------------------------------------- lion_bm25 | cannot rank through index "rcon_d" because row-level security is enabled on table "rcon" lion_bm25, a named column | cannot rank through index "rcon_d" because row-level security is enabled on table "rcon" lion_bm25_score | cannot score through index "rcon_d" because row-level security is enabled on table "rcon" lion_bm25_score, a named column | cannot score through index "rcon_d" because row-level security is enabled on table "rcon" lion_index_count | cannot count through index "rcon_k" because row-level security is enabled on table "rcon" lion_index_count, two keys | cannot count through index "rcon_k" because row-level security is enabled on table "rcon" lion_index_count_any | cannot count through index "rcon_k" because row-level security is enabled on table "rcon" lion_index_count_group_stats | cannot count through index "rcon_k" because row-level security is enabled on table "rcon" lion_index_count_stats | cannot count through index "rcon_k" because row-level security is enabled on table "rcon" lion_index_facets | cannot count through index "rcon_d" because row-level security is enabled on table "rcon" lion_index_facets, filtered | cannot count through index "rcon_j" because row-level security is enabled on table "rcon" lion_index_slice_agg | cannot count through index "rcon_j" because row-level security is enabled on table "rcon" (12 rows) -- the planner's paths apply the policy: the same rows as seq scan SELECT rcon_same($$SELECT count(*) FROM rcon WHERE k = 3$$); rcon_same ----------- same (1 row) SELECT rcon_same($$SELECT k, count(*) FROM rcon GROUP BY k ORDER BY k$$); rcon_same ----------- same (1 row) SELECT rcon_same($$SELECT count(*) FROM rcon WHERE d @@ 'secret'$$); rcon_same ----------- same (1 row) -- ranking scores by statistics of every row, so it is refused here too SELECT rcon_try($$SELECT string_agg(id::text, ',') FROM (SELECT id FROM %1$s WHERE d @@ 'w3 | secret' ORDER BY lion_bm25_score(d, 'w3 | secret', 'rcon_d') DESC, id LIMIT 20) x$$, 'rcon'); rcon_try ------------------------------------------------------------------------------------------- cannot score through index "rcon_d" because row-level security is enabled on table "rcon" (1 row) RESET ROLE; DROP POLICY rcon_hide ON rcon; ALTER TABLE rcon DISABLE ROW LEVEL SECURITY; -- EXECUTE revoked on the key types' equality: refused by what stands for it REVOKE EXECUTE ON FUNCTION int4eq(int4, int4), texteq(text, text) FROM PUBLIC; SET ROLE rcon_reader; SELECT name, rcon_try(stmt, 'rcon') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+--------------------------------------- lion_bm25 | 10 lion_bm25, a named column | 10 lion_bm25_score | true lion_bm25_score, a named column | true lion_index_count | permission denied for function int4eq lion_index_count, two keys | permission denied for function int4eq lion_index_count_any | permission denied for function int4eq lion_index_count_group_stats | permission denied for function int4eq lion_index_count_stats | permission denied for function int4eq lion_index_facets | permission denied for function texteq lion_index_facets, filtered | permission denied for function int4eq lion_index_slice_agg | permission denied for function int4eq (12 rows) RESET ROLE; GRANT EXECUTE ON FUNCTION int4eq(int4, int4), texteq(text, text) TO PUBLIC; -- ... and on an aggregate the query a reader stands for calls: refused by it -- (lion_index_slice_agg() stands for count, sum, min and max of the column) REVOKE EXECUTE ON FUNCTION sum(int4) FROM PUBLIC; SET ROLE rcon_reader; SELECT name, rcon_try(stmt, 'rcon') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+------------------------------------- lion_bm25 | 10 lion_bm25, a named column | 10 lion_bm25_score | true lion_bm25_score, a named column | true lion_index_count | 300 lion_index_count, two keys | 43 lion_index_count_any | 600 lion_index_count_group_stats | 1 lion_index_count_stats | 300 lion_index_facets | secret:1000 w0:300 w1:300 lion_index_facets, filtered | 3:15 5:15 0:14 lion_index_slice_agg | permission denied for aggregate sum (12 rows) RESET ROLE; GRANT EXECUTE ON FUNCTION sum(int4) TO PUBLIC; -- an index that is not valid, as a failed CREATE INDEX CONCURRENTLY leaves -- one: refused UPDATE pg_index SET indisvalid = false WHERE indexrelid IN ('rcon_k'::regclass, 'rcon_j'::regclass, 'rcon_d'::regclass); SELECT name, rcon_try(stmt, 'rcon') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+-------------------------------------------------------------------- lion_bm25 | cannot rank through index "rcon_d" because the index is not valid lion_bm25, a named column | cannot rank through index "rcon_d" because the index is not valid lion_bm25_score | cannot score through index "rcon_d" because the index is not valid lion_bm25_score, a named column | cannot score through index "rcon_d" because the index is not valid lion_index_count | cannot count through index "rcon_k" because the index is not valid lion_index_count, two keys | cannot count through index "rcon_k" because the index is not valid lion_index_count_any | cannot count through index "rcon_k" because the index is not valid lion_index_count_group_stats | cannot count through index "rcon_k" because the index is not valid lion_index_count_stats | cannot count through index "rcon_k" because the index is not valid lion_index_facets | cannot count through index "rcon_d" because the index is not valid lion_index_facets, filtered | cannot count through index "rcon_j" because the index is not valid lion_index_slice_agg | cannot count through index "rcon_j" because the index is not valid (12 rows) UPDATE pg_index SET indisvalid = true WHERE indexrelid IN ('rcon_k'::regclass, 'rcon_j'::regclass, 'rcon_d'::regclass); -- an unpopulated materialized view: refused, as querying it is CREATE MATERIALIZED VIEW rcon_mv AS SELECT * FROM rcon WITH NO DATA; CREATE INDEX rcon_mv_k ON rcon_mv USING lion (k); CREATE INDEX rcon_mv_j ON rcon_mv USING lion (j) WITH (slices = on); CREATE INDEX rcon_mv_d ON rcon_mv USING lion (d) WITH (store_positions = true); SELECT name, rcon_try(stmt, 'rcon_mv') FROM rcon_readers ORDER BY name; name | rcon_try ---------------------------------+---------------------------------------------------- lion_bm25 | materialized view "rcon_mv" has not been populated lion_bm25, a named column | materialized view "rcon_mv" has not been populated lion_bm25_score | false lion_bm25_score, a named column | false lion_index_count | materialized view "rcon_mv" has not been populated lion_index_count, two keys | materialized view "rcon_mv" has not been populated lion_index_count_any | materialized view "rcon_mv" has not been populated lion_index_count_group_stats | materialized view "rcon_mv" has not been populated lion_index_count_stats | materialized view "rcon_mv" has not been populated lion_index_facets | materialized view "rcon_mv" has not been populated lion_index_facets, filtered | materialized view "rcon_mv" has not been populated lion_index_slice_agg | materialized view "rcon_mv" has not been populated (12 rows) DROP MATERIALIZED VIEW rcon_mv; DROP FUNCTION rcon_same(text); DROP FUNCTION rcon_try(text, text); DROP TABLE rcon, rcon_readers; DROP ROLE rcon_reader;