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

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 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?

@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.

@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}]}.....

`

@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.

@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 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.

@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.

@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.

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)

While trying to install nodebb on Heroku i am having problems to connect to postgres databases provided by Heroku add-ons - "Standard 0" and up. Connecting to lower performance Heroku postgres add-ons, like "Hobby dev" and "Hobby basic", works.

I found the recommendation to add a ssl param to the connection string when connecting to postgres via node.js apps but did not found the right place in the nodebb code to add an additional param (https://devcenter.heroku.com/articles/connecting-to-heroku-postgres-databases-from-outside-of-heroku).

Can anyone give me a hint?