[Bug]: NC 25 RC5 not compatible with postgresql 15

[SimplyCorbett]

⚠️ This issue respects the following points: ⚠️

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

Bug description

Postgresql 15 was released today and nextcloud 25 RC5 and it is not happy with postgresql 15. Any ETA on a fix?

Steps to reproduce

Install PHP 8.1-fpm and postgresql 15 and try installing NC 25 RC5 on debian 11.

Install using source https://download.nextcloud.com/server/prereleases/nextcloud-25.0.0rc5.zip

Expected behavior

Works as expected

Installation method

No response

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

PostgreSQL

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

Fresh Nextcloud Server install

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

No response

List of activated Apps

All standard apps on install

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

[szaimen]

Did you get some error logs with that or what doesnt work?

[SimplyCorbett]

It doesn't install. There are no errors listed in the nextcloud.log but this is output on the website:

[SimplyCorbett]

Likely related to this:

https://www.postgresql.org/docs/release/15.0/

Remove PUBLIC creation permission on the [public schema](https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PUBLIC) (Noah Misch)

[szaimen]

An easy workaround would be creating the user and database manually beforehand:

https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html#:~:text=then%20a%20postgres%3D%23%20prompt%20will%20appear.%20now%20enter%20the%20following%20lines%20and%20confirm%20them%20with%20the%20enter%20key%3A

[jotoeri]

It doesn't install. There are no errors listed in the nextcloud.log but this is output on the website:

Same error on App CI now.
https://github.com/nextcloud/forms/actions/runs/3254396564/jobs/5343637661#step:7:71
https://github.com/nextcloud/contacts/actions/runs/3253872431/jobs/5341551723#step:7:69

[SimplyCorbett]

An easy workaround would be creating the user and database manually beforehand:

That doesn't work either.

postgres=# CREATE DATABASE nextcloud TEMPLATE template0 ENCODING 'UNICODE';
ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".

[szaimen]

Does the following work?

CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UTF8';
CREATE USER nextcloud_user WITH PASSWORD 'nextcloud_user_password' CREATEDB;
ALTER DATABASE nextcloud_db OWNER TO nextcloud_user ;

[SimplyCorbett]

Does the following work?

CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UTF8';
CREATE USER nextcloud_user WITH PASSWORD 'nextcloud_user_password' CREATEDB;
ALTER DATABASE nextcloud_db OWNER TO nextcloud_user ;

No.

postgres=# CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UTF8';
ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".

[szaimen]

postgres=# CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UTF8';
ERROR: encoding "UTF8" does not match locale "en_US"
DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1".

found this: https://stackoverflow.com/a/33030572

[SimplyCorbett]

postgres=# CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UTF8';
ERROR: encoding "UTF8" does not match locale "en_US"
DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1".

found this: https://stackoverflow.com/a/33030572

Doesn't work.

postgres=# CREATE DATABASE nextcloud TEMPLATE template0 ENCODING 'UNICODE' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';
ERROR:  invalid locale name: "en_US.UTF-8"

I'm not confident in nextcloud actually working even if I can get the database created. Have you contacted any of your enterprise customers or have one of your team members figure out the incompatibilities with postgresql 15?

[szaimen]

Honestly I rather think that your database is wrongly configured. However postgresql 15 is obviously not supported yet.

[SimplyCorbett]

Honestly I rather think that your database is wrongly configured. However postgresql 15 is obviously not supported yet.

It's installed as per https://www.postgresql.org/download/linux/debian/

It's the same way I've installed nextcloud with postgresql for the last couple of years.

Have you tested yourself?

[szaimen]

Have you tested yourself?

no

[youduda]

@Neeham Did you upgrade your postgresql cluster? https://www.postgresql.org/docs/current/pgupgrade.html

[Neeham]

Decided to remove all volume/container etc... and do a fresh install.
Same error on NC 24 (PostgreSQL 15):
Nextcloud: Initializing nextcloud 24.0.6.1
PostgreSQL: starting PostgreSQL 15.0 (Debian 15.0-1.pgdg110+1)

The error:
Error while trying to initialise the database: An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for schema public LINE 1: CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version))

[marcelklehr]

Also getting the same error on CI: https://github.com/nextcloud/recognize/actions/runs/3262003961/jobs/5358144416

[Gchbg]

FWIW I can confirm that with NC 24.0.6 migrating from PostgreSQL 14 to 15 using pg_dumpall works fine.

[jeffrson]

@Gchbg You moved to 24.0.6 before upgrading PgSQL?

For me, the upgrade process from 23.0.10 to 24.0.6 failed, because I moved to PgSQL15 beforehand:

