[Bug]: PostgreSQL database `GRANT`s out-of-date (was "db:add-missing-indices not working anymore")

[neeral85]

⚠️ 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 OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

command sudo -u www-data php /var/www/nextcloud/occ db:add-missing-indices reports Insufficient privilege.

with 31.0.7 the command runs as expected. adter upgrade to 31.0.8 I get this errors:

In ExceptionConverter.php line 87:

  An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public

In Exception.php line 24:

  SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public

In Connection.php line 33:

  SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public

Steps to reproduce

1. sudo -u www-data php /var/www/nextcloud/occ db:add-missing-indices

Expected behavior

no error

Nextcloud Server version

31

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.3

Web server

Apache (supported)

Database engine version

PostgreSQL

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

Updated from a MINOR version (ex. 32.0.1 to 32.0.2)

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

List of activated Apps

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

Additional info

No response

[kesselb]

Thanks for your report 🙏

The error "Insufficient privilege: 7 ERROR: permission denied for schema public" is coming from your pgsql server. Are you sure you have the right permissions?

I'm also using pgsql (16.8) and add-missing-indices works.

Here's how I usually create the database:

PGHOST=postgres PGPORT=5432 PGUSER=postgres PGPASSWORD=postgres psql \
-c "CREATE ROLE server WITH LOGIN PASSWORD 'server123'" \
-c "CREATE DATABASE server OWNER server"

Our installer uses a bit different way of assigning, the permissions so maybe there's something off. Is it possible that you did upgrade pgsql from recently?

[joshtrichards]

with 31.0.7 the command runs as expected. adter upgrade to 31.0.8 I get this errors:

Do you know if you there were missing any indices before you ran it in 31.0.7 and/or was there any output? The command may have worked simply because it didn't attempt to add any.

[neeral85]

Our installer uses a bit different way of assigning, the permissions so maybe there's something off. Is it possible that you did upgrade pgsql from recently?

The server was installed using the Nextcloud VM Appliance Script. However, the database was upgraded several times over the years.

Do you know if you there were missing any indices before you ran it in 31.0.7 and/or was there any output? The command may have worked simply because it didn't attempt to add any.

I did recover a vm from previous snapshot to test this. There was no missing index with 31.0.7 and command was executed successfully.

[joshtrichards]

There was no missing index with 31.0.7 and command was executed successfully.

That explains why you reported it with 31.0.8. It's not actually a 31.0.8 regression, however (fortunately!).

The server was installed using the Nextcloud VM Appliance Script. However, the database was upgraded several times over the years.

The VM script needs to be updated from the looks of it:

vm/nextcloud_install_production.sh

Lines 367 to 370 in 0fe3e7a

	sudo -u postgres psql <<END
	CREATE USER $PGDB_USER WITH PASSWORD '$PGDB_PASS';
	CREATE DATABASE nextcloud_db WITH OWNER $PGDB_USER TEMPLATE template0 ENCODING 'UTF8';
	END

That does not match our current docs:

https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html#postgresql-database

The requirements changed for the public schema ~3 years ago, for example.

Depending on what version of Ubuntu you install it on, there may be different PostgreSQL versions. Surprised this did not come before with the VM script. I'll move this over that repo.

[neeral85]

nextcloud_db=# \dn+ public

Schema-Liste

Name	Owner	Access privileges	Description
public	pg_database_owner	pg_database_owner=UC/pg_database_owner+ =U/pg_database_owner	standard public schema

---

nextcloud_db=# \l+
Datenbanken

Name	Owner	Encoding	Locale Provider	Collate	Ctype	ICU Locale	ICU Rules	Access privileges	Size	Tablespace	Description
nextcloud_db	postgres	UTF8	libc	en_US.UTF-8	en_US.UTF-8				95 MB	pg_default
postgres	postgres	UTF8	libc	en_US.UTF-8	en_US.UTF-8				7500 kB	pg_default	default administrative connection database

This does solve the issue
ALTER DATABASE nextcloud_db OWNER TO ncadmin;

[small1]

We haven't had any reports like this. We should update the script a bit regardless. But if this was a regular thing we should have had more reports. I just tested on three different instances and it does work on them.