Re: Query on User account password change details

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On May 7, 2021, at 4:47 PM, Vipin Madhusoodanan <vipin.madhusoodanan@xxxxxxxxx> wrote:

Can someone help with suggestions or ideas for a workaround to achieve this? 

You can audit the table and log when the user change their password.  Once you have that information you can easily determine if the user has not changed their password over a given period of time.

1. Create a table with the current password hash:

select usename

 , usesysid

 , passwd

 , now() as audit_date

  into passwd_audit

from pg_shadow;

alter table passwd_audit

  add constraint spasswd_audit_pkey

  primary key (usesysid, audit_date)

;

2. Create a view that will show which passwords have changed since last audited

create or replace view passwd_audit_report

as

select s.usename

  , s.usesysid

  , s.passwd

  , now() as audit_date

from pg_shadow s

join (

  select pa.usesysid

    , pa.passwd

  from passwd_audit pa

  join ( 

    select usesysid

      , max(audit_date) as audit_date

    from passwd_audit

  group by usesysid

  ) idx on idx.usesysid = pa.usesysid

    and idx.audit_date is not distinct from pa.audit_date

) a on a.usesysid = s.usesysid

 and a.passwd is distinct from s.passwd

;

3. Run the view periodically to find changed passwords and record them in the audit table (daily/hourly/etc).

insert into passwd_audit

select * 

from passwd_audit_report

returning *

;