Permission Denied to Regular Read/Write User to Create New Partitioned Table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Permission Denied to Regular Read/Write User to Create New Partitioned Table

KARIN SUSANNE HILBERT
I manage a PostgreSQL database for the GITLAB application.
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.
Thanks, 
Karin Hilbert

Reply | Threaded
Open this post in threaded view
|

Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table

KARIN SUSANNE HILBERT
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 <[hidden email]>
Sent: Thursday, November 5, 2020 10:44 AM
To: Hilbert, Karin <[hidden email]>; [hidden email] <[hidden email]>
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?


Yours,
Laurenz Albe
--
Cybertec | https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cybertec-postgresql.com%2F&amp;data=04%7C01%7Cioh1%40psu.edu%7Ccbc363b1506c4e79820308d881a1a3f4%7C7cf48d453ddb4389a9c1c115526eb52e%7C0%7C1%7C637401878524136413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=ouGaunFdzbKCkCSlHP1eDiTwZXRHXraDeB7npzWbc8M%3D&amp;reserved=0