RDS while connection error: no pg_hba.conf entry for host

May 2024 ยท 3 minute read

I need assistance as I'm having trouble connecting to a managed Postgres database on RDS. I'm encountering the following error message: "no pg_hba.conf entry for host '16.151.149.51', user 'analytics', database 'database', no encryption" I have confirmed that encryption is functioning properly, and I've also added the IP to the security groups. What steps should I take to resolve this issue?

4

4 Answers

If you're using Engine 15 or higher:

When setting up a database in RDS, the default parameter group for postgres15 (default.postgres15) is used. However, we need to change the 'rds.force_ssl' parameter, which isn't editable in the default.postgres15 group. To do this, we'll create a new parameter group for postgres15, which allows us to make edits.

Once the new parameter group is created, we'll select it and find the 'rds.force_ssl' parameter. We'll change its value from 1 to 0 (the default is 1).

Then, in the database configuration tab, we'll switch the 'DB instance parameter group' from the default group to the new one.

After making these changes, we'll reboot the database and try connecting again. This should work.

new Pool({ user: "", password: "", host: "", database: "", port: "", ssl: { rejectUnauthorized: false } })

For node Postgres

3

Okay, OP might have resolved the issue already but for others who come across this: ensure you have the AWS RDS CA certificates downloaded and double-check that your credentials are correct. In my case the Terraform module I used had manage_master_user_password set to true by default which nicely silently discarded my provided password and set it on its own.

But basically, for the unaware, the steps to connect using SSL is:

  • Create RDS that is either publicly accessible (eg publicly_accessible = true in TF) or open to at least your VPC subnets with the proper ingress rules eg:
    •  ingress { from_port = 5432 to_port = 5432 protocol = "tcp" description = "PostgreSQL access for VPC and local machine" cidr_blocks = [ module.vpc.vpc_cidr_block, "1.1.1.1/32" ] }
  • Then in your ec2-instance / client, download the needed root CAs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html Global is the easiest eg: curl -O https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
  • Create .postgresql folder wherever Postgres wants it and move the certs there as root.crt eg: mv global-bundle.pem /root/.postgresql/root.crt
  • Connect using the proper connection string with SSL enforced eg: psql postgresql://<your user>:<your pass>@postgres-1-project.z4dadjt5qxpn.us-east-1.rds.amazonaws.com:5432/<your db>?sslmode=verify-full
  • You should be connected to your RDS. If it says FATAL: password authentication failed for user check your credentials again. SSL should work (or at least complain something) if you have it set to verify-full
    • 3

    Piggybacking off of Nikhil P K's answer, this CDK code will turn SSL off for Postgres 15+:

     const engine = DatabaseInstanceEngine.postgres({ version: PostgresEngineVersion.VER_15, }); const parameterGroup = new ParameterGroup( this, "parameter-group", { engine, parameters: { "rds.force_ssl": "0", }, } ); this.database = new DatabaseInstance(this, "database", { engine, parameterGroup, // ...the rest of the setup });

    ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm9waW59c3%2BOq5usZaedtq2xjJymp6aVmMGqu81mnKuqn6d6r7uMqZ5moJKWeqS7zZ9knqakp8Zuss6rZKGno6k%3D