Heroku setup error "NodeBB could not connect to your PostgreSQL database"

nodebber

I want to install NodeBB 16 on Heroku using postgres and followed the installation instructions on https://docs.nodebb.org/installing/cloud/heroku/. When running the setup i get the error message "NodeBB could not connect to your PostgreSQL database" (the connection params are working with pgadmin).

Has anyone an idea how to fix this?

This is my console output:

./nodebb setup
2021-03-08T13:43:36.537Z [23379] - info: NodeBB Setup Triggered via Command Line

Welcome to NodeBB v1.16.2!

This looks like a new installation, so you'll have to answer a few questions about your environment before we can proceed.
Press enter to accept the default setting (shown in brackets).
2021-03-08T13:43:36.647Z [23379] - info:
Now configuring postgres database:
2021-03-08T13:43:36.739Z [23379] - error: NodeBB could not connect to your PostgreSQL database. PostgreSQL returned the following error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off
2021-03-08T13:43:36.741Z [23379] - warn: NodeBB Setup Aborted.
 error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off
    at Parser.parseErrorMessage (XXX/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (XXX/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (XXX/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (XXX/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:223:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:181:23)

gotwf

@nodebber said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

Now configuring postgres database:
2021-03-08T13:43:36.739Z [23379] - error: NodeBB could not connect to your PostgreSQL database. PostgreSQL returned the following error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off
2021-03-08T13:43:36.741Z [23379] - warn: NodeBB Setup Aborted.
error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off

Is there an entry in pg_hba.conf for the box NodeBB itself is connecting from? Is that SAME box you are able to connect up using pgadmin?

I do not use heroku so no clue there and since you have obfuscated the IP addresses I am guessing that you may be using externally routable IP, i.e. the one associated w/the dns hostname. If this is all on same vm then maybe you need to add one for localhost and/or 127.0.0.1

Sorry not to be more helpful. Been a few too many years and I don't have postgres on the tip of my fingers nowadays. Bottom line is to ensure you have an entry in pg_hba.conf for the host NodeBB is connecting FROM. And if that is all on single box/vm, you will likely need one for localhost/127.0.0.1

PostgreSQL is pretty strict about connection security. And rightly so. Hence, you may also need to "require ssl/tls" if not all on same vm.

Best of my recollection, anyways. Good luck and have fun. o/

P.S.; You could also be connecting from a pg socket if on same vm. depending on how pg is configured.

nodebber

@gotwf Thanks for your answer. Sorry, but i think i do not understand your question > "Is there an entry in pg_hba.conf for the box NodeBB itself is connecting from?".

What i did was following the installation instructions for heroku - i checked out NodeBB, configured a heroku app and a heroku postgres plugin and then run "./nodebb setup" locally. But this results in the message "no pg_hba.conf entry for host ...".

Then i tested the connection params using my local pgadmin and could connect to the database.

"Bottom line is to ensure you have an entry in pg_hba.conf for the host NodeBB is connecting FROM. And if that is all on single box/vm, you will likely need one for localhost/127.0.0.1"

I dont know how to check the pg_hba.conf - i think this is located at the heroku-postgres plugin container.

nodebber

@gotwf What i found out is that adding the following ssl-param to the connection settings allows to run "./nodebb setup" (locally) and to populate the database.

ssl: {
rejectUnauthorized: false
}

After finishing the setup i am pushing the configuration files to heroku like mentioned in the instructions. There at heroku the connection problem occurs again and leads to an app crash.

PitaJ

@nodebber have you double checked the config.json at heroku still has that option?

gotwf

Sorry but I have no experience with Heroku. Clueless there. Sure seems like something may well be amiss with your pg_hba.conf though.

Maybe this will help shed some light:

https://www.postgresql.org/docs/current/client-authentication.html

Season for whatever version of postgres you/heroku is running.

Postgres has many options for authenticating users wh/act as gatekeepers to the pgsql server itself. In addition to that, users must also meet role and database access restrictions after connecting. You are not connecting. So that is either pg_hba.conf server side or something amiss nodebb app side. I'd try to verify former before tearing my hair out on the latter?

How to troubleshoot this on Heroku, I do not know. Seems you do not have access to common config files, e.g. pg_hba.conf so kind of groping about in the dark.

Sorry I do not have more precisely targeted solution. Just tossin' some stuff out there that may help you look in the right directions. Hopefully.

Update: Sure seems like it is postgres conf side though:

PostgreSQL returned the following error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off

Cuz, presuming no typo in your XXX settings aboove, how/why else could/would postgres return that error if you're not even talking to it?

nodebber

@pitaj Yes, i checked the config.json and the option was still there. I also tried different other params found at stackoverflow and elsewhere but none worked.

I am also not sure if the ssl-param has the right notation and place. In the heroku docs they mention the connection string should contain an additional ssl-param and give an example of the Client class of the pg module (see here). In the NodeBB version (12) i want to use the Pool class of the module is adressed for the postgres connection (as far as i understand this).

To get ./nodebb setup running i added the ssl-param to the connection options in postgres.js (connOptions.ssl = {rejectUnauthorized: false};) but this is not working after pushing it to heroku. So i am asking myself what part of the code where connections are set up i have missed.

nodebber

@gotwf Thanks for your suggestions. The strange thing is that i can access the database when running ./nodebb setup and successfully populate it from my local device but not connect from the heroku app.

Sounds like there are two different ways connections are established when ./nodebb setup and ./nodebb start are called but i only found one file till now where this is handled (postgres.js in nodebb version 12).

I will have a deeper look into the postgres documentation - thanks for the link.

PitaJ

@nodebber just to confirm, it works when you do

local nodebb -> heroku pg

But not when you do

heroku nodebb -> heroku pg

nodebber

@pitaj Yes i can run ./nodebb setup on my local device but when i push to heroku and run ./nodebb start there the app is crashing.

PitaJ

@nodebber what error do you see on startup?

nodebber

@pitaj After running ./nodebb start the app crashes and i find in logs/output.log the message

node:40) UnhandledPromiseRejectionWarning: error: no pg_hba.conf entry for host "34.XXX.XXX.XXX", user "XXX", database "XXX", SSL off
    at Connection.parseE (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:555:11)
    at Connection.parseMessage (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:380:19)
    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:120:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as _originalOnread] (internal/stream_base_commons.js:94:17)
    at TCP.onread (/app/node_modules/async-listener/glue.js:188:31)
(node:40) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:40) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
2021-03-09T22:48:23.264Z [48283/40] - error: uncaughtException: Connection terminated unexpectedly
Error: Connection terminated unexpectedly
    at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)
    at Object.onceWrapper (events.js:286:20)
    at Connection.emit (events.js:198:13)
    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)
    at Socket.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at /app/node_modules/async-listener/glue.js:188:31
    at process._tickCallback (internal/process/next_tick.js:63:19) {"error":{},"stack":"Error: Connection terminated unexpectedly\n    at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)\n    at Object.onceWrapper (events.js:286:20)\n    at Connection.emit (events.js:198:13)\n    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)\n    at Socket.emit (events.js:203:15)\n    at endReadableNT (_stream_readable.js:1145:12)\n    at /app/node_modules/async-listener/glue.js:188:31\n    at process._tickCallback (internal/process/next_tick.js:63:19)","exception":true,"date":"Tue Mar 09 2021 22:48:23 GMT+0000 (Coordinated Universal Time)","process":{"pid":40,"uid":52322,"gid":52322,"cwd":"/app","execPath":"/app/.heroku/node/bin/node","version":"v10.16.3","argv":["/app/.heroku/node/bin/node","/app/app.js"],"memoryUsage":{"rss":153505792,"heapTotal":114143232,"heapUsed":80573096,"external":435084}},"os":{"loadavg":[1.85888671875,1.759765625,2.30126953125],"uptime":1139164},"trace":[{"column":9,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js","function":"Connection.con.once","line":200,"method":"once","native":false},{"column":20,"file":"events.js","function":"Object.onceWrapper","line":286,"method":"onceWrapper","native":false},{"column":13,"file":"events.js","function":"Connection.emit","line":198,"method":"emit","native":false},{"column":10,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js","function":null,"line":130,"method":null,"native":false},{"column":15,"file":"events.js","function":"Socket.emit","line":203,"method":"emit","native":false},{"column":12,"file":"_stream_readable.js","function":"endReadableNT","line":1145,"method":null,"native":false},{"column":31,"file":"/app/node_modules/async-listener/glue.js","function":null,"line":188,"method":null,"native":false},{"column":19,"file":"internal/process/next_tick.js","function":"process._tickCallback","line":63,"method":"_tickCallback","native":false}]}.....

