Postgresql – Create event trigger as non-superuser in Postgres

amazon-dmsamazon-rdsddl-triggerpostgresql

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?

Best Answer

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

Related Solutions

Postgresql – How does postgres use the schema search path when making indexes

Given the following setup:

regress=> CREATE SCHEMA A;
CREATE SCHEMA
regress=> CREATE SCHEMA B;
CREATE SCHEMA
regress=> SET search_path = B, public;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

I cannot reproduce the problem you report in PostgreSQL 9.2:

regress=> SET search_path = A, B;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
regress=> CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

However, rather than using the search_path, it's safer to use explicit schema-qualification. For example, I'd re-write the above as:

regress=> RESET search_path;
RESET
regress=> SHOW search_path ;
  search_path   
----------------
 "$user",public
(1 row)

CREATE TABLE B.bar(email text);
CREATE UNIQUE INDEX b.index_bar_on_email ON b.bar USING btree (email);
CREATE TABLE A.bar(email text);
CREATE UNIQUE INDEX index_bar_on_email ON A.bar USING btree (email);

The indexes are automatically created in the schema of their associated table; see:

regress=> \di B.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 b      | index_bar_on_email | index | craig | bar
(1 row)

regress=> \di A.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 a      | index_bar_on_email | index | craig | bar
(1 row)

Update based on question change:

Yes, what you've shown does look like a Rails adapter issue. It's checking to see whether the index exists in any schema. It should be checking to see whether the first table of the given name in the search_path has the named index.

I would write the query differently. I'd leave off the join on pg_class entirely, instead using a cast to regclass to handle search_path resolution for me. I'd use the resulting oid to search for the index. Compare original, then updated, below. Note that the updated query does require search_path to be set first.

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'bar'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('b','a') )
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey |  oid  | amname 
--------------------+-------------+--------+-------+--------
 index_bar_on_email | t           | 1      | 28585 | btree
 index_bar_on_email | t           | 1      | 28592 | btree
(2 rows)

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, 'bar'::regclass::oid, am.amname
 FROM pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND 'bar'::regclass = d.indrelid
 AND i.relam = am.oid
 ORDER BY i.relname                                                              
;
      relname       | indisunique | indkey | regclass | amname 
--------------------+-------------+--------+----------+--------
 index_bar_on_email | t           | 1      | 28585    | btree
(1 row)

Postgresql – Schema information within Trigger

If the effect isn't required until a later point in time, I would execute the payload at that later point in time and not via trigger. This way you can collect multiple updates on the underlying table and avoid redundant calls of the DDL script.

Depending on your circumstances, a timestamp column or simple boolean flag in the underlying table might suffice to mark rows that should trigger delayed events.

is it possible to obtain the current schema from the underlying table ..

Yes. You can query practically any possible detail from the system catalogs or from the standardized (but much slower) information schema. Furthermore, in a trigger procedure you have the TG_TABLE_SCHEMA variable set.

This related answer has more information and an example:
How do I list all columns for a specified table

Best Answer

Related Solutions

Postgresql – How does postgres use the schema search path when making indexes

Postgresql – Schema information within Trigger

Related Question