Home / server / Questions / 775009
Accepted
fadedbee
Asked: 2016-05-06 04:31:43 +0800 CST

Why doesn't "psql -U" work for me?

  • 772

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?

postgresql psql ubuntu-14.04

2 Answers

  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.

    • 1
  2. Best Answer

    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