I afraid there is a bug in ALTER EVENT TRIGGER:
I run the following script:
\set ON_ERROR_ROLLBACK ON
BEGIN;
CREATE FUNCTION event_trigger_function() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'event_trigger_function';
END$$ LANGUAGE plPgSQL;
CREATE EVENT TRIGGER a_ddl_trigger ON ddl_command_end
EXECUTE PROCEDURE event_trigger_function();
SET LOCAL ROLE super;
CREATE EVENT TRIGGER a_ddl_trigger ON ddl_command_end
EXECUTE PROCEDURE event_trigger_function();
RESET ROLE;
CREATE EVENT TRIGGER other_ddl_trigger ON ddl_command_end
EXECUTE PROCEDURE event_trigger_function();
-- disable trigger and change the function again, index is corrupt; reindex
\dy+ a_ddl_trigger
ALTER EVENT TRIGGER a_ddl_trigger DISABLE;
SELECT SESSION_USER, CURRENT_USER;
\du andreas
\dy+ a_ddl_trigger
ROLLBACK;
\set ON_ERROR_ROLLBACK interactive
An get the following output:
andreas@localhost:testdb=>\i t_reindexfn.sql
BEGIN
CREATE FUNCTION
psql:t_reindexfn.sql:13: ERROR: permission denied to create event
trigger "a_ddl_trigger"
HINT: Must be superuser to create an event trigger.
SET
CREATE EVENT TRIGGER
RESET
psql:t_reindexfn.sql:19: ERROR: permission denied to create event
trigger "other_ddl_trigger"
HINT: Must be superuser to create an event trigger.
List of event triggers
Name | Event | Owner | Enabled | Procedure |
Tags | Description
---------------+-----------------+-------+---------+------------------------+------+-------------
a_ddl_trigger | ddl_command_end | super | enabled |
event_trigger_function | |
(1 row)
ALTER EVENT TRIGGER
session_user | current_user
--------------+--------------
andreas | andreas
(1 row)
List of roles
Role name | Attributes | Member of
-----------+------------+------------------------------------
andreas | | {super,adminusr,admindb,developer}
List of event triggers
Name | Event | Owner | Enabled | Procedure
| Tags | Description
---------------+-----------------+-------+----------+------------------------+------+-------------
a_ddl_trigger | ddl_command_end | super | disabled |
event_trigger_function | |
(1 row)
ROLLBACK
What I did:
I created a event trigger function event_trigger_function()
I tried to create an event trigger -- failed, because I'm not a superuser
Set role to super
Create an event trigger -- success
Reset role
I tried again to create an event trigger -- failed, because I'm not a
superuser
\dy shows the trigger is present and enabled
Diable the trigger -- success although I'm not a superuser
SELECT SESSION_USER, CURRENT_USER; shows the user
\du andreas shows the attributes
\dy shows the trigger is disabled
Or where is my mistake?
I tried this in a snapshot of 9.5 (May, 22th) and in 9.4.0
Documantation of ALTER EVENT TRIGGER says: "You must be superuser to
alter an event trigger."
Regards Andreas