-- 15_restore_privilege (gemini audit P1, CRITICAL + follow-up): a subscription's
-- owner_role must be the role that REGISTERED it (captured in the SECURITY
-- INVOKER pgr.subscribe), and pgr.restore_subscriptions() must replay it as that
-- role — never as the extension-owning superuser it runs as. Otherwise a
-- subscription registered with an attacker-controlled search_path + query
-- escalates to the superuser on the next restart.
--
-- This test drives the REAL path: a low-privilege role actually calls
-- pgr.subscribe (an earlier version of this test hand-inserted owner_role and so
-- masked that _persist_subscription, being SECURITY DEFINER, recorded
-- current_user = the superuser definer for every subscription).
CREATE EXTENSION pg_reactive;
-- A low-privilege role granted exactly what a role needs to register a delta
-- subscription directly (the misconfiguration the docs warn about, and the only
-- way an attacker gets a subscription persisted at all).
CREATE ROLE pgr_rp_owner;
GRANT USAGE, CREATE ON SCHEMA pgr TO pgr_rp_owner;
GRANT USAGE ON SEQUENCE pgr.subscription_generation_seq TO pgr_rp_owner;
GRANT EXECUTE ON FUNCTION pgr.subscribe(text, text, text, jsonb) TO pgr_rp_owner;
GRANT EXECUTE ON FUNCTION pgr._subscribe_internal(text, text, text, jsonb, bigint) TO pgr_rp_owner;
GRANT EXECUTE ON FUNCTION pgr._persist_subscription(text, text, text, jsonb, text, bigint, text) TO pgr_rp_owner;
GRANT EXECUTE ON FUNCTION pgr.trigger_func() TO pgr_rp_owner;
CREATE TABLE rp_t (id int primary key);
ALTER TABLE rp_t OWNER TO pgr_rp_owner;
INSERT INTO rp_t VALUES (1), (2);
-- Register as the low-priv role via the real API.
SET ROLE pgr_rp_owner;
SELECT (pgr.subscribe('rp_q', 'SELECT id FROM rp_t', 'delta', NULL))->>'status' AS subscribe_status;
 subscribe_status 
------------------
 subscribed
(1 row)

RESET ROLE;
-- owner_role must be the registrant, NOT the superuser definer of
-- _persist_subscription (the bug 0.1.8 closes).
SELECT owner_role AS persisted_owner FROM pgr.persisted_subscriptions WHERE query_id = 'rp_q';
 persisted_owner 
-----------------
 pgr_rp_owner
(1 row)

-- Wipe the snapshot to mimic a restart (shmem gone, catalog persists), then
-- restore. The rebuilt snapshot's owner reveals which role restore replayed as.
DROP TABLE IF EXISTS pgr."_snap_rp_q";
SELECT pgr.restore_subscriptions();
 restore_subscriptions 
-----------------------
                     1
(1 row)

SELECT tableowner AS restored_snapshot_owner
FROM pg_tables WHERE schemaname = 'pgr' AND tablename = '_snap_rp_q';
 restored_snapshot_owner 
-------------------------
 pgr_rp_owner
(1 row)

-- Cleanup.
SELECT pgr.unsubscribe('rp_q');
 unsubscribe 
-------------
 t
(1 row)

DELETE FROM pgr.persisted_subscriptions WHERE query_id = 'rp_q';
DROP TABLE rp_t;
DROP OWNED BY pgr_rp_owner;
DROP ROLE pgr_rp_owner;
DROP EXTENSION pg_reactive;