No pg_hba.conf entry for host {HOST}, user "avnadmin", database "defaultdb", no encryption

Aiven Community
1

I recently migrated from ElephantSQL to Aiven PostreSQL. I was able to migrate all my data but I’m having trouble getting my existing application (node/express) to connect to the new hosting. At first I was getting

could not connect to postgres Error: self-signed certificate in certificate chain

After reading some posts on the node-postgres github project, I tried removing ?ssl=require from my connection string, but now I’m getting

no pg_hba.conf entry for host {HOST}, user "avnadmin", database "defaultdb", no encryption

any ideas how to fix either of these issues and get the connection working again?

2

also the ‘HOST’ in the error message is the IP address of the server that my node app is hosted on, NOT the database host ({MYINSTANCENAME}.g.aivencloud.com) that i included in my connection string

my connection setup looks like this:

const pg = require("pg");
const configDB = require("./app/config/knex");
const client = new pg.Client(configDB.configConnection);

where configDB.configConnection =

{ 
   client: "pg",
    connection: process.env.STG_DATABASE_ENDPOINT,
    migrations: {
      directory: "./db/migrations/production"
    },
    seeds: {
      directory: "./db/seeds/production"
    },
    ssl: {
      require: true,
      rejectUnauthorized: false
    }
}

and the endpoint env var is copied directly from the aiven console.

I don’t understand why I’m getting an error message about a config file on my application server when the postgres is hosted at aiven and I have supplied the host URL in the connection string

3

Well, in case anybody else is dealing with this issue, I ended up going with a workaround which works in this case because this is a staging version of the app and doesn’t hold any real user data. So I replaced the ?sslmode=require at the end of my connection string, and set

process.env["NODE_TLS_REJECT_UNAUTHORIZED"] = 0;

for the staging environment only and that took care of it. No idea how you would fix this in a production environment though, and the Aiven documentation doesn’t seem to address it anywhere.

4

Your host string should be the database host, so yes {MYINSTANCENAME}.g.aivencloud.com.

In the ssl section you should provide the certificate.pem which you can download from the database connection information e.g.

ssl: {
      require: true,
      rejectUnauthorized: false
      ca: fs.readFileSync('./path/to/certificate.pem')
    }
5

I tried this but was struggling to get tests to pass during the deployment process, because the absolute file path was throwing an error when running tests on Travis. Even though I use a different db config object for the testing environment, the config object for the staging environment is in my knexfile.js and just having that line of code in the object was causing the whole test process to choke during deployment because the filepath was invalid. Eventually I just gave up and changed the env variable.

But if anybody has tips on how to keep multiple db config objects in the same knexfile, one of which references a filepath that exists only on a specific server, without causing tests to fail when run on a different server, I’d love to hear them.

1 Like
6

This isn’t a perfect solution and I’m not sure I have a better one for you, but I am going to mark this as solved just so that folks coming in via google can see that :slight_smile: