-- Access control, row-level security and collation rules of the count paths
-- (findings of the 2026-09-20 adversarial review).
SET client_min_messages = warning;
CREATE EXTENSION IF NOT EXISTS pg_lion;
RESET client_min_messages;
SET synchronous_commit = on;
CREATE ROLE lion_sec_reader;
CREATE ROLE lion_sec_other;
CREATE TABLE lion_sec (id int, tenant text NOT NULL, secret text NOT NULL, k int NOT NULL);
INSERT INTO lion_sec SELECT g, 't' || (g % 3), 's' || (g % 7), g % 5 FROM generate_series(1, 3000) g;
CREATE INDEX lion_sec_secret ON lion_sec USING lion (secret);
CREATE INDEX lion_sec_k ON lion_sec USING lion (k);
CREATE INDEX lion_sec_tenant ON lion_sec USING lion (tenant);
VACUUM ANALYZE lion_sec;
-- ---------- privileges: the SQL count functions need SELECT like the query would ----------
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_secret', 's1'::text);           -- no privilege at all
SELECT lion_index_count('lion_sec_tenant', 't1'::text, 'lion_sec_secret', 's1'::text);
SELECT * FROM lion_index_count_stats('lion_sec_secret', 's1'::text);
RESET ROLE;
GRANT SELECT (k) ON lion_sec TO lion_sec_reader;              -- column privilege on k only
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_k', 1);                    -- allowed: only k is referenced
SELECT lion_index_count('lion_sec_secret', 's1'::text);           -- still denied: secret is not granted
SELECT lion_index_count('lion_sec_tenant', 't1'::text, 'lion_sec_secret', 's1'::text);   -- denied: tenant and secret are not granted
RESET ROLE;
GRANT SELECT ON lion_sec TO lion_sec_reader;
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_secret', 's1'::text) = (SELECT count(*) FROM lion_sec WHERE secret = 's1') AS ok;
RESET ROLE;
-- ---------- row-level security: the functions refuse, the planner path stays correct ----------
GRANT SELECT ON lion_sec TO lion_sec_other;
ALTER TABLE lion_sec ENABLE ROW LEVEL SECURITY;
CREATE POLICY lion_sec_t1 ON lion_sec FOR SELECT TO lion_sec_other USING (tenant = 't1');
SET ROLE lion_sec_other;
SELECT lion_index_count('lion_sec_k', 1);                    -- refused: RLS applies to this role
SELECT count(*) FROM lion_sec WHERE k = 1;                      -- policy-filtered answer
SELECT count(*) = (SELECT count(*) FROM lion_sec WHERE k = 1 AND tenant = 't1') AS policy_applied
  FROM lion_sec WHERE k = 1;
EXPLAIN (COSTS OFF) SELECT count(*) FROM lion_sec WHERE k = 1;  -- no LionCount under security quals
RESET ROLE;
SELECT lion_index_count('lion_sec_k', 1) = (SELECT count(*) FROM lion_sec WHERE k = 1) AS owner_bypasses_rls;
ALTER TABLE lion_sec DISABLE ROW LEVEL SECURITY;
-- ---------- collation: an index built under another collation is not used by the pushdown ----------
CREATE TABLE lion_coll (id int, name text NOT NULL, k int NOT NULL);
INSERT INTO lion_coll SELECT g, (ARRAY['a','B','c'])[1 + g % 3], g % 4 FROM generate_series(1, 3000) g;
CREATE INDEX lion_coll_name_c ON lion_coll USING lion (name COLLATE "C");
CREATE INDEX lion_coll_k ON lion_coll USING lion (k);
VACUUM (FREEZE, ANALYZE) lion_coll;
-- EXPLAIN in a form every supported release prints the same way: subplans
-- are named "expr_N" as in PostgreSQL 19 ("N" before), 18's "Disabled: true"
-- lines are dropped, actual row counts are integers (18 adds ".00"), and
-- sorting is off while it plans, so that a query the pushdown must refuse gets
-- the same core plan whether disabled paths are counted (18) or priced (16,
-- 17).  LionCount never sorts, so this cannot hide it.
CREATE OR REPLACE FUNCTION lion_explain_norm(q text, opts text DEFAULT 'COSTS OFF')
RETURNS SETOF text
LANGUAGE plpgsql AS $$
DECLARE
	l text;
BEGIN
	PERFORM set_config('enable_sort', 'off', true);
	FOR l IN EXECUTE 'EXPLAIN (' || opts || ') ' || q LOOP
		CONTINUE WHEN l ~ '^\s*Disabled: true$';
		l := regexp_replace(l, '(InitPlan|SubPlan) (\d+)', '\1 expr_\2', 'g');
		RETURN NEXT regexp_replace(l, 'rows=(\d+)\.00 ', 'rows=\1 ', 'g');
	END LOOP;
	PERFORM set_config('enable_sort', 'on', true);
