From: | "Hilbert, Karin" <ioh1(at)psu(dot)edu> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table |
Date: | 2020-11-06 16:11:29 |
Message-ID: | DM6PR02MB4939F0BF43FECC8B60699AA889ED0@DM6PR02MB4939.namprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thanks Laurenz,
That's not what I wanted to hear, but what I expected.
You are correct, we cannot modify the application code.
Regards, Karin
________________________________
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Thursday, November 5, 2020 10:44 AM
To: Hilbert, Karin <ioh1(at)psu(dot)edu>; pgsql-novice(at)postgresql(dot)org <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table
On Thu, 2020-11-05 at 14:25 +0000, Hilbert, Karin wrote:
> 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:
Yes, granting CREATE on the schema is the proper remedy for that error.
> 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"
Seems that the user is trying to create a partition for a table that it doesn't own.
You must be the owner of a table to create a new partition for it.
If that happens during regular operation, your only choice will be to make the
application user the owner of the partitioned table (or a member of the owner).
I guess you cannot modify the application code, right?
From | Date | Subject | |
---|---|---|---|
Next Message | Hadil Bader | 2020-12-10 09:31:16 | Is it doable to employ functional programming in implementing intraoperation parallelism in Postgres? |
Previous Message | Laurenz Albe | 2020-11-05 15:44:08 | Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table |