Date: Thu, 13 Nov 2025 10:47:14 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	"Ing. Marijo Kristo" <marijo.kristo@icloud.com>,
	PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: =?utf-8?B?wqAgUmU=?= =?utf-8?Q?=3A?= Re: Revoke Connect
 Privilege from Database not working
Message-ID: <aRYLkTpazxKhnS_w@nathan>
References: 
 <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
 <d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com>
 <CAKFQuwbB-ZKtN_p_y5sWa2MrTuy5=pRNPWSj1Ud4HHvTuhb54w@mail.gmail.com>
 <3467676.1744041977@sss.pgh.pa.us>
 <CAKFQuwbpC5w6sUq8gZQATrviZUT4bYpxW+=2uH6sWWMg7fWjzg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0180Z+uLTgRXM/m1"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKFQuwbpC5w6sUq8gZQATrviZUT4bYpxW+=2uH6sWWMg7fWjzg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aRYLkTpazxKhnS_w%40nathan>
Precedence: bulk


--0180Z+uLTgRXM/m1
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Mon, Apr 07, 2025 at 09:22:45AM -0700, David G. Johnston wrote:
> On Mon, Apr 7, 2025 at 9:06 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I believe what's going on there is explained by the rule that
>> "grants and revokes done by a superuser are done as if issued
>> by the object owner".  So here, what would be revoked is
>> test_user=c/postgres, which isn't the privilege at issue.
>> Include GRANTED BY in the REVOKE to override the default
>> choice of grantor.
> 
> The command in question did include "granted by" which is why this is a
> bug.  The explicit granted by specification is being ignored if the
> invoking user is a superuser.

This is admittedly a half-formed idea, but perhaps we could have whatever's
specified in GRANTED BY override select_best_grantor(), like in the
attached patch.  I've no idea if this is the intention of the standard, but
it should at least address the reported issue.  FWIW I recently received an
independent report about the same thing.  

-- 
nathan

--0180Z+uLTgRXM/m1
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=v1-0001-GRANTED-BY.patch