Recover PostgreSQL password with admin access to host

Question

Good day.

I have a PostgreSQL server.

Once i had admin login and password, then i created a new login for web application (with low permissions like select form some tables etc). And i just lost my admin login and password.

I have access (administator) to host where the server is running. Server is windows 2008 server. Is there any way to recover admin permissions? May be i somehow could create another superuser?

Answer 1

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.

• Craig Ringer: A pg_ctl reload is sufficient, but it's sufficiently hard to do that on Windows that restarting the service via the Services control panel (services.msc) is going to be easier.