4

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
four-eyes
  • 10,740
  • 29
  • 111
  • 220
  • 2
    Please don't post the same copy and paste question to tons of sites. This seems to be posted to http://superuser.com/q/916655/167160, http://dba.stackexchange.com/q/101893/7788 and http://stackoverflow.com/q/30325623/398670 – Craig Ringer May 19 '15 at 13:14

1 Answers1

9

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:

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for your answer. I just realised that I have a horrible typo in there. I tried with `_postgres` which gave me the error message. – four-eyes May 19 '15 at 12:58
  • @Stoffer: My answer still applies. I was about to ask why you did not get another error message, which is clear now. – Erwin Brandstetter May 19 '15 at 13:03
  • Erwin, I understand the problem and I dont. Sorry, I am very new databases and just got started. I underatand that there is a `db user` which would be `postgresql` and I understand that there is a system user `_postgresql` or `postgresql_`. I can log into the `db` with `$ psql -U postgres` because its the corresponding role to the `db`. Then it becomes tricky. I do not underatand the problem you describe under `peer authentication`... – four-eyes May 19 '15 at 13:15
  • Thanks! That worked without error. Could you explain why that worked? Do I have a `user` now for the `db postgres` or is it a user for which i can create `databases` now? Or, do you have a good, comprehensive document for someone who just started learning `psql` where all the difference are described?! I feel like lost in translation/lacking some crucial definitions of words... The documentation is not very helpful because it assumes already some knowledge. – four-eyes May 19 '15 at 13:24
  • @Stoffer: Peer authentication allows password-less access if the name of the OS user and the DB user match. Follow the links I provided for more explanation. DB roles are for whole DB clusters: Detailed explanation: http://stackoverflow.com/questions/24918367/grant-privileges-for-a-particular-database-in-postgresql/24923877#24923877 – Erwin Brandstetter May 19 '15 at 13:24
  • Thanks for updating and taking your time! The so created user `christoph`. Is that a super user or just another role to log in do the `db` where the `db role` `postgres` has access to? – four-eyes May 19 '15 at 13:48
  • @Stoffer: Plain user. Read the fine manual [here](http://www.postgresql.org/docs/current/interactive/sql-createrole.html) and [here](http://www.postgresql.org/docs/current/interactive/app-createuser.html). – Erwin Brandstetter May 19 '15 at 13:51
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78208/discussion-between-stoffer-and-erwin-brandstetter). – four-eyes May 19 '15 at 13:54