Cannot connect to postgres DB listening on localhost.

[matrixbot]

This issue was originally created by @barkyq at https://github.com/matrix-org/dendrite/issues/3378.

Background information

• Dendrite version or git SHA: 0.13.7+46902e5
• SQLite3 or Postgres?: Postgres
• Running in Docker?: no
• go version: go version go1.22.2 linux/amd64
• Client used (if applicable): N/A

Description

• What is the problem: cannot connect to DB when postgres is listening on 127.0.0.1
• Who is affected: cannot start server
• How is this bug manifesting: cannot start server
• When did this first appear: after updating

Steps to reproduce

Default Postgres set-up listening on 127.0.0.1

in dendrite.yaml have the line

postgresql://dendrite:PASSWORD@127.0.0.1:5432/dendrite?sslmode=disable

and when I start the server with:

./bin/dendrite -config dendrite.yaml -http-bind-address 127.0.0.1:8008

it fails with the error:

PANI[2024-05-21T14:13:42.984256370Z] failed to connect to room server db           error="sqlutil.Open: failed to find maximum conections: pq: no pg_hba.conf entry for host \"X.X.X.X\", user \"dendrite\", database \"dendrite\", SSL off"

where X.X.X.X is the public IP of my server (not 127.0.0.1).

I was able to fix by manually editing postgres conf files. This was not necessary before.

sudo nano /etc/postgresql/13/main/postgresql.conf

change `listen_addresses = 'localhost'` to `listen_addresses = '*'`

sudo nano /etc/postgresql/13/main/pg_hba.conf

add line `host  all  all 0.0.0.0/0 md5`