PostgreSQL event trigger to force extension installations into particular schema

Question

I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without specify particular schema.

So this should fail

CREATE EXTENSION tablefunc;

but this should succeed:

CREATE EXTENSION tablefunc SCHEMA extensions;

For this purpose, I tried creating event trigger:

CREATE EVENT TRIGGER e010_verify_extension_schema_et
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

and in verify_extension_schema() function I am trying to make use of pg_catalog.pg_event_trigger_ddl_commands(). However, the function does not return anything I can use to check if schema has been specified or which schema for that matter...

Does anyone have any ideas how I could accomplish this?

Evan Carroll · Accepted Answer · 2017-08-09T19:54:11.823

Make sure you're using ddl_command_end, it can still fail or remind users which is what you want. From the docs,

Similarly, if a ddl_command_end trigger fails with an error, the effects of the DDL statement will be rolled back, just as they would be in any other case where the containing transaction aborts.

Unfortunately, you have two options after that

Process it possibly making assumptions, but only knowing the place in which the extension was installed. For instance, CREATE EXTENSION x WITH SCHEMA CURRENT_SCHEMA and CREATE EXTENSION x will be conflated. Simple access to the command executed isn't available because some simple commands issue multiple commands (ex, serial being a macro).
Process the internal pg_ddl_command with a C function.

Because the second option sounds like less joy, let's go with the first.

Check out the list under Capturing Changes at Command End which supports objid being returned from the SRF pg_event_trigger_ddl_commands(). Note, schema_name is not what you're after.

CREATE OR REPLACE FUNCTION verify_extension_schema()
RETURNS event_trigger
AS $$
DECLARE
  schemafail bool;
BEGIN
  schemafail = (
    SELECT n.nspname = 'public'
    FROM pg_event_trigger_ddl_commands() AS ev
    INNER JOIN pg_catalog.pg_extension AS e
      ON ev.objid = e.oid
    INNER JOIN pg_catalog.pg_namespace AS n
      ON e.extnamespace = n.oid
  );
  IF schemafail THEN
    RAISE EXCEPTION 'Creating extensions into "public" is disabled';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER nag_my_users
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

Now you can

test=# CREATE EXTENSION intarray ;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA public;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA foo;
CREATE EXTENSION

If you just want a notification, you can change it to RAISE NOTICE

PostgreSQL event trigger to force extension installations into particular schema

1 Answers1