Обсуждение: Event trigger and CREATE/ALTER ROLE/USER
Simple question: Is there any reason for event trigger to not support CREATE/ALTER ROLE/USER? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Wed, Sep 14, 2016 at 9:26 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > Simple question: Is there any reason for event trigger to not support > CREATE/ALTER ROLE/USER? Because it performs a global administrative task, no? -- Michael
>> Simple question: Is there any reason for event trigger to not support >> CREATE/ALTER ROLE/USER? > > Because it performs a global administrative task, no? So is there any technical difficulty? Can you please elaborate? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Wed, Sep 14, 2016 at 9:34 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: >>> Simple question: Is there any reason for event trigger to not support >>> CREATE/ALTER ROLE/USER? >> >> Because it performs a global administrative task, no? > > So is there any technical difficulty? I don't recall the exact details but... > Can you please elaborate? Roles are global objects, and event triggers cannot operate on such ones. See for example 296f3a60 mentioning for example why REVOKE/GRANT can only operate on local objects. -- Michael
>>>> Simple question: Is there any reason for event trigger to not support >>>> CREATE/ALTER ROLE/USER? >>> >>> Because it performs a global administrative task, no? >> >> So is there any technical difficulty? > > I don't recall the exact details but... > >> Can you please elaborate? > > Roles are global objects, and event triggers cannot operate on such > ones. See for example 296f3a60 mentioning for example why REVOKE/GRANT > can only operate on local objects. Thanks. I will look into it. Pgpool-II has its own password file which needs to be sync with the md5 hashed password of PostgreSQL. So I thought it would be nice if the event trigger could be used for the purpose. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Tatsuo Ishii wrote: > Simple question: Is there any reason for event trigger to not support > CREATE/ALTER ROLE/USER? As I understand the issue, the main reason is that event triggers execute procedures, and those exist in a single database only. If you were to create an event trigger in database A, then a user gets created in database B, your function would not be invoked, which becomes a problem. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
<p dir="ltr"><p dir="ltr">On 14 Sep. 2016 9:44 am, "Alvaro Herrera" <<a href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>wrote:<br /> ><br /> > Tatsuo Ishii wrote:<br/> > > Simple question: Is there any reason for event trigger to not support<br /> > > CREATE/ALTERROLE/USER?<br /> ><br /> > As I understand the issue, the main reason is that event triggers<br /> >execute procedures, and those exist in a single database only. If you<br /> > were to create an event trigger indatabase A, then a user gets created<br /> > in database B, your function would not be invoked, which becomes a<br />> problem.<br /><br /> Yeah... You'd need something at the C level as a hook because you can't rely on pg_proc etc.<pdir="ltr">For BDR I've been thinking of optionally replicating such actions using a ProcessUtility_hook and deparseor simply verbatim sql capture. Then replicating via generic logical wal messages.
> As I understand the issue, the main reason is that event triggers > execute procedures, and those exist in a single database only. If you > were to create an event trigger in database A, then a user gets created > in database B, your function would not be invoked, which becomes a > problem. Can't we just create the event trigger in database B as well? We have been living with similar situation, for example, functions for years. (a work around would be creating functions in template1. This only works for freshly created database though). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Wed, Sep 14, 2016 at 11:09 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: >> As I understand the issue, the main reason is that event triggers >> execute procedures, and those exist in a single database only. If you >> were to create an event trigger in database A, then a user gets created >> in database B, your function would not be invoked, which becomes a >> problem. > > Can't we just create the event trigger in database B as well? We have > been living with similar situation, for example, functions for years. > (a work around would be creating functions in template1. This only > works for freshly created database though). Just a random thought: wouldn't what you are looking for be more reliable if you use the utility hook and have it loaded via preload_shared_libraries? -- Michael