31

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       | ******** |          |
aef
  • 4,498
  • 7
  • 26
  • 44

1 Answers1

49

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.

jdiver
  • 2,228
  • 1
  • 19
  • 20
  • 1
    Note, you must be a PostgreSQL superuser to access those tables. On RDS you're an RDS_SUPERUSER which is not equivalent and you cannot access those tables. – Eloff Jul 28 '21 at 20:43
  • Checking pg_authid table also helps to determine if the passwords are stored encrypted (as they should be). – linux64kb Aug 22 '23 at 15:09