Re: Query on User account password change details

Ron <ronljohnsonjr@xxxxxxxxx> · Sat, 8 May 2021 19:55:48 -0500



    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.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 *
            ;
          
        
    -- 

      Angular momentum makes the world go 'round.