Today, I was being asked whether I know any methods on how to retrieve or reset Postgres password. Although we had done some research but the provided solutions are quite confusing and not giving detailed explanations. We just left the research aside and continue with our work but I still feel this is doable. The reason is forgetting passwords is a common problem for every users and I believe there is a way to retrieve or reset the password. Even games also have their cheat codes and tricks

So, I decided to find the workaround at home since postgres is installed in my personal laptop. The postgres version that I’m using is 8.2 and installed in Windows environment. Let’s venture every step with detailed explanations:

>> Further Investigation (using ‘trust’)

1)      Edit pg_hba.conf which is located in <postgres_home>/data directory for the following line:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

2) Start the command prompt via All Programs > PostgreSQL or just access to the /bin directory of the PostgreSQL installation directory:

C:\Program Files\PostgreSQL\8.2\bin>

3) Access to the database

C:\Program Files\PostgreSQL\8.2\bin> psql template1 –U postgres

4) Note that postgres is case sensitive. You need to have the following format to access to the table ‘pg_shadow’ or ‘pg_authid’ system catalog that list all the usernames and passwords:

SELECT * from pg_shadow;

template1=# SELECT * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd| valuntil | useconfig
———-+———-+————-+———-+———–+———————
—————-+———-+———–
postgres | 10 | t | t | t | md532e12f215ba27cb750c9e093ce4b5127 | |

(1 row)

Note:
You can see that the password has been encrypted in md5.

5) Reset the password to ‘password’

template1=# ALTER USER postgres WITH PASSWORD ‘password’;

6) Restart postgres

Alternative Workaround

Crack the Postgres password hashes with MDCrack – http://pentestmonkey.net/blog/cracking-postgres-hashes/

>> Further Zooming

Why edit pg_hba.conf to ‘trust’?

Most of the workaround will be providing the solution by editing the pg_hba.conf which is located in <postgres_home>/data directory but I would like to know the difference between ‘md5’ and ‘trust’.

1)      Start a command prompt via All Programs > PostgreSQL or just access to the /bin directory of the PostgreSQL installation directory:

C:\Program Files\PostgreSQL\8.2\bin>

2)      Access to the database

C:\Program Files\PostgreSQL\8.2\bin> psql template1 –U postgres

3)      Enter password for user postgres:

Note: If you had set to ‘trust’ in pg_hba.conf, you will not be prompted to enter password

What are other Authentication Methods?
There is a list for postgres authentication methods:
• trust
• password
• Kerberos
• Ident-based
• LDAP authentication
• PAM authentication

Documentation: http://www.postgresql.org/docs/8.2/interactive/auth-methods.html

How User Password Encryption Works?

My previous post related to user password encryption (general idea):

• Encryption of User Passwords