Обсуждение: Permission Denied to Regular Read/Write User to Create New Partitioned Table
Permission Denied to Regular Read/Write User to Create New Partitioned Table
От
"Hilbert, Karin"
Дата:
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
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_dynamicat 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
Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table
От
"Hilbert, Karin"
Дата:
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.albe@cybertec.at>
Sent: Thursday, November 5, 2020 10:44 AM
To: Hilbert, Karin <ioh1@psu.edu>; pgsql-novice@postgresql.org <pgsql-novice@postgresql.org>
Subject: Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table
Sent: Thursday, November 5, 2020 10:44 AM
To: Hilbert, Karin <ioh1@psu.edu>; pgsql-novice@postgresql.org <pgsql-novice@postgresql.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?
Yours,
Laurenz Albe
--
Cybertec | https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cybertec-postgresql.com%2F&data=04%7C01%7Cioh1%40psu.edu%7Ccbc363b1506c4e79820308d881a1a3f4%7C7cf48d453ddb4389a9c1c115526eb52e%7C0%7C1%7C637401878524136413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ouGaunFdzbKCkCSlHP1eDiTwZXRHXraDeB7npzWbc8M%3D&reserved=0
> 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&data=04%7C01%7Cioh1%40psu.edu%7Ccbc363b1506c4e79820308d881a1a3f4%7C7cf48d453ddb4389a9c1c115526eb52e%7C0%7C1%7C637401878524136413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ouGaunFdzbKCkCSlHP1eDiTwZXRHXraDeB7npzWbc8M%3D&reserved=0