Re: Create roles trigger

On Fri, Oct 5, 2018 at 4:26 PM Nasby, Jim <nasbyj(at)amazon(dot)com> wrote:

> Please do not post to multiple lists. Moving -hackers to BCC.
>
> > On Sep 27, 2018, at 4:07 AM, Александр Аникин <anikin24(at)list(dot)ru> wrote:
> >
> > Hey, folks!
> >
> > I've got a question about events on roles creation.
> >
> > I need to execute some custom logic on role creation.
> >
> > All information about roles located in `pg_authid` table.
> > The first thing that comes to mind is to create trigger on `pg_authid`
> table, but this is not possible, because this is a system table.
> > Next thing, is to create a view (select * from pg_authid) and create
> trigger on view, but this didn't work out too.
>
> Not sure what you were attempting with the view, but catalog tables are a
> special case, so those kind of tricks are straight-out not going to work.
>
> > One more thing is to create event trigger (CREATE EVENT TRIGGER trigger
> ON ddl_command_start WHEN TAG IN ('CREATE ROLE')), but it's also not
> possible, event triggers are not supported for 'CREATE ROLE'.
> >
> > Is there is any possibility to do that ? May be, create custom extension
> or something else.
>
> I would also love to have event triggers for global objects, but
> unfortunately that’s a very tough nut to crack, because changes to global
> objects could happen from any database in the entire system (which could
> easily number in the thousands). For global event triggers to work, there
> would need to be a global list of event triggers, including which database
> the event trigger was in. There would also need to be a way to fire those
> triggers (currently, a backend can not run anything in a separate
> database). You’d also face the challenge of whether the user running the
> event trigger had permission to connect to all of those databases.
>
> In short, there’s a lot of work left to be done in order to support what
> you’re looking for.
>
> BTW, if you only need auditing then you should take a look at pg_audit.

Perhaps if you share what sorts of things you're trying to do on role
creation, someone could provide a different method that may work for what
you're trying to do. For example, if you're just trying to manage
privileges, you could use the DEFAULT PRIVILEGES system to manage that.

/docs/current/static/sql-alterdefaultprivileges.html

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com