Connection to external Postgresql via SSL

[quaternionma]

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

1. set on the Postgresql server the hostssl key in the pg_hba.conf file
2. no connection possible
3. set on the Postgresql server the host key in the pg_hba.conf file
4. 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]

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]

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]

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]

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.

[Rohmilchkaese]

Sorry for warming this up again!

I used Helm to install. I had the same problem with the DB. So one thing i encounter now:

docservice [2025-03-05T19:55:58.763] [WARN] [docId] [userId] nodeJS - Express server starting...
docservice [2025-03-05T19:55:58.776] [WARN] [docId] [userId] nodeJS - notifyLicenseExpiration(): expiration date is not defined
docservice [2025-03-05T19:55:58.779] [WARN] [docId] [userId] nodeJS - notifyLicenseExpiration(): expiration date is not defined
docservice [2025-03-05T19:55:59.020] [ERROR] [docId] [userId] nodeJS - DB table "task_result" does not exist
docservice [2025-03-05T19:55:59.021] [ERROR] [docId] [userId] nodeJS - DB table "doc_changes" does not exist

I guess the DB migrations come from one of the jobs?