Обсуждение: RE: user creation time for audit

Поиск
Список
Период
Сортировка

RE: user creation time for audit

От
"Boyapalli, Kousal"
Дата:

Hi Team,

We are looking for the user creation date

 

Tried from both from pg admin and psql by using

select * from pg_catalog.pg_user  and /du,  /du+  we were able to get the users but is there  a way where we can get the creation date

 

 

Can any one please help/any suggestions

 

Thanks in Advance !

 

Freundliche Grüße/Kind regards

Kousal Boyapalli

Database Administrator

 

Kousal.Boyapalli.ext@uniper.energy

 

Assigned by,

F_OI5-D3 Support Cross Functions

Sales&Trading IT

Uniper

 

www.uniper.energy

Uniper IT GmbH, Holzstraße 6, 40221 Düsseldorf, Germany; Sitz/Registered Office: Düsseldorf, Amtsgericht/District Court Düsseldorf HRB 78258;Geschäftsführung/Managing Directors: Damian Bunyan, Hans Pezold

Postanschrift/Postal address: Uniper IT GmbH,E.ON Platz 1, D-40479  Düsseldorf

 

Please consider the environment before printing this email.

 

Вложения

Re: user creation time for audit

От
Julien Rouhaud
Дата:
On Mon, Aug 30, 2021 at 4:43 PM Boyapalli, Kousal
<Kousal.Boyapalli.ext@uniper.energy> wrote:
>
> We are looking for the user creation date
>
> Tried from both from pg admin and psql by using
>
> select * from pg_catalog.pg_user  and /du,  /du+  we were able to get the users but is there  a way where we can get
thecreation date
 

Postgres doesn't keep track of object creation date.  There are
multiple discussions about that in the mailing-list archives if you're
interested but mostly the reason is that it's not clear that a single
creation date would suits everyone's need (for instance when doing
backup/restore, should the creation date be reset or not?).

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.

Re: user creation time for audit

От
Vijaykumar Jain
Дата:
On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud <rjuju123@gmail.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.


 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


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

--
Thanks,
Vijay
Mumbai, India

Re: user creation time for audit

От
Julien Rouhaud
Дата:
On Mon, Aug 30, 2021 at 5:24 PM Vijaykumar Jain
<vijaykumarjain.github@gmail.com> wrote:
>
> On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud <rjuju123@gmail.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

Ah right, sorry about that.

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

You could also periodically check for new user creation, or write a
dedicated module using ProcessUtility_hook which could allow you to do
something for each CREATE/DROP/ALTER ROLE (or any other utility
statement).