Event trigger and CREATE/ALTER ROLE/USER

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

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

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

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

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

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

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

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

On 14 Sep. 2016 9:44 am, "Alvaro Herrera" <alvherre@2ndquadrant.com> 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.

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

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

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