Docker can't connect to Postgres RDS error: no pg_hba.conf entry for host

Question

I'm trying to connect a local instance of Directus 9 running on Docker engine 20 on an macOS M1 to an AWS RDS Postgres 15.

I'm able to connect to the RDS database ok locally with Postico, so public remote connections through the VPC are working. I'm also able to connect to my local Postgres database at host.docker.internal

I have tried to use Parameter Groups in the RDS config to not require encrypted connections. setting rds.force_ssl = 0 which didn't work.

I get the below error when i try to run docker compose up

ERROR: no pg_hba.conf entry for host "XX.XXX.XX.XX", user "postrgres", database "EXAMPLEDB", no encryption
directus  |     err: {
directus  |       "type": "DatabaseError",
directus  |       "message": "no pg_hba.conf entry for host \"XX.XXX.XX.XX\", user \"postrgres\", database \"EXAMPLEDB\", no encryption",
directus  |       "stack":

I believe this means that the production Postgres db wont allow non-encrypted connections.

I've cloned the Directus repo and used docker to build from the docker-compose.yml . Ive not used a .env file for configuration but put everything into this compose file. Im new to docker today and don't fully understand how to write this config correctly yet, I have referred to this guide https://docs.directus.io/self-hosted/config-options.html#database

services:
  database:
    container_name: database
    image: postgis/postgis:13-master
    # Required when running on platform other than amd64, like Apple M1/M2:
    #platform: linux/amd64
    #volumes:
    # - ./data/database:/var/lib/postgresql/data2
    networks:
      - directus
    environment:
      POSTGRES_USER: 'directus'
      POSTGRES_PASSWORD: 'directus'
      POSTGRES_DB: 'directus'

  directus:
    environment:
      DB_CLIENT: 'postgres'
      DB_HOST: 'xxx.rds.amazonaws.com'
      DB_PORT: '5432'
      DB_DATABASE: 'XXXX'
      DB_USER: 'postrgres'
      DB_PASSWORD: 'XXX' 

   directus exited with code 1

I assume it's not possible to change anything to the pg_hba.conf on the RDS and it shouldn't be edited or considered a solution.

Could the cause of the error be that the docker image doesn't have SSL installed or something?

I used all of the provided Dockerfile and files from https://github.com/directus/directus

Any ideas as to what likely cause solution to this error?

Tripodic

What happens if you configure Postico to disable use of SSL? Do you then get the same as error as you do for directus? – Theoretician

@Theoretician "Postico always tries to connect to PostgreSQL servers via an encrypted connection (SSL). ", I have tried a few other free clients, I haven't found one with a non encrypted connection. Investigation continues – Tripodic

Use psql. It is free, it comes with PostgreSQL, and it is the tool most familiar to the people who are in the best position to help you. It prefers to use SSL by default, but you can disable that by setting environment PGSSLMODE=disable or by including 'sslmode=disable' into the connection string. – Theoretician

Answer 1

Solution:

1. There is an SSL issue, possibly to do with using self-signed certificates in RDS, I set the following ENV variable in docker-compose.yml as follows

DB_SSL__REJECT_UNAUTHORIZED: false

that cleared the issue of this question. Then...

2. The username for the RDS database was incorrect, The database user should be as follows : postrgres -> postgres

Tripodic

For me, it was just that my DB connection was wrongly set to sslmode=disable. Going back to SSL mode solve the issue – Deanedeaner

Answer 2

In my case, it was because of an SSL setting of RDS, i.e, rds.force_ssl

Synn

Answer 3

I think you are setting the DB_CLIENT variable wrong. it should be pg according to Directus official documentation https://docs.directus.io/self-hosted/config-options.html#config-yaml

Jorgan

docs.directus.io/self-hosted/config-options.html#database says One of pg or postgres – Tripodic

Answer 4

I fixed it by creating new Parameter groups clone from my default Parameter groups of postgres (i use posgres 16) and change value rds.force_ssl to 0 (default is 1 it mean need connection have ssl to connect) and restart this postgres RDS by click Action button in right and choose "Update now" and it worked !!

i reference in this link https://github.com/dbeaver/dbeaver/issues/21616

Custombuilt