Message-ID: <092337ddb6c1320edd7ab512bac807f5c0ea259b.camel@cybertec.at>
Subject: Re: Permission Denied to Regular Read/Write User to Create New
 Partitioned Table
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Hilbert, Karin" <ioh1@psu.edu>, "pgsql-novice@postgresql.org"
	 <pgsql-novice@postgresql.org>
Date: Thu, 05 Nov 2020 16:44:08 +0100
In-Reply-To: 
 <DM6PR02MB4939A862EE2E7E7259233B8289EE0@DM6PR02MB4939.namprd02.prod.outlook.com>
References: 
	<DM6PR02MB49391323EF5612D6CC34C7D689EF0@DM6PR02MB4939.namprd02.prod.outlook.com>
	 <DM6PR02MB4939A862EE2E7E7259233B8289EE0@DM6PR02MB4939.namprd02.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com