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_productionfail, as they attempt to write to an audit table created by the superuser.