ALTER EVENT TRIGGER as non superuser - Mailing list pgsql-general

From Andreas Ulbrich
Subject ALTER EVENT TRIGGER as non superuser
Date
Msg-id 556E161F.4010008@matheversum.de
Whole thread Raw
List pgsql-general
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



pgsql-general by date:

Previous
From: Andreas Ulbrich
Date:
Subject: Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT
Next
From: Robert Haas
Date:
Subject: Re: [HACKERS] Re: 9.4.1 -> 9.4.2 problem: could not access status of transaction 1