END
$$;
SET enable_seqscan = off;
-- clause collation (default) differs from the index collation ("C"): no pushdown, no index use
SELECT * FROM lion_explain_norm($q$SELECT count(*) FROM lion_coll WHERE name = 'B'$q$) AS p("QUERY PLAN");
SELECT count(*) FROM lion_coll WHERE name = 'B';
-- matching collation: pushed down
EXPLAIN (COSTS OFF) SELECT count(*) FROM lion_coll WHERE name = 'B' COLLATE "C";
SELECT count(*) FROM lion_coll WHERE name = 'B' COLLATE "C";
-- GROUP BY under the column's collation cannot drive from the "C" index
SELECT * FROM lion_explain_norm('SELECT name, count(*) FROM lion_coll GROUP BY name') AS p("QUERY PLAN");
-- a collation-insensitive column is unaffected
EXPLAIN (COSTS OFF) SELECT count(*) FROM lion_coll WHERE k = 2;
RESET enable_seqscan;
DROP TABLE lion_coll;
-- ---------- a PARTIAL index's predicate is read too (2026-09-23 review, finding 1) ----------
-- The count of a partial index is the count of rows that satisfy its
-- predicate, so it needs SELECT on the predicate's columns exactly as the
-- query it stands for does; SELECT(id) alone would reveal `secret` a row at a
-- time.
CREATE TABLE lion_sec_p (id int NOT NULL, secret boolean NOT NULL);
INSERT INTO lion_sec_p SELECT g, g % 2 = 0 FROM generate_series(1, 1000) g;
CREATE INDEX lion_sec_p_id ON lion_sec_p USING lion (id) WHERE secret;
VACUUM ANALYZE lion_sec_p;
GRANT SELECT (id) ON lion_sec_p TO lion_sec_reader;
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_p_id', 2);                 -- denied: the predicate reads secret
SELECT count(*) FROM lion_sec_p WHERE secret AND id = 2;         -- as the query it stands for is
SELECT * FROM lion_index_count_group_stats('lion_sec_p_id');     -- and the grouped form
RESET ROLE;
GRANT SELECT (secret) ON lion_sec_p TO lion_sec_reader;
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_p_id', 2) AS even, lion_index_count('lion_sec_p_id', 3) AS odd;
RESET ROLE;
DROP TABLE lion_sec_p;
-- ---------- an index that reads NO column, and functions in index expressions ----------
-- A query that references no column at all (SELECT count(*) FROM t) needs
-- SELECT on the table or on at least one column, and so does the count of
-- an index on a constant.  An index expression or predicate that calls a
-- function makes the query call it too, which needs EXECUTE on it whatever
-- the table grants say (2026-09-23 review, second round).
CREATE TABLE lion_sec_c (id int NOT NULL, other int NOT NULL);
INSERT INTO lion_sec_c SELECT g, g FROM generate_series(1, 1000) g;
CREATE INDEX lion_sec_c_one ON lion_sec_c USING lion ((1));
CREATE FUNCTION lion_sec_fn(int) RETURNS int IMMUTABLE LANGUAGE sql AS 'SELECT $1 % 7';
REVOKE EXECUTE ON FUNCTION lion_sec_fn(int) FROM PUBLIC;
CREATE INDEX lion_sec_c_fn ON lion_sec_c USING lion (lion_sec_fn(id));
CREATE INDEX lion_sec_c_pred ON lion_sec_c USING lion (other) WHERE lion_sec_fn(id) = 3;
VACUUM ANALYZE lion_sec_c;
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_c_one', 1);                -- denied: no privilege at all
SELECT lion_index_count_any('lion_sec_c_one', ARRAY[1]);
SELECT * FROM lion_index_count_group_stats('lion_sec_c_one');
SELECT count(*) FROM lion_sec_c;                              -- as the plain count is
RESET ROLE;
GRANT SELECT (other) ON lion_sec_c TO lion_sec_reader;      -- any one column will do
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_c_one', 1) AS constant_index;
RESET ROLE;
GRANT SELECT ON lion_sec_c TO lion_sec_reader;              -- the whole table, but no EXECUTE
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_c_fn', 3);                  -- denied: the expression calls lion_sec_fn
-- As the query does, whatever plan answers it.  The value is 4 rather than 3:
-- lion_sec_c_pred is a partial index WHERE lion_sec_fn(id) = 3, and when the
-- relcache entry was built by a role without EXECUTE its predicate is not
-- inlined, so `lion_sec_fn(id) = 3` is implied by it, dropped, and answered
-- by a keyless scan of that index without calling lion_sec_fn - core's rule
-- for partial indexes, the same for a B-tree (DESIGN.md §9).
SELECT count(*) FROM lion_sec_c WHERE lion_sec_fn(id) = 4;
SELECT lion_index_count('lion_sec_c_pred', 3);                -- and through a predicate
RESET ROLE;
GRANT EXECUTE ON FUNCTION lion_sec_fn(int) TO lion_sec_reader;
SET ROLE lion_sec_reader;
SELECT lion_index_count('lion_sec_c_fn', 3) = (SELECT count(*) FROM lion_sec_c WHERE lion_sec_fn(id) = 3) AS expression_ok,
       lion_index_count('lion_sec_c_pred', 3) = (SELECT count(*) FROM lion_sec_c WHERE lion_sec_fn(id) = 3 AND other = 3) AS predicate_ok;
