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

Поиск
Список
Период
Сортировка

Query on User account password change details

От
Vipin Madhusoodanan
Дата:
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. 

Please advise.

Thank you,
Vipin
--
Thanks,
Vipin
 

Re: Query on User account password change details

От
Bruce Momjian
Дата:
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.




Re: Query on User account password change details

От
Holger Jakobs
Дата:


Am 6. Mai 2021 21:52:00 MESZ schrieb Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>:
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. 

Please advise.

Thank you,
Vipin
--
Thanks,
Vipin
 

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


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

Re: Query on User account password change details

От
Vijaykumar Jain
Дата:

Yes auditing is a major issue.
end to end encryption too is not very straightforward.

Sadly, we had our databases managed via configuration management system, which also dictated role creation, db access, pg_hba changes etc.
the git history of cfg mgmt tool was our audit :)

Basically, we did not allow any admin to make any changes locally, but use the cfg mgmt tool to make any access changes.
The newer versions are integrating hashicorp vault to manage roles and access, and audit is still managed externally.


On Fri, 7 May 2021 at 01:42, Holger Jakobs <holger@jakobs.com> wrote:


Am 6. Mai 2021 21:52:00 MESZ schrieb Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>:
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. 

Please advise.

Thank you,
Vipin
--
Thanks,
Vipin
 

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


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -


--
Thanks,
Vijay
Mumbai, India

Re: Query on User account password change details

От
Vipin Madhusoodanan
Дата:
But still admins have the ability to change it. 
For AD accounts we have full  control and sufficient data for audit purposes. But we have md5 password authenticated local PostgreSQL users due to application dependencies and for these users we are having challenges. Does feeding md5 encrypted keys into a central table on a daily basis and comparing the results to identify password change will be a viable solution? Will these feature can be expected one next releases?

Thank you,
Vipin

On Thu, May 6, 2021 at 3:58 PM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

Yes auditing is a major issue.
end to end encryption too is not very straightforward.

Sadly, we had our databases managed via configuration management system, which also dictated role creation, db access, pg_hba changes etc.
the git history of cfg mgmt tool was our audit :)

Basically, we did not allow any admin to make any changes locally, but use the cfg mgmt tool to make any access changes.
The newer versions are integrating hashicorp vault to manage roles and access, and audit is still managed externally.


On Fri, 7 May 2021 at 01:42, Holger Jakobs <holger@jakobs.com> wrote:


Am 6. Mai 2021 21:52:00 MESZ schrieb Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>:
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. 

Please advise.

Thank you,
Vipin
--
Thanks,
Vipin
 

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


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -


--
Thanks,
Vijay
Mumbai, India
--
Thanks,
Vipin
 

Re: Query on User account password change details

От
Ron
Дата:
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.



Re: Query on User account password change details

От
Scott Ribe
Дата:
> 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. 


Re: Query on User account password change details

От
Ron
Дата:
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.



Re: Query on User account password change details

От
Scott Ribe
Дата:
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





Re: Query on User account password change details

От
Bruce Momjian
Дата:
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.




Re: Query on User account password change details

От
Ron
Дата:
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.



Re: Query on User account password change details

От
Tom Lane
Дата:
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



Re: Query on User account password change details

От
Vipin Madhusoodanan
Дата:
Can someone help with suggestions or ideas for a workaround to achieve this? 

Thank you, 
Vipin 

On Thu, May 6, 2021, 4:18 PM Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com> wrote:
But still admins have the ability to change it. 
For AD accounts we have full  control and sufficient data for audit purposes. But we have md5 password authenticated local PostgreSQL users due to application dependencies and for these users we are having challenges. Does feeding md5 encrypted keys into a central table on a daily basis and comparing the results to identify password change will be a viable solution? Will these feature can be expected one next releases?

Thank you,
Vipin

On Thu, May 6, 2021 at 3:58 PM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

Yes auditing is a major issue.
end to end encryption too is not very straightforward.

Sadly, we had our databases managed via configuration management system, which also dictated role creation, db access, pg_hba changes etc.
the git history of cfg mgmt tool was our audit :)

Basically, we did not allow any admin to make any changes locally, but use the cfg mgmt tool to make any access changes.
The newer versions are integrating hashicorp vault to manage roles and access, and audit is still managed externally.


On Fri, 7 May 2021 at 01:42, Holger Jakobs <holger@jakobs.com> wrote:


Am 6. Mai 2021 21:52:00 MESZ schrieb Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>:
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. 

Please advise.

Thank you,
Vipin
--
Thanks,
Vipin
 

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


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -


--
Thanks,
Vijay
Mumbai, India
--
Thanks,
Vipin
 

Re: Query on User account password change details

От
Laurenz Albe
Дата:
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




Re: Query on User account password change details

От
Rui DeSousa
Дата:



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

Re: Query on User account password change details

От
Ron
Дата:
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.

Re: Query on User account password change details

От
Bruce Momjian
Дата:
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.




Re: Query on User account password change details

От
Rui DeSousa
Дата:

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




Re: Query on User account password change details

От
Vipin Madhusoodanan
Дата:
Thanks much Rui and All for the valuable inputs. Rui's suggested solution would be sufficient for our audit requirement. 

Thank you, 
Vipin 

On Sat, May 8, 2021, 7:59 PM Rui DeSousa <rui@crazybean.net> wrote:


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