Bare metal setup with postgres fails at initial 'invoke update'

[3inven]

InvenTree noob trying to manually set up a bare metal installation (ubuntu 22.04, python 3.10, postgresql 16).
Added database access details and static/media/backup dirs in config.yaml.

The initial 'invoke update' fails with the following 3 errors:

Error 1:
Updating plugin registry hash: ...
Failed to update plugin registry hash: relation "common_inventreesetting" does not exist

Error 2:
Running migrations:
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...

Error 3:
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...

Tried granting permissions for inventree user in the public schema as in https://stackoverflow.com/a/12236582:
psql inventree -c "GRANT ALL ON ALL TABLES IN SCHEMA public to inventree;"
psql inventree -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to inventree;"
psql inventree -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to inventree;"

It did not help. What am I missing?

[SchrodingersGat]

Error 1:
Updating plugin registry hash: ...
Failed to update plugin registry hash: relation "common_inventreesetting" does not exist

This has been addressed in latest firmware, this error message has been removed, you can ignore this one.

Error 2:
Running migrations:
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...

Error 3 is the same thing here. Likely that the database username/password you have configured in your InvenTree settings does not have the correct permissions to access the database

[3inven]

Thank you very much for the quick reply. On further investigation, this CREATE permission problem is introduced by PostgreSQL 15 and later (I am using the latest PostgreSQL 16 release) where CREATE permissions in the public schema are restricted to the owner of a database. This post offered the insight.

For the moment the quick fix that worked for me was to change the ownership of my InvenTree database to the InvenTree database user using a query like:

ALTER DATABASE myinventreedb OWNER TO myinventreeuser;

after which the InvenTree setup completes smoothly. Perhaps a note added to the Bare Metal Setup page in the InvenTree documentation could save some time for PostgreSQL 15+ users.

This may not be the ideal fix though. On a cursory browse of the PostgreSQL 15 release notes, a more secure solution appears to be to create a schema named after the InvenTree database user and grant the InvenTree database user CREATE permissions in that schema. I have not worked out that solution yet. Thanks again for your help.

[SchrodingersGat]

Great insights @3inven - if you do work out a more comprehensive solution please do let us know so they we can update the documentation