Database pg_depend update failes

[scs_it_team]

Upgrading from 10.0.12 to 10.1.1 fails due less permissions.

espocrm.log:
ERROR: (0) Failed to rebuild database schema. Rebuild database early-rebuild error.
[object] (RuntimeException(code: 0): Rebuild database early-rebuild error. at /var/www/espocrm/application/Espo/Core/Utils/Database/Schema/SchemaManager.php:163)
[previous exception] [object] (RuntimeException(code: 0): An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for table pg_depend
...
...

postgresql.log:
espocrmdbuser@espocrmdb ERROR: permission denied for table pg_depend
espocrmdbuser@espocrmdb CONTEXT: SQL statement "UPDATE pg_depend

What I tried to check the permissions on Postgresql:
postgres=# \c espocrmdb espocrmdbuser
You are now connected to database "espocrmdb" as user "espocrmdbuser".
espocrmdb=> SELECT has_table_privilege('espocrmdbuser', 'pg_catalog.pg_depend', 'SELECT');
has_table_privilege
---------------------
t

espocrmdb=> SELECT * FROM pg_catalog.pg_depend LIMIT 1;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
1247 | 12002 | 0 | 1259 | 12000 | 0 | i


Any hints?

[yuri]

We updated Doctrine DBAL library. The new version uses Identity column for auto-increments. The migration script might require more permissions to do its operations?

https://github.com/espocrm/espocrm/issues/3801

[scs_it_team]

the update script runs as "root". espocrmdbuser has full access to the database. how to change the permission for pg_depend?
shoud be done by the update-script or not?

[ThomasB]

AI says this:

By default, all users (PUBLIC) have SELECT (read) permission on pg_depend, while only superusers have write (INSERT, UPDATE, DELETE) permissions.

Because pg_depend is a core system catalog inside pg_catalog that tracks object dependencies, PostgreSQL restricts direct modifications to prevent catalog corruption.

Default Privileges on pg_depend:

• Read Access (SELECT). Granted to PUBLIC by default, allowing any user who can connect to the database to query object dependencies.
• Write Access (INSERT, UPDATE, DELETE). Restricted strictly to superusers (typically the postgres role). Even database owners or admin users on managed cloud services (such as AWS RDS, Azure, or DigitalOcean) do not have write access to system catalogs

Getting "Permission Denied" on UPDATE or INSERT
Manually modifying pg_depend (for example, changing deptype when converting a SERIAL column to IDENTITY) will fail for non-superusers. Instead of editing pg_depend directly, use standard DDL commands like ALTER TABLE, ALTER SEQUENCE ... OWNED BY, or DROP ... CASCADE so PostgreSQL updates the catalog safely.


https://stackoverflow.com/questions/...s-to-modify-pg

[Manuel Capeder]

I got it working by giving the espocrm user superuser privileges for the update and revoking it afterwards.

psql postgres
postgres=# ALTER USER espocrm WITH SUPERUSER;

Then update espocrm with:

php command.php upgrade

And revoking privileges again

postgres=# ALTER USER espocrm WITH NOSUPERUSER;

DBAL knew about this issue and provided an alternative solution. It would be interesting to know why the developer chose the approach with SU privileges required. I assume due to the mentioned trade-off that the original sequence object is dropped.