Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzPPH-00G5D2-9C for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 22:46:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qzPPF-007v33-Sx for pgsql-general@arkaria.postgresql.org; Sat, 04 Nov 2023 22:46:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzPPF-007v2v-IC for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 22:46:17 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzPPD-003zTd-6h for pgsql-general@lists.postgresql.org; Sat, 04 Nov 2023 22:46:16 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3A4MkDfl3529058; Sat, 4 Nov 2023 18:46:13 -0400 From: Tom Lane To: Ron cc: pgsql-general@lists.postgresql.org Subject: Re: Postgres limitation in user management In-reply-to: <9faf2c18-5029-4d77-a937-ece6d25f9678@gmail.com> References: <20231104213303.4mtgamfyo4v5i33e@hjp.at> <20231104215321.ojqhqo346bbsistg@hjp.at> <9faf2c18-5029-4d77-a937-ece6d25f9678@gmail.com> Comments: In-reply-to Ron message dated "Sat, 04 Nov 2023 17:02:26 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3529056.1699137973.1@sss.pgh.pa.us> Date: Sat, 04 Nov 2023 18:46:13 -0400 Message-ID: <3529057.1699137973@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron writes: > On 11/4/23 16:53, Peter J. Holzer wrote: >> This doesn't answer the question why ALTER TABLE privilege would be >> required. > I bet the Good Idea Fairy whispered something into the CISO's ear. Yeah. This is blatantly obviously the brainchild of some person with no actual experience in fulfilling the roles they want to circumscribe. Having said that, maybe: * Role foo_owner actually owns the tables, but revokes its own DML privileges (select etc) * Role foo_app is granted foo_owner so it can do DDL on the tables, and is also granted DML privileges on the tables * Role foo_dba is granted foo_owner but not DML privileges. This is, of course, trivially breakable by any foo_dba who doesn't want to play by the rules, but as long as you log DDL there will at least be log traces that she did so. regards, tom lane