1-- should fail, return type mismatch 2create event trigger regress_event_trigger 3 on ddl_command_start 4 execute procedure pg_backend_pid(); 5 6-- OK 7create function test_event_trigger() returns event_trigger as $$ 8BEGIN 9 RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; 10END 11$$ language plpgsql; 12 13-- should fail, can't call it as a plain function 14SELECT test_event_trigger(); 15 16-- should fail, event triggers cannot have declared arguments 17create function test_event_trigger_arg(name text) 18returns event_trigger as $$ BEGIN RETURN 1; END $$ language plpgsql; 19 20-- should fail, SQL functions cannot be event triggers 21create function test_event_trigger_sql() returns event_trigger as $$ 22SELECT 1 $$ language sql; 23 24-- should fail, no elephant_bootstrap entry point 25create event trigger regress_event_trigger on elephant_bootstrap 26 execute procedure test_event_trigger(); 27 28-- OK 29create event trigger regress_event_trigger on ddl_command_start 30 execute procedure test_event_trigger(); 31 32-- OK 33create event trigger regress_event_trigger_end on ddl_command_end 34 execute procedure test_event_trigger(); 35 36-- should fail, food is not a valid filter variable 37create event trigger regress_event_trigger2 on ddl_command_start 38 when food in ('sandwich') 39 execute procedure test_event_trigger(); 40 41-- should fail, sandwich is not a valid command tag 42create event trigger regress_event_trigger2 on ddl_command_start 43 when tag in ('sandwich') 44 execute procedure test_event_trigger(); 45 46-- should fail, create skunkcabbage is not a valid command tag 47create event trigger regress_event_trigger2 on ddl_command_start 48 when tag in ('create table', 'create skunkcabbage') 49 execute procedure test_event_trigger(); 50 51-- should fail, can't have event triggers on event triggers 52create event trigger regress_event_trigger2 on ddl_command_start 53 when tag in ('DROP EVENT TRIGGER') 54 execute procedure test_event_trigger(); 55 56-- should fail, can't have event triggers on global objects 57create event trigger regress_event_trigger2 on ddl_command_start 58 when tag in ('CREATE ROLE') 59 execute procedure test_event_trigger(); 60 61-- should fail, can't have event triggers on global objects 62create event trigger regress_event_trigger2 on ddl_command_start 63 when tag in ('CREATE DATABASE') 64 execute procedure test_event_trigger(); 65 66-- should fail, can't have event triggers on global objects 67create event trigger regress_event_trigger2 on ddl_command_start 68 when tag in ('CREATE TABLESPACE') 69 execute procedure test_event_trigger(); 70 71-- should fail, can't have same filter variable twice 72create event trigger regress_event_trigger2 on ddl_command_start 73 when tag in ('create table') and tag in ('CREATE FUNCTION') 74 execute procedure test_event_trigger(); 75 76-- should fail, can't have arguments 77create event trigger regress_event_trigger2 on ddl_command_start 78 execute procedure test_event_trigger('argument not allowed'); 79 80-- OK 81create event trigger regress_event_trigger2 on ddl_command_start 82 when tag in ('create table', 'CREATE FUNCTION') 83 execute procedure test_event_trigger(); 84 85-- OK 86comment on event trigger regress_event_trigger is 'test comment'; 87 88-- should fail, event triggers are not schema objects 89comment on event trigger wrong.regress_event_trigger is 'test comment'; 90 91-- drop as non-superuser should fail 92create role regress_evt_user; 93set role regress_evt_user; 94create event trigger regress_event_trigger_noperms on ddl_command_start 95 execute procedure test_event_trigger(); 96reset role; 97 98-- all OK 99alter event trigger regress_event_trigger enable replica; 100alter event trigger regress_event_trigger enable always; 101alter event trigger regress_event_trigger enable; 102alter event trigger regress_event_trigger disable; 103 104-- regress_event_trigger2 and regress_event_trigger_end should fire, but not 105-- regress_event_trigger 106create table event_trigger_fire1 (a int); 107 108-- regress_event_trigger_end should fire on these commands 109grant all on table event_trigger_fire1 to public; 110comment on table event_trigger_fire1 is 'here is a comment'; 111revoke all on table event_trigger_fire1 from public; 112drop table event_trigger_fire1; 113create foreign data wrapper useless; 114create server useless_server foreign data wrapper useless; 115create user mapping for regress_evt_user server useless_server; 116alter default privileges for role regress_evt_user 117 revoke delete on tables from regress_evt_user; 118 119-- alter owner to non-superuser should fail 120alter event trigger regress_event_trigger owner to regress_evt_user; 121 122-- alter owner to superuser should work 123alter role regress_evt_user superuser; 124alter event trigger regress_event_trigger owner to regress_evt_user; 125 126-- should fail, name collision 127alter event trigger regress_event_trigger rename to regress_event_trigger2; 128 129-- OK 130alter event trigger regress_event_trigger rename to regress_event_trigger3; 131 132-- should fail, doesn't exist any more 133drop event trigger regress_event_trigger; 134 135-- should fail, regress_evt_user owns some objects 136drop role regress_evt_user; 137 138-- cleanup before next test 139-- these are all OK; the second one should emit a NOTICE 140drop event trigger if exists regress_event_trigger2; 141drop event trigger if exists regress_event_trigger2; 142drop event trigger regress_event_trigger3; 143drop event trigger regress_event_trigger_end; 144 145-- test support for dropped objects 146CREATE SCHEMA schema_one authorization regress_evt_user; 147CREATE SCHEMA schema_two authorization regress_evt_user; 148CREATE SCHEMA audit_tbls authorization regress_evt_user; 149CREATE TEMP TABLE a_temp_tbl (); 150SET SESSION AUTHORIZATION regress_evt_user; 151 152CREATE TABLE schema_one.table_one(a int); 153CREATE TABLE schema_one."table two"(a int); 154CREATE TABLE schema_one.table_three(a int); 155CREATE TABLE audit_tbls.schema_one_table_two(the_value text); 156 157CREATE TABLE schema_two.table_two(a int); 158CREATE TABLE schema_two.table_three(a int, b text); 159CREATE TABLE audit_tbls.schema_two_table_three(the_value text); 160 161CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql 162 CALLED ON NULL INPUT 163 AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$; 164CREATE AGGREGATE schema_two.newton 165 (BASETYPE = int, SFUNC = schema_two.add, STYPE = int); 166 167RESET SESSION AUTHORIZATION; 168 169CREATE TABLE undroppable_objs ( 170 object_type text, 171 object_identity text 172); 173INSERT INTO undroppable_objs VALUES 174('table', 'schema_one.table_three'), 175('table', 'audit_tbls.schema_two_table_three'); 176 177CREATE TABLE dropped_objects ( 178 type text, 179 schema text, 180 object text 181); 182 183-- This tests errors raised within event triggers; the one in audit_tbls 184-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects(). 185CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger 186LANGUAGE plpgsql AS $$ 187DECLARE 188 obj record; 189BEGIN 190 PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs'; 191 IF NOT FOUND THEN 192 RAISE NOTICE 'table undroppable_objs not found, skipping'; 193 RETURN; 194 END IF; 195 FOR obj IN 196 SELECT * FROM pg_event_trigger_dropped_objects() JOIN 197 undroppable_objs USING (object_type, object_identity) 198 LOOP 199 RAISE EXCEPTION 'object % of type % cannot be dropped', 200 obj.object_identity, obj.object_type; 201 END LOOP; 202END; 203$$; 204 205CREATE EVENT TRIGGER undroppable ON sql_drop 206 EXECUTE PROCEDURE undroppable(); 207 208CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger 209LANGUAGE plpgsql AS $$ 210DECLARE 211 obj record; 212BEGIN 213 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() 214 LOOP 215 IF obj.object_type = 'table' THEN 216 EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I', 217 format('%s_%s', obj.schema_name, obj.object_name)); 218 END IF; 219 220 INSERT INTO dropped_objects 221 (type, schema, object) VALUES 222 (obj.object_type, obj.schema_name, obj.object_identity); 223 END LOOP; 224END 225$$; 226 227CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop 228 WHEN TAG IN ('drop table', 'drop function', 'drop view', 229 'drop owned', 'drop schema', 'alter table') 230 EXECUTE PROCEDURE test_evtrig_dropped_objects(); 231 232ALTER TABLE schema_one.table_one DROP COLUMN a; 233DROP SCHEMA schema_one, schema_two CASCADE; 234DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three'; 235DROP SCHEMA schema_one, schema_two CASCADE; 236DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three'; 237DROP SCHEMA schema_one, schema_two CASCADE; 238 239SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast'; 240 241DROP OWNED BY regress_evt_user; 242SELECT * FROM dropped_objects WHERE type = 'schema'; 243 244DROP ROLE regress_evt_user; 245 246DROP EVENT TRIGGER regress_event_trigger_drop_objects; 247DROP EVENT TRIGGER undroppable; 248 249CREATE OR REPLACE FUNCTION event_trigger_report_dropped() 250 RETURNS event_trigger 251 LANGUAGE plpgsql 252AS $$ 253DECLARE r record; 254BEGIN 255 FOR r IN SELECT * from pg_event_trigger_dropped_objects() 256 LOOP 257 IF NOT r.normal AND NOT r.original THEN 258 CONTINUE; 259 END IF; 260 RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%', 261 r.original, r.normal, r.is_temporary, r.object_type, 262 r.object_identity, r.address_names, r.address_args; 263 END LOOP; 264END; $$; 265CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop 266 EXECUTE PROCEDURE event_trigger_report_dropped(); 267CREATE SCHEMA evttrig 268 CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two') 269 CREATE INDEX one_idx ON one (col_b) 270 CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42); 271 272ALTER TABLE evttrig.two DROP COLUMN col_c; 273ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT; 274ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey; 275DROP INDEX evttrig.one_idx; 276DROP SCHEMA evttrig CASCADE; 277DROP TABLE a_temp_tbl; 278 279DROP EVENT TRIGGER regress_event_trigger_report_dropped; 280 281-- only allowed from within an event trigger function, should fail 282select pg_event_trigger_table_rewrite_oid(); 283 284-- test Table Rewrite Event Trigger 285CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 286LANGUAGE plpgsql AS $$ 287BEGIN 288 RAISE EXCEPTION 'rewrites not allowed'; 289END; 290$$; 291 292create event trigger no_rewrite_allowed on table_rewrite 293 execute procedure test_evtrig_no_rewrite(); 294 295create table rewriteme (id serial primary key, foo float); 296insert into rewriteme 297 select x * 1.001 from generate_series(1, 500) as t(x); 298alter table rewriteme alter column foo type numeric; 299alter table rewriteme add column baz int default 0; 300 301-- test with more than one reason to rewrite a single table 302CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 303LANGUAGE plpgsql AS $$ 304BEGIN 305 RAISE NOTICE 'Table ''%'' is being rewritten (reason = %)', 306 pg_event_trigger_table_rewrite_oid()::regclass, 307 pg_event_trigger_table_rewrite_reason(); 308END; 309$$; 310 311alter table rewriteme 312 add column onemore int default 0, 313 add column another int default -1, 314 alter column foo type numeric(10,4); 315 316-- shouldn't trigger a table_rewrite event 317alter table rewriteme alter column foo type numeric(12,4); 318 319-- typed tables are rewritten when their type changes. Don't emit table 320-- name, because firing order is not stable. 321CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 322LANGUAGE plpgsql AS $$ 323BEGIN 324 RAISE NOTICE 'Table is being rewritten (reason = %)', 325 pg_event_trigger_table_rewrite_reason(); 326END; 327$$; 328 329create type rewritetype as (a int); 330create table rewritemetoo1 of rewritetype; 331create table rewritemetoo2 of rewritetype; 332alter type rewritetype alter attribute a type text cascade; 333 334-- but this doesn't work 335create table rewritemetoo3 (a rewritetype); 336alter type rewritetype alter attribute a type varchar cascade; 337 338drop table rewriteme; 339drop event trigger no_rewrite_allowed; 340drop function test_evtrig_no_rewrite(); 341 342-- test Row Security Event Trigger 343RESET SESSION AUTHORIZATION; 344CREATE TABLE event_trigger_test (a integer, b text); 345 346CREATE OR REPLACE FUNCTION start_command() 347RETURNS event_trigger AS $$ 348BEGIN 349RAISE NOTICE '% - ddl_command_start', tg_tag; 350END; 351$$ LANGUAGE plpgsql; 352 353CREATE OR REPLACE FUNCTION end_command() 354RETURNS event_trigger AS $$ 355BEGIN 356RAISE NOTICE '% - ddl_command_end', tg_tag; 357END; 358$$ LANGUAGE plpgsql; 359 360CREATE OR REPLACE FUNCTION drop_sql_command() 361RETURNS event_trigger AS $$ 362BEGIN 363RAISE NOTICE '% - sql_drop', tg_tag; 364END; 365$$ LANGUAGE plpgsql; 366 367CREATE EVENT TRIGGER start_rls_command ON ddl_command_start 368 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE start_command(); 369 370CREATE EVENT TRIGGER end_rls_command ON ddl_command_end 371 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE end_command(); 372 373CREATE EVENT TRIGGER sql_drop_command ON sql_drop 374 WHEN TAG IN ('DROP POLICY') EXECUTE PROCEDURE drop_sql_command(); 375 376CREATE POLICY p1 ON event_trigger_test USING (FALSE); 377ALTER POLICY p1 ON event_trigger_test USING (TRUE); 378ALTER POLICY p1 ON event_trigger_test RENAME TO p2; 379DROP POLICY p2 ON event_trigger_test; 380 381-- Check the object addresses of all the event triggers. 382SELECT 383 evtname, 384 pg_describe_object('pg_event_trigger'::regclass, oid, 0), 385 pg_identify_object('pg_event_trigger'::regclass, oid, 0), 386 pg_identify_object_as_address('pg_event_trigger'::regclass, oid, 0) 387 FROM pg_event_trigger 388 ORDER BY evtname; 389 390DROP EVENT TRIGGER start_rls_command; 391DROP EVENT TRIGGER end_rls_command; 392DROP EVENT TRIGGER sql_drop_command; 393