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?

Answer 1

Another possible cause/solution: if you told RDS to use AWS Secrets Manager to store the creds (in my case, Aurora PG Serverless v2), by default the password will be rotated every seven days, and most importantly, will be rotated very soon after the DB is created.

The delay before initial rotation is long enough for you to pick up the initial password from Secrets Manager, and then try to use it. Often, when you try to use it, it has since been rotated, and if you check Secrets Manager again, you'll see the new password, which should eventually work.

When the password is auto-rotated, it takes a while for RDS to pick-up the new password from Secrets Manager. During the period between when the password is rotated, and when RDS recognizes the new password, you'll get this misleading error about pg_hba.conf.

The simple solution is to either wait a bit, or restart the cluster so RDS will immediately pick-up the new password from Secrets Manager.

Just guessing, but I tend to think a lot of the "disable SSL" solutions simply benefit from the necessary cluster restart, and disabling SSL isn't actually necessary - it wasn't in my case.

A couple of points here:

• When RDS is out of sync with Secrets Manager, it seem that you will get the misleading pg_hba.conf error for any incorrect password, whether the bad initial password, or the rotated password before RDS has reloaded.
• Once RDS is back in sync with Secrets Manager, bad passwords will get the expected "password authentication failed" error, and not the misleading error.
• By default, psql will try both SSL and non-SSL, reporting the same error for both.

Answer 2

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.

---

Note: this method removes the default SSL security in the connection and shouldn't be used in production databases.

Answer 3

Getting the same issue while pulling data from RDS using glue 2.0 but working fine in glue 3, 4

So below is workaround for glue 2

First downloaded the jar file of postgres from https://jdbc.postgresql.org/download/

After Adding below parameter it is working fine

--extra-jars -> path/to/s3/postgresql-42.6.2.jar

--user-jars-first -> true

Answer 4

You need to add the cert chain to your cert store manually.

RDS uses a self-signed certificate that isn't going to be verified by a public CA. You can download the certificate chain for your region from here.

1. Place your .pem certificate chain in /usr/share/ca-certificates

2. Edit /etc/ca-certificates.conf and add your certificate name there. (Look at update-ca-certificates man page for more information.)

3. Then run sudo update-ca-certificates

4. Try to connect to your instance

If you're using an application like Authelia, you may instead need to provide the certificate directly to your application. In the case of Authelia, that would be in the /config/certificates directory. This was my personal case.

In case the link to the certificate chains ever dies, try finding an AWS article with the text "Download certificate bundles for Amazon RDS".

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.

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.

Answer 10

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

Answer 11

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 :)

Answer 12

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

Answer 13

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

For node Postgres

Answer 14

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