Quick Fix: To resolve the issue, go to the Secrets Manager secret associated with your RDS and manually rotate the secret. This action updates RDS credentials, causing the error to disappear.
The Problem:
You have two AWS RDS Postgres databases, one in Ireland (eu-west-1) and the other in N. Virginia (us-east-1). For no apparent reason, the database in Ireland started throwing an error: "no pg_hba.conf entry for host…, user…, database…, no encryption". You’d like to know why this is happening and what needs to be done to resolve it.
The Solutions:
Solution 1: Rotate the Secrets Manager secret
In case of using a serverless RDS cluster with Secrets Manager and automatic password rotation, if password rotation fails silently, there can be a secrets mismatch, resulting in the error “no pg_hba.conf entry”. To resolve this:
Go to the Secrets Manager secret associated with your RDS.
Click "Rotate secret immediately".
Wait a few minutes for the Last rotate date to update.
Once the password rotation is successful, the connection to the RDS cluster should be re-established, and the error should be resolved.
Q&A
Why did Postgres throw "no pg_hba.conf entry for host"?
—
Secret rotation mismatch between AWS Secret Manager and RDS.
How to solve the "no pg_hba.conf" error?
—
Rotate the secret in the Secrets Manager manually.
Video Explanation:
The following video, titled "common aws rds errors: unable to connect rds - YouTube", provides additional insights and in-depth exploration related to the topics discussed in this post.
How to create PostgreSQL database on AWS RDS and ... Free AWS RDS MySQL and VS Code or MySQL Workbench Connection | Fix for connection errors.
