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

postgresql ubuntu-14.04 psql

9,085

Solution 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!).

Solution 2

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.

9,085

fadedbee

Updated on September 18, 2022

Comments

fadedbee over 1 year
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?
- Jenny D almost 8 years
  
  What do the logs say? What does your config look like?
- Michael Hampton almost 8 years
  
  chris is not postgres.
- fadedbee almost 8 years
  
  @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 almost 8 years
  
  @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?
- Henrik supports the community almost 8 years
  
  The point of -U comes when you configure postgres to allow specific unix users to connect as specific postgres roles.
- Jenny D almost 8 years
  
  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 almost 8 years
  
  You need to configure the access rules. Have a look at postgresql.org/docs/9.5/static/auth-pg-hba-conf.html .