PostgreSQL: Peer authentication failed for user

Ever since Oracle took the ownership of MySQL, I’ve been leaning more and more toward PostgreSQL, or other NoSQL database like MongoDB.

Also, I’m not completely new to PostgreSQL, but the fact that I only have to touch it once in awhile, makes me feel PostgreSQL is so hard to use and missing the goold-oldie MySQL.

Anyway, this is the issue I had:

# su - postgres
-bash-4.1$ psql -l
                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres-db | postgres | UTF8     | en_US.UTF8  | en_US.UTF8  |
 puppetdb    | puppetdb | UTF8     | en_US.UTF8  | en_US.UTF8  |
 template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
(5 rows)

-bash-4.1$ psql -d puppetdb -U puppetdb --password
Password for user puppetdb:
psql: FATAL:  Peer authentication failed for user "puppetdb"

To fix it, we need to modify pg_hba.conf, which is located in the /var/lib/pgsql/9.3/data/ directory on my system.

Change it to:

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

And of course, restarting the PostgreSQL service for the change to take effect.

Reference:

• etting error: Peer authentication failed for user “postgres”