How to get a user's password in PostgreSQL?

Question

I am logged into my database as superuser postgres.

postgres=# SELECT *  FROM pg_user;
+----------+----------+-------------+----------+---------+--------------+----------+----------+-----------+
| usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig |
+----------+----------+-------------+----------+---------+--------------+----------+----------+-----------+
| postgres |       10 | t           | t        | t       | t            | ******** | (null;)  | (null;)   |
| test     |    24763 | f           | f        | f       | f            | ******** | (null;)  | (null;)   |
+----------+----------+-------------+----------+---------+--------------+----------+----------+-----------+
(2 rows)

I have listed all users in the database. How can I get the password for the plain user test?

Answer 1

The password is encrypted according to the documentation:

The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. The method of encryption is determined by the configuration parameter password_encryption. If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption (since the system cannot decrypt the specified encrypted password string, to encrypt it in a different format). This allows reloading of encrypted passwords during dump/restore.

So, you won't be able to get the original password of the normal user. What's stored in the system is an encryption (e.g. MD5) of the original password. If you already know the password, its encrypted value will match. But you cannot get the password that generated the encrypted value. That's the point of encrypting the password.

As admin, you can ALTER the user's password, but cannot get the password from the encrypted version.

-- Update --

To get the encryptedvalue of the password for the user test, you can do:

SELECT * FROM pg_authid WHERE rolname='test';

For example:

SELECT rolname, rolpassword FROM pg_authid where rolname='test';

generates:

test | SCRAM-SHA-256$4096:O4JqOPBA9uDbytmsgvzcdA==$LN5pfo59nHr19nTDb1LX+21JK/UgQZoWDTFP8Tw2z3E=:Ciq8DY2pz8I2BxGGV2sq3VE6i1E30en0OdDD94Jlij4=

Source

Answer 2

So, may be not a direct answer to the question. But I still decided to put it here, may be it'll help someone.

I have PostgreDB instance, running in Docker Container. And also forgotten what was my password for the default administrative login, which in my case was named 'postgres'.

I remembered that I specified it when creating the container itself in docker run command. There is a way to show a docker run command, that was originally used to create a Docker Container. You just open Docker GUI and in the tab with containers for the required container with PostgreSQL click three dots in the right corner (Actions column), and in the opened context menu select "Copy Docker run":

Then just paste it somewhere else and checkout password in it