Check if a role in PostgreSQL has a password set

Question

I wonder how I can verify whether or not a role (users are just a specific kind of role) has a password set in PostgreSQL 9.1.

I tried the command \dg+ and \du+ but they don't show you anything password related. I also used the following query, but it doesn't help either because of its indifference (I'm quite sure that the postgresql user has no password set in this case):

SELECT * FROM pg_user;   usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig  ----------+----------+-------------+----------+-----------+---------+----------+----------+-----------  postgres |       10 | t           | t        | t         | t       | ******** |          |   aef      |    16201 | t           | t        | t         | t       | ******** |          |  

Answer 1

Passwords are stored in pg_shadow

In documentation:

Password (possibly encrypted); null if none. See pg_authid for details of how encrypted passwords are stored.

So you should select * from pg_shadow;

You should also check pg_authid table.