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

Question

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?

Augur

"I have confirmed that encryption is functioning properly," What specifically did you do? I wouldn't think it is possible to do that if you can't even establish a connection in the first place. – Flavio

The error message means the client requested a connection without ssl, and the server refused. Presumably that means rds.force_ssl is set to 1. – Flavio

I changed the rds.force_ssl to be 0 and I got the same error msg – Augur

Are you sure your change has taken effect, and that you are connecting the same serve as the one you made the change for? I don't see how you could get this particular error message any other way on RDS. – Flavio

Answer 1

First of all I wanna note that Nick's answer resolved my issue, but I just would love to add a detailed steps to follow for those who's new to AWS:

Create a New Parameter Group:

1. Open the Amazon RDS console at https://console.aws.amazon.com/rds/.
2. In the navigation pane, choose "Parameter groups".
3. Click "Create parameter group" at the top right of the page.
4. In the "Parameter group family" dropdown, select "postgres15".
5. In the "Group name" field, enter a name for the new parameter group.
6. In the "Description" field, enter a description for the new parameter group.
7. Click "Create" at the bottom right of the page.

Modify the rds.force_ssl Parameter of your new Parameter Group:

1. In the list of parameter groups, click on the name of the new parameter group you just created.
2. In the "Filter parameters" box, type rds.force_ssl and press Enter.
3. You should see the rds.force_ssl parameter. Click "Edit parameters".
4. Change the value of rds.force_ssl from 1 to 0, then click "Save changes".

Associate Your RDS Instance with the New Parameter Group:

1. In the navigation pane, choose "Databases".
2. Click on the name of your RDS instance.
3. Click "Modify" at the top right of the page.
4. In the "Database options" section, find the "DB parameter group" setting and select the new parameter group you created from the dropdown menu.
5. Scroll down and click "Continue".
6. Review the summary of modifications and click "Modify DB Instance".

Reboote Your RDS Instance:

1. In the navigation pane, choose "Databases".
2. Click on the name of your RDS instance.
3. Click "Actions" at the top right of the page, then "Reboot".
4. Confirm that you want to reboot the instance.

By following these steps, you should be able to successfully modify the rds.force_ssl parameter in your Amazon RDS instance. And hopefully the connection issue would be resolved.

Parliament

why to lower the security? why rds.force_ssl from 1 to 0? We should figure out how to make it work when ssl is enabled. – Solitaire

Thanks, this works. For anyone wondering why you want to do this and lower security, it's because if you already have a live app in production, you must first get it back running to buy time for a proper fix + testing. Especially if you have a active users or integrated with many platforms sending you data (say webhooks). Chances are, if you're in this situation because you've upgraded your RDS version, you probably didn't have a secure config originally and most likely don't have other infra in place to help go to the secure solution quickly. – Allx

Answer 2

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.

Steps to Follow

• Set up a new parameter group for postgres15, as the default parameter group (default.postgres15) does not allow editing the 'rds.force_ssl' parameter.
• Select the newly created parameter group for postgres15. Locate the 'rds.force_ssl' parameter within the selected parameter group.
• Change the value of the 'rds.force_ssl' parameter from 1 to 0 (the default value is 1).
• Navigate to the database configuration tab.
• Switch the 'DB instance parameter group' from the default group to the newly created parameter group for postgres15.
• After applying these changes, reboot the database. Attempt to connect to the database again to ensure the changes are effective.

Biplane

Answer 3

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

For node Postgres

Toul

Anyone can give an explanation ? – Merrile

This works, but if any one with more security knowledge could chime in on the consequences of this, that'd be very helpful. – Media

node-postgres.com/features/ssl this may help you. – Toul

It tells postgres to ignore the error when SSL connectivity fails, not a good idea to disable this imo – Circumvent

Answer 4

The approach suggested by @theiskaa and @Nikhil P K might allow for a successful connection to the RDS database but they potentially bypass the use of SSL, which is highly unadvisable in production environments.

To connect securely to your RDS database, follow these steps:

1. Modify your database connection config to include SSL:




   const fs = require('fs');

   const dbConfig = { 
     user: 'user', 
     host: 'host', 
     database: 'name', 
     password: 'password', 
     port: port, 
     ssl: { 
       require: true,
       rejectUnauthorized: true,
       ca: fs.readFileSync('/pathto/rds-ca-cert.pem').toString(), 
     } 
   }; 
         
   

2. Download the CA certificate bundle that matches your RDS instance region from the AWS RDS documentation; https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html#UsingWithRDS.SSL.RegionCertificates You can check which one you need in your AWS RDS console in the “Connectivity and settings” section, under the Certificate authority:

3. After downloading the CA certificate bundle, place it in your project directory.

4. Make sure that the path in the fs.readFileSync points to where you have the CA certificate within your project directory.

Now you should be able to connect securely by verifying the server certificate against the downloaded AWS RDS CA certificate

Note: I used Node.js in this example. If you are using a different environment or language, you need to adjust the syntax and method of reading the CA certificate file accordingly.

Rothrock

This is the only correct answer here. I cannot believe that people fix security problems by ignoring it. – Laurinda

Thank you so much. Others need to learn from @Abdel on how to not misguide people by fixing such important problems by just ignoring them and presenting them in such a way. – Liard

Answer 5

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
    });

Bangui

Answer 6

Just want to share my experience with this problem in the last couple of days

after trying all the suggested solutions the problem was wrong "Master username "

i have been copied the correct Master username from Configuration tab on aws RDS and it is successfully worked :)

Oldcastle

I am using IAM token authentication, and received this error because I had recreated my database but forgot to update the IAM Policy with the new database ID – Mcgowan

Answer 7

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:

1. 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"
    ]
  }

2. 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
3. Create .postgresql folder wherever Postgres wants it and move the certs there as root.crt eg: mv global-bundle.pem /root/.postgresql/root.crt
4. 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
5. 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

Enchantment

How does one figure out "wherever Postgres wants it"? – Manifestative

It should say in the error message. Eg if you have installed it with root, /root/.postgresql – Enchantment

How can you set this if you are calling from a node app locally? – Peach

This is the solution I actually needed to connect to my new RDS, thank you! – Isomorph

Answer 8

An alternative to the voted answer is to change the client instead of the server (rds) instance.

One way to do it, if you don't want to set up certificates, is to setting the sslMode to require allow or prefer. See https://jdbc.postgresql.org/documentation/use/#connection-parameters and https://jdbc.postgresql.org/documentation/ssl/#configuring-the-client.

So your jdbc url would be: jdbc:postgresql://<host>/<db_name>?sslmode=prefer

Seringapatam

Answer 9

In my use case, I saw this exact same error, while testing connectivity with a Postgres endpoint that didn't use encrypted connection.

Resolution: I modified the endpoint to require SSL. Just this, and it connected successfully.

Hexapartite

Answer 10

I'm new to AWS, too. Ran into this problem when I used Beekeeper to test connection to a new Postgres db. AWS automatically provides a CA, so I didn't need to download the certificate. I did have to toggle the button "Enable SSL" when creating a new connection in Beekeeper and that worked for me.

Prelature

Answer 11

I used the "Set up EC2 connection" from Connected compute resources section inside RDS settings, and it started working

Krispin