Reset password for 'postgres' on v9.3 (Win Srv)

Question

I'm trying to reset the password of the user 'postgres' on a postgres 9.3 installation, but have been unable to do so...

I've tried every combination in pg_hba.conf I could find online (ex. 'host all all localhost trust'), but when I run 'psql -U postgres' or try to connect via pgAdmin III it asks for a password. If I leave it blank I get an error saying 'psql: fe_sendauth: no password supplied'

Running on a Windows Server 2012 machine.

We are suspecting a corrupt DB/filesystem by the way, so that might have something to do with it, but if you have any suggestions...

I just tried with

host all all localhost trust

at the top of pg_hba.conf, with everything else commented out. It complained about “no entry for host ::1”, so I added

host all all ::1/128 trust

Then I get the error

FATAL: role "postgres" does not exist

when running psql -U postgres. Nothing happens with telnet localhost 5432.

Answer 1

Then you will have to start PostgreSQL in single-user mode. Stop the PostgreSQL service. Then, from cmd.exe, run

C:\Program Files\PostgreSQL\9.3\bin\postgres --single -D C:\Program Files\PostgreSQL\9.3\data postgres

(You have to replace the path to the postgres.exe executable and to the data directory with the correct values.)

Then look for the name of the bootstrap superuser:

SELECT rolname FROM pg_authid WHERE oid = 10

And change the password for that user:

ALTER ROLE superuser_name PASSWORD 'new_password'

Then exit with CTRL+Z. Now you should be able to start PostgreSQL normally and login with that user.

If you run into problems with that (for example, if pg_authid is corrupted), you might have to restore your backup.