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 1xKsjy-00000001B0Z-3kK8 for pgsql-general@arkaria.postgresql.org; Sun, 25 Oct 2026 07:34:03 +0000 Received: from localhost ([127.0.0.1]:37652 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xKsix-00000002JgW-2Quu for pgsql-general@arkaria.postgresql.org; Sun, 25 Oct 2026 07:32:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:37422) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xKsix-00000002JgG-0u98 for pgsql-general@lists.postgresql.org; Sun, 25 Oct 2026 07:32:59 +0000 Received: from mail-wm1-x329.google.com ([2a00:1450:4864:20::329]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xKsis-00000000qvS-2lmz for pgsql-general@lists.postgresql.org; Sun, 25 Oct 2026 07:32:58 +0000 Received: by mail-wm1-x329.google.com with SMTP id 5b1f17b1804b1-4a35a60499eso7413735e9.0 for ; Sun, 25 Oct 2026 00:32:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1792913572; x=1793518372; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:content-type :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to:content-type; bh=wMDzEoGh4Q53ctLroQdzjsJMK7YuSuSFaAd5hfztgL0=; b=D/03Xpt7z2PyxLOoZSIQIlqlmlmtyecOJMYARKkuyPNGZwN4yNzEdqz5He5mAfkAlO sA05kTsUAsIANSqflyKao7xpNw5c9IUEoyKvbJuI+O/sqlgl7wuIuHXjf5fYgmjkp1Ok MpsTkL3OWfVDp8nx+LhK+6Un8cIrPiy8JcbDnr4p9Ps0PDfJTaoBTnnSpjZu21DE33dp DBktyHNQ1bGww8T6It1m6QcWhQQonN5sKdMDzjRwsgUUVwBUPwjRvmU0/HWrEoSzyiX5 2FkCkNqJrZMvraL7446RvdPF8YguFtxTGDzvAWC5ONL21sf4YizrZRAIfYCNjKqphbfz kw4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1792913572; x=1793518372; h=mime-version:user-agent:content-transfer-encoding:content-type :references:in-reply-to:date:to:from:subject:message-id:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=wMDzEoGh4Q53ctLroQdzjsJMK7YuSuSFaAd5hfztgL0=; b=luDlmgz+7C5R40Dx/z1pzr8BdEcvSEhDZXX6sR/VQhzl0tGE/SoxDZvioEkcpacwJp juJ6gXVPreSbBFUE9/S76+OtzQ9eqgXqCLyEhVVKHns99bnVWrUcc+QjuF9Xpem9hUlR G7KPqEXM/mA3oHVYdobwRICo+Eb30GeJNaGbcXCPVXjG0Top6Caa6Q09cRdBIz4ML7Xp opt1hDUEhNj6n83/wiv+PV7pHc7P/Bq1xInK2oyvS84zJm3/gahMOlqvaArA7qNPHoSG EnPGeBpg6rAUi1yKtgQ0pdGXUZHm8SNTAHIYrAeqE2YF7usC/YMEW8PlFmtshGsRcMB9 qt8g== X-Forwarded-Encrypted: i=1; AKwUvBzaqd4MaCx+NlsDxOFSCkonhop9RacvhIntthk+reHWI7Gudvlai1aaICa2b0ovBJ3Is2/JvUiTIMZnbPTL@lists.postgresql.org X-Gm-Message-State: AFq9FYJ006/I6i0L7cgDGDEUxW4KEBw6TGCOr51JWLiNsJOOO26DV6VL dK0WiDSq3xYmWSoBs2jP2763+6P9C/Uci0ZoRhsqH112yfZgcP5VlbdChx7/7mi4PCs= X-Gm-Gg: AYBFou3BS39nelrKgCTWcQVDOCDLa+DbLzigTNdTqQA/JlVemU6TfNuaAH787JYdnig VcZe6QfK0sEJqZBXuyNT2SXMnoDOKVOziX2feggz4+lYZcg/IPS/MQuqKu9ej7WizP9RgRDYiA4 sJtijA9JicgFzsWwGmafKeIfzWKe9F29qYsaT3VCKNZP3Wt/Z8+moJ5JhwAZMfxq76osVd887P9 X62Y9lQnegY1zqsCERzLsIjpoOrXop7ID2QjA50+XmpQbhQvwDcsWg8yCizPmC0ODqXWKPX6bKt 83tAASjmf7TXybgGBY0FzsyQQImT9VjMnToY8w5Wf5hORa6dfcNLdsOsL/lYiaUeH9Tugh+CR7C pO4wMWDvZbxk5BQYZJ38fsIO67/79hpetDZBnMR3WQcaxFz9pc1Fa4V1i32Ksz8KwcBYomlaj+z G8SS/lapqviC5YIOZqaLSN2m5n9DwEdVkZxAcEh8vDrbrnd4x9dKIizkgBq5AOa27xB87SXoHpJ om+u4pwBTs9zrVO9cYzaTEfKo0Kw2YTI0MWoyUi8q+8YDfnh2BjVGFc0Gm5vx7LklMylGb9iSad CS5ffxaKD9K3 X-Received: by 2002:a05:600c:4583:b0:4a1:825a:56fd with SMTP id 5b1f17b1804b1-4a35acc2773mr67659305e9.32.1792913572526; Sun, 25 Oct 2026 00:32:52 -0700 (PDT) Received: from laurenz.albe-K4N0CV00F97414D ([2001:871:270:b677:677f:591c:a18a:2956]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-48e3ebcb90dsm7830835f8f.22.2026.10.25.00.32.51 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sun, 25 Oct 2026 00:32:52 -0700 (PDT) Message-ID: <3fbab45e94b26f0a10a0ec25b00a4adc991e1f4f.camel@cybertec.at> Subject: Re: PostgreSQL 17: protecting role attributes between verification and COMMIT From: Laurenz Albe To: Dustin Sanza , pgsql-general@lists.postgresql.org Date: Sun, 25 Oct 2026 08:32:51 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.60.2 (3.60.2-2.fc44) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2026-10-23 at 14:20 -0700, Dustin Sanza wrote: > I'm looking for a supported PostgreSQL 17 mechanism to keep selected > role attributes and memberships stable between verification and > COMMIT. This is a design question, not a bug report. >=20 > Assume the transaction's role is NOSUPERUSER, has CREATEROLE and ADMIN > OPTION on the relevant non-superuser roles, but has no direct UPDATE > privilege on system catalogs. Other legitimately authorized > administrative sessions may be running; this is not an attempt to > constrain a malicious superuser. >=20 > The intended sequence is: > 1. Acquire the necessary protection for a finite, identified set of roles= . > 2. Verify their relevant attributes and memberships against an > expected state; abort on unexpected drift. > 3. Complete a release-state change and COMMIT without a competing role > change invalidating that verification in the meantime. >=20 > [...] > > My reading is that COMMENT ON ROLE could therefore serialize > membership GRANT/REVOKE for that granted role, but does not by itself > cover concurrent ALTER ROLE attribute changes. I have not run a > concurrency reproduction yet. >=20 > 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. Yours, Laurenz Albe