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.98.2) (envelope-from ) id 1xKxwY-00000001Dwt-2uha for pgsql-general@arkaria.postgresql.org; Sun, 25 Oct 2026 13:07:23 +0000 Received: from localhost ([127.0.0.1]:53774 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xKxwY-00000002t9b-0lLK for pgsql-general@arkaria.postgresql.org; Sun, 25 Oct 2026 13:07:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:52294) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKxwX-00000002t9S-3di3 for pgsql-general@lists.postgresql.org; Sun, 25 Oct 2026 13:07:21 +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.98.2) (envelope-from ) id 1xKxwV-00000000rCb-41US for pgsql-general@lists.postgresql.org; Sun, 25 Oct 2026 13:07:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 69PD7Gqc322425; Sun, 25 Oct 2026 09:07:16 -0400 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 69PD7Gqc322425 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1792933636; bh=0h5mFQr8IcBR73TdRU/xFzqDMJGDR5+1YIRSljlqZLc=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=2lobYI6JB3ell+8upL/mLL9EnVWblwRnI25dqs07Zsyi+xAsNdQIUe32ue7PAuIC0 js5PuFzruCOdxXAe9g4kjitO8rpK8/6lJKFxEtc2mCYNxTKJrphxzQHWgYA2HKicVD cii+E9HjghjKzA+tbZ1Z0G13MOP/4V72Ps6/1B3k= From: Tom Lane To: Laurenz Albe cc: Dustin Sanza , pgsql-general@lists.postgresql.org Subject: Re: PostgreSQL 17: protecting role attributes between verification and COMMIT In-reply-to: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at> References: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at> Comments: In-reply-to Laurenz Albe message dated "Sun, 25 Oct 2026 08:32:51 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <322423.1792933636.1@sss.pgh.pa.us> Date: Sun, 25 Oct 2026 09:07:16 -0400 Message-ID: <322424.1792933636@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz Albe writes: > On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote: >> The narrow question: under the privileges above, is there supported >> SQL that prevents or safely serializes concurrent ALTER ROLE attribute >> changes until COMMIT without first rewriting those attributes and >> potentially concealing unexpected drift? > I don't think so. > You could use a SECURITY DEFINER function owned by a superuser that > LOCK pg_catalog.pg_authid IN SHARE MODE; > to prevent concurrent role modifications. Or SELECT ... FROM pg_catalog.pg_authid FOR UPDATE, which would have the advantage of locking only relevant rows not the whole catalog. regards, tom lane