public inbox for [email protected]
help / color / mirror / Atom feedPermission Denied to Regular Read/Write User to Create New Partitioned Table
3+ messages / 2 participants
[nested] [flat]
* Permission Denied to Regular Read/Write User to Create New Partitioned Table
@ 2020-11-05 14:25 Hilbert, Karin <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Hilbert, Karin @ 2020-11-05 14:25 UTC (permalink / raw)
To: pgsql-novice
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table
@ 2020-11-05 15:44 Laurenz Albe <[email protected]>
parent: Hilbert, Karin <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2020-11-05 15:44 UTC (permalink / raw)
To: Hilbert, Karin <[email protected]>; pgsql-novice
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table
@ 2020-11-06 16:11 Hilbert, Karin <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Hilbert, Karin @ 2020-11-06 16:11 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; 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 <[email protected]>
Sent: Thursday, November 5, 2020 10:44 AM
To: Hilbert, Karin <[email protected]>; [email protected] <[email protected]>
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&...
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-11-06 16:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-05 14:25 Permission Denied to Regular Read/Write User to Create New Partitioned Table Hilbert, Karin <[email protected]>
2020-11-05 15:44 ` Laurenz Albe <[email protected]>
2020-11-06 16:11 ` Hilbert, Karin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox