Hi there.
So running into some issues with PostgresSQL I setup a secure HA Postgres DB in my environment that uses SSL certs. I previously was using a mariaDB but I want to migrate my nextcloud to my new HA postgres setup.
I am running nextcloud in a docker container. At first I attempted to migrate my DB using the migration process explained in the documentation, but this feature is disabled until the next maintenance release. I opted for using pgloader and was able to migrate my mariaDB over to my postgres DB no problem.
However, when i attempt to connect my nextcloud instance to the new database i am met with connection issues. It seems I have to set SSL in my connection but it isn’t clear to me on how I can do so. I tried a few different things but I can’t seem to get this to work.
I tried adding the self-signed SSL certs manually but I am not sure where they need to go. By default /root/.postgresql/postgresql.crt does not exist inside of the container so tried creating it and adding the certs there with no luck. I also played around a bit with setting in a few different locations but nothing seems to work.
I am able to connect to the DB using psql:
debian@Local-Cloud:/srv/containers/next-cloud/www/nextcloud/config$ psql "host=10.20.66.170 port=5432 dbname=nextcloud_db user=nextcloud sslmode=require"
Password for user nextcloud:
psql (17.2 (Debian 17.2-1.pgdg120+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.
nextcloud_db=>
Really not sure what I am doing wrong. Any assistance with this would be much appreciated. Thank you
config.php:
'dbtype' => 'pgsql',
'version' => '30.0.4.1',
'overwrite.cli.url' => 'https://nextcloud.mydomain.com',
'dbname' => 'nextcloud_db',
'dbhost' => '10.20.66.170:5432;sslmode=verify-ca;sslcert=/app/certs/server.crt;sslkey=/app/certs/server.key;sslrootcert=/app/certs/server.crt',
'dbport' => '',
'dbtableprefix' => 'oc_',
'dbuser' => 'nextcloud',
'dbpassword' => 'my_secret',
'installed' => true,
Errors:
debian@Local-Cloud:/srv/containers/next-cloud/www/nextcloud/config$ sudo docker exec -u 1000 -it nextcloud-x4kw80s48ws4ooc48go8goc8 php /app/www/public/occ db:add-missing-indices
An unhandled exception has been thrown:
Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] connection to server at "10.20.66.170", port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
connection to server at "10.20.66.170", port 5432 failed: FATAL: no pg_hba.conf entry for host "10.20.66.160", user "nextcloud", database "nextcloud_db", no encryption in /app/www/public/lib/private/DB/Connection.php:233
Stack trace:
#0 /app/www/public/3rdparty/doctrine/dbal/src/Connection.php(453): OC\DB\Connection->connect()
#1 /app/www/public/3rdparty/doctrine/dbal/src/Connection.php(411): Doctrine\DBAL\Connection->getDatabasePlatformVersion()
#2 /app/www/public/3rdparty/doctrine/dbal/src/Connection.php(318): Doctrine\DBAL\Connection->detectDatabasePlatform()
#3 /app/www/public/lib/private/DB/Connection.php(899): Doctrine\DBAL\Connection->getDatabasePlatform()
#4 /app/www/public/lib/private/DB/ConnectionAdapter.php(235): OC\DB\Connection->getDatabaseProvider()
#5 /app/www/public/lib/private/DB/QueryBuilder/QueryBuilder.php(96): OC\DB\ConnectionAdapter->getDatabaseProvider()
#6 /app/www/public/lib/private/AppConfig.php(1211): OC\DB\QueryBuilder\QueryBuilder->expr()
#7 /app/www/public/lib/private/AppConfig.php(237): OC\AppConfig->loadConfig()
#8 /app/www/public/lib/private/AppConfig.php(1351): OC\AppConfig->searchValues()
#9 /app/www/public/lib/private/App/AppManager.php(126): OC\AppConfig->getValues()
#10 /app/www/public/lib/private/App/AppManager.php(147): OC\App\AppManager->getInstalledAppsValues()
#11 /app/www/public/lib/private/legacy/OC_App.php(191): OC\App\AppManager->getInstalledApps()
#12 /app/www/public/lib/private/AppFramework/Bootstrap/Coordinator.php(48): OC_App::getEnabledApps()
#13 /app/www/public/lib/base.php(659): OC\AppFramework\Bootstrap\Coordinator->runInitialRegistration()
#14 /app/www/public/lib/base.php(1134): OC::init()
#15 /app/www/public/console.php(28): require_once('...')
#16 /app/www/public/occ(11): require_once('...')