9

I just installed PostgreSQL 9.4 on Ubuntu 15.10.

  1. I created a user with createuser -P myuser
  2. I created a database with createdb -O myuser mydatabase
  3. I edited pg_hba.conf and added local mydatabase myuser md5
  4. I restarted PostgreSQL with sudo service postgresql restart

User myuser is a PostgresSQL user only and has no user account on Ubuntu.

When I try to connect to the database with psql -W mydatabase myuser it fails with psql: FATAL: Peer authentication failed for user "myuser".

PostgreSQL is running …

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2016-03-03 09:53:00 CET; 9min ago
  Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 22219 (code=exited, status=0/SUCCESS)

Mar 03 09:53:00 SERVER01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 03 09:53:00 SERVER01 systemd[1]: Started PostgreSQL RDBMS.

... and listening.

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:postgresql    *:*                     LISTEN
tcp6       0      0 localhost:postgresql    [::]:*                  LISTEN
Active UNIX domain sockets (only servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     151534   /var/run/postgresql/.s.PGSQL.5432

What do I have to do to connect with user myuser to database mydatabase?

12

In a fresh install from a few days ago, the second line of my pg_hba.conf is

local   all             all              peer

I believe this is the one that makes your connection attempt fail.

The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there is no second try, so the connection attempt will likely fail. Or, as the documentation states:

There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

The solution is easy: either remove the above line if you don't plan to use peer authentication, or move your specific rule above this one.

  • Moving the line worked. – Daniel Mar 3 '16 at 9:17
1

First... check that you have the lines permissioning to the myuser user in pg_hba.conf. For example:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Or any other lines of permission to IPV4 (and IPv6 if you use) with: TYPE DATABASE USER ADDRESS METHOD

After this check, run the psql as follows:

psql -h localhost -U myuser mydatabase

And then, the requested prompt, enter the user's password myuser.

  • 1
    And of course, remove the peer authentication. – Alvaro Neto Mar 4 '16 at 14:54
  • Have an instance I updated to allow remote connections, the listen address is set to '*' and added the host all all 0.0.0.0/0 md5 rule. Didn't have to remove the peer auth. Just worked with the -h localhost param. Best answer since you most likely don't have to touch the config on a stock install. – Mark Carpenter Jr Jul 7 '18 at 11:56

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.