Re: Query on User account password change details

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(dot)madhusoodanan(at)gmail(dot)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(dot)github(at)gmail(dot)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(at)jakobs(dot)com> wrote:
>>
>>>
>>>
>>> Am 6. Mai 2021 21:52:00 MESZ schrieb Vipin Madhusoodanan <
>>> vipin(dot)madhusoodanan(at)gmail(dot)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
>
>