Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Michael Paquier]

On Wed, Sep 14, 2016 at 11:09 AM, Tatsuo Ishii  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Tatsuo Ishii]

> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Craig Ringer]

On 14 Sep. 2016 9:44 am, "Alvaro Herrera"  wrote:
>
> 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.

Yeah... You'd need something at the C level as a hook because you can't
rely on pg_proc etc.

For BDR I've been thinking of optionally replicating such actions using a
ProcessUtility_hook and deparse or simply verbatim sql capture. Then
replicating via generic logical wal messages.

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Alvaro Herrera]

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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Tatsuo Ishii]

 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Michael Paquier]

On Wed, Sep 14, 2016 at 9:34 AM, Tatsuo Ishii  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Tatsuo Ishii]

>> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Michael Paquier]

On Wed, Sep 14, 2016 at 9:26 AM, Tatsuo Ishii  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Event trigger and CREATE/ALTER ROLE/USER

[Tatsuo Ishii]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers