Postgresql – Why doesn’t “psql -U” work for me

postgresqlpsqlubuntu-14.04

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?

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

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – psql empty database before restore

There are two options here - if your backup is plain text then you add the -c option to pg_dump - ie

pg_dump -c mydb > mydb.sql

Otherwise if your using -Fc or -Ft on pg_dump then you use the -c flag on pg_restore

pg_dump -Ft -b mydb > db.tar

pg_restore -c -d newdb db.tar

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Postgresql – psql empty database before restore

Related Topic