-- The settings (sift.*) as ordinary roles: the ones for tuning a session can be set, the ones that change what a query may see or how an index -- is written are for superusers (or for a role that was granted SET on the parameter), and the settings a role keeps with ALTER ROLE follow -- the same rule. CREATE EXTENSION pg_sift; \set VERBOSITY terse \set ECHO none SELECT rr_roles_drop('rs_'); rr_roles_drop --------------- (1 row) CREATE ROLE rs_own; CREATE ROLE rs_user; CREATE ROLE rs_grant; CREATE SCHEMA rs; GRANT USAGE ON SCHEMA rs TO rs_user, rs_grant; GRANT USAGE, CREATE ON SCHEMA rs TO rs_own; SET enable_seqscan = off; -- every setting of the extension, set to its reset value by an ordinary role: the outcome follows the context of the setting CREATE FUNCTION rs_try() RETURNS TABLE(name text, context text, outcome text) LANGUAGE plpgsql AS $$ DECLARE s record; BEGIN FOR s IN SELECT p.name, p.context, p.reset_val FROM pg_settings p WHERE p.name LIKE 'sift.%' ORDER BY p.name LOOP name := s.name; context := s.context; BEGIN SET LOCAL SESSION AUTHORIZATION rs_user; PERFORM set_config(s.name, s.reset_val, true); outcome := 'ok'; EXCEPTION WHEN OTHERS THEN outcome := 'error ' || SQLSTATE; END; RESET SESSION AUTHORIZATION; RETURN NEXT; END LOOP; END $$; SELECT context, outcome FROM rs_try() GROUP BY 1, 2 ORDER BY 1, 2; context | outcome -----------+------------- sighup | error 55P02 superuser | error 42501 user | ok (3 rows) SELECT name, context FROM rs_try() WHERE context NOT IN ('user', 'superuser', 'sighup') ORDER BY 1; name | context ------+--------- (0 rows) -- the settings that matter most, with the error SELECT * FROM rr_run($$ rs_user | SET sift.enable_scan = off rs_user | SET sift.enable_agg = off rs_user | SET sift.enable_count = off rs_user | SET sift.enable_topn = off rs_user | SET sift.agg_lookup_max_rows = 1000 rs_user | SET sift.agg_rollup_cost = 2.5 rs_user | SET sift.table_backpressure = 100 rs_user | SET sift.enable_scan = 'maybe' rs_user | SET sift.rls_scores = on rs_user | SET sift.col_format_minor = 4 rs_user | SET sift.async_maintenance = off rs_user | SET sift.maintenance_cost_delay = 0 rs_user | SET sift.maintenance_naptime = 500 rs_user | SET sift.max_expansions = 10 rs_user | SET sift.log_min_duration = 0 rs_user | SET sift.standby_conflicts = off rs_user | SET sift.col_auto_merge = on rs_user | SET LOCAL sift.rls_scores = on rs_user | SELECT set_config('sift.rls_scores', 'on', false) rs_user | SELECT set_config('sift.enable_scan', 'on', false) rs_user | RESET sift.rls_scores rs_user | RESET sift.enable_scan rs_user | SHOW sift.rls_scores rs_user | SELECT current_setting('sift.col_format_minor') IS NOT NULL rs_user | ALTER ROLE rs_user SET sift.enable_scan = off rs_user | ALTER ROLE rs_user SET sift.rls_scores = on rs_user | ALTER ROLE rs_user RESET ALL rs_user | ALTER ROLE rs_grant SET sift.enable_scan = off rs_user | ALTER DATABASE postgres SET sift.enable_scan = off $$); n | role | stmt | result ----+---------+-------------------------------------------------------------+----------------------------------------------------------------------------------------- 1 | rs_user | SET sift.enable_scan = off | ok 2 | rs_user | SET sift.enable_agg = off | ok 3 | rs_user | SET sift.enable_count = off | ok 4 | rs_user | SET sift.enable_topn = off | ok 5 | rs_user | SET sift.agg_lookup_max_rows = 1000 | ok 6 | rs_user | SET sift.agg_rollup_cost = 2.5 | ok 7 | rs_user | SET sift.table_backpressure = 100 | ERROR: 100 is outside the valid range for parameter "sift.table_backpressure" (0 .. 99) 8 | rs_user | SET sift.enable_scan = 'maybe' | ERROR: parameter "sift.enable_scan" requires a Boolean value 9 | rs_user | SET sift.rls_scores = on | ERROR: permission denied to set parameter "sift.rls_scores" 10 | rs_user | SET sift.col_format_minor = 4 | ERROR: permission denied to set parameter "sift.col_format_minor" 11 | rs_user | SET sift.async_maintenance = off | ERROR: permission denied to set parameter "sift.async_maintenance" 12 | rs_user | SET sift.maintenance_cost_delay = 0 | ERROR: permission denied to set parameter "sift.maintenance_cost_delay" 13 | rs_user | SET sift.maintenance_naptime = 500 | ERROR: parameter "sift.maintenance_naptime" cannot be changed now 14 | rs_user | SET sift.max_expansions = 10 | ERROR: permission denied to set parameter "sift.max_expansions" 15 | rs_user | SET sift.log_min_duration = 0 | ERROR: permission denied to set parameter "sift.log_min_duration" 16 | rs_user | SET sift.standby_conflicts = off | ERROR: permission denied to set parameter "sift.standby_conflicts" 17 | rs_user | SET sift.col_auto_merge = on | ERROR: permission denied to set parameter "sift.col_auto_merge" 18 | rs_user | SET LOCAL sift.rls_scores = on | ERROR: permission denied to set parameter "sift.rls_scores" 19 | rs_user | SELECT set_config('sift.rls_scores', 'on', false) | ERROR: permission denied to set parameter "sift.rls_scores" 20 | rs_user | SELECT set_config('sift.enable_scan', 'on', false) | ok: (on) 21 | rs_user | RESET sift.rls_scores | ERROR: permission denied to set parameter "sift.rls_scores" 22 | rs_user | RESET sift.enable_scan | ok 23 | rs_user | SHOW sift.rls_scores | ok: (off) 24 | rs_user | SELECT current_setting('sift.col_format_minor') IS NOT NULL | ok: (t) 25 | rs_user | ALTER ROLE rs_user SET sift.enable_scan = off | ok 26 | rs_user | ALTER ROLE rs_user SET sift.rls_scores = on | ERROR: permission denied to set parameter "sift.rls_scores" 27 | rs_user | ALTER ROLE rs_user RESET ALL | ok 28 | rs_user | ALTER ROLE rs_grant SET sift.enable_scan = off | ERROR: permission denied to alter role 29 | rs_user | ALTER DATABASE postgres SET sift.enable_scan = off | ERROR: must be owner of database postgres (29 rows) -- GRANT SET ON PARAMETER hands a restricted setting to one role GRANT SET ON PARAMETER sift.rls_scores, sift.col_format_minor TO rs_grant; SELECT * FROM rr_run($$ rs_grant | SET sift.rls_scores = on rs_grant | SET sift.col_format_minor = 4 rs_grant | SET sift.async_maintenance = off rs_grant | RESET sift.rls_scores rs_user | SET sift.rls_scores = on $$); n | role | stmt | result ---+----------+----------------------------------+-------------------------------------------------------------------- 1 | rs_grant | SET sift.rls_scores = on | ok 2 | rs_grant | SET sift.col_format_minor = 4 | ok 3 | rs_grant | SET sift.async_maintenance = off | ERROR: permission denied to set parameter "sift.async_maintenance" 4 | rs_grant | RESET sift.rls_scores | ok 5 | rs_user | SET sift.rls_scores = on | ERROR: permission denied to set parameter "sift.rls_scores" (5 rows) -- a setting changes the plan of the session that sets it SELECT * FROM rr_run($$ rs_own | CREATE TABLE rs.docs (id int, body text, g int) rs_own | INSERT INTO rs.docs SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(1, 300) i rs_own | CREATE INDEX docs_idx ON rs.docs USING sift (body) rs_own | GRANT SELECT ON rs.docs TO rs_user, rs_grant rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' rs_user | SET sift.enable_scan = off rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' rs_user | SELECT count(*) FROM rs.docs WHERE body @@> 'alpha' rs_user | RESET sift.enable_scan rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' $$); n | role | stmt | result ----+---------+----------------------------------------------------------------------------------------+---------------------------------------- 1 | rs_own | CREATE TABLE rs.docs (id int, body text, g int) | ok 2 | rs_own | INSERT INTO rs.docs SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(1, 300) i | ok (300) 3 | rs_own | CREATE INDEX docs_idx ON rs.docs USING sift (body) | ok 4 | rs_own | GRANT SELECT ON rs.docs TO rs_user, rs_grant | ok 5 | rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' | ok: plan Custom Scan (SiftScan) 6 | rs_user | SET sift.enable_scan = off | ok 7 | rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' | ok: plan Bitmap Index Scan on docs_idx 8 | rs_user | SELECT count(*) FROM rs.docs WHERE body @@> 'alpha' | ok: (300) 9 | rs_user | RESET sift.enable_scan | ok 10 | rs_user | EXPLAIN SELECT id FROM rs.docs WHERE body @@> 'alpha' | ok: plan Custom Scan (SiftScan) (10 rows) DROP SCHEMA rs CASCADE; DROP FUNCTION rs_try(); 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 rs_own, rs_user, rs_grant; DROP ROLE rs_own, rs_user, rs_grant; SELECT count(*) AS parameter_grants_left FROM pg_parameter_acl WHERE parname LIKE 'sift.%'; parameter_grants_left ----------------------- 0 (1 row) DROP EXTENSION pg_sift;