Cannot able to connect postgreSQL with Mesh-central in development region.

[a-imran99]

HI @Ylianst , I have been trying to use postgreSQL database with Mesh-central and can able to connect to it in local environment (While running both Mesh-central as well as PostgreSQL server in localhost). But when deployed, It throws error. The postgres server that we run is without any SSL certificate. Is that the source of problem? It would be great if you take your time to reply.

Link I referred to connect to postgres. : https://meshcentral2.blogspot.com/2021/11/meshcentral-postgresql-streaming-pre.html

"postgres": {
            "user": "postgres",
            "password": "*********",
            "port": "5432",
            "host": "hostname.namespacename.svc" ( postgres running on cluster )
}

Error while trying to run Mesh-central server.

Installing acme-client...
Installing [email protected]...
Installing [email protected]...
Installing [email protected]...
ERR: (node:67) UnhandledPromiseRejectionWarning: error: SSL required
    at Parser.parseErrorMessage (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/opt/meshcentral/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
ERR: (node:67) 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(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:67) [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.
(node:67) UnhandledPromiseRejectionWarning: error: SSL required
    at Parser.parseErrorMessage (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/opt/meshcentral/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/opt/meshcentral/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
(node:67) 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(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
SELECT doc FROM main WHERE id = $1 [ 'DatabaseIdentifier' ] Error: Connection terminated unexpectedly
    at Connection.<anonymous> (/opt/meshcentral/node_modules/pg/lib/client.js:132:73)
    at Object.onceWrapper (events.js:420:28)
    at Connection.emit (events.js:314:20)
    at Socket.<anonymous> (/opt/meshcentral/node_modules/pg/lib/connection.js:107:12)
    at Socket.emit (events.js:326:22)
    at endReadableNT (_stream_readable.js:1241:12)
    at processTicksAndRejections (internal/process/task_queues.js:84:21)
SELECT doc FROM main WHERE id = $1 [ 'SchemaVersion' ] Error: Connection terminated unexpectedly
    at Connection.<anonymous> (/opt/meshcentral/node_modules/pg/lib/client.js:132:73)
    at Object.onceWrapper (events.js:420:28)
    at Connection.emit (events.js:314:20)
    at Socket.<anonymous> (/opt/meshcentral/node_modules/pg/lib/connection.js:107:12)
    at Socket.emit (events.js:326:22)
    at endReadableNT (_stream_readable.js:1241:12)
    at processTicksAndRejections (internal/process/task_queues.js:84:21)

[dinger1986]

this is a similar issue as I am having #4507 I think that maybe mesh doesnt create a database in postgres

[dinger1986]

ah no worries, I didnt even think of checking the package but that makes sense then, I am sure @Ylianst will be happy to take any PRs which improves things

[Ylianst]

FYI. This issue has been fixed in the latest MeshCentral version. It's a PostgreSQL specific issue. MeshCentral would detect that the DB did not exist and create and and create the tables and indexes... but if the DB already existed, it would not create the tables and indexes. This is fixed now, if the DB exists but main table does not exists, all tables and indexes will be created.

[dinger1986]

I think its more to do with there not being a similar option in config.json for postgres as there is for mysql

This is from mysql

             "description": "SSL Options. Set to true (boolean) for default options.",
              "properties": {
                "caCertPath": { "type": "string", "description": "Absolute path to the CA certificate. Required for self-signed certificates" },
                "clientCertPath": { "type": "string", "description": "Absolute path to the client certificate. Required for two-way SSL Authentication" },
                "clientKeyPath": { "type": "string", "description": "Absolute path to the client key. Required for two-way SSL Authentication" },
                "dontCheckServerIdentity": { "type": "boolean", "description": "Set true to not check the server hostname during verification" }
              }

Which means you cant use SSL with postgres for hosted DBs, but this looks to be a restriction with pgtools not meshcentral :)

[a-imran99]

FYI. This issue has been fixed in the latest MeshCentral version

Oh That really great to know @Ylianst, will try to push the latest mesh-central's code and try to connect the DB.

But still as @dinger1986 said , it seems to be an pgtool related issue and not mesh central's issue I guess.
any thoughts ??

[a-imran99]

@dinger1986 @Ylianst

So I am trying to get mesh central connected to postgres without any SSL verification and added NODE_TLS_REJECT_UNAUTHORIZED=0 at Docker file.

but its throwing error. Why is mesh central throwing error even if I don't need to verify SSL certificate

I've also tried downgrading the pg version to 7.4.3 for this issue brianc/node-postgres#2009

The error

Installing [email protected]...
Installing [email protected]...
Installing semver...
Installing [email protected]...
Installing [email protected]...
ERR: (node:63) Warning: Setting the NODE_TLS_REJECT_UNAUTHORIZED environment variable to '0' makes TLS connections and HTTPS requests insecure by disabling certificate verification.
(Use node --trace-warnings ... to show where the warning was created)