Re: Query on User account password change details

The problem with this scheme is that any role with SUPERUSER privs can
modify that table.

On 5/8/21 7:49 PM, Rui DeSousa wrote:
>
>
>
>> On May 7, 2021, at 4:47 PM, Vipin Madhusoodanan
>> <vipin(dot)madhusoodanan(at)gmail(dot)com <mailto: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 *
> ;
>

--
Angular momentum makes the world go 'round.