-- ALTER EXTENSION pg_sift UPDATE by a superuser, with ordinary roles in the database. At version 3.2 (3.2.0 and 3.2.1) every DROP of a role that -- is not a superuser fails ("permission denied for schema sift"); the update gives the event trigger functions the extension owner's rights, and -- the tables, indexes and rows made at 3.2 go on working for the same roles. -- The released SQL of 3.2 is installed here by CREATE EXTENSION ... VERSION '3.2', on the library of this build. -- DROP EXTENSION leaves the schema sift, with the USAGE that 3.3 gave PUBLIC, so a database that had 3.3 is cleared of it first. SET client_min_messages = warning; DROP SCHEMA IF EXISTS sift; RESET client_min_messages; CREATE EXTENSION pg_sift VERSION '3.2'; \set VERBOSITY terse \set ECHO none SELECT rr_roles_drop('ru_'); rr_roles_drop --------------- (1 row) CREATE ROLE ru_own; CREATE ROLE ru_mem IN ROLE ru_own; CREATE ROLE ru_rd; CREATE ROLE ru_new; CREATE SCHEMA ru; GRANT USAGE ON SCHEMA ru TO ru_rd; GRANT USAGE, CREATE ON SCHEMA ru TO ru_own, ru_mem; GRANT USAGE ON SCHEMA sift TO ru_own, ru_mem, ru_rd; -- 3.2 grants the schema to nobody, and ru_new was not given it SET enable_seqscan = off; -- at 3.2: what the roles make works, a drop does not SELECT * FROM rr_run($$ ru_own | CREATE TABLE ru.docs (id int, body text, g int) ru_own | INSERT INTO ru.docs SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(1, 300) i ru_own | CREATE INDEX docs_idx ON ru.docs USING sift (body) ru_own | CREATE INDEX docs_col ON ru.docs USING sift_col (id, g) ru_own | CREATE TABLE ru.facts (id int, g int, doc jsonb) USING sift_table ru_own | INSERT INTO ru.facts SELECT i, i % 3, '{"a": 1}' FROM generate_series(1, 300) i ru_own | CREATE TABLE ru.plain (id int) ru_own | GRANT SELECT ON ru.docs, ru.facts TO ru_rd ru_own | ALTER TABLE ru.plain ADD COLUMN extra int ru_own | !SELECT sift.table_set_paths('ru.facts', 'doc', 'doc->a int4') ru_own | DROP TABLE ru.plain ru_own | DROP INDEX ru.docs_col ru_mem | DROP TABLE ru.plain ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' ru_new | SELECT sift.version() $$); n | role | stmt | result ----+--------+----------------------------------------------------------------------------------------+------------------------------------------------ 1 | ru_own | CREATE TABLE ru.docs (id int, body text, g int) | ok 2 | ru_own | INSERT INTO ru.docs SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(1, 300) i | ok (300) 3 | ru_own | CREATE INDEX docs_idx ON ru.docs USING sift (body) | ok 4 | ru_own | CREATE INDEX docs_col ON ru.docs USING sift_col (id, g) | ok 5 | ru_own | CREATE TABLE ru.facts (id int, g int, doc jsonb) USING sift_table | ok 6 | ru_own | INSERT INTO ru.facts SELECT i, i % 3, '{"a": 1}' FROM generate_series(1, 300) i | ok (300) 7 | ru_own | CREATE TABLE ru.plain (id int) | ok 8 | ru_own | GRANT SELECT ON ru.docs, ru.facts TO ru_rd | ok 9 | ru_own | ALTER TABLE ru.plain ADD COLUMN extra int | ok 10 | ru_own | SELECT sift.table_set_paths('ru.facts', 'doc', 'doc->a int4') | ERROR: permission denied for table table_paths 11 | ru_own | DROP TABLE ru.plain | ERROR: permission denied for table table_paths 12 | ru_own | DROP INDEX ru.docs_col | ERROR: permission denied for table table_paths 13 | ru_mem | DROP TABLE ru.plain | ERROR: permission denied for table table_paths 14 | ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' | ok: (300) 15 | ru_new | SELECT sift.version() | ERROR: permission denied for schema sift (15 rows) SET client_min_messages = error; ALTER EXTENSION pg_sift UPDATE; SET client_min_messages = warning; SELECT extversion = (SELECT default_version FROM pg_available_extensions WHERE name = 'pg_sift') AS updated_to_the_default FROM pg_extension WHERE extname = 'pg_sift'; updated_to_the_default ------------------------ t (1 row) -- at the current version: the same statements work and so does everything that was there SELECT * FROM rr_run($$ ru_own | DROP TABLE ru.plain ru_own | DROP INDEX ru.docs_col ru_own | CREATE TABLE ru.plain (id int) ru_mem | DROP TABLE ru.plain ru_own | !SELECT sift.table_set_paths('ru.facts', 'doc', 'doc->a int4') ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' ru_rd | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' ru_rd | SELECT g, count(*) FROM ru.facts GROUP BY g ORDER BY g ru_own | CREATE INDEX docs_col ON ru.docs USING sift_col (id, g) ru_own | !SELECT sift.promote('ru.docs_idx') ru_own | !SELECT sift.col_verify('ru.docs_col', true) ru_rd | !SELECT * FROM sift.workers ru_new | SELECT sift.version() ~ '^[0-9]+([.][0-9]+)+$' ru_rd | DROP TABLE ru.docs ru_own | INSERT INTO ru.docs SELECT i + 300, 'alpha gamma ' || i, i % 3 FROM generate_series(1, 20) i ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' ru_own | DROP TABLE ru.docs ru_own | DROP TABLE ru.facts $$); n | role | stmt | result ----+--------+----------------------------------------------------------------------------------------------+------------------------------------ 1 | ru_own | DROP TABLE ru.plain | ok 2 | ru_own | DROP INDEX ru.docs_col | ok 3 | ru_own | CREATE TABLE ru.plain (id int) | ok 4 | ru_mem | DROP TABLE ru.plain | ok 5 | ru_own | SELECT sift.table_set_paths('ru.facts', 'doc', 'doc->a int4') | ok 6 | ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' | ok: (300) 7 | ru_rd | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' | ok: (300) 8 | ru_rd | SELECT g, count(*) FROM ru.facts GROUP BY g ORDER BY g | ok: (0,100) ; (1,100) ; (2,100) 9 | ru_own | CREATE INDEX docs_col ON ru.docs USING sift_col (id, g) | ok 10 | ru_own | SELECT sift.promote('ru.docs_idx') | ok 11 | ru_own | SELECT sift.col_verify('ru.docs_col', true) | ok 12 | ru_rd | SELECT * FROM sift.workers | ok 13 | ru_new | SELECT sift.version() ~ '^[0-9]+([.][0-9]+)+$' | ok: (t) 14 | ru_rd | DROP TABLE ru.docs | ERROR: must be owner of table docs 15 | ru_own | INSERT INTO ru.docs SELECT i + 300, 'alpha gamma ' || i, i % 3 FROM generate_series(1, 20) i | ok (20) 16 | ru_own | SELECT count(*) FROM ru.docs WHERE body @@> 'alpha' | ok: (320) 17 | ru_own | DROP TABLE ru.docs | ok 18 | ru_own | DROP TABLE ru.facts | ok (18 rows) SELECT count(*) AS paths_left FROM sift.table_paths; paths_left ------------ 0 (1 row) DROP SCHEMA ru 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 ru_own, ru_mem, ru_rd, ru_new; DROP ROLE ru_own, ru_mem, ru_rd, ru_new; DROP EXTENSION pg_sift;