PostgreSQL 15 database update is not working

[Unfaehig]

Got this error today after watchtower update. Not sure if it is some issue with Postgre Container, but till it worked before the last update and only the Nextcloud was updated, i guess it is related to the upgrade process.

I'm using Postgresql 15 latest as DB
and latest Nextcloud fpm image

Initializing nextcloud 25.0.2.3 ...
Upgrading nextcloud from 25.0.1.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
Updated database
Disabled incompatible app: extract
Updating <photos> ...
Exception: Database error when running migration 20003Date20221102170153 for app photos
An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public
LINE 1: CREATE TABLE oc_photos_albums_collabs (id BIGSERIAL NOT NULL...```

[Unfaehig]

OK seems Postgres has made major changes to the security concept, introducing that you need to grant permissions for each user who wants to create things in public schema. soruce

Means if you are like me, coming from Postgres 14 and upgraded a while back ago to Postgres 15. You need to assign your Nextcloud Admin user, not the DB admin user you defined for Container, permissions to create new Data Tables.
Means execute the following in your database (login to your container and use psql):
GRANT ALL ON SCHEMA public TO oc_ADMIN;
Please change ADMIN to the username of your admin account, but don't get rid of "oc_"! if you are unsure please use \dt, to see all tables and current owner of them.

It could happen, that the Container not auto execute the upgrade process again. To do so use:
docker exec --user www-data CONTAINERNAME php occ upgrade and
docker exec --user www-data CONTAINERNAME php occ maintenance:mode --off

P.S. would be nice, if we get a notification after an upgraded DB is detected.
Something like: warning we detect your have upgraded to Postgres 15 please be sure you grant permissions to all on public schema to your admin user!

[joshtrichards]

Hi @Unfaehig - Glad you found a solution.

The Nextcloud Admin Manual was reworked (see nextcloud/documentation#9227) around the same time as your original report. Similar GRANTs/etc were added to the docs then.

would be nice, if we get a notification after an upgraded DB is detected. Something like: warning we detect your have upgraded to Postgres 15 please be sure you grant permissions to all on public schema to your admin user!

This is out-of-scope for the Docker image. If anywhere, it would perhaps belong the Nextcloud Server level.