PostgreSQL: Reset root password
I had an old installation of postgreSQL on my Win32 XP box. I never really used it until today when I was forced to ditch MySQL in favor of postgreSQL. But the classical problem now: I had forgotten the password for my default postgres account. Since I had no other user(role) created, this meant had no choice but to forcefully reset my postgres password.
Unfortunately people at postgreSQL seem to be suffering from a widespread tendency of treating a windows user as someone who needs to be punished for committing the cardinal sin of opting windows. I remember several of my batchmates having had their brains fried trying to install postgreSQL on their windows machines (before I stepped in to save them, of course :D).
Anyway, enough digression! Here is how it worked out for me:
- Get the server running:
pg_ctl.exe start -D "C:\Program Files\PostgreSQL\8.3\data"
- Open the file
"C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf"
Initial Content:
# TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
Changed to:
# TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 trust sameuser # IPv6 local connections: host all all ::1/128 trust sameuser
- reload the server config:
pg_ctl.exe reload -D "C:\Program Files\PostgreSQL\8.3\data"
- run:
psql -U postgres
At this point if you get this error:
psql: FATAL: role "postgres" does not exist
do this:
createuser.exe -s postgres
- run this to start command line client:
psql -U postgres
- run SQL to change password:
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';
Note that the inverted commas around the password are required but are not a part of the password
- Rollback the changes made in step 2
- reload the server config again as in step 3
- Curse the postgre config for one last time
- Get going!
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
#host all all 127.0.0.1/32 trust sameuser
# IPv6 local connections:
host all all ::1/128 md5
#host all all ::1/128 trust sameuser
Informative post. I really enjoyed it.
I am a windows user, can I use the steps outlinned above to reset my root password?
If so, where do I start.
Andrew, the steps described above are for windows so starting from step 1 would be my suggestion 🙂
Hello
i want to mention that the last line worked for me:
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD ‘mypassword’;
Thank You 🙂
Assuming you don’t need the databases contained therein, would removing and reinstalling postgreSQL (and/or pgAdmin) be sufficient?
THANKS A LOT! (yes, i’m yelling ;))
I got the following error at step 6 … any ideas on how to fix this?
Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
it doesnt work when executing: createuser.exe -s postgres
Helpful, works in 9.0 too. Thanks
Reblogged this on Eknaprasath.
Awesome blog post dude. Like the effort.