Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kb4L5-0006eO-26 for pgsql-novice@arkaria.postgresql.org; Fri, 06 Nov 2020 16:11:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kb4L3-00041G-P6 for pgsql-novice@arkaria.postgresql.org; Fri, 06 Nov 2020 16:11:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kb4L3-00040L-5A for pgsql-novice@lists.postgresql.org; Fri, 06 Nov 2020 16:11:45 +0000 Received: from mail-co1nam11on2106.outbound.protection.outlook.com ([40.107.220.106] helo=NAM11-CO1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kb4Kv-0002Y7-2L for pgsql-novice@postgresql.org; Fri, 06 Nov 2020 16:11:44 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=kBOnAKySiL2Na7mkWJk5pQOpnddNZEyRprm1J7Nmaw7OeCm5bM4bI//eN14yvMwICbPM2ka5GnifNno+2+wp+W2iCuKvTm4sTHk+3dzD+PtuKNFuBQH8GlKxhIu5RoFDpT/EOxz1aPsdbOHx8iAb4/NeQhz+ids1DMfhtp3eaJnrWyGmXiBgg6MdubtF0TfVm901OphrHDzq20qD3J6HKkIA+1uCwXLfLPbISxBEJaQb0Yx/HbCMfhma8i1vzxDmAm4AbtzkFO1qP43sQ2sVmMsdHdtFB9cfzym8sofUgB5PFiEjF7PQrGVduh/tnGAyuFU5naYqWAurBYM1zm2pTw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=KNdBPfw1nGpQS2zM/OW13eMhs5HDnLuB8M7xlyYyN4o=; b=fYCNXfYAMnSrpgaaWlAM8KBndU+5GFjjYH6gMuzeTWNHXfFGwJnX1KU9LLDFBFuM23CcAi6CkK+LjK+wyt9kFhjJ13ohm/F0r7WLIHoRA6xNZ5CvwmVKH4P+6CQhxBTub09f8UG6w6Cr7VsVjM8SKTAfggLK8cwB/sY6LywaTqC4yQnV/qtKcyMiXKty3mfsiNxKWRycqDw5DsjVii8BGMkh8RGG6NTQDaovUnWOd/d17J8o9l+InXiCceXV3JN0d8uMP9sB+H5hA3Sq20rvxDS9w/KVKLQ/tesHswr2BJ49ehtBDHEKXFayJS7lhScq5R3MBK7FYInbyKbsjuHapw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=psu.edu; dmarc=pass action=none header.from=psu.edu; dkim=pass header.d=psu.edu; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=psu.edu; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=KNdBPfw1nGpQS2zM/OW13eMhs5HDnLuB8M7xlyYyN4o=; b=OYpOxtkF4/fYCWTSUAUWLRmZw6fJV04z1ft/ROyLaUe2VEaYGL0Y4kz8Ht85UJyWdEgRSkwPqO3sZ3bonMHPu31owkU+FUewNxmLys7fwsj/KkmzoGPveDP0f9U162+1Vsh0nbCHyHuYUVYinU65ioN4rN3OBTIjhxq2HFcibXo= Received: from DM6PR02MB4939.namprd02.prod.outlook.com (2603:10b6:5:18::15) by DM5PR02MB2492.namprd02.prod.outlook.com (2603:10b6:3:40::17) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3499.18; Fri, 6 Nov 2020 16:11:29 +0000 Received: from DM6PR02MB4939.namprd02.prod.outlook.com ([fe80::19b7:1d05:4c6f:ed15]) by DM6PR02MB4939.namprd02.prod.outlook.com ([fe80::19b7:1d05:4c6f:ed15%5]) with mapi id 15.20.3541.021; Fri, 6 Nov 2020 16:11:29 +0000 From: "Hilbert, Karin" To: Laurenz Albe , "pgsql-novice@postgresql.org" Subject: Re: Permission Denied to Regular Read/Write User to Create New Partitioned Table Thread-Topic: Permission Denied to Regular Read/Write User to Create New Partitioned Table Thread-Index: AQHWsvry0NHmWGAMo0aEg4kkgzpbIqm5mNF+gAAWOACAAZkY8g== Date: Fri, 6 Nov 2020 16:11:29 +0000 Message-ID: References: ,<092337ddb6c1320edd7ab512bac807f5c0ea259b.camel@cybertec.at> In-Reply-To: <092337ddb6c1320edd7ab512bac807f5c0ea259b.camel@cybertec.at> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: cybertec.at; dkim=none (message not signed) header.d=none;cybertec.at; dmarc=none action=none header.from=psu.edu; x-originating-ip: [174.54.69.9] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 458645b8-ee62-421e-6b9d-08d8826e9eb2 x-ms-traffictypediagnostic: DM5PR02MB2492: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:8273; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: ak4LQB+NEsRRXVgxc9dbFPCo8liqT7wsV4Wp/yjIbgMbNasKGU28v8py2GXE4WPNr6rktMBu8b+LcZFhipXzSisehZnmN0SZU1fZTxNjNAeMdLSfrMywYQIGJgAwAlf9VeKol00WT2gwOuc8Zji+JwUFfxCjy9iS67TBQAEW8LLkND5NbSLDk3UuSgcy5Serv43V+Lgufncq6uZu35RVg6rH9v9d1ROxIav4SVsOcyjwgSKmD1yja/ecgdCnZ1KXd1wnsPfT4GfFjCoU+n0e/uK9IhjoSdyfTspouq1BDPJC5QLedmWBOzBR+z7u26CMJY2gH8bIaRd2JswJogZlCLchoCkElr0f7i2iHJXjVmP4DkRoVoFJceZdOsQzu9iT74SYz8ltUOutLYZYZuqEi3+/hGl/xF6b9XtHltfA8QGBxyd+QLkmMKffSe+GavFnpt8x8hTkYdA3F/uj0KjU5Q== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DM6PR02MB4939.namprd02.prod.outlook.com;PTR:;CAT:NONE;SFS:(4636009)(396003)(39860400002)(136003)(346002)(376002)(366004)(478600001)(9686003)(316002)(53546011)(6506007)(45080400002)(76116006)(786003)(66946007)(75432002)(7696005)(186003)(83380400001)(966005)(2906002)(8676002)(71200400001)(8936002)(52536014)(5660300002)(19627405001)(33656002)(26005)(86362001)(110136005)(55016002)(66476007)(66446008)(64756008)(66556008)(166002)(88722002);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata: TigyE0uDvSvD3b4eyrrBKeCDdgZjYafUxQ1bKeE1o/O1RrtAjk5aenUU4rBXjD9i3lCCSEYE+0gZZSSfpXDX5h8QLdRec+MscNzJjsRvQWWd8AyAB48sGK9oNnfif49NJ0ufsysbAWpnI4vDyPmp9/noIat5d3jAu8OZ76d7Zto+kFAoRyFuTsDa3UMLnKkbLaFAwfZMmyPEa33hvmSItxh7kzLSq2owfYWMi4qt0/KFprYEdB1736qoFoa3dvOqmxAHckc35sTxLh0Jzai+pg+xmfiMQS4C4/VgC9U73lQ+NpipurhDd946vEo3RgtkEjgFVMWtn3duC1OkulA94FdxFb4fHmi7mQiWQvHQhwuWQB612M0DRoW1/wLq/FOGCfgSIjheAof0Wb/6N/b8A87oHoL3g0D0m2fvgnMztPxivAFLtyNHXqWQ4njjzxiXgYlg25cPwd/fAzyuT2wYA/QWscgDCNAI1TOJKv8yjztgAquj8E0HqBZmxvoU4XPfTS0AwExWyJMGXi4yhFdzD6tPW5xRWhpES0REb5oLw2KPL3wcqkKq94ci/XjxK7tQIRsXIP+PTd74No58nvBA5oW/ICymkkZfNO2XlEnDW2NwVR77AUIyyZuEGddLgkfPbRjxAYVtODCXDbEZnbj51Q== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_DM6PR02MB4939F0BF43FECC8B60699AA889ED0DM6PR02MB4939namp_" MIME-Version: 1.0 X-OriginatorOrg: psu.edu X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DM6PR02MB4939.namprd02.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 458645b8-ee62-421e-6b9d-08d8826e9eb2 X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Nov 2020 16:11:29.1932 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7cf48d45-3ddb-4389-a9c1-c115526eb52e X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: ct+hW9D/WM+b1myyR9MCp3/DdoMDlJAyj8OmzSM46mgccL9d+oJyYPI0MjwZvK6l X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM5PR02MB2492 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_DM6PR02MB4939F0BF43FECC8B60699AA889ED0DM6PR02MB4939namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 Sent: Thursday, November 5, 2020 10:44 AM To: Hilbert, Karin ; pgsql-novice@postgresql.org Subject: Re: Permission Denied to Regular Read/Write User to Create New Par= titioned 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 a= pplication upgrade. > We allow the application to connect as the DB Owner account only during t= hose times & > switch back to the regular account when the upgrade is done. > > However, with the latest application upgrade, GITLAB is now using partiti= oned 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=3Dxxxxxx,user=3Dxxxxxx ER= ROR: permission denied for schema gitlab_partitions_dynamic at character 2= 8 > 2020-11-03 18:21:31 EST [56435]: [89-1] db=3Dxxxxxx,user=3Dxxxxxx ST= ATEMENT: CREATE TABLE IF NOT EXISTS "gitlab_partitions_dynamic"."audit_eve= nts_part_5fc467ac26_202105" > > I tried to solve the error by granting the regular user acct "usage, crea= te" 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_par= titions_dynamic to xxxxxx;" > > Now we're getting a "must be owner" error: > > 2020-11-04 12:21:17 EST [28013]: [420-1] db=3Dxxxxxx,user=3Dxxxxxx E= RROR: must be owner of table audit_events_part_5fc467ac26 > 2020-11-04 12:21:17 EST [28013]: [421-1] db=3Dxxxxxx,user=3Dxxxxxx S= TATEMENT: CREATE TABLE IF NOT EXISTS "gitlab_partitions_dynamic"."audit_ev= ents_part_5fc467ac26_202105" Seems that the user is trying to create a partition for a table that it doe= sn'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 own= er). I guess you cannot modify the application code, right? Yours, Laurenz Albe -- Cybertec | https://nam01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2= F%2Fwww.cybertec-postgresql.com%2F&data=3D04%7C01%7Cioh1%40psu.edu%7Ccb= c363b1506c4e79820308d881a1a3f4%7C7cf48d453ddb4389a9c1c115526eb52e%7C0%7C1%7= C637401878524136413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2l= uMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=3DouGaunFdzbKCkCSlHP1e= DiTwZXRHXraDeB7npzWbc8M%3D&reserved=3D0 --_000_DM6PR02MB4939F0BF43FECC8B60699AA889ED0DM6PR02MB4939namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
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 <laur= enz.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
 
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 permiss= ions.
> For GITLAB, the application updates the database as required during th= e application upgrade.
> We allow the application to connect as the DB Owner account only durin= g those times &
>  switch back to the regular account when the upgrade is done.
>
> However, with the latest application upgrade, GITLAB is now using part= itioned 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=3Dxxxxxx,user=3Dxxxxxx ERROR:  permission denied for schema gitlab_= partitions_dynamic at character 28
>      2020-11-03 18:21:31 EST [56435]: [89-1] = db=3Dxxxxxx,user=3Dxxxxxx STATEMENT:  CREATE TABLE IF NOT EXISTS "= ;gitlab_partitions_dynamic"."audit_events_part_5fc467ac26_202105&= quot;
>
> I tried to solve the error by granting the regular user acct "usa= ge, create" on the schema:

Yes, granting CREATE on the schema is the proper remedy for that error.<= /b>

> psql -d xxxxxx -U xxxxxxxxxx -c "grant usage, create on schema gi= tlab_partitions_dynamic to xxxxxx;"
>
> Now we're getting a "must be owner" error:
>
>      2020-11-04 12:21:17 EST [28013]: [420-1]= db=3Dxxxxxx,user=3Dxxxxxx ERROR:  must be owner of table audit_events= _part_5fc467ac26
>      2020-11-04 12:21:17 EST [28013]: [421-1]= db=3Dxxxxxx,user=3Dxxxxxx STATEMENT:  CREATE TABLE IF NOT EXISTS &quo= t;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 own= er).

I guess you cannot modify the application code, right?

Yours,
Laurenz Albe
--
Cybertec | https://nam01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2F%2Fwww.cyb= ertec-postgresql.com%2F&amp;data=3D04%7C01%7Cioh1%40psu.edu%7Ccbc363b15= 06c4e79820308d881a1a3f4%7C7cf48d453ddb4389a9c1c115526eb52e%7C0%7C1%7C637401= 878524136413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLC= JBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=3DouGaunFdzbKCkCSlHP1eDiT= wZXRHXraDeB7npzWbc8M%3D&amp;reserved=3D0

--_000_DM6PR02MB4939F0BF43FECC8B60699AA889ED0DM6PR02MB4939namp_--