Sep 11, 2011
tom
No Comments

Lost PostgreSQL Superuser Username and Password

Question

I have a PostgreSQL 8.4 database running on Windows, but I’ve lost the name of the superuser username, so am unable to connect.

I’ve already configured pg_hba.conf to use trust to allow connections without a password, but I still need to know the name of a valid user to login.

I’ve tried postgres, admin etc, but I always get:

psql: FATAL:  role "USERNAME" does not exist

Answer

You can try to run PostgreSQL in single-user mode. On my system (Linux), I’ve been able to get superuser sql shell with following command: sudo -u postgres postgres90 --single postgres -D /var/lib/postgresql/9.0/data/.

sudo -u postgres here is to drop priveledges (postgres refuses to run under priveledged account`, other params should be easy to understand.

In single-user shell you should be able to create new user or SELECT * FROM pg_user;

Check more discussion of this question.

Related posts:

  1. What’s the default superuser username/password for postgres after a new install?
  2. Cannot change password for user postgres in postgresql
  3. Recover PostgreSQL database from filesystem backup
  4. Installation of PostgreSQL 8.2 with PostgreSQL 8.4 on Ubuntu 10.10
  5. How can I get access to password hashing in postgresql? Tried installing postgresql-contrib in ubuntu, still can’t access hashing functions

Leave a comment