Unable to connect to PostgreSQL database – Peer authentication failed

Support - Assistance technique
1

Hi everyone,

I’m trying to connect DHIS2 to a PostgreSQL database named test, but I keep running into a connection issue. When I attempt to connect via psql, I get the following error:

paul@tracker:~$ lxc exec postgres – bash
root@postgres:~# psql -U test -d test

psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: Peer authentication failed for user “test”

Here is some context about my setup:

Containers:

lxc list
±---------±--------±-------------------±-----±----------±----------+
| NAME | STATE | IPV4 | IPV6 | TYPE | SNAPSHOTS |
±---------±--------±-------------------±-----±----------±----------+
| test | RUNNING | 17x.x.x.x (eth0) | | CONTAINER | 0 |
| dhis | RUNNING | 17x.x.x.x (eth0) | | CONTAINER | 0 |
| postgres | RUNNING | 17x.x.x.x (eth0) | | CONTAINER | 0 |
±---------±--------±-------------------±-----±----------±----------+

DHIS2 dhis.conf:

Database connection for PostgreSQL

connection.dialect = org.hibernate.dialect.PostgreSQLDialect
connection.driver_class = org.postgresql.Driver
connection.url = jdbc:postgresql://17x.x.x.x/viva
connection.username = test
connection.password = test123

PostgreSQL databases:

postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype
-----------±---------±---------±----------------±--------±--------
test | app | UTF8 | libc | C.UTF-8 | C.UTF-8
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8

pg_hba.conf:

“local” is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

IPv6 local connections:

host all all ::1/128 scram-sha-256

Allow replication connections

local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

Custom connection for user ‘test’

host test test 17x.x.x.x/32 scram-sha-256

I suspect this may be related to the authentication method (peer vs scram-sha-256) in PostgreSQL, but I’m not sure how to fix it
Has anyone faced a similar issue connecting DHIS2 to PostgreSQL when using peer authentication, and how did you resolve it?

Thanks in advance for any guidance!

2

Try

psql -U test -d -h 127.0.0.1 test

3

Hi @jason,

Should I run this from the host paul@tracker:~$ or within postgres container?

Thanks in advance

4

Inside the Postgres container.
And noticed a typo. :smiley:

psql -U test -h 127.0.0.1 -d test

5

Hi @jason

Thanks again for your help.

I have one more question to better understand the pg_hba.conf setup:

In your suggestion you used 127.0.0.1 (IPv4 local connections), which matches this rule:

host all all 127.0.0.1/32 scram-sha-256

My question is: when should I use the custom rule instead?

host test test 17x.x.x.x/32 scram-sha-256

Specifically, in which cases is it appropriate to connect using 127.0.0.1 (loopback inside the container) versus using the container’s actual IP address (17x.x.x.x) with a dedicated database/user rule?

6

Well, it depends on what you are trying to achieve. Your original question was based on trying to access Postgres from within the container using psql. If you need an external app (like DHIS2) to connect to the database from an external container, then you would need to use the IP address (e.g. 17x.x.x.x).

7

Hi @jason,

Thanks a lot for the clear explanation.

I appreciate your time and support.