1

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?

fadedbee
  • 2,068
  • 5
  • 24
  • 36
  • 1
    What do the logs say? What does your config look like? – Jenny D May 05 '16 at 12:45
  • 1
    `chris` is not `postgres`. – Michael Hampton May 05 '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 05 '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 05 '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 supports the community May 05 '16 at 14:23
  • Please don't post log entries or configuration as a comment. Instead, us the "edit" link below your question to edit it into the question. – Jenny D May 05 '16 at 18:15
  • You need to configure the access rules. Have a look at http://www.postgresql.org/docs/9.5/static/auth-pg-hba-conf.html . – Jenny D May 05 '16 at 18:21

2 Answers2

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.

Peter Eisentraut
  • 3,665
  • 1
  • 24
  • 21
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!).

Andrew Wolfe
  • 126
  • 3