1-- 2-- PUBLICATION 3-- 4CREATE ROLE regress_publication_user LOGIN SUPERUSER; 5CREATE ROLE regress_publication_user2; 6CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER; 7SET SESSION AUTHORIZATION 'regress_publication_user'; 8 9CREATE PUBLICATION testpub_default; 10 11COMMENT ON PUBLICATION testpub_default IS 'test publication'; 12SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p; 13 14CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert); 15 16ALTER PUBLICATION testpub_default SET (publish = update); 17 18-- error cases 19CREATE PUBLICATION testpub_xxx WITH (foo); 20CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); 21 22\dRp 23 24ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); 25 26\dRp 27 28--- adding tables 29CREATE SCHEMA pub_test; 30CREATE TABLE testpub_tbl1 (id serial primary key, data text); 31CREATE TABLE pub_test.testpub_nopk (foo int, bar int); 32CREATE VIEW testpub_view AS SELECT 1; 33CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a); 34 35CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); 36ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update'); 37 38CREATE TABLE testpub_tbl2 (id serial primary key, data text); 39-- fail - can't add to for all tables publication 40ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; 41-- fail - can't drop from all tables publication 42ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; 43-- fail - can't add to for all tables publication 44ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; 45 46SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; 47\d+ testpub_tbl2 48\dRp+ testpub_foralltables 49 50DROP TABLE testpub_tbl2; 51DROP PUBLICATION testpub_foralltables; 52 53CREATE TABLE testpub_tbl3 (a int); 54CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); 55CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; 56CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; 57\dRp+ testpub3 58\dRp+ testpub4 59 60DROP TABLE testpub_tbl3, testpub_tbl3a; 61DROP PUBLICATION testpub3, testpub4; 62 63-- fail - view 64CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view; 65CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; 66-- fail - already added 67ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; 68-- fail - already added 69CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; 70 71\dRp+ testpub_fortbl 72 73-- fail - view 74ALTER PUBLICATION testpub_default ADD TABLE testpub_view; 75-- fail - partitioned table 76ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_parted; 77 78ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1; 79ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; 80ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; 81 82ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; 83 84\d+ pub_test.testpub_nopk 85\d+ testpub_tbl1 86\dRp+ testpub_default 87 88ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk; 89-- fail - nonexistent 90ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; 91 92\d+ testpub_tbl1 93 94-- permissions 95SET ROLE regress_publication_user2; 96CREATE PUBLICATION testpub2; -- fail 97 98SET ROLE regress_publication_user; 99GRANT CREATE ON DATABASE regression TO regress_publication_user2; 100SET ROLE regress_publication_user2; 101CREATE PUBLICATION testpub2; -- ok 102 103ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail 104 105SET ROLE regress_publication_user; 106GRANT regress_publication_user TO regress_publication_user2; 107SET ROLE regress_publication_user2; 108ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok 109 110DROP PUBLICATION testpub2; 111 112SET ROLE regress_publication_user; 113REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; 114 115DROP TABLE testpub_parted; 116DROP VIEW testpub_view; 117DROP TABLE testpub_tbl1; 118 119\dRp+ testpub_default 120 121-- fail - must be owner of publication 122SET ROLE regress_publication_user_dummy; 123ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; 124RESET ROLE; 125 126ALTER PUBLICATION testpub_default RENAME TO testpub_foo; 127 128\dRp testpub_foo 129 130-- rename back to keep the rest simple 131ALTER PUBLICATION testpub_foo RENAME TO testpub_default; 132 133ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; 134 135\dRp testpub_default 136 137DROP PUBLICATION testpub_default; 138DROP PUBLICATION testpib_ins_trunct; 139DROP PUBLICATION testpub_fortbl; 140 141DROP SCHEMA pub_test CASCADE; 142 143RESET SESSION AUTHORIZATION; 144DROP ROLE regress_publication_user, regress_publication_user2; 145DROP ROLE regress_publication_user_dummy; 146