How to know when Postgresql password is changed

My question is how to know the date when a postgresql user password is changed.
Is it possible?
I know postgresql has account expiry feature,
but I'm asked to check if users have changed their passwords and to get the date.

Regards,

I tried pg_shadow etc. but they seem to have no date info.

You can't. PostgreSQL does not record the time at which user information was updated.

The internal table storing this info is pg_authid. The only timestamp column there is rolvaliduntil, which does not help you.

The best you can do is determine the transaction ID that changed that user the most recently. This might not be the same as the one that changed the password. You can get this with:

select xmin from pg_authid where rolname = 'theuser';

You can compare this to SELECT txid_current() to get a very rough idea of how long ago it might've been changed, in terms of the server's transaction rate. If your tx rate is very bursty this won't do you any good. It might also be cleared (set to FrozenXID) if transaction ID wrap-around has happened on a busy server. I do not recommend relying on this method for anything even remotely interesting.

You can't determine who performed the transaction that made the change.

I imagine that a patch to add a last-change timestamp to pg_authid would probably be accepted by the community if you were to submit one to PostgreSQL 9.5. Raise this on pgsql-hackers. You will need to know some C programming and be ready to read some documentation, or to pay a developer to write it for you.

For the problem you are working on it would be more typical to have a higher layer in the application keeping track of audit information like this, where users only change their passwords through the app.

StackExchange Database Administrators Q#91252, answer score: 4