Postgres database has no roles

Question

I recently installed postgres version 9.3.5 on osx 10.10 and I am unable to connect to it with psql. I started the server as my own user as follows:

jestep$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

With pg_ctl status I confirmed the server was running. However I cannot connect to the server regardless of what username I try.

jestep$ psql
psql: FATAL:  role "jestep" does not exist
jestep$ psql -U postgres
psql: FATAL:  role "postgres" does not exist
jestep$ psql -U _postgres
psql: FATAL:  role "_postgres" does not exist
jestep$ psql -U admin
psql: FATAL:  role "admin" does not exist
jestep$ psql -U root
psql: FATAL:  role "root" does not exist

Here are the contents of my pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

I have checked in /etc/passwd, and there is no user called postgres, though there is _postgres.

This happened to me once before on Ubuntu and I actually had to uninstall postgres and all extensions, reinstall them, and initialize a completely new database. Is there any less drastic approach I can take?

@wildplasser the issue at hand is that I cannot log onto the database--as anybody. Although the database is running, since it (apparently) has no roles at all there is no way to connect to it. — Jed Estep
– Jed Estep, Commented Mar 30, 2015 at 0:06
How did you create the db cluster? Manual initdb? If you ran initdb yourself then the superuser role will be the same name as the user who ran initdb, unless you passed an explicit -U parameter to initdb. If all else fails you should either (a) delete the datadir and re-initdb, if there's nothing on there you care about, or (b) start PostgreSQL in single-user mode and create a new role. — Craig Ringer
– Craig Ringer, Commented Mar 30, 2015 at 0:08
@wildplasser: I cannot log in as user 'postgres', it says the role does not exist. I mentioned this in the original post. @CraigRinger: I just initialized a new db in a new directory: initdb -D ~/.local/pgsql. The output told me that the server would be owned by user jestep. However I cannot connect to it as jestep or as postgres, both times it claims the user does not exist. — Jed Estep
– Jed Estep, Commented Mar 30, 2015 at 0:11
@CraigRinger a fresh initdb did the job. Maybe it took a second for the old process to shut down. Thanks for your help. — Jed Estep
– Jed Estep, Commented Mar 30, 2015 at 0:14

Craig Ringer · Accepted Answer · 2015-03-30 00:19:21Z

1

Solved this on @CraigRinger's advice by running a fresh initdb in a new directory.

Note: Deleting your data dir and running a new initdb will destroy all existing data, so it's not something you should do if there's anything in your PostgreSQL install you care about.

edited Mar 30, 2015 at 0:19

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Mar 30, 2015 at 0:15

Jed Estep

6051 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres database has no roles

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related