Thread: how to recover superuser password ?

My superuser (postgres) had a password.
I attempted to change it by doing (and not thinking very much):
template1=# alter user postgres with password '';
ALTER USER
template1=# \q

After what I am unable to connect anymore as superuser, which is a
big problem (plain [ENTER] on the password prompt is rejected)

Is there any way to change the superuser password without completely
deleting my postgreSQL installation which is in use in production ?
(I have complete unix root access)

Thanks to all in advance.
I did not found anything relevant online.

Patrick.

On Mon, Dec 02, 2002 at 05:34:04PM +0100, Patrick wrote:
> My superuser (postgres) had a password.
> I attempted to change it by doing (and not thinking very much):
> template1=# alter user postgres with password '';
> ALTER USER
> template1=# \q
>
> After what I am unable to connect anymore as superuser, which is a
> big problem (plain [ENTER] on the password prompt is rejected)
>
> Is there any way to change the superuser password without completely
> deleting my postgreSQL installation which is in use in production ?
> (I have complete unix root access)
>
> Thanks to all in advance.
> I did not found anything relevant online.

Change your pg_hba.conf to allow connections from the localhost as
"trust" (then kill -HUP the postmaster to notice the changes here).
Log in from localhost (you won't be prompted for a
password). Now you can change your password.

Be sure to change pg_hba.conf back to what it was!

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

On Mon, Dec 02, 2002 at 11:42:25AM -0500, Joel Burton took time to write:
> Change your pg_hba.conf to allow connections from the localhost as
> "trust" (then kill -HUP the postmaster to notice the changes here).
> Log in from localhost (you won't be prompted for a
> password). Now you can change your password.

Joel, thanks a lot !

The solution was obvious, I am an idiot.

Patrick.