Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lfXjk-0003uZ-9O for pgsql-admin@arkaria.postgresql.org; Sun, 09 May 2021 00:56:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lfXjj-0006ZF-5X for pgsql-admin@arkaria.postgresql.org; Sun, 09 May 2021 00:55:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lfXji-0006Z6-Oy for pgsql-admin@lists.postgresql.org; Sun, 09 May 2021 00:55:58 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lfXjc-0006Lp-7o for pgsql-admin@lists.postgresql.org; Sun, 09 May 2021 00:55:58 +0000 Received: by mail-oi1-x22d.google.com with SMTP id v24so12463042oiv.9 for ; Sat, 08 May 2021 17:55:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=tsqmydhGXKw5HtXb1M/ouAUvhAlSrPZGMB+n7OC0kn4=; b=PoMSxP1ltW+PGtvKlf1KP5TfiYcCRHA20wgU3J6aF4bPu4HoW7j27Kk2guNn671P1r AGu5ApoVakk4aPz0Y2XJoCjJ5y9NwQAbbOZ2diofVsinswL1anMrTjRbg0Bq+JzrcpJU XOEhzi5Xdmzg46ia4YBtBw8sRTDLgXSRCnEJbvH9LD3ZgAGQnsmdCCf9ZxTEZBIvq4kk vd2fXptZCXN0XX5SDe5sga2nZ+N7DrAInoHi40I62PwdMbn/18kVCZQl1eUhu6RFuuuE ZjCa9NLt5xHu0373y4xrGGgTMkeMxN0RIWMPsIcSz9EyHFT3jm7JQe2f+F6GnuINmQd6 3Mlw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=tsqmydhGXKw5HtXb1M/ouAUvhAlSrPZGMB+n7OC0kn4=; b=b62XPA5CU0KWzRNXMVvTHt9V6i/afd42WBPBtWTr59TfZL5MjiQT63NHwSrVqPMz3D RTSt9TVw2EHwEJP1/bVZ6UTEHdQ5sRvTLOBEy6jM5cVF0EhM3HZ6MMrxB8U3g6Q+SYb5 dS4wJ1jaD28lPhkUz/Jpm82s0GUCSbJVU7YXZH0KfJb53Kgu1RFhU7dus1i4TcXuzqoC aa3a1+hECKsYFnu7f4UoraetwEwY66loybpbKNjO03dGEakwhnhnt1RO2L0DSBPG9LvF teOt4xadgkpELCUtKt1SLvswqnhFJJ3gFZLPISl3KGhz50duWjuBAsl8UtSvjm9FLlb6 2xXw== X-Gm-Message-State: AOAM5309FP0WCcrqk8FAkQPbzvNWrTe9T5OLAS7/OGfRKJRk2rcxdoAb 2KTe4kH3MEwUUTcAjC2CmzcYIiH5ngU= X-Google-Smtp-Source: ABdhPJxEKt6e4eMwTKI1PAKl0emAmmSj7DRpD5MYGASz9kUhu1JeBmZkzRq+u1RwbAoedQ2rSI8T3A== X-Received: by 2002:aca:47d2:: with SMTP id u201mr20218146oia.19.1620521749824; Sat, 08 May 2021 17:55:49 -0700 (PDT) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id g26sm2202371otr.37.2021.05.08.17.55.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 08 May 2021 17:55:49 -0700 (PDT) Subject: Re: Query on User account password change details To: pgsql-admin@lists.postgresql.org References: <90B6B3A1-BB95-4D69-99EF-D86C0A859431@jakobs.com> From: Ron Message-ID: <7b066839-cebe-e716-d7e2-0c052bdc0656@gmail.com> Date: Sat, 8 May 2021 19:55:48 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------8B56FA06E0C5BA0AA28F839B" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------8B56FA06E0C5BA0AA28F839B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 >> > 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. --------------8B56FA06E0C5BA0AA28F839B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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@gmail.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.
--------------8B56FA06E0C5BA0AA28F839B--