Connection to external Postgresql via SSL about documentserver HOT 4 CLOSED

Hello @quaternionma, i recheked today and not get error, in my pg_hba.conf:

hostssl all             all             0.0.0.0/0               cert

I guess you have a configuration error, check the postgres documentation:
https://www.postgresql.org/docs/16/ssl-tcp.html
https://www.postgresql.org/docs/16/libpq-ssl.html

from documentserver.

This is strange. I'm using the same database with several other services and all connects through SSL without problems.
Usually i refrain from using client certificates for identity verification and my pg_hba line looks like this:

hostssl onlyoffice onlyoffice 10.10.0.151/32 scram-sha-256

According to your configuration you are using client certificates for client identity verification. I did not found any config option for this in the actual onlyoffice documentation, so in order to reproduce your config i put client cert, key and root cert in a directory named .postgresql in the home folder of the user ds and changed the line to

hostssl onlyoffice onlyoffice 10.10.0.151/32 cert

Unfortunately i had still no luck. If this standard way is not the way to go, would you please give some advice? Is there any not documented option?

The relevant error line in /var/log/onlyoffice/documentserver/docservice/out.log is again

nodeJS - getTableColumns error: error: kein pg_hba.conf-Eintrag für Host »10.10.0.151«, Benutzer »onlyoffice«, Datenbank »onlyoffice«, keine Verschlüsselung.

Again: changing hostssl to host resolves the problem but this is not really a good solution. I'm using AlmaLinux release 9.4 (Seafoam Ocelot) and Postgesql 15.6 on the database server.

So I#m stuck here!

from documentserver.

I see what is the problem, you add certificates to a separate file. You need to add cert as string (you can convert to string with awk as i show in the post), we do not support reading from file in our configuration files, my sql section looks like:

      "sql": {
        "type": "postgres",
        "dbHost": "192.168.0.110",
        "dbPort": "5432",
        "dbName": "onlyoffice",
        "dbUser": "onlyoffice",
        "dbPass": "onlyoffice",
        "pgPoolExtraOptions": {
          "ssl":{
            "rejectUnauthorized": false,
            "ca": "-----BEGIN CERTIFICATE-----\n...root_crt...\n",
            "key": "-----BEGIN PRIVATE KEY-----\n...client_key...\n",
            "cert": "-----BEGIN CERTIFICATE-----\n...client_crt...\n"
          }
        }
      },

Onlyoffice can work without checking client certs, i just checked . My sql section in the local.json:

      "sql": {
        "type": "postgres",
        "dbHost": "192.168.0.110",
        "dbPort": "5432",
        "dbName": "onlyoffice",
        "dbUser": "onlyoffice",
        "dbPass": "onlyoffice",
        "pgPoolExtraOptions": {
          "ssl":{
            "rejectUnauthorized": false
          }
        }
      },

pg_hba.conf:

hostssl onlyoffice             onlyoffice             192.168.0.151/32               scram-sha-256

Maybe you specified the wrong IP address in the pg_hba.conf? Judging by your mistake postgresql rejects connection, can you show postgresql log?

from documentserver.

Many thanks @igwyd.

"pgPoolExtraOptions": {
          "ssl":{
            "rejectUnauthorized": false
          }
        }

did the magic. It works now. So the "rejectUnauthorized": false JSON key seems to be mandatory for connecting through SSL, at least for certificates signed by a local CA. Unfortunately there is no mentioniong about in the documentation.
It would be great if this can be added to https://helpcenter.onlyoffice.com/installation/docs-community-install-ubuntu.aspx in order to prevent further confusion and frustation, especially because it was mentioned in #1708 (comment) as a temporary workaround.

from documentserver.