1

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.

5
  • After changing pg_hba.conf did you restart the PostgreSQL server or use pg_ctl reload? As for corruption: Read and act on this now: wiki.postgresql.org/wiki/Corruption Sep 2, 2014 at 2:37
  • I restart the postgres windows service after I edit the file.. I also set up a test server and installed postgres 9.3 on that with the same results. Could no reset the password. Sep 2, 2014 at 6:11
  • What does telnet localhost 5432 respond to you? Sep 2, 2014 at 6:56
  • When you edited pg_hba.conf, did you put the new/altered line first? It needs to be, because the first matching line is used. Sep 2, 2014 at 7:08
  • Just tried with 'host all all localhost trust' at the top, 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' Sep 2, 2014 at 7:20

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.