Create event trigger as non-superuser in Postgres

Question

Is it possible to give a role or a set of attributes to a non-superuser in postgres that would allow the user to create an EVENT TRIGGER? Simple example of what (obviously) doesn't work in psql:

=> CREATE OR REPLACE FUNCTION do_nothing()
->   RETURNS event_trigger
->  LANGUAGE plpgsql
->   AS $$
$> BEGIN
$>   SELECT 1;
$> END;
$> $$;
CREATE FUNCTION

=> CREATE EVENT TRIGGER do_nothing_on_ddl_change ON ddl_command_start
->    EXECUTE PROCEDURE do_nothing();
ERROR:  permission denied to create event trigger "do_nothing_on_ddl_change"
HINT:  Must be superuser to create an event trigger.

The root reason for needing this is that I'm using an RDS-backed Postgres instance, and attempting to setup streaming of that database (data and schema changes) into a Redshift instance. Using Amazons DMS services, setting this up is up is fairly straightforward - however, there's a catch: Starting up the streaming process creates a DDL audit table in the database, as well as an event trigger that captures any DDL changes. The trigger writes the changes to the audit table, and those changes are streamed into redshift.

Here's where the problems lie:

• A non-superuser (lets call it app_production, since this is part of a rails app) is responsible for making DDL changes to the db.

• If we use the non-superuser to create the data streaming task in AWS, that user is unable to create the required event triggers to stream schema changes.

• If we use the superuser to create the streaming task, then future schema migrations made by app_production fail, as they attempt to write to an audit table created by the superuser.

Any ideas?

Answer 1

I was just looking into this topic, found this question, and another one with the answer. I figured I'd link back to the original answer, and the RDS docs.

The short answer is:

• Yes you can, using the RDS master account.
• You have to delete the event triggers before upgrading between major versions.

Details:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.EventTriggers