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
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 |