0

I'm just starting with Postgres.

This is a fresh install of Postgres 9.5 on Ubuntu 14.04 LTS. I used the apt repo from: http://www.postgresql.org/download/linux/ubuntu/

The intro page http://www.postgresql.org/docs/9.5/static/tutorial-createdb.html says that "-U" should work. It doesn't:

chris@blue:~$ psql -U postgres
psql: FATAL:  Peer authentication failed for user "postgres"

Yet, if I "su" to postgres, all is well:

chris@blue:~$ sudo su - postgres
postgres@blue:~$ psql
psql (9.5.2)
Type "help" for help.

postgres=# 

What have I misunderstood?

  • 1
    What do the logs say? What does your config look like? – Jenny D says Reinstate Monica May 5 '16 at 12:45
  • 1
    chris is not postgres. – Michael Hampton May 5 '16 at 12:58
  • @MichaelHampton Doesn't "-U" set the user to something other than your unix username? "... you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name." – fadedbee May 5 '16 at 13:18
  • @JennyD "2016-05-05 12:49:39 BST [18183-1] postgres@postgres LOG: provided user name (postgres) and authenticated user name (chris) do not match" - What's the point of "-U" if the only allowed name is your unix login name? – fadedbee May 5 '16 at 13:22
  • The point of -U comes when you configure postgres to allow specific unix users to connect as specific postgres roles. – Henrik May 5 '16 at 14:23
1

psql -U is trying to work correctly. However, the way postgresql is trying to authenticate you is failing.

When you sudo to postgres, the psql command takes your identity from your sudo'ed shell. Without "-U", it tries to use the user ID and looks it up in the list of roles. It says 'hey, the client is running under the user id postgres! We can trust it!'

In both cases, it's the pg_hba.conf file that is controlling things. It's telling postgresql to trust a local user named 'postgres.' But otherwise, it is using 'peer' authentication.

With 'peer' authentication, it expects to see a database user ("role") with your name, and then it will authenticate you as that user (only!).

1

The tutorial only works when you install PostgreSQL from source. On your Ubuntu installation, access control was already set up, so not everything can log in (hence "authentication failed"). To follow the tutorial, try logging into the postgres user (sudo -u postgres -i) and then follow the steps in the tutorial.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.