Postgresql – Recover PostgreSQL password with admin access to host

postgresql

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?

Best Answer

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.

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Postgresql – Can’t get past password lock on fresh install of postgreSQL

Try to add the option -U postgres to your createdb command. The error clearly states that PostgreSQL tried to execute it with the current Windows username, while you set superuser password (presumably) for user postgres.

Best Answer

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

Postgresql – Can’t get past password lock on fresh install of postgreSQL

Related Question