Search code examples
postgresqleventtrigger

Event trigger to change owner on CREATE

I'm trying to create a PostgreSQL event trigger, that fires whenever a user creates anything (table, view, function, sequence...) and sets the owner of this newly created thing to a certain role.

So far I have the even trigger itself, that fires on any CREATE command:

CREATE EVENT TRIGGER setOwnerToMyRole ON ddl_command_end
WHEN tg_tag LIKE 'CREATE%'
EXECUTE PROCEDURE setOwnerToMyRole();

But I am having problems with the function itself:

CREATE FUNCTION setOwnerToMyRole() RETURNS event_trigger LANGUAGE plpgsql
AS $$ BEGIN
    ALTER <type> <name> OWNER TO myRole 
END; $$;

How do I get the type (as in table, view, etc.) and how do i get the name of the newly created thing?

edit: Looking at this question and this question and of course CREATE EVENT TRIGGER and Trigger Procedures, this is currently not really possible :(

Solution

  • Guess, I should answer this question properly and not just in an edit: What I want is currently not possible. Perhaps a future update to Postgres will add more functionality to eventtriggers.

    Sources: