pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate roles trigger
4+ messages / 4 participants
[nested] [flat]
* Create roles trigger
@ 2018-09-27 09:07 Александр Аникин <anikin24@list.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Александр Аникин @ 2018-09-27 09:07 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-admin; +Cc: borey2005@gmail.com
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.
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create roles trigger
@ 2018-10-05 20:26 Nasby, Jim <nasbyj@amazon.com>
parent: Александр Аникин <anikin24@list.ru>
0 siblings, 2 replies; 4+ messages in thread
From: Nasby, Jim @ 2018-10-05 20:26 UTC (permalink / raw)
To: Александр Аникин <anikin24@list.ru>; +Cc: borey2005@gmail.com <borey2005@gmail.com>; pgsql-admin
Please do not post to multiple lists. Moving -hackers to BCC.
> On Sep 27, 2018, at 4:07 AM, Александр Аникин <anikin24@list.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create roles trigger
@ 2018-10-05 20:32 Scott Whitney <scott@journyx.com>
parent: Nasby, Jim <nasbyj@amazon.com>
1 sibling, 0 replies; 4+ messages in thread
From: Scott Whitney @ 2018-10-05 20:32 UTC (permalink / raw)
To: Александр Аникин <anikin24@list.ru>; Nasby, Jim <nasbyj@amazon.com>; +Cc: borey2005@gmail.com <borey2005@gmail.com>; pgsql-admin
Honestly, this is a perfect use-case for calling shell scripts.
________________________________
From: Nasby, Jim <nasbyj@amazon.com>
Sent: Friday, October 5, 2018 3:26:22 PM
To: Александр Аникин
Cc: borey2005@gmail.com; pgsql-admin@postgresql.org
Subject: Re: Create roles trigger
Please do not post to multiple lists. Moving -hackers to BCC.
> On Sep 27, 2018, at 4:07 AM, Александр Аникин <anikin24@list.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.
[http://journyx.com/emailImages/ScottWhitney.png]
[http://journyx.com/emailImages/linkedin.png]<https://www.linkedin.com/company/journyx-inc/; [http://journyx.com/emailImages/twitter.png] <https://www.twitter.com/journyxinc;
[http://journyx.com/emailImages/bluebar.jpg]
Scott Whitney
Director of IT
[http://journyx.com/emailImages/journyx.jpg]
scott@journyx.com<mailto:scott@journyx.com> | +1 (800) 755-9878
7600 Burnet Road, Ste. 300
Austin, TX 78757
Visit us at www.journyx.com<https://www.journyx.com/;
To unsubscribe from Journyx promotional emails, click here <http://journyx.com/communication-preferences; or visit http://journyx.com/communication-preferences.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create roles trigger
@ 2018-10-05 20:43 Keith Fiske <keith.fiske@crunchydata.com>
parent: Nasby, Jim <nasbyj@amazon.com>
1 sibling, 0 replies; 4+ messages in thread
From: Keith Fiske @ 2018-10-05 20:43 UTC (permalink / raw)
To: nasbyj@amazon.com; +Cc: anikin24@list.ru; borey2005@gmail.com; pgsql-admin
On Fri, Oct 5, 2018 at 4:26 PM Nasby, Jim <nasbyj@amazon.com> wrote:
> Please do not post to multiple lists. Moving -hackers to BCC.
>
> > On Sep 27, 2018, at 4:07 AM, Александр Аникин <anikin24@list.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.
https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-10-05 20:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-27 09:07 Create roles trigger Александр Аникин <anikin24@list.ru>
2018-10-05 20:26 ` Nasby, Jim <nasbyj@amazon.com>
2018-10-05 20:32 ` Scott Whitney <scott@journyx.com>
2018-10-05 20:43 ` Keith Fiske <keith.fiske@crunchydata.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox