Обсуждение: Query on User account password change details

On Thu, May  6, 2021 at 02:52:00PM -0500, Vipin Madhusoodanan wrote:
> Hi Team,
> 
> Please advise on the possibilities to retrieve “last password change date”  for
> a PostgreSQL user account. We have an audit requirement to identify the
> password change details for local PostgreSQL user accounts. We are able to
> track AD users using AD Group Policy, but unable to fetch these details for
> local user accounts. Tried to explore pg_users and pg_shadow catalog views, but
> this information was not available. 

Yep, it's not available.  If you are authenticating Postgres using AD,
you don't really change the password in Postgres, right?  You can just
check the AD date.  I don't think triggers work on system tables, so I
don't think that is an option.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 5/6/21 3:12 PM, Holger Jakobs wrote:
[snip]
> Actually, opposed to the opinion of people having lived under a stone for 
> the last couple of years, it's absolutely not advisable to have a regular 
> password changing scheme.
>
> These were in fashion in the 1990s and early 2000s

Comments like this are indicative of someone who's never been through an 
external audit.

-- 
Angular momentum makes the world go 'round.

> On May 6, 2021, at 11:40 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Comments like this are indicative of someone who's never been through an external audit.

While maybe true, the point stands that even the original source of the requirement has admitted it's a bad idea, and
standardsbodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the
kindof defense that might work in an audit. 

On 5/7/21 7:30 AM, Scott Ribe wrote:
>> On May 6, 2021, at 11:40 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Comments like this are indicative of someone who's never been through an external audit.
> While maybe true, the point stands that even the original source of the requirement has admitted it's a bad idea, and
standardsbodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the
kindof defense that might work in an audit.
 

The problem is that Postgresql allows Really Short Passwords without 
uttering a peep, and that's not defensible to an auditor.

psql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# create role foo password 'a';
CREATE ROLE
postgres=#


-- 
Angular momentum makes the world go 'round.

On May 7, 2021, at 7:55 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> The problem is that Postgresql allows Really Short Passwords without uttering a peep, and that's not defensible to an
auditor.

fair point

On Fri, May  7, 2021 at 08:55:15AM -0500, Ron wrote:
> On 5/7/21 7:30 AM, Scott Ribe wrote:
> > > On May 6, 2021, at 11:40 PM, Ron <ronljohnsonjr@gmail.com> wrote:
> > > 
> > > Comments like this are indicative of someone who's never been through an external audit.
> > While maybe true, the point stands that even the original source of the requirement has admitted it's a bad idea,
andstandards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have
thekind of defense that might work in an audit.
 
> 
> The problem is that Postgresql allows Really Short Passwords without
> uttering a peep, and that's not defensible to an auditor.
> 
> psql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))
> Type "help" for help.
> 
> postgres=# create role foo password 'a';
> CREATE ROLE
> postgres=#

Have you considered passwordcheck?

    https://www.postgresql.org/docs/13/passwordcheck.html

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 5/7/21 9:10 AM, Bruce Momjian wrote:
> On Fri, May  7, 2021 at 08:55:15AM -0500, Ron wrote:
>> On 5/7/21 7:30 AM, Scott Ribe wrote:
>>>> On May 6, 2021, at 11:40 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>>>>
>>>> Comments like this are indicative of someone who's never been through an external audit.
>>> While maybe true, the point stands that even the original source of the requirement has admitted it's a bad idea,
andstandards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have
thekind of defense that might work in an audit.
 
>> The problem is that Postgresql allows Really Short Passwords without
>> uttering a peep, and that's not defensible to an auditor.
>>
>> psql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))
>> Type "help" for help.
>>
>> postgres=# create role foo password 'a';
>> CREATE ROLE
>> postgres=#
> Have you considered passwordcheck?
>
>     https://www.postgresql.org/docs/13/passwordcheck.html

This might satisfy my own audit requirements!

-- 
Angular momentum makes the world go 'round.

Bruce Momjian <bruce@momjian.us> writes:
> On Fri, May  7, 2021 at 08:55:15AM -0500, Ron wrote:
>> The problem is that Postgresql allows Really Short Passwords without
>> uttering a peep, and that's not defensible to an auditor.

> Have you considered passwordcheck?
>     https://www.postgresql.org/docs/13/passwordcheck.html

BTW, this is a perfect example of why obsolete auditing rules actually
are a net negative to security.  The only way passwordcheck can enforce
anything about the password's strength is if the server gets to see the
cleartext password.  In these days of SCRAM, requiring that is in
itself bad practice: the cleartext password ought never leave the
client's machine.

            regards, tom lane

On Fri, 2021-05-07 at 15:47 -0500, Vipin Madhusoodanan wrote:
> Can someone help with suggestions or ideas for a workaround to achieve this? 
> 
> > > > > Please advise on the possibilities to retrieve “last password change date”  for a PostgreSQL user account.
> > > > >  We have an audit requirement to identify the password change details for local PostgreSQL user accounts.

You cannot do that unless you want to modify PostgreSQL.

For requirements like this, the recommendation is *not* to use passwords
in the database for authentication.  Use one of the other authentication
methods that uses an external authority.

Identity management systems specialize in that kind of thing, and you may
be able to get that information from there.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

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 *

;

On Sat, May  8, 2021 at 07:55:48PM -0500, Ron wrote:
> The problem with this scheme is that any role with SUPERUSER privs can modify
> that table.

I think only external auditing can detect what superusers do:

    https://momjian.us/main/blogs/pgblog/2019.html#January_30_2019

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

> On May 8, 2021, at 8:55 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> The problem with this scheme is that any role with SUPERUSER privs can modify that table.
>

True; and if that is a concern then audit the table from a different system where those superusers do not have access
to.