Re: user creation time for audit

On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

>
> The easy way around that is to track those events yourself with the
> rules that suit your needs, which can be done easily using an event
> trigger.
>

Please correct me if I am missing anything, but the doc said, event
triggers are not allowed on global objects.
PostgreSQL: Documentation: 13: 39.2. Event Trigger Firing Matrix
<https://www.postgresql.org/docs/current/event-trigger-matrix.html>

test=# create function test_event_trigger() returns event_trigger as $$
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$ language plpgsql;
CREATE FUNCTION
Time: 7.621 ms
test=# create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('create table', 'create role')
execute procedure test_event_trigger();
*ERROR: event triggers are not supported for create role*
Time: 0.214 ms
test=# create table x(id int); drop table x;
CREATE TABLE
Time: 7.932 ms
DROP TABLE
Time: 2.002 ms
test=# create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('create table')
execute procedure test_event_trigger();
CREATE EVENT TRIGGER
Time: 8.744 ms
test=# create table x(id int); drop table x;
NOTICE: test_event_trigger: ddl_command_start CREATE TABLE
CREATE TABLE
Time: 7.878 ms
DROP TABLE
Time: 3.489 ms

ref: postgres/event_trigger.sql at master · postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/master/src/test/regress/sql/event_trigger.sql#L56>

Anyways, I think the options were using external mechanisms to role audits,
or pgaudit via statement logging ?

--
Thanks,
Vijay
Mumbai, India