Heroku Postgres SSL Errors

Stack Overflow
Heroku Postgres: "psql: FATAL: no pg_hba.conf entry for host"
There are a number of Heroku CLI Postgres commands that all return the same error. For example: $ heroku pg:bloat psql: FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL...
17 Replies
elithrar
elithrarOP12mo ago
@Mitya started a thread here
Mitya
Mitya12mo ago
Sorry just saw the thread - moving my reply here. --- Thanks, I'll look at this. I just can't figure out why Heroku wouldn't throw this message when a non-Wrangler app on my local machine connets to the same DB, but does for the Wrangler app. Surely Heroku should either allow my IP or it shouldn't.
elithrar
elithrarOP12mo ago
I don't see anything regarding your IP address here. Hyperdrive requires SSL (TLS), and it seems like Heroku is rejecting that for some reason.
Mitya
Mitya12mo ago
I meant that the DB error I'm getting mentions my IP - no pg_hba.conf entry for host "my.ip.here". I guess I'll have to take it up with Heroku. Appreciate the help, though.
elithrar
elithrarOP12mo ago
Is your DB in a private space or something? Just a regular Heroku Postgres instance?
Mitya
Mitya12mo ago
Regular DB - no IP restrictions or anything, hence other non-Wrangler apps on my machine can connect to it, and I can connect to it via the pg_admin and Heidi SQL clients.
elithrar
elithrarOP12mo ago
https://query-cache-tutorial.silverlock.workers.dev/ 1. Created a fresh Heroku account 2. A new Heroku Postgres instance 3. Connected Hyperdrive to it - with NO changes 4. Deployed a Worker against that config 5. Works
import { Client } from 'pg';

export interface Env {
// If you set another name in wrangler.toml as the value for 'binding',
// replace "HYPERDRIVE" with the variable name you defined.
HEROKU: Hyperdrive;
}

export default {
async fetch(request: Request, env: Env, ctx: ExecutionContext) {
// Create a database client that connects to our database via Hyperdrive
//
// Hyperdrive generates a unique connection string you can pass to
// supported drivers, including node-postgres, Postgres.js, and the many
// ORMs and query builders that use these drivers.
const client = new Client({ connectionString: env.HEROKU.connectionString });

try {
// Connect to our database
await client.connect();

// A very simple test query
let result = await client.query({ text: 'SELECT * FROM pg_tables' });

// Return our result rows as JSON
return Response.json({ result: result });
} catch (e) {
console.log(e);
return Response.json({ error: JSON.stringify(e) }, { status: 500 });
}
},
};
import { Client } from 'pg';

export interface Env {
// If you set another name in wrangler.toml as the value for 'binding',
// replace "HYPERDRIVE" with the variable name you defined.
HEROKU: Hyperdrive;
}

