Get encrypted password for PostgreSQL login role

Question

Is there a way to fetch the encrypted password for a login roll from a PostgreSQL server?

To give some insight into my problem, I'm trying to manage the postgres user's password via Ansible. To do so, I would like to check the current value of the encrypted password (e.g. 'md5...') to see if it's current or not. If it is not, I would execute the appropriate ALTER ROLL command to update it.

I know I can use pg_dumpall to see the password, e.g:

$ pg_dumpall --roles-only
<snip>
CREATE ROLE postgres;
ALTER ROLE postgres WITH ... PASSWORD 'md5...';

But this doesn't seem like a very reliable way of doing so.

Answer 1

Try to read rolpassword field.

SELECT rolpassword FROM pg_authid

pg_authid

The catalog pg_authid contains information about database authorization identifiers (roles). A role subsumes the concepts of "users" and "groups". A user is essentially just a role with the rolcanlogin flag set. Any role (with or without rolcanlogin) can have other roles as members; see pg_auth_members.

Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.

Chapter 19 contains detailed information about user and privilege management.

Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.