-- DDL as ordinary roles: the owner of a table (a role that has only CREATE on a schema), a member of the owner, a role that owns nothing and -- roles that only read or write. The event triggers of the extension fire for every one of them on each DROP and ALTER TABLE: 3.2.0 and 3.2.1 -- refused every DROP of a role that was neither a superuser nor the extension's owner ("permission denied for schema sift"). CREATE EXTENSION pg_sift; \set VERBOSITY terse \set ECHO none SELECT rr_roles_drop('rrd_'); rr_roles_drop --------------- (1 row) CREATE ROLE rrd_owner; CREATE ROLE rrd_member IN ROLE rrd_owner; CREATE ROLE rrd_other; CREATE ROLE rrd_writer; CREATE ROLE rrd_reader; CREATE ROLE rrd_none; -- no role has USAGE on the schema sift: the triggers must work for roles that cannot use it REVOKE USAGE ON SCHEMA sift FROM PUBLIC; CREATE SCHEMA rrd; CREATE SCHEMA rrd2; GRANT USAGE ON SCHEMA rrd, rrd2 TO rrd_writer, rrd_reader, rrd_none; GRANT USAGE, CREATE ON SCHEMA rrd, rrd2 TO rrd_owner, rrd_member, rrd_other; SELECT current_database() AS db \gset GRANT CREATE ON DATABASE :"db" TO rrd_owner; SET enable_seqscan = off; -- the owner: plain objects and the three kinds of sift objects, created, changed, moved and dropped SELECT * FROM rr_run($$ rrd_owner | CREATE TABLE rrd.plain (id int, body text) rrd_owner | CREATE VIEW rrd.plain_v AS SELECT id FROM rrd.plain rrd_owner | CREATE TABLE rrd.docs (id int PRIMARY KEY, body text, g int, v float8, doc jsonb) rrd_owner | INSERT INTO rrd.docs SELECT i, 'alpha beta ' || i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i rrd_owner | CREATE INDEX docs_idx ON rrd.docs USING sift (body) rrd_owner | CREATE INDEX docs_col ON rrd.docs USING sift_col (id, g, v) rrd_owner | REINDEX INDEX rrd.docs_idx rrd_owner | REINDEX INDEX rrd.docs_col rrd_owner | REINDEX TABLE rrd.docs rrd_owner | ALTER INDEX rrd.docs_idx RENAME TO docs_bm25 rrd_owner | ALTER TABLE rrd.docs ADD COLUMN extra int rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN extra TYPE bigint rrd_owner | ALTER TABLE rrd.docs RENAME COLUMN extra TO extra2 rrd_owner | ALTER TABLE rrd.docs DROP COLUMN extra2 rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN v TYPE float4 rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN body TYPE varchar rrd_owner | ALTER TABLE rrd.docs SET TABLESPACE pg_default rrd_owner | SELECT count(*) FROM rrd.docs WHERE body @@> 'alpha' rrd_owner | ALTER TABLE rrd.docs SET SCHEMA rrd2 rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD sift_table rrd_owner | DROP INDEX rrd2.docs_col rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD sift_table rrd_owner | ALTER TABLE rrd2.docs ALTER COLUMN doc TYPE json USING doc::json rrd_owner | ALTER TABLE rrd2.docs ADD COLUMN extra int rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD heap rrd_owner | ALTER TABLE rrd2.docs OWNER TO rrd_other rrd_owner | TRUNCATE rrd2.docs rrd_owner | DROP INDEX rrd2.docs_bm25 rrd_owner | DROP TABLE rrd2.docs rrd_owner | DROP VIEW rrd.plain_v rrd_owner | DROP TABLE rrd.plain $$); n | role | stmt | result ----+-----------+--------------------------------------------------------------------------------------------------------+---------------------------------------------- 1 | rrd_owner | CREATE TABLE rrd.plain (id int, body text) | ok 2 | rrd_owner | CREATE VIEW rrd.plain_v AS SELECT id FROM rrd.plain | ok 3 | rrd_owner | CREATE TABLE rrd.docs (id int PRIMARY KEY, body text, g int, v float8, doc jsonb) | ok 4 | rrd_owner | INSERT INTO rrd.docs SELECT i, 'alpha beta ' || i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i | ok (300) 5 | rrd_owner | CREATE INDEX docs_idx ON rrd.docs USING sift (body) | ok 6 | rrd_owner | CREATE INDEX docs_col ON rrd.docs USING sift_col (id, g, v) | ok 7 | rrd_owner | REINDEX INDEX rrd.docs_idx | ok 8 | rrd_owner | REINDEX INDEX rrd.docs_col | ok 9 | rrd_owner | REINDEX TABLE rrd.docs | ok 10 | rrd_owner | ALTER INDEX rrd.docs_idx RENAME TO docs_bm25 | ok 11 | rrd_owner | ALTER TABLE rrd.docs ADD COLUMN extra int | ok 12 | rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN extra TYPE bigint | ok 13 | rrd_owner | ALTER TABLE rrd.docs RENAME COLUMN extra TO extra2 | ok 14 | rrd_owner | ALTER TABLE rrd.docs DROP COLUMN extra2 | ok 15 | rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN v TYPE float4 | ok 16 | rrd_owner | ALTER TABLE rrd.docs ALTER COLUMN body TYPE varchar | ok 17 | rrd_owner | ALTER TABLE rrd.docs SET TABLESPACE pg_default | ok 18 | rrd_owner | SELECT count(*) FROM rrd.docs WHERE body @@> 'alpha' | ok: (300) 19 | rrd_owner | ALTER TABLE rrd.docs SET SCHEMA rrd2 | ok 20 | rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD sift_table | ERROR: sift_col indexes require a heap table 21 | rrd_owner | DROP INDEX rrd2.docs_col | ok 22 | rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD sift_table | ok 23 | rrd_owner | ALTER TABLE rrd2.docs ALTER COLUMN doc TYPE json USING doc::json | ok 24 | rrd_owner | ALTER TABLE rrd2.docs ADD COLUMN extra int | ok 25 | rrd_owner | ALTER TABLE rrd2.docs SET ACCESS METHOD heap | ok 26 | rrd_owner | ALTER TABLE rrd2.docs OWNER TO rrd_other | ERROR: must be able to SET ROLE "rrd_other" 27 | rrd_owner | TRUNCATE rrd2.docs | ok 28 | rrd_owner | DROP INDEX rrd2.docs_bm25 | ok 29 | rrd_owner | DROP TABLE rrd2.docs | ok 30 | rrd_owner | DROP VIEW rrd.plain_v | ok 31 | rrd_owner | DROP TABLE rrd.plain | ok (31 rows) -- a role that is not the owner: every statement that changes the table or its indexes is refused with the usual error, never one about the -- sift schema or its tables SELECT * FROM rr_run($$ rrd_owner | CREATE TABLE rrd.docs (id int PRIMARY KEY, body text, g int, v float8, doc jsonb) rrd_owner | INSERT INTO rrd.docs SELECT i, 'alpha beta ' || i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i rrd_owner | CREATE INDEX docs_idx ON rrd.docs USING sift (body) rrd_owner | CREATE INDEX docs_col ON rrd.docs USING sift_col (id, g, v) rrd_owner | CREATE TABLE rrd.facts (id int, g int, val float8, doc jsonb) USING sift_table rrd_owner | INSERT INTO rrd.facts SELECT i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i rrd_owner | GRANT SELECT ON rrd.docs, rrd.facts TO rrd_reader, rrd_writer rrd_owner | GRANT INSERT, UPDATE, DELETE ON rrd.docs, rrd.facts TO rrd_writer $$); n | role | stmt | result ---+-----------+--------------------------------------------------------------------------------------------------------+---------- 1 | rrd_owner | CREATE TABLE rrd.docs (id int PRIMARY KEY, body text, g int, v float8, doc jsonb) | ok 2 | rrd_owner | INSERT INTO rrd.docs SELECT i, 'alpha beta ' || i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i | ok (300) 3 | rrd_owner | CREATE INDEX docs_idx ON rrd.docs USING sift (body) | ok 4 | rrd_owner | CREATE INDEX docs_col ON rrd.docs USING sift_col (id, g, v) | ok 5 | rrd_owner | CREATE TABLE rrd.facts (id int, g int, val float8, doc jsonb) USING sift_table | ok 6 | rrd_owner | INSERT INTO rrd.facts SELECT i, i % 3, i, '{"a": 1}' FROM generate_series(1, 300) i | ok (300) 7 | rrd_owner | GRANT SELECT ON rrd.docs, rrd.facts TO rrd_reader, rrd_writer | ok 8 | rrd_owner | GRANT INSERT, UPDATE, DELETE ON rrd.docs, rrd.facts TO rrd_writer | ok (8 rows) SELECT * FROM rr_matrix(ARRAY['rrd_other', 'rrd_writer', 'rrd_reader', 'rrd_none'], $$ CREATE INDEX x_idx ON rrd.docs USING sift (body) CREATE INDEX x_col ON rrd.docs USING sift_col (g) CREATE INDEX x_btree ON rrd.facts (g) DROP INDEX rrd.docs_idx DROP INDEX rrd.docs_col REINDEX INDEX rrd.docs_idx REINDEX INDEX rrd.docs_col REINDEX TABLE rrd.facts ALTER INDEX rrd.docs_idx RENAME TO x ALTER TABLE rrd.docs ADD COLUMN z int ALTER TABLE rrd.facts ADD COLUMN z int ALTER TABLE rrd.docs ALTER COLUMN v TYPE float4 ALTER TABLE rrd.docs SET ACCESS METHOD heap ALTER TABLE rrd.facts SET ACCESS METHOD heap ALTER TABLE rrd.docs SET SCHEMA rrd2 ALTER TABLE rrd.docs RENAME TO x ALTER TABLE rrd.docs OWNER TO rrd_other TRUNCATE rrd.docs TRUNCATE rrd.facts DROP TABLE rrd.docs DROP TABLE rrd.facts DROP TABLE rrd.docs, rrd.facts DROP SCHEMA rrd CASCADE DROP OWNED BY rrd_owner REASSIGN OWNED BY rrd_owner TO rrd_other $$); stmt | result | by_role ---------------------------------------------------+----------------------------------------------+--------------------------------------------- CREATE INDEX x_idx ON rrd.docs USING sift (body) | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none CREATE INDEX x_col ON rrd.docs USING sift_col (g) | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none CREATE INDEX x_btree ON rrd.facts (g) | ERROR: must be owner of table facts | rrd_other, rrd_writer, rrd_reader, rrd_none DROP INDEX rrd.docs_idx | ERROR: must be owner of index docs_idx | rrd_other, rrd_writer, rrd_reader, rrd_none DROP INDEX rrd.docs_col | ERROR: must be owner of index docs_col | rrd_other, rrd_writer, rrd_reader, rrd_none REINDEX INDEX rrd.docs_idx | ERROR: permission denied for index docs_idx | rrd_other, rrd_writer, rrd_reader, rrd_none REINDEX INDEX rrd.docs_col | ERROR: permission denied for index docs_col | rrd_other, rrd_writer, rrd_reader, rrd_none REINDEX TABLE rrd.facts | ERROR: permission denied for table facts | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER INDEX rrd.docs_idx RENAME TO x | ERROR: must be owner of index docs_idx | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs ADD COLUMN z int | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.facts ADD COLUMN z int | ERROR: must be owner of table facts | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs ALTER COLUMN v TYPE float4 | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs SET ACCESS METHOD heap | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.facts SET ACCESS METHOD heap | ERROR: must be owner of table facts | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs SET SCHEMA rrd2 | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs RENAME TO x | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none ALTER TABLE rrd.docs OWNER TO rrd_other | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none TRUNCATE rrd.docs | ERROR: permission denied for table docs | rrd_other, rrd_writer, rrd_reader, rrd_none TRUNCATE rrd.facts | ERROR: permission denied for table facts | rrd_other, rrd_writer, rrd_reader, rrd_none DROP TABLE rrd.docs | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none DROP TABLE rrd.facts | ERROR: must be owner of table facts | rrd_other, rrd_writer, rrd_reader, rrd_none DROP TABLE rrd.docs, rrd.facts | ERROR: must be owner of table docs | rrd_other, rrd_writer, rrd_reader, rrd_none DROP SCHEMA rrd CASCADE | ERROR: must be owner of schema rrd | rrd_other, rrd_writer, rrd_reader, rrd_none DROP OWNED BY rrd_owner | ERROR: permission denied to drop objects | rrd_other, rrd_writer, rrd_reader, rrd_none REASSIGN OWNED BY rrd_owner TO rrd_other | ERROR: permission denied to reassign objects | rrd_other, rrd_writer, rrd_reader, rrd_none (25 rows) SELECT count(*) AS docs_left FROM rrd.docs; docs_left ----------- 300 (1 row) SELECT count(*) AS facts_left FROM rrd.facts; facts_left ------------ 300 (1 row) -- a member of the owner role has the owner's rights; what it creates is its own SELECT * FROM rr_run($$ rrd_member | CREATE INDEX docs_idx2 ON rrd.docs USING sift (body) rrd_member | REINDEX INDEX rrd.docs_idx2 rrd_member | DROP INDEX rrd.docs_idx2 rrd_member | ALTER TABLE rrd.docs ADD COLUMN z int rrd_member | ALTER TABLE rrd.facts ADD COLUMN z int rrd_member | TRUNCATE rrd.facts rrd_member | CREATE TABLE rrd.facts_m (id int, val int) USING sift_table rrd_member | DROP TABLE rrd.facts_m rrd_member | DROP TABLE rrd.docs, rrd.facts rrd_other | CREATE TABLE rrd.facts_o (id int, val int) USING sift_table rrd_other | DROP TABLE rrd.facts_o $$); n | role | stmt | result ----+------------+-------------------------------------------------------------+-------- 1 | rrd_member | CREATE INDEX docs_idx2 ON rrd.docs USING sift (body) | ok 2 | rrd_member | REINDEX INDEX rrd.docs_idx2 | ok 3 | rrd_member | DROP INDEX rrd.docs_idx2 | ok 4 | rrd_member | ALTER TABLE rrd.docs ADD COLUMN z int | ok 5 | rrd_member | ALTER TABLE rrd.facts ADD COLUMN z int | ok 6 | rrd_member | TRUNCATE rrd.facts | ok 7 | rrd_member | CREATE TABLE rrd.facts_m (id int, val int) USING sift_table | ok 8 | rrd_member | DROP TABLE rrd.facts_m | ok 9 | rrd_member | DROP TABLE rrd.docs, rrd.facts | ok 10 | rrd_other | CREATE TABLE rrd.facts_o (id int, val int) USING sift_table | ok 11 | rrd_other | DROP TABLE rrd.facts_o | ok (11 rows) SELECT count(*) AS left_in_rrd FROM pg_class WHERE relnamespace = 'rrd'::regnamespace; left_in_rrd ------------- 0 (1 row) -- schemas and ownership: the owner creates a schema (the database CREATE privilege) and drops it with everything in it; DROP OWNED and -- REASSIGN OWNED fire the drop trigger for sift tables too. DROP OWNED warns that it revoked nothing and names the database, whose name -- depends on how the tests are run SET client_min_messages = error; SELECT * FROM rr_run($$ rrd_owner | CREATE SCHEMA rrd_own rrd_owner | CREATE TABLE rrd_own.docs (id int, body text) rrd_owner | CREATE INDEX ON rrd_own.docs USING sift (body) rrd_owner | CREATE INDEX ON rrd_own.docs USING sift_col (id) rrd_owner | CREATE TABLE rrd_own.facts (id int, val int) USING sift_table rrd_other | DROP SCHEMA rrd_own CASCADE rrd_other | DROP SCHEMA rrd_own rrd_owner | DROP SCHEMA rrd_own rrd_owner | DROP SCHEMA rrd_own CASCADE rrd_owner | CREATE TABLE rrd.docs (id int, body text) rrd_owner | CREATE INDEX ON rrd.docs USING sift (body) rrd_owner | CREATE TABLE rrd.facts (id int, val int) USING sift_table rrd_member | REASSIGN OWNED BY rrd_owner TO rrd_member rrd_member | ALTER TABLE rrd.docs ADD COLUMN z int rrd_owner | DROP OWNED BY rrd_owner rrd_member | DROP OWNED BY rrd_member $$); n | role | stmt | result ----+------------+---------------------------------------------------------------+---------------------------------------------------------------------- 1 | rrd_owner | CREATE SCHEMA rrd_own | ok 2 | rrd_owner | CREATE TABLE rrd_own.docs (id int, body text) | ok 3 | rrd_owner | CREATE INDEX ON rrd_own.docs USING sift (body) | ok 4 | rrd_owner | CREATE INDEX ON rrd_own.docs USING sift_col (id) | ok 5 | rrd_owner | CREATE TABLE rrd_own.facts (id int, val int) USING sift_table | ok 6 | rrd_other | DROP SCHEMA rrd_own CASCADE | ERROR: must be owner of schema rrd_own 7 | rrd_other | DROP SCHEMA rrd_own | ERROR: must be owner of schema rrd_own 8 | rrd_owner | DROP SCHEMA rrd_own | ERROR: cannot drop schema rrd_own because other objects depend on it 9 | rrd_owner | DROP SCHEMA rrd_own CASCADE | ok 10 | rrd_owner | CREATE TABLE rrd.docs (id int, body text) | ok 11 | rrd_owner | CREATE INDEX ON rrd.docs USING sift (body) | ok 12 | rrd_owner | CREATE TABLE rrd.facts (id int, val int) USING sift_table | ok 13 | rrd_member | REASSIGN OWNED BY rrd_owner TO rrd_member | ok 14 | rrd_member | ALTER TABLE rrd.docs ADD COLUMN z int | ok 15 | rrd_owner | DROP OWNED BY rrd_owner | ok 16 | rrd_member | DROP OWNED BY rrd_member | ok (16 rows) RESET client_min_messages; SELECT count(*) AS left_in_schemas FROM pg_class WHERE relnamespace IN ('rrd'::regnamespace, 'rrd2'::regnamespace); left_in_schemas ----------------- 0 (1 row) SELECT count(*) AS paths_left FROM sift.table_paths; paths_left ------------ 0 (1 row) -- partitioned tables and inheritance, with sift indexes on the partitions and sift_table partitions SELECT * FROM rr_run($$ rrd_owner | CREATE TABLE rrd.part (id int, body text, g int) PARTITION BY RANGE (id) rrd_owner | CREATE TABLE rrd.part_1 PARTITION OF rrd.part FOR VALUES FROM (0) TO (100) rrd_owner | CREATE TABLE rrd.part_2 PARTITION OF rrd.part FOR VALUES FROM (100) TO (200) USING sift_table rrd_owner | CREATE TABLE rrd.part_3 (id int, body text, g int) rrd_owner | INSERT INTO rrd.part SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(0, 199) i rrd_owner | CREATE INDEX part_idx ON rrd.part USING sift (body) rrd_owner | CREATE INDEX part_col ON rrd.part USING sift_col (id, g) rrd_owner | CREATE INDEX part_1_idx ON rrd.part_1 USING sift (body) rrd_owner | CREATE INDEX part_3_idx ON rrd.part_3 USING sift (body) rrd_owner | ALTER TABLE rrd.part ATTACH PARTITION rrd.part_3 FOR VALUES FROM (200) TO (300) rrd_owner | ALTER TABLE rrd.part DETACH PARTITION rrd.part_3 rrd_owner | ALTER TABLE rrd.part ADD COLUMN extra int rrd_owner | GRANT SELECT ON rrd.part, rrd.part_1, rrd.part_2, rrd.part_3 TO rrd_reader rrd_reader | SELECT count(*) FROM rrd.part WHERE body @@> 'alpha' rrd_reader | SELECT count(*) FROM rrd.part_1 WHERE body @@> 'alpha' rrd_reader | SELECT count(*), sum(id) FROM rrd.part GROUP BY g ORDER BY g rrd_reader | DROP TABLE rrd.part_1 rrd_owner | DROP TABLE rrd.part_3 rrd_owner | DROP TABLE rrd.part_2 rrd_owner | DROP TABLE rrd.part rrd_owner | CREATE TABLE rrd.base (id int, body text) rrd_owner | CREATE TABLE rrd.child (extra int) INHERITS (rrd.base) rrd_owner | CREATE INDEX base_idx ON rrd.base USING sift (body) rrd_owner | CREATE INDEX child_idx ON rrd.child USING sift (body) rrd_owner | INSERT INTO rrd.child SELECT i, 'alpha ' || i, 1 FROM generate_series(1, 50) i rrd_owner | ALTER TABLE rrd.child NO INHERIT rrd.base rrd_owner | ALTER TABLE rrd.child INHERIT rrd.base rrd_owner | DROP TABLE rrd.child rrd_owner | DROP TABLE rrd.base $$); NOTICE: sift_col index "part_1_id_g_idx": 1 advice (track_changes) n | role | stmt | result ----+------------+-----------------------------------------------------------------------------------------------+---------------------------------------------- 1 | rrd_owner | CREATE TABLE rrd.part (id int, body text, g int) PARTITION BY RANGE (id) | ok 2 | rrd_owner | CREATE TABLE rrd.part_1 PARTITION OF rrd.part FOR VALUES FROM (0) TO (100) | ok 3 | rrd_owner | CREATE TABLE rrd.part_2 PARTITION OF rrd.part FOR VALUES FROM (100) TO (200) USING sift_table | ok 4 | rrd_owner | CREATE TABLE rrd.part_3 (id int, body text, g int) | ok 5 | rrd_owner | INSERT INTO rrd.part SELECT i, 'alpha beta ' || i, i % 3 FROM generate_series(0, 199) i | ok (200) 6 | rrd_owner | CREATE INDEX part_idx ON rrd.part USING sift (body) | ok 7 | rrd_owner | CREATE INDEX part_col ON rrd.part USING sift_col (id, g) | ERROR: sift_col indexes require a heap table 8 | rrd_owner | CREATE INDEX part_1_idx ON rrd.part_1 USING sift (body) | ok 9 | rrd_owner | CREATE INDEX part_3_idx ON rrd.part_3 USING sift (body) | ok 10 | rrd_owner | ALTER TABLE rrd.part ATTACH PARTITION rrd.part_3 FOR VALUES FROM (200) TO (300) | ok 11 | rrd_owner | ALTER TABLE rrd.part DETACH PARTITION rrd.part_3 | ok 12 | rrd_owner | ALTER TABLE rrd.part ADD COLUMN extra int | ok 13 | rrd_owner | GRANT SELECT ON rrd.part, rrd.part_1, rrd.part_2, rrd.part_3 TO rrd_reader | ok 14 | rrd_reader | SELECT count(*) FROM rrd.part WHERE body @@> 'alpha' | ok: (200) 15 | rrd_reader | SELECT count(*) FROM rrd.part_1 WHERE body @@> 'alpha' | ok: (100) 16 | rrd_reader | SELECT count(*), sum(id) FROM rrd.part GROUP BY g ORDER BY g | ok: (67,6633) ; (67,6700) ; (66,6567) 17 | rrd_reader | DROP TABLE rrd.part_1 | ERROR: must be owner of table part_1 18 | rrd_owner | DROP TABLE rrd.part_3 | ok 19 | rrd_owner | DROP TABLE rrd.part_2 | ok 20 | rrd_owner | DROP TABLE rrd.part | ok 21 | rrd_owner | CREATE TABLE rrd.base (id int, body text) | ok 22 | rrd_owner | CREATE TABLE rrd.child (extra int) INHERITS (rrd.base) | ok 23 | rrd_owner | CREATE INDEX base_idx ON rrd.base USING sift (body) | ok 24 | rrd_owner | CREATE INDEX child_idx ON rrd.child USING sift (body) | ok 25 | rrd_owner | INSERT INTO rrd.child SELECT i, 'alpha ' || i, 1 FROM generate_series(1, 50) i | ok (50) 26 | rrd_owner | ALTER TABLE rrd.child NO INHERIT rrd.base | ok 27 | rrd_owner | ALTER TABLE rrd.child INHERIT rrd.base | ok 28 | rrd_owner | DROP TABLE rrd.child | ok 29 | rrd_owner | DROP TABLE rrd.base | ok (29 rows) -- ordinary objects in a database where the extension is installed: the triggers must not get in the way SELECT * FROM rr_run($$ rrd_owner | CREATE TABLE rrd.t (id int PRIMARY KEY, val int, tags text[]) rrd_owner | CREATE TABLE rrd.t_copy AS SELECT * FROM rrd.t rrd_owner | CREATE MATERIALIZED VIEW rrd.mv AS SELECT val, count(*) FROM rrd.t GROUP BY val rrd_owner | CREATE SEQUENCE rrd.s rrd_owner | CREATE TYPE rrd.ty AS (a int, b text) rrd_owner | CREATE DOMAIN rrd.dom AS int CHECK (VALUE > 0) rrd_owner | CREATE FUNCTION rrd.f(int) RETURNS int LANGUAGE sql AS 'SELECT $1 + 1' rrd_owner | CREATE INDEX t_val ON rrd.t (val) rrd_owner | CREATE INDEX t_tags ON rrd.t USING gin (tags) rrd_owner | ALTER TABLE rrd.t ADD COLUMN extra text DEFAULT 'x' rrd_owner | ALTER TABLE rrd.t ADD CONSTRAINT t_chk CHECK (val >= 0) rrd_owner | ALTER TABLE rrd.t ALTER COLUMN id TYPE bigint rrd_owner | ALTER TABLE rrd.t RENAME COLUMN extra TO extra2 rrd_owner | ALTER TABLE rrd.t DROP COLUMN extra2 rrd_owner | ALTER TABLE rrd.t SET UNLOGGED rrd_owner | ALTER TABLE rrd.t SET LOGGED rrd_owner | ALTER TABLE rrd.t SET (fillfactor = 70) rrd_owner | COMMENT ON TABLE rrd.t IS 'plain' rrd_owner | GRANT SELECT ON rrd.t TO rrd_reader rrd_owner | REVOKE SELECT ON rrd.t FROM rrd_reader rrd_owner | ALTER TABLE rrd.t RENAME TO t2 rrd_owner | ALTER TABLE rrd.t2 SET SCHEMA rrd2 rrd_owner | DROP INDEX rrd2.t_val rrd_owner | DROP MATERIALIZED VIEW rrd.mv rrd_owner | DROP TABLE rrd.t_copy, rrd2.t2 rrd_owner | DROP FUNCTION rrd.f(int) rrd_owner | DROP DOMAIN rrd.dom rrd_owner | DROP TYPE rrd.ty rrd_owner | DROP SEQUENCE rrd.s rrd_reader | CREATE TABLE rrd.r (id int) rrd_owner | CREATE TABLE rrd.t (id int) rrd_reader | ALTER TABLE rrd.t ADD COLUMN x int rrd_reader | DROP TABLE rrd.t rrd_owner | DROP TABLE rrd.t $$); n | role | stmt | result ----+------------+---------------------------------------------------------------------------------+----------------------------------------- 1 | rrd_owner | CREATE TABLE rrd.t (id int PRIMARY KEY, val int, tags text[]) | ok 2 | rrd_owner | CREATE TABLE rrd.t_copy AS SELECT * FROM rrd.t | ok 3 | rrd_owner | CREATE MATERIALIZED VIEW rrd.mv AS SELECT val, count(*) FROM rrd.t GROUP BY val | ok 4 | rrd_owner | CREATE SEQUENCE rrd.s | ok 5 | rrd_owner | CREATE TYPE rrd.ty AS (a int, b text) | ok 6 | rrd_owner | CREATE DOMAIN rrd.dom AS int CHECK (VALUE > 0) | ok 7 | rrd_owner | CREATE FUNCTION rrd.f(int) RETURNS int LANGUAGE sql AS 'SELECT $1 + 1' | ok 8 | rrd_owner | CREATE INDEX t_val ON rrd.t (val) | ok 9 | rrd_owner | CREATE INDEX t_tags ON rrd.t USING gin (tags) | ok 10 | rrd_owner | ALTER TABLE rrd.t ADD COLUMN extra text DEFAULT 'x' | ok 11 | rrd_owner | ALTER TABLE rrd.t ADD CONSTRAINT t_chk CHECK (val >= 0) | ok 12 | rrd_owner | ALTER TABLE rrd.t ALTER COLUMN id TYPE bigint | ok 13 | rrd_owner | ALTER TABLE rrd.t RENAME COLUMN extra TO extra2 | ok 14 | rrd_owner | ALTER TABLE rrd.t DROP COLUMN extra2 | ok 15 | rrd_owner | ALTER TABLE rrd.t SET UNLOGGED | ok 16 | rrd_owner | ALTER TABLE rrd.t SET LOGGED | ok 17 | rrd_owner | ALTER TABLE rrd.t SET (fillfactor = 70) | ok 18 | rrd_owner | COMMENT ON TABLE rrd.t IS 'plain' | ok 19 | rrd_owner | GRANT SELECT ON rrd.t TO rrd_reader | ok 20 | rrd_owner | REVOKE SELECT ON rrd.t FROM rrd_reader | ok 21 | rrd_owner | ALTER TABLE rrd.t RENAME TO t2 | ok 22 | rrd_owner | ALTER TABLE rrd.t2 SET SCHEMA rrd2 | ok 23 | rrd_owner | DROP INDEX rrd2.t_val | ok 24 | rrd_owner | DROP MATERIALIZED VIEW rrd.mv | ok 25 | rrd_owner | DROP TABLE rrd.t_copy, rrd2.t2 | ok 26 | rrd_owner | DROP FUNCTION rrd.f(int) | ok 27 | rrd_owner | DROP DOMAIN rrd.dom | ok 28 | rrd_owner | DROP TYPE rrd.ty | ok 29 | rrd_owner | DROP SEQUENCE rrd.s | ok 30 | rrd_reader | CREATE TABLE rrd.r (id int) | ERROR: permission denied for schema rrd 31 | rrd_owner | CREATE TABLE rrd.t (id int) | ok 32 | rrd_reader | ALTER TABLE rrd.t ADD COLUMN x int | ERROR: must be owner of table t 33 | rrd_reader | DROP TABLE rrd.t | ERROR: must be owner of table t 34 | rrd_owner | DROP TABLE rrd.t | ok (34 rows) -- CONCURRENTLY runs outside a transaction block: the owner, then a role that is not the owner SET SESSION AUTHORIZATION rrd_owner; CREATE TABLE rrd.conc (id int, body text, g int, v float8); INSERT INTO rrd.conc SELECT i, 'alpha ' || i, i % 3, i FROM generate_series(1, 300) i; CREATE INDEX CONCURRENTLY conc_btree ON rrd.conc (g); CREATE INDEX CONCURRENTLY conc_idx ON rrd.conc USING sift (body); CREATE INDEX CONCURRENTLY conc_col ON rrd.conc USING sift_col (id, g, v); NOTICE: sift_col index "conc_col": 1 advice (track_changes) REINDEX INDEX CONCURRENTLY rrd.conc_idx; REINDEX INDEX CONCURRENTLY rrd.conc_col; NOTICE: sift_col index "conc_col": 1 advice (track_changes) REINDEX TABLE CONCURRENTLY rrd.conc; NOTICE: sift_col index "conc_col": 1 advice (track_changes) SELECT count(*) FROM rrd.conc WHERE body @@> 'alpha'; count ------- 300 (1 row) RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION rrd_other; CREATE INDEX CONCURRENTLY conc_x ON rrd.conc USING sift (body); ERROR: must be owner of table conc REINDEX INDEX CONCURRENTLY rrd.conc_idx; ERROR: permission denied for index conc_idx REINDEX TABLE CONCURRENTLY rrd.conc; ERROR: permission denied for table conc DROP INDEX CONCURRENTLY rrd.conc_idx; ERROR: must be owner of index conc_idx RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION rrd_member; DROP INDEX CONCURRENTLY rrd.conc_idx; DROP INDEX CONCURRENTLY rrd.conc_col; DROP TABLE rrd.conc; RESET SESSION AUTHORIZATION; DROP SCHEMA rrd, rrd2 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 rrd_owner, rrd_member, rrd_other, rrd_writer, rrd_reader, rrd_none; DROP ROLE rrd_owner, rrd_member, rrd_other, rrd_writer, rrd_reader, rrd_none; DROP EXTENSION pg_sift;