Is there a way to break into a PostgreSQL database if you forgot the password?

Question

I have a client that has a PostgreSQL database and he cannot remember the password that we used when the database was setup. Is there a way to recover that information so I do not have to blow away his database and start from scratch?

The database is running on a PC.

Answer 1

Step 1: Edit PostgreSQL config file to establish trust relationship to login without password:

vi /var/lib/pgsql/data/pg_hba.conf

Old Line:

local all postgres password

Change it to:

local all postgres trust

Step 2: Restart PostgreSQL Server:

service postgresql restart

Step 3: Change password:

psql -U postgres template1 -c alter user postgres with password ‘newpassword’;

Step 4: Password has been updated. Revert back the original settings of config file:

vi /var/lib/pgsql/data/pg_hba.conf

Old Line:

local all postgres trust

Change it to:

local all postgres password

Step 5: Restart server and use your new password to access PostgreSQL Server.

service postgresql restart

Source

Answer 2

For 9.2, in windows:

Stop the service:

Net stop postgresql-x64-9

Modify the config file, change data/pg_hba.conf, basically md5 to trust:

host all all 127.0.0.1/32 trust

host all all ::1/128 trust

Start the service:

Net start postgresql-x64-9

Execute the sql statement to set your desired password, on a sql console or using psql:

alter user postgres with password ‘newpassword’;

Put back the original config file.