Postgresql SSL support

[BobyMCbobs]

Issue

Postgres configuration should support SSL mode.

I'm wanting to use this with a Postgres distribution that enforces SSL on connection.
Currently, the error is:

Initializing nextcloud 19.0.3.1 ...
Initializing finished
New nextcloud instance
Installing with PostgreSQL database
starting nextcloud installation
PostgreSQL username and/or password not valid
 -> You need to enter details of an existing account.
retrying install...
An unhandled exception has been thrown:
OC\DatabaseException: An exception occurred while executing 'SHOW SERVER_VERSION':

Failed to connect to the database: An exception occurred in driver: SQLSTATE[08006] [7] ERROR:  pgbouncer cannot connect to server
ERROR:  SSL required in /var/www/html/lib/private/legacy/OC_DB.php:73
Stack trace:
#0 /var/www/html/lib/private/legacy/OC_DB.php(139): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
#1 /var/www/html/lib/private/legacy/OC_Util.php(971): OC_DB::executeAudited(Array)
#2 /var/www/html/lib/private/legacy/OC_Util.php(951): OC_Util::checkDatabaseVersion()
#3 /var/www/html/lib/private/Console/Application.php(161): OC_Util::checkServer(Object(OC\SystemConfig))
#4 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 /var/www/html/occ(11): require_once('/var/www/html/c...')
#6 {main}retrying install...
An unhandled exception has been thrown:
OC\DatabaseException: An exception occurred while executing 'SHOW SERVER_VERSION':

Failed to connect to the database: An exception occurred in driver: SQLSTATE[08006] [7] timeout expired in /var/www/html/lib/private/legacy/OC_DB.php:73
Stack trace:
#0 /var/www/html/lib/private/legacy/OC_DB.php(139): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
#1 /var/www/html/lib/private/legacy/OC_Util.php(971): OC_DB::executeAudited(Array)
#2 /var/www/html/lib/private/legacy/OC_Util.php(951): OC_Util::checkDatabaseVersion()
#3 /var/www/html/lib/private/Console/Application.php(161): OC_Util::checkServer(Object(OC\SystemConfig))
#4 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 /var/www/html/occ(11): require_once('/var/www/html/c...')
#6 {main}retrying install...
An unhandled exception has been thrown:
OC\DatabaseException: An exception occurred while executing 'SHOW SERVER_VERSION':

Failed to connect to the database: An exception occurred in driver: SQLSTATE[08006] [7] ERROR:  pgbouncer cannot connect to server
ERROR:  SSL required in /var/www/html/lib/private/legacy/OC_DB.php:73
Stack trace:
#0 /var/www/html/lib/private/legacy/OC_DB.php(139): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
#1 /var/www/html/lib/private/legacy/OC_Util.php(971): OC_DB::executeAudited(Array)
#2 /var/www/html/lib/private/legacy/OC_Util.php(951): OC_Util::checkDatabaseVersion()
#3 /var/www/html/lib/private/Console/Application.php(161): OC_Util::checkServer(Object(OC\SystemConfig))
#4 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 /var/www/html/occ(11): require_once('/var/www/html/c...')
#6 {main}retrying install...

To fix this, a variable should be available: POSTGRES_SSLMODE which sets ?sslmode= in the connection string (or sslmode variable equivalent)

[BobyMCbobs]

Same issue here, is there a workaround to this?

I haven't found one

[haslersn]

Same problem here.

[loganmarchione]

Does this not work?

nextcloud/server#4110 (comment)

[RegisHubelia]

Same here - Tried @loganmarchione workaround, and I see the string in the config.php file with the options, but still gives the same error...

[jessebot]

Bit of advice after playing with this for a while...

In addition to using the postgresql host as a full connection string, you may also need to provide env vars for postgresql, otherwise it may always look in /root/.postgresql/ or ~/.postgresql/ for the certs, even if you provide them inline like this:

mynextcloudpostgreshostname:5432;sslmode=verify-full;sslrootcert=/etc/secrets/ca/ca.crt;sslcert=/etc/secrets/nextcloud/tls.crt;sslkey=/etc/secrets/nextcloud/tls.key

To fix where it looks for the certs, try setting the following env vars to the paths you want to use (full docs here):

• PGSSLCERT
• PGSSLKEY
• PGSSLROOTCERT

Spent a while on this, so hope that helps some friends down the line.