3

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
Improve this question
3
  • 1
    Hmm, there should be 'postgres' user - it's there by default and it's hard to remove it, as it owns a lot of system tables/views/templates. Is that exact message you are getting? Is "USERNAME" being replaced by actual username you're entering?
    – rvs
    Sep 11, 2011 at 7:29
  • "USERNAME" is being replaced by the actual username :)
    – Cocowalla
    Sep 11, 2011 at 7:32
  • Ok, sorry - just checking. :)
    – rvs
    Sep 11, 2011 at 7:38

1 Answer 1

Reset to default
11

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;

Improve this answer
2
  • Perfect, I was able to start the server in single-user mode, then use that to create a new superuser role
    – Cocowalla
    Sep 11, 2011 at 8:04
  • +1 Incidentally, this solved my problem of creating roles headlessly in a Vagrant provisioning script (i.e., sudo -E -u postgres createuser --createdb --login --superuser vagrant; sudo -E -u postgres createdb vagrant).
    – msanford
    Jan 13, 2014 at 20:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .