Connection to external Postgresql via SSL

quaternionma commented 1 week ago

This issue is unique.

[X] I have used the search tool and did not find an issue describing my bug.

Operating System of DocumentServer

Linux (DEB package)

Version information

8.1.0

Expected Behavior

Connection to external Postgresql Database should be possible via SSL

Actual Behavior

According to https://github.com/ONLYOFFICE/DocumentServer/issues/1708#issue-1186071317 it should be possible since v7.2 to connect to external Postgresql via SSL. But it is still not possible in v8.1.0.

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

Reproduction Steps

set on the Postgresql server the hostssl key in the pg_hba.conf file
no connection possible
set on the Postgresql server the host key in the pg_hba.conf file
connection is now possible and everything works as expected

Additional information

It is by the way still possible to initiate a ssl secured connection to the onlyoffice database via psql -U onlyoffice -p 5432 -h postgres.database

igwyd commented 4 days ago

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

quaternionma commented 3 days ago

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!

igwyd commented 1 day ago

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?

masa-gymmich commented 1 day ago

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 https://github.com/ONLYOFFICE/DocumentServer/issues/1708#issuecomment-1091981622 as a temporary workaround.

ONLYOFFICE / DocumentServer