Initializing nextcloud 24.0.6.1 ...
Upgrading nextcloud from 23.0.10.1 ...
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
Turned on maintenance mode
Updating database schema
Exception: Database error when running migration 24000Date20211222112246 for app core
An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public
LINE 1: CREATE TABLE oc_reactions (id BIGSERIAL NOT NULL, parent_id ...
                     ^
Update failed
Maintenance mode is kept active
Resetting log level

[szaimen]

@jeffrson how did you upgrade? can you point out the exact steps?

[jeffrson]

My Nextcloud is running via Docker. I was starting from some earlier 23.0.x (23.0.6.1, but I'm not sure).

I used pg_dumpall to save the database, updated PgSQL (13 to 15), reimported the dump. Now I had to reset all DB user passwords (md5 hash to scram-sha-256).

Then my Nextcloud was running, still 23.0.x, but PgSQL 15. I pulled the latest v23: docker pull nextcloud:23 - restart, everything seems fine. docker logs showed, Nextcloud was running a minor upgrade.
Finally I pulled v24: docker pull nextcloud:24 - when starting (docker compose up) the upgrade failed with the above message.

I was afraid, there might have been something wrong with the database upgrade (first time for me), but the same steps did work with PgSQL14: The upgrade to 24.0.6 was successful now (had to trigger it via occ, though, because it did not start automatically because of maintenance mode).

I hope these steps are clear enough.

[szaimen]

All right! I think after the import you need to run GRANT ALL PRIVILEGES ON SCHEMA public TO $DB_USER in order to make it work.

[jeffrson]

$DB_USER is the same as "dbuser" from config.php?

I wonder, because I realized today, that although I specified a certain user "DBAdmin" via environment POSTGRES_USER_FILE, the initial installation from Docker created "oc_dbadmin". Both are used (DBAdmin is owner of database, for example).

Thanks!

[vitormattos]

The problem occur because with PostgreSQL, when we use an user that have permission to create new users, the setup will do:

• CREATE USER
• REVOKE ALL PRIVILEGES ON DATABASE nextcloud FROM PUBLIC
  These steps are good to get the setup more secure.

In release notes of version 15 was explained that common users haven't more permission to schema public, the side effect of this is that won't create tables. I followed this suggestion from release notes:

For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

And this solved the problem, adding this row to setup flow:

GRANT CREATE ON SCHEMA public TO user

I opened this PR #34645 fixing the problem

[Gchbg]

@Gchbg You moved to 24.0.6 before upgrading PgSQL?

I was and still am running 24.0.6. On that version upgrading PgSQL from 14 to 15 went smoothly.

Note also that my script does this on first installation of Nextcloud, before Nextcloud gets to run at all:

alter default privileges revoke all on types from public; alter default privileges revoke all on routines from public; revoke all on database postgres from public; revoke all on database template1 from public; revoke all on database template0 from public; create role someuser login noinherit password 'somepassword'; create database somedb owner someuser; revoke all on database somedb from public;

(sorry about the formatting)

[vitormattos]

I was and still am running 24.0.6. On that version upgrading PgSQL from 14 to 15 went smoothly.

The problem only occur on the first setup using PostgreSQL 15 because need to give the right permissions to database user.

If you have a Nextcloud running and only upgrade the database, this will work fine.

[Gchbg]

The problem occur because with PostgreSQL, when we use an user that have permission to create new users, the setup will do:

* CREATE USER

* REVOKE ALL PRIVILEGES ON DATABASE nextcloud FROM PUBLIC
  These steps are good to get the setup more secure.

In release notes of version 15 was explained that common users haven't more permission to schema public, the side effect of this is that won't create tables. I followed this suggestion from release notes:

For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

And this solved the problem, adding this row to setup flow:

GRANT CREATE ON SCHEMA public TO user

I opened this PR #34645 fixing the problem

I tried a clean new installation of NextCloud 25.0.0.0 with the changes from your PR applied manually to the PHP code, with PostgreSQL 15. Unfortunately I got the error:

Error while trying to initialise the database: An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for schema public LINE 1: CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, versi...

I don't know if this is relevant, but what I do is create the database myself first:

create role nextcloud login noinherit password 'somepassword'; create database nextcloud owner nextcloud; revoke all on database nextcloud from public; grant create on schema public to nextcloud;

And then I run the Nextcloud setup with your modifications.

TIA for any insight.

[mmBesar]

No update yet!

[szaimen]

Indeed. The PR #34645 is not yet merged.

[MartinX3]

Will there be a v25.0.2 release?

[szaimen]

Yes

[adripo]

I had the same problem as described by @jeffrson after following his same steps and fixed by executing

in the db container:

psql -d nextcloud -U nextcloud -c 'GRANT CREATE ON SCHEMA public TO $OC_USER'

After that I get back to nextcloud container and executed as user www-data:

./occ upgrade
./occ maintenance:mode --off

[Narsskrarc]

Not shure if it's still relevant...

I upgraded from NC 25 to NC 26.0.10 with postgresql 15.
In the upgrade process I got the error

nextcloud-db-1     | 2024-01-08 10:50:06.386 UTC [47] ERROR:  permission denied for schema public
nextcloud-db-1     | 2024-01-08 10:50:06.386 UTC [47] STATEMENT:  CREATE INDEX dname ON oc_circles_circle (display_name)

I had to change the owner of the tablespace to the $OC_USER:
psql -d nextcloud -U nextcloud -c 'ALTER TABLESPACE pg_default OWNER TO $OC_USER'
It's not enough to grant CREATE to this user (and it had the permission already).

It seems there are not that many people using postgresql as database for nextcloud...