[Bug]: After upgrade from PostgreSQL 13 to 14, Nextcloud is KO

doc75 commented 2 years ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

I just upgraded my installation from postgreSQL 13 to PostgreSQL 14 (without change of Nextcloud version). After this upgrade, Nextcloud cannot connect anymore to the DB according to the message shown in the Web browser:

Internal Server Error

The server encountered an internal error and was unable to complete your request.
Please contact the server administrator if this error reappears multiple times, please include the technical details below in your report.
More details can be found in the server log.

I found out that this is related to the change of the default authentication scheme of PostgreSQL from md5 to scram-sha-256.

If I get back to md5, it is working fine.

How can we make sure Nextcloud is able to work with this new default authentication scheme of PostgreSQL ?

I am not sure if this is something for Nextcloud or for a library you are using (in the last case let me know which one).

Steps to reproduce

Have a nextcloud instance running on pstgreSQL v13
Backup the DB with pg_dumpall
Update your PostgreSQL image to v14
Import your backup
Restart Nextcloud image

Expected behavior

Nextcloud should run without error message

Installation method

Official Docker image

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.0

Web server

Nginx

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Updated from a minor version (ex. 22.2.3 to 22.2.4)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

[X] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

{
    "system": {
        "memcache.local": "\\OC\\Memcache\\APCu",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.localhost"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "22.2.3.0",
        "overwrite.cli.url": "http:\/\/cloud.localhost",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "tls",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "587",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "loglevel": 2,
        "updater.release.channel": "stable",
        "data-fingerprint": "60ea9c3d6e3c69fb4a9364c54ea5553c",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "has_rebuilt_cache": true,
        "theme": "",
        "encryption.legacy_format_support": false,
        "encryption.key_storage_migrated": false,
        "default_phone_region": "FR",
        "app_install_overwrite": [
            "twofactor_totp"
        ]
    }
}

List of activated Apps

Enabled:
  - accessibility: 1.8.0
  - activity: 2.15.0
  - admin_audit: 1.12.0
  - bookmarks: 10.0.3
  - bruteforcesettings: 2.3.0
  - calendar: 3.0.5
  - circles: 22.1.1
  - cloud_federation_api: 1.5.0
  - comments: 1.12.0
  - contacts: 4.0.7
  - contactsinteraction: 1.3.0
  - cospend: 1.4.3
  - dashboard: 7.2.0
  - dav: 1.19.0
  - deck: 1.5.5
  - drawio: 1.0.2
  - encryption: 2.10.0
  - federatedfilesharing: 1.12.0
  - federation: 1.12.0
  - files: 1.17.0
  - files_external: 1.13.0
  - files_markdown: 2.3.5
  - files_mindmap: 0.0.26
  - files_pdfviewer: 2.3.1
  - files_rightclick: 1.1.0
  - files_sharing: 1.14.0
  - files_trashbin: 1.12.0
  - files_versions: 1.15.0
  - files_videoplayer: 1.11.0
  - firstrunwizard: 2.11.0
  - gpxpod: 4.3.0
  - impersonate: 1.9.0
  - keeweb: 0.6.8
  - logreader: 2.7.0
  - lookup_server_connector: 1.10.0
  - mail: 1.11.6
  - maps: 0.1.10
  - nextcloud_announcements: 1.11.0
  - notes: 4.3.0
  - notifications: 2.10.1
  - oauth2: 1.10.0
  - password_policy: 1.12.0
  - photos: 1.4.0
  - privacy: 1.6.0
  - provisioning_api: 1.12.0
  - quota_warning: 1.13.0
  - recommendations: 1.1.0
  - serverinfo: 1.12.0
  - settings: 1.4.0
  - sharebymail: 1.12.0
  - spreed: 12.2.3
  - support: 1.5.0
  - survey_client: 1.10.0
  - systemtags: 1.12.0
  - text: 3.3.0
  - theming: 1.13.0
  - twofactor_backupcodes: 1.11.0
  - twofactor_totp: 6.2.0
  - updatenotification: 1.12.0
  - user_status: 1.2.0
  - viewer: 1.6.0
  - weather_status: 1.2.0
  - workflowengine: 2.4.0
Disabled:
  - user_ldap

Nextcloud Signing status

Cannot get this information after upgrade to Postgre

Nextcloud Logs

Nothing found in the log, related to this issue

Additional info

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 "db" (172.30    .0.4), port 5432 failed: FATAL:  password authentication failed for user "oc_myuser" in /var/www/html/lib/private/DB/Connection.php:87
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1519): OC\DB\Connection->connect()
#1 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1041): Doctrine\DBAL\Connection->getWrappedConnection()
#2 /var/www/html/lib/private/DB/Connection.php(236): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM "...', Array, Array, NULL)
#3 /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(345): OC\DB\Connection->executeQuery('SELECT * FROM "...', Array, Array)
#4 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(287): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /var/www/html/lib/private/AppConfig.php(344): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /var/www/html/lib/private/AppConfig.php(109): OC\AppConfig->loadConfigValues()
#7 /var/www/html/lib/private/AppConfig.php(300): OC\AppConfig->getApps()
#8 /var/www/html/lib/private/legacy/OC_App.php(971): OC\AppConfig->getValues(false, 'installed_versi...')
#9 /var/www/html/lib/private/Server.php(687): OC_App::getAppVersions()
#10 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(160): OC\Server->OC\{closure}(Object(OC\Server))
#11 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object    (Pimple\Container))
#12 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(127): Pimple\Container->offsetGet('OC\\Memcache\\Fac...')
#13 /var/www/html/lib/private/ServerContainer.php(136): OC\AppFramework\Utility\SimpleContainer->query('OC\\Memcache\\Fac...', false)
#14 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query('OC\\Memcache\\Fac...')
#15 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(182): OC\AppFramework\Utility\SimpleContainer->get('OC\\Memcache\\Fac...')
#16 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(160): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(O    bject(OC\Server))
#17 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object    (Pimple\Container))
#18 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(127): Pimple\Container->offsetGet('OCP\\ICacheFacto...')
#19 /var/www/html/lib/private/ServerContainer.php(136): OC\AppFramework\Utility\SimpleContainer->query('OCP\\ICacheFacto...', false)
#20 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query('OCP\\ICacheFacto...')
#21 /var/www/html/lib/private/Server.php(1064): OC\AppFramework\Utility\SimpleContainer->get('OCP\\ICacheFacto...')
#22 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(160): OC\Server->OC\{closure}(Object(OC\Server))
#23 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object    (Pimple\Container))
#24 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(127): Pimple\Container->offsetGet('OCP\\Lock\\ILocki...')
#25 /var/www/html/lib/private/ServerContainer.php(136): OC\AppFramework\Utility\SimpleContainer->query('OCP\\Lock\\ILocki...', false)
#26 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query('OCP\\Lock\\ILocki...')
#27 /var/www/html/lib/private/Server.php(2024): OC\AppFramework\Utility\SimpleContainer->get('OCP\\Lock\\ILocki...')
#28 /var/www/html/lib/private/Files/View.php(118): OC\Server->getLockingProvider()
#29 /var/www/html/lib/private/Server.php(435): OC\Files\View->__construct()
#30 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(160): OC\Server->OC\{closure}(Object(OC\Server))
#31 /var/www/html/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object    (Pimple\Container))
#32 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(127): Pimple\Container->offsetGet('OC\\Files\\Node\\H...')
#33 /var/www/html/lib/private/ServerContainer.php(136): OC\AppFramework\Utility\SimpleContainer->query('OC\\Files\\Node\\H...', false)
#34 /var/www/html/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query('OC\\Files\\Node\\H...')
#35 /var/www/html/lib/private/Server.php(1387): OC\AppFramework\Utility\SimpleContainer->get('OC\\Files\\Node\\H...')
#36 /var/www/html/lib/base.php(594): OC\Server->boot()
#37 /var/www/html/lib/base.php(1089): OC::init()
#38 /var/www/html/console.php(48): require_once('/var/www/html/l...')
#39 /var/www/html/occ(11): require_once('/var/www/html/c...')
#40 {main}

szaimen commented 2 years ago

Thank you for taking the time to report a bug! 👍

As this seems to be a setup issue I would like to ask you to raise your question in the forums: https://help.nextcloud.com If you wish support with setup issues from Nextcloud GmbH we offer this as part of the Nextcloud subscription. Learn more about this at https://nextcloud.com/enterprise/

doc75 commented 2 years ago

@szaimen I am not sure why you think this is a setup issue. It seems to me that when migrating from PostgreSQL 13 to 14, it is currently not possible to have Nextcloud working with the new default authentication scheme.

I have not found in the doc any information on how to support this new scheme or how to migrate to this new scheme.

It is a problem to me as Nextcloud will not benefit from the new scheme which is more robust than md5.

doc75 commented 2 years ago

@szaimen after some more research (I have spent the day on this yesterday already 😉 ), I found out how to solve this. Here is the information in case somebody else needs it. It could be interesting to add it in the documentation (in a troubleshooting section).

This procedure should allow you to move from md5 to scram-sha-256 authentication scheme with PrstgreSQL DB. After your upgrade, make sure to change the password of the users.

Check the users of your nextcloud DB:

select * from pg_shadow;

It should return something like this (md5 is not the one of a real password in this example):

-----------+----------+-------------+----------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+----------+-----------
 oc_user    |    12345 | t           | f        | f       | f            | md5de1b45a87f674a6bd2ecf299340b7767 |          | 
 nextcloud |       10 | t           | t        | t       | t            | md582d1e5440ec2196ab71b72fb448f9a10 |          | 
(2 rows)

Update the password

ALTER ROLE nextcloud WITH PASSWORD 'yourSecuredPassword';
ALTER ROLE oc_user WITH PASSWORD 'yourSecuredPassword';

Now you should something like this when launching select * from pg_shadow;:

-----------+----------+-------------+----------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+----------+-----------
 oc_user    |    12345 | t           | f        | f       | f            | SCRAM-SHA-256$4096:xxxxxxxxxxxxxxxxxxxx |          | 
 nextcloud |       10 | t           | t        | t       | t            | SCRAM-SHA-256$4096:yyyyyyyyyyyyyyyyyyyyy |          | 
(2 rows)

That's it.

AdrienMatricon commented 1 year ago

@doc75 What password do you use for oc_user here ? The one I use to log in as that user doesn't seem to make things work

doc75 commented 1 year ago

@doc75 What password do you use for oc_user here ? The one I use to log in as that user doesn't seem to make things work

oc_user password to use is the one found in the config.php of your nextcloud installation (I don’t remind the key name by heart)

AdrienMatricon commented 1 year ago

Oh wow, I had forgotten that was a thing. Thanks a lot!

getdev44 commented 5 months ago

Thanks a lot. Yesterday evening I tried upgrade from postgres 13 to 16 on nextcloud 27. So I had expermiented the same 'internal server error' message. Nothing on my docker service logs, not more in nextcloud.log... After updating password all work fine.

nextcloud / server