RESET ROLE;
DROP TABLE lion_sec_c;
DROP FUNCTION lion_sec_fn(int);
-- ---------- the diagnostic functions are not for every role (2026-09-23 review, finding 2) ----------
-- lion_index_posting_root() answers "is this key in the index" for any key,
-- lion_index_stats() gives row and key counts, lion_index_verify() reads the
-- whole heap: none of them checks table privileges or RLS, so like
-- pageinspect's and amcheck's functions they are not executable by PUBLIC.
CREATE TABLE lion_sec_d (k int NOT NULL);
INSERT INTO lion_sec_d SELECT 1 FROM generate_series(1, 5000);
CREATE INDEX lion_sec_d_k ON lion_sec_d USING lion (k) WITH (inline_limit = 64);
SELECT lion_index_posting_root('lion_sec_d_k', 1) IS NOT NULL AS superuser_sees_the_root;
SET ROLE lion_sec_reader;
SELECT lion_index_posting_root('lion_sec_d_k', 1);
SELECT lion_index_posting_root('lion_sec_d_k', 2);
SELECT entries, ntids FROM lion_index_stats('lion_sec_d_k');
SELECT lion_index_verify('lion_sec_d_k');
RESET ROLE;
-- pg_stat_scan_tables may read the statistics, as it may pgstattuple's
GRANT pg_stat_scan_tables TO lion_sec_reader;
SET ROLE lion_sec_reader;
SELECT entries, ntids FROM lion_index_stats('lion_sec_d_k');
SELECT lion_index_posting_root('lion_sec_d_k', 1);           -- still not the probe
RESET ROLE;
REVOKE pg_stat_scan_tables FROM lion_sec_reader;
DROP TABLE lion_sec_d;
-- ---------- argument checks of the SQL count functions (2026-09-23 review, third round) ----------
-- Key column 0 is no key column: lion_index_count_group_stats() took it for
-- "no column", which a one-column index satisfies, and then read the
-- operator class of column -1 before anything noticed.  It is refused up
-- front now, as a bad argument (the handler catches only that SQLSTATE).
CREATE FUNCTION lion_sec_attno(idx regclass, attno int2) RETURNS text
LANGUAGE plpgsql AS $$
BEGIN
	PERFORM lion_index_count_group_stats(idx, false, attno);
	RETURN 'accepted';
EXCEPTION WHEN invalid_parameter_value THEN
	RETURN SQLERRM;
END
$$;
SELECT lion_sec_attno('lion_sec_k', 0::int2);
SELECT lion_sec_attno('lion_sec_k', (-1)::int2);
SELECT lion_sec_attno('lion_sec_k', 2::int2);
SELECT lion_sec_attno('lion_sec_k', 1::int2);
DROP FUNCTION lion_sec_attno(regclass, int2);
-- A relation that does not exist (dropped, or never there) is named by its
-- OID, not as "(null)".
SELECT lion_index_count(4294967295::oid::regclass, 1);
SELECT lion_index_count_any(4294967295::oid::regclass, ARRAY[1]);
-- A role with no privilege on the table is refused before any lock is taken
-- on it (test/isolation/count_lock_privilege.spec shows the lock); a role
-- with some column privilege gets as far as the exact check, and the same
-- error.
CREATE ROLE lion_sec_none;
SET ROLE lion_sec_none;
SELECT lion_index_count('lion_sec_k', 1);
SELECT lion_index_count_any('lion_sec_k', ARRAY[1]);
SELECT * FROM lion_index_count_group_stats('lion_sec_k');
RESET ROLE;
GRANT SELECT (id) ON lion_sec TO lion_sec_none;
SET ROLE lion_sec_none;
SELECT lion_index_count('lion_sec_k', 1);
RESET ROLE;
REVOKE SELECT (id) ON lion_sec FROM lion_sec_none;
DROP ROLE lion_sec_none;
-- A key of a multi-key column (DESIGN.md §17) is not a column value: the
-- keyed functions hashed a whole tsvector as if it were one and answered a
-- meaningless count.  They refuse it, as the grouped form always did.
CREATE TABLE lion_sec_mk (d tsvector NOT NULL);
INSERT INTO lion_sec_mk SELECT to_tsvector('simple', 'w' || (g % 5)) FROM generate_series(1, 100) g;
CREATE INDEX lion_sec_mk_d ON lion_sec_mk USING lion (d);
SELECT lion_index_count('lion_sec_mk_d', 'w1'::tsvector);
SELECT lion_index_count('lion_sec_mk_d', 'w1'::tsvector, 'lion_sec_mk_d', 'w2'::tsvector);
SELECT * FROM lion_index_count_stats('lion_sec_mk_d', 'w1'::tsvector);
SELECT lion_index_count_any('lion_sec_mk_d', ARRAY['w1'::tsvector]);
SELECT * FROM lion_index_count_group_stats('lion_sec_mk_d');
SELECT count(*) FROM lion_sec_mk WHERE d @@ 'w1'::tsquery;   -- the question the operator answers
DROP TABLE lion_sec_mk;
DROP TABLE lion_sec;
DROP ROLE lion_sec_reader;
DROP ROLE lion_sec_other;