ALTER EVENT TRIGGER as non superuser

From: Andreas Ulbrich <andreas(dot)ulbrich(at)matheversum(dot)de>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: ALTER EVENT TRIGGER as non superuser
Date: 2015-06-02 20:46:23
Message-ID: 556E161F.4010008@matheversum.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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

Browse pgsql-general by date

  From Date Subject
Next Message Robert Haas 2015-06-02 21:09:13 Re: Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Previous Message Andreas Ulbrich 2015-06-02 20:40:16 Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT