Could not connect to database postgres: FATAL: role"_postgres" does not exist

Question

Working on OS X 10.10, installed postgreSQL and PostGIS from here, psql vers 9.3.5. I am having a hard time getting postgreSQL running.

I installed the packages as admin on my computer. My username is christoph

The only way I can log in is via:

$ psql -U postgres

I want to create user called christoph, as my admin name on the computer. I tried (from the terminal, without being "logged in into psql"):

$ sudo -u postgres createuser christoph
> sudo: unknown user: postgres

Then I read a little and tried (from the terminal, without being "logged in into psql"):

$ sudo -u _postgres createuser christoph
> Password: ****
> Could not connect to database postgres: FATAL: role "_postgres" does not exist

Why isn that working?

Answer 1

On recent version of OS X and with some installation methods the system user is created with a '_' prepended or appended to 'postgres', so the name of your system user is postgres_ or _postgres in your case, not 'postgres'. I don't use OS X, so I don't know what drives them to do this. Seems like they want to adhere to a naming schema for system accounts. Not to be confused with the Postgres DB user (login role) of the name postgres. This mismatch causes all sorts of confusion. At least people become aware of the different meaning of some syntax elements ...

That's why you can log into Postgres via:

$ psql -U postgres

postgres is the DB role here, not the OS user.

But this won't work:

$ sudo -u postgres

Because there is no OS user of that name. Try instead:

$ sudo -u _postgres

But then peer authentication still won't work, because there is no DB user of the same name _postgres. Related answer:

• Postgres user does not exist?

The authentication method activated by default in standard installations is peer authentication, where a system user on the local system has password-less access to a database role of the same name. That explains the last error message:

Could not connect to database postgres: FATAL: role "_postgres" does not exist

Your system tried to log into the DB with the same name as your current OS user using peer authentication, which fails due to the naming mismatch.

Your last command should work like this:

$ sudo -u _postgres createuser -U postgres christoph

The added -U postgres is an option to createuser specifying the DB role to log in with.

You still have to enter the password. I would consider using an entry in the a .pgpass file for password-less access, while the system user is different from the supposedly associated DB role.

Related:

• Login Failed with Existing User on PostgreSQL
• Run batch file with psql command without password