We manage the database & the application admins manage the application.
We just recently upgraded to PostgreSQL v12.4 for this database & the application was upgraded to v13.4.4.
Our database cluster is on a Linux VM, with OS:
Flavor: redhat_7
Release: 3.10.0-1160.2.1.el7.x86_64
We normally create our databases & objects with a DB Owner account & the application connects with a regular user account that has read/write permissions. For GITLAB, the application updates the database as required during the application upgrade. We allow the application to connect as the DB Owner account only during those times & switch back to the regular account when the upgrade is done.
However, with the latest application upgrade, GITLAB is now using partitioned tables. When the table reaches a certain size, the application tries to create a new partitioned table, which errors out for the regular user account:
2020-11-03 18:21:31 EST [56435]: [88-1] db=xxxxxx,user=xxxxxx ERROR: permission denied for schema gitlab_partitions_dynamic at character 28
2020-11-03 18:21:31 EST [56435]: [89-1] db=xxxxxx,user=xxxxxx STATEMENT: CREATE TABLE IF NOT EXISTS "gitlab_partitions_dynamic"."audit_events_part_5fc467ac26_202105"
I tried to solve the error by granting the regular user acct "usage, create" on the schema:
psql -d xxxxxx -U xxxxxxxxxx -c "grant usage, create on schema gitlab_partitions_dynamic to xxxxxx;"
Now we're getting a "must be owner" error:
2020-11-04 12:21:17 EST [28013]: [420-1] db=xxxxxx,user=xxxxxx ERROR: must be owner of table audit_events_part_5fc467ac26
2020-11-04 12:21:17 EST [28013]: [421-1] db=xxxxxx,user=xxxxxx STATEMENT: CREATE TABLE IF NOT EXISTS "gitlab_partitions_dynamic"."audit_events_part_5fc467ac26_202105"
The errors occur every 6 hours since this past Sunday.
Is there any way to resolve this other than to allow the application to always connect as the DB Owner acct?
We could let it slide for the DEV database, but we really don't want to do that for the PROD database.
Any help would be appreciated.
We're planning to migrate the PROD database/application to the new versions soon.
