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 1tl9It-009g2B-RI for pgsql-general@arkaria.postgresql.org; Thu, 20 Feb 2025 16:21:35 +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 1tl9Ir-009ImT-SJ for pgsql-general@arkaria.postgresql.org; Thu, 20 Feb 2025 16:21:34 +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 1tl9Ir-009ImK-DR for pgsql-general@lists.postgresql.org; Thu, 20 Feb 2025 16:21:33 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tl9Ip-001vW1-2d for pgsql-general@lists.postgresql.org; Thu, 20 Feb 2025 16:21:32 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-3f3da35555eso644198b6e.1 for ; Thu, 20 Feb 2025 08:21:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740068491; x=1740673291; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=0Kf0AEm7yPuhYTx3AOBFRr8rysGsOANYZPz+mhiIVRk=; b=cpYSpvGA1/C1RioXcZe+yiTHBSke26Zm7SAsTq7eG0KvBh555djm7qqnXWk96lIlog jxs2mM9T8/6fnWAx22otg1N2YqmpgMWlauk7Ci7JMCE4R+zBK3Rp41SvCS1EeXtmSlMw X5hVjy0E2LgugP9llQnX2olkZMfoM3Logf8FvBLEeCNEcX36Uk9KKv+BQPC7x3JPEpSv dNMNjSO6+GNToQfYik6oV7FMjTvayesOi7gQpOTCOcorcyh2r9nInbcOuNLfdyFvNs8b iPwLMMMQQpMjeOmDR7mOQnQLIbPAGPFICMv6TEurRCtFD5f3SraBQe6ODEAalkj9JURb kJJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740068491; x=1740673291; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=0Kf0AEm7yPuhYTx3AOBFRr8rysGsOANYZPz+mhiIVRk=; b=CubmAvtIL881sEjDYdioUL6gmYpRAxMB1+1pgRpc3BgSuLunWBt/53xkTiszPQWSrg sy4nStRvD5EbDHVh8ZuOQoa1Pmm08h3Bnr6ohy+CB8iyTsVFhYboy2WE4///UwU13MLB GIyy7a5stR0KJQk2DOHLeZyUq1ov/OulNJd8uyAHXLbrNPcpQYAfkrVXKz2SkEe4Qbv9 1OEVx+9sZGOO/xwFjoLkPYU1el754mbqkx2oPYrDThNjwAOlmUIwAeDo3V9kMlb1HZDe lFBVJ8OKdGUcr/lMpMezFC95cIsAmkSR16DxLWB7gMNW+jYfTY5Cu2luLKW7dWdA9tQV lwKw== X-Forwarded-Encrypted: i=1; AJvYcCUry6v7LxwfvEhYZ9ZR6m/CzVDFAajs3Qh7w+nzgD16Q4dEUg6shR8u1R5gzKrvtYfGH0ZXeH5HYW+QG1uC@lists.postgresql.org X-Gm-Message-State: AOJu0YxXpyz94TTdrLDxQEvwivW1Bje8OzJ/lRUqfXwRXtmrMi5IeNWN Hd7ovwUtwET+Kf9dCCqLf403Q93YsqGvMeRJruk2kQuGEX0OIxs5pvETr9m/RrlKoXIuoip2bbm YwhI6qwdtRAiEkTxWClbCbtAvIzM= X-Gm-Gg: ASbGncslOSBTAS6GqVDAjxtLRivLcmAIaHXM27JClAqTzPDchk0t7A224PjmjaiXs8f TngAMHRgOF7qaR7v6F26/aca79bfX2LBWbY8F0NMkWSfFU4Nu8VV4SNQKdP5OS29AHYB7MkY= X-Google-Smtp-Source: AGHT+IFiTDPEGfUfSdbDVdnwp3NHKSz2GYQUwEV35eb3LcGlu5fMJ8vaiVcT8tM5YnngzB2Iv6RKSQCKvJgEk+df0o8= X-Received: by 2002:a05:6808:2e96:b0:3f4:b0c:ab6 with SMTP id 5614622812f47-3f41b8c122bmr3216627b6e.1.1740068490831; Thu, 20 Feb 2025 08:21:30 -0800 (PST) MIME-Version: 1.0 References: <2705563.1740067550@sss.pgh.pa.us> In-Reply-To: <2705563.1740067550@sss.pgh.pa.us> From: "David G. Johnston" Date: Thu, 20 Feb 2025 09:20:54 -0700 X-Gm-Features: AWEUYZmgsu_WCWrhTputb-L4f9fNJEM7Ladxu4VLwxrygd02X4WV-iUU7WtT2rU Message-ID: Subject: Re: DROP ROLE as SUPERUSER To: Tom Lane Cc: Dominique Devienne , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000047ed49062e954449" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000047ed49062e954449 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Feb 20, 2025 at 9:05=E2=80=AFAM Tom Lane wrote: > "David G. Johnston" writes: > > On Thursday, February 20, 2025, Dominique Devienne > > wrote: > >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silent= ly > >> did nothing, even with CASCADE, when I was running it as SUPERUSER, > >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the > GRANTOR, do > >> the REVOKE, which DID something this time, and then I could DROP the > role. > > > This has nothing to do with power/permissions. It is about not > specifying > > =E2=80=9Cgranted by=E2=80=9D in your SQL command and thus failing to fu= lly and correctly > > specify the single permission you want to revoke. > > It used to be that if a superuser issued GRANT/REVOKE, the operation > was silently done as the owner of the affected object. > That is still the case according to the docs (REVOKE): "If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object." The docs seem to be missing reasonable exposition regarding "granted by". The clause isn't even formally mentioned on the page; though I suppose it is because it is delegated to the GRANT page specification. Though the description there says it is basically an ignored compatibility clause - not something that a superuser can use to make things more explicit than using SET ROLE (not sure if it can ATM...). David J. --00000000000047ed49062e954449 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Feb 20, 2025 at 9:05=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@g= mail.com> writes:
> On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com>
> wrote:
>> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE si= lently
>> did nothing, even with CASCADE, when I was running it as SUPERUSER= ,
>> preventing DROP'ing the ROLE. I had to manually SET ROLE to th= e GRANTOR, do
>> the REVOKE, which DID something this time, and then I could DROP t= he role.

> This has nothing to do with power/permissions.=C2=A0 It is about not s= pecifying
> =E2=80=9Cgranted by=E2=80=9D in your SQL command and thus failing to f= ully and correctly
> specify the single permission you want to revoke.

It used to be that if a superuser issued GRANT/REVOKE, the operation
was silently done as the owner of the affected object.
That is still the case according to the docs (REVOKE):
"If a superuser chooses to issue a GRANT or RE= VOKE command, the command is performed as though it were issued by the owne= r of the affected object."

The = docs seem to be missing reasonable exposition regarding "granted by&qu= ot;.=C2=A0 The clause isn't even formally mentioned on the page; though= I suppose it is because it is delegated to the GRANT page specification.= =C2=A0 Though the description there says it is basically an ignored compati= bility clause - not something that a superuser can use to make things more = explicit than using SET ROLE (not sure if it can ATM...).

David J.

--00000000000047ed49062e954449--