From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Query on User account password change details
Date: Sat, 8 May 2021 19:55:48 -0500
Message-ID: <7b066839-cebe-e716-d7e2-0c052bdc0656@gmail.com> (raw)
In-Reply-To: <E3977E87-876C-4417-BB61-F305AEA202A8@crazybean.net>
References: <CAPOO3u4cxoCmJsXJaMHCLavecb-Bc77=qh=dfQN+bbYJ6gneWA@mail.gmail.com>
<90B6B3A1-BB95-4D69-99EF-D86C0A859431@jakobs.com>
<CAM+6J95bx6yUwv-rw4Cxft68YkxiQHKWDOW0LLNbymbt6538Kw@mail.gmail.com>
<CAPOO3u7Y=_suBOaJuZZdB=rMYRtekQZO8=ZsEatDoR+cqZb3DQ@mail.gmail.com>
<CAPOO3u4-McoLHk06fR6h6EhYMjarK7aNX-tLXLyq927hG-SByQ@mail.gmail.com>
<E3977E87-876C-4417-BB61-F305AEA202A8@crazybean.net>
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 <mailto: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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Query on User account password change details
In-Reply-To: <7b066839-cebe-e716-d7e2-0c052bdc0656@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox