Re: Query on User account password change details

> On May 7, 2021, at 4:47 PM, Vipin Madhusoodanan <vipin(dot)madhusoodanan(at)gmail(dot)com> 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 *
;