Thread: RE: user creation time for audit
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
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.
Attachment
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.
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.
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).