AWS RDS PostgreSQL 14, 15, 16 version pg_hba.conf error during connection

aws_rds_postgresql_pg_hba_connection_error.md

The error

2024-01-01T01:02:03 [METADATA_MANAGE ]E: RetCode: SQL_ERROR SqlState: 08001 NativeError: 101 Message: [unixODBC]FATAL: no pg_hba.conf entry for host "172.27.32.78", user "app_production", database "app_production", no encryption [1022502] (ar_odbc_conn.c:579)

Affected drivers

Any driver which does not support SSL and/or password encryption. E.g. PowerBI ODBC or even AWS DMS might be the affected software.

Analysis

1. Since you already get pg_hba error, it means that one of security groups allows direct connection to the database.
2. Ensure that you have correct security groups in place and move to the next step.
3. If you have direct connection working with some other client like dbeaver, then you can execute this query to check pg_hba setup:

SELECT * FROM pg_hba_file_rules();

Same command you can execute from Rails console, e.g. if you don't have direct access to database, but your app has connection:

ActiveRecord::Base.connection.execute("SELECT * FROM pg_hba_file_rules();").to_a

4. Check AWS RDS database parameter group if it's default, check force_ssl and password_encryption parameters

Possible solution

With PostgreSQL upgrade you get new parameters for the database, create a new parameters group and check these two parameters and values:

rds.force_ssl = 0
password_encryption = md5

Before applying these values ensure that you have strict security groups associated with the database, so that the access is allowed only from the set of IP addresses.