pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery on User account password change details
20+ messages / 9 participants
[nested] [flat]
* Query on User account password change details
@ 2021-05-06 19:52 Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Vipin Madhusoodanan @ 2021-05-06 19:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-06 20:10 Bruce Momjian <bruce@momjian.us>
parent: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: Bruce Momjian @ 2021-05-06 20:10 UTC (permalink / raw)
To: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-06 20:12 Holger Jakobs <holger@jakobs.com>
parent: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
1 sibling, 2 replies; 20+ messages in thread
From: Holger Jakobs @ 2021-05-06 20:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org,Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 -
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-06 20:58 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 1 reply; 20+ messages in thread
From: Vijaykumar Jain @ 2021-05-06 20:58 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-06 21:18 Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Vipin Madhusoodanan @ 2021-05-06 21:18 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 05:40 Ron <ronljohnsonjr@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 1 reply; 20+ messages in thread
From: Ron @ 2021-05-07 05:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 12:30 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Scott Ribe @ 2021-05-07 12:30 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 standards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the kind of defense that might work in an audit.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 13:55 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 20+ messages in thread
From: Ron @ 2021-05-07 13:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 standards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the kind 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=#
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 13:59 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: Scott Ribe @ 2021-05-07 13:59 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 14:10 Bruce Momjian <bruce@momjian.us>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 20+ messages in thread
From: Bruce Momjian @ 2021-05-07 14:10 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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, and standards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the kind 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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 14:14 Ron <ronljohnsonjr@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 20+ messages in thread
From: Ron @ 2021-05-07 14:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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, and standards bodies are dropping it. So, unlike many other things we might consider pointless, with this one, you have the kind 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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 14:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 20+ messages in thread
From: Tom Lane @ 2021-05-07 14:41 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-07 20:47 Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
parent: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Vipin Madhusoodanan @ 2021-05-07 20:47 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; pgsql-admin@lists.postgresql.org; +Cc: Holger Jakobs <holger@jakobs.com>
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
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-08 03:44 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2021-05-08 03:44 UTC (permalink / raw)
To: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; Vijaykumar Jain <vijaykumarjain.github@gmail.com>; pgsql-admin@lists.postgresql.org; +Cc: Holger Jakobs <holger@jakobs.com>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-08 13:37 Bruce Momjian <bruce@momjian.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 20+ messages in thread
From: Bruce Momjian @ 2021-05-08 13:37 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; Vijaykumar Jain <vijaykumarjain.github@gmail.com>; pgsql-admin@lists.postgresql.org, Holger Jakobs <holger@jakobs.com>
On Sat, May 8, 2021 at 05:44:52AM +0200, Laurenz Albe wrote:
> 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.
Here is a blog entry about using cerrificate authenticaiton and rotating them:
https://momjian.us/main/blogs/pgblog/2020.html#July_17_2020
The certificates must have expiration dates.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-09 00:49 Rui DeSousa <rui@crazybean.net>
parent: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Rui DeSousa @ 2021-05-09 00:49 UTC (permalink / raw)
To: Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>; +Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>; Holger Jakobs <holger@jakobs.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 *
;
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-09 00:55 Ron <ronljohnsonjr@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 2 replies; 20+ messages in thread
From: Ron @ 2021-05-09 00:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-09 00:58 Bruce Momjian <bruce@momjian.us>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: Bruce Momjian @ 2021-05-09 00:58 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-09 00:58 Rui DeSousa <rui@crazybean.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Rui DeSousa @ 2021-05-09 00:58 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Query on User account password change details
@ 2021-05-09 03:46 Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 20+ messages in thread
From: Vipin Madhusoodanan @ 2021-05-09 03:46 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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.
>
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2021-05-09 03:46 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-06 19:52 Query on User account password change details Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
2021-05-06 20:10 ` Bruce Momjian <bruce@momjian.us>
2021-05-06 20:12 ` Holger Jakobs <holger@jakobs.com>
2021-05-06 20:58 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-05-06 21:18 ` Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
2021-05-07 20:47 ` Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
2021-05-08 03:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-05-08 13:37 ` Bruce Momjian <bruce@momjian.us>
2021-05-09 00:49 ` Rui DeSousa <rui@crazybean.net>
2021-05-09 00:55 ` Ron <ronljohnsonjr@gmail.com>
2021-05-09 00:58 ` Bruce Momjian <bruce@momjian.us>
2021-05-09 00:58 ` Rui DeSousa <rui@crazybean.net>
2021-05-09 03:46 ` Vipin Madhusoodanan <vipin.madhusoodanan@gmail.com>
2021-05-07 05:40 ` Ron <ronljohnsonjr@gmail.com>
2021-05-07 12:30 ` Scott Ribe <scott_ribe@elevated-dev.com>
2021-05-07 13:55 ` Ron <ronljohnsonjr@gmail.com>
2021-05-07 13:59 ` Scott Ribe <scott_ribe@elevated-dev.com>
2021-05-07 14:10 ` Bruce Momjian <bruce@momjian.us>
2021-05-07 14:14 ` Ron <ronljohnsonjr@gmail.com>
2021-05-07 14:41 ` Tom Lane <tgl@sss.pgh.pa.us>
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