export default {
async fetch(request: Request, env: Env, ctx: ExecutionContext) {
// Create a database client that connects to our database via Hyperdrive
//
// Hyperdrive generates a unique connection string you can pass to
// supported drivers, including node-postgres, Postgres.js, and the many
// ORMs and query builders that use these drivers.
const client = new Client({ connectionString: env.HEROKU.connectionString });

try {
// Connect to our database
await client.connect();

// A very simple test query
let result = await client.query({ text: 'SELECT * FROM pg_tables' });

// Return our result rows as JSON
return Response.json({ result: result });
} catch (e) {
console.log(e);
return Response.json({ error: JSON.stringify(e) }, { status: 500 });
}
},
};
elithrar
elithrarOP12mo ago
No description
elithrar
elithrarOP12mo ago
I'd suggest trying on a fresh, temporary Heroku Postgres database first, to rule out any issues with your existing DB, especially if you've made settings changes to it in the past
Mitya
Mitya12mo ago
Wow, appreciate your efforts. OK that's interesting. I haven't made any settings edits to this DB and as I say I can connect to it fine outside of Wrangler so this is a curious one. I'll try with a new DB.
elithrar
elithrarOP12mo ago
Also, when you say “outside of Wrangler” - do you explicitly mean “from wrangler dev” (local dev) or from a Worker you have deployed? If it is the former, what version of Wrangler are you using?
Mitya
Mitya12mo ago
By that I mean apps running on my local machine but which aren't anything to do with CF/Wrangler/Workers but are connecting to the same DB. Wrangler 3.18.0 So for example the app I'm using with Workers started life as an Express app, and in its Express incarnation it connects fine, with the same connection string.
elithrar
elithrarOP12mo ago
Right, but how are you running into issues? Are you trying to develop locally with wrangler dev - ? if you can step back and run me through, step by step, what commands you are running and how the error is generated, it will help a lot
Mitya
Mitya12mo ago
OK. So I had a fully-working Express app that I needed to convert to serverless. So here I am now with Wrangler/CF Workers. I changed to a serverless-friendly router (Itty). My DB code is unchanged from the Express days. It calls the same DB in the same way, specifically:
import { Client } from 'pg'
const query = async (q, params) => {
const client = new Client({
connectionString: env[env.DB+'-db'].connectionString,
ssl: {rejectUnauthorized: false}
});
await client.connect();
const res = await client.query(q, params);
return res;
};
import { Client } from 'pg'
const query = async (q, params) => {
const client = new Client({
connectionString: env[env.DB+'-db'].connectionString,
ssl: {rejectUnauthorized: false}
});
await client.connect();
const res = await client.query(q, params);
return res;
};
As I've mentioned, it's a simple dev DB running on Heroku with no weird settings or edits re: restricted access, hence other non-Wrangler apps running locally on my machine, as well as DB clients e.g. HeidiSQL, can connect to it. My connection string (env[env.DB+'-db'].connectionString) is a standard Postgres connection string, specified in my TOML under my Hyperdrive definition i.e.
[[hyperdrive]]
binding = "dev-db"
id = "<my-hyperdrive-id>"
localConnectionString = "postgres://<user>:<pass>@<host>:5432/<db>"
[[hyperdrive]]
binding = "dev-db"
id = "<my-hyperdrive-id>"
localConnectionString = "postgres://<user>:<pass>@<host>:5432/<db>"
I have console.log'd env[env.DB+'-db'].connectionString to ensure the connection string is what it should be. My dev command in package.json is set to wrangler dev. With this setup, my DB connection fails, with the error I've posted. If I change the dev command to wrangler dev --remote the problem goes away. Hope that's sufficient - thanks.
elithrar
elithrarOP12mo ago
Yes, so —remote means you’re running on Hyperdrive proper - on our network. wrangler dev is local-only. 1. Can you remove the rejectUnauthorized / ssl object entirely? 2. Can you psql to that local connection string on the same machine? Do you successfully connect to your Postgres instance? not using the Heroku CLI, just plain psql From everything here this is entirely local and I am trying to rule out a local configuration issue on your machine or with wrangler dev
Mitya
Mitya11mo ago
Thanks for this - I'll try the above steps tomorrow and feed back. Hi - apologies for the late comeback here, Christmas etc. I've tried what you said and it's the same issue. Removing the ssl definition in the object has no effect, and yes I can connect to the DB over PSQL on my machine. This is verified by connecting to the DB in pg_admin and then accessing it via the "PSQL tool" option, where I can then run commands e.g. \d to see a list of tables. I've just tried a different DB, hosted on Azure, and exactly the same issue. I can connect it locally via pg_admin/psql/DB client software etc., but not via Wrangler. Shall I post this as a bug? Since you can't seem to recreate it, I'm not sure how helpful that would be, but it's definitely an issue for me. I can provide any more diagnostic info that may help. Aware this is becoming a bit stream-of-consciousness, but I'm also getting inexplicable DB errors that say Error: Connection terminated unexpectedly. Again, this is only when connecting to the DB via an app running under Wrangler. When I connect to the same DB in my Express app, or via clients etc., no problem.
elithrar
elithrarOP11mo ago
Please open a bug. This appears to be local only (e.g. via wrangler) + not in production/when deployed. I suspect a possible Windows or firewall issue even?
Want results from more Discord servers?
Add your server