`

gotwf

@nodebbr On a related note: I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.

nodebber

@gotwf said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.

Just because i had less experience with nosql databases.

nodebber

@nodebber I contacted herokus support regarding my the connection problem and they found a solution. I had to set a new environment variable named "PGSSLMODE" to the value "require" and afterwards to restart my app / dyno.

As it took me some days to find this out i would suggest an update of the heroku install documentation to prevent future suffering.

Is there a official way to make documentation prosals - maybe as github issue?

PitaJ

@nodebber yeah an issue on NodeBB/docs

julian

@nodebber said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

Is there a official way to make documentation prosals - maybe as github issue?

Indeed! If you want to make a pull request to the repo @PitaJ linked to, then you will be contributing back and ensuring other users will not experience similar troubles

nodebber

The problem resulted from a change on side of heroku that now (early 2021) require ssl for all heroku-postgres addons.

To solve the problem for nodeBB 1.12 i had to do the following:

set a environment variable at heroku - an advice i got from the heroku support team and found (in herokus documentation it is noted as "PGSSLMODE" = "no-verify" which did not worked for me)

"PGSSLMODE" = "require"

add a ssl param to the postgres connection in src/database/postgres.js before creating a new Pool (two appearances in nodeBB 1.12)

+	connOptions.ssl =  {"rejectUnauthorized": false}
	const db = new Pool(connOptions);

add ssl param as json object to the postgres json object in the config.json

"postgres" {
    ....
    "database": "DBNAME",
+   "ssl" { "rejectUnauthorized": false }
}
...

set my local node version to 10.16.3 for populating the database running ./nodebb setup locally like described in nodebb's documentation
set the node version to 10.16 in package.json before pushing to heroku

To solve the problem for nodeBB 1.16 i had to do the following:

set a environment variable at heroku

"PGSSLMODE" = "require"

add a ssl param to the postgres connection in src/database/postgres.js and src/database/postgres/connection.js before creating a new Pool

+	connOptions.ssl =  {"rejectUnauthorized": false}
 	const db = new Pool(connOptions);

add ssl param as json object to the postgres json object in the config.json

"postgres" {
    ....
    "database": "DBNAME",
+   "ssl" { "rejectUnauthorized": false }
}

set my local node version to 14.16.0 for populating the database running ./nodebb setup locally like described in nodebb's documentation
set the node version to 14.16 in package.json before pushing to heroku

nodebber

@julian Thanks for the invitation to contribute. Is there something i should consider regarding the issue / pull request - markup, document structure,...?