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 1u1pIM-008hrN-Hg for pgsql-bugs@arkaria.postgresql.org; Mon, 07 Apr 2025 16:25:58 +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 1u1pIK-000skQ-Vw for pgsql-bugs@arkaria.postgresql.org; Mon, 07 Apr 2025 16:25:57 +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 1u1pFt-000pAr-7D for pgsql-bugs@lists.postgresql.org; Mon, 07 Apr 2025 16:23:25 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u1pFr-003VCl-2U for pgsql-bugs@lists.postgresql.org; Mon, 07 Apr 2025 16:23:24 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-2c764f2c223so1524496fac.0 for ; Mon, 07 Apr 2025 09:23:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744043003; x=1744647803; 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=ReB660mBz+Oe0b5e57WZ71pqnK5YDuO0zuWlMEvcRwA=; b=hnOMHLrfLizGPcfUeeWTGC7kEPeJ7B7IOQK0XXP9Bk7SSF0DDKtBuwQuGCR1ieCkS8 CaeH/GSwg3VCUgGWynWKp108guXprw7XGqNwfj6LXFxuVPppURuzYphuGTe3LdhSJdRJ D+V5Pf1PIKmn9Q8ygiHBq1EzxZwCCMu1SfVFMkfZKu0JPH4TQSE/pIf3BRjz8Vj+v1JR sJ3ieiPw4TPbek86xkymQXUuXLZYbHBqW3NcoJEoZYslI+ZifKdfmFUn0fte0SO4hI8y 3HQrkrdVxtRYOby94fxrfD8RjLzAL6C7J0aCsJHqfT3SxK3/UBYp4S6S8LI8z5BUUC6t ZZTw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744043003; x=1744647803; 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=ReB660mBz+Oe0b5e57WZ71pqnK5YDuO0zuWlMEvcRwA=; b=Vho23xxLMawLNkWly9b2XtFLUbSQd7fpHnaL1G/O1XD4NYNX0C3mfTZkToUWR2+iZi MUfUMQSNFD6FfQdWrjaAspObAqXlAf8cXbnQoxLSGuYkeXjfuvy4ZgWe+gdN2dpiUDzN t9mw3QGamWNCSSzg48MKpY8P0v7ypmt3LRh/D8WeBhDiXYPO7Q8fnAVmTZkHU7R5wgtj OBnDP+NuH5jU21f3eb/sSRrCHKk3dC6Oljfb8LtR+FUv9kffo1ozT3gb/yTPu0IC/qW1 bF1gPFJyb8ioIttip0qDhpUpSnI80TVaDCw5vZawZQjRFEu6Vmp8aQ7dg3A9IAkEZ4+E ztFw== X-Forwarded-Encrypted: i=1; AJvYcCV+L1guggEGpanyREOPjra7mO38YhVIiknG8LeHCFNAVHETwBhhEGSWFOXT/lM60yNgMooAsB3RrMRN@lists.postgresql.org X-Gm-Message-State: AOJu0Yz0KOZOt+EkI12QXb6gadhnXGilz9XkKJnKaoNH/t+hPDzOnMUU X7WOSkdBJosvfGP0FREbtkgak5lvbWwr6d6eHKQztwUp9gqCf8JhCGLeIwYKO3C6SodL9InS1dQ e9jh0QNDbPWHiexKGHPr12bmjh9g= X-Gm-Gg: ASbGncvglAx3qjyehn6l2xXFv48xgDkWRu8aHAmDRpJ7BGBHxvxvLM9Eg2Qu8GEtSB9 ZebVP0XYGkxBweldMaWPsCp8H+nduGbABek3VQSUyB8bv7MPI9PSmdZkSsXSnDYLdGbAOfYLj67 5U1ocyRg5c4kN3CUEax8dv6hJe X-Google-Smtp-Source: AGHT+IGnzLXGF3OIZHmzd29e8ya3ZFAVIGfycGDFtE4irq8Iab1z+TR3vuB2HOuYSDo107HQJjqje0gQzkWVHHIM4ZQ= X-Received: by 2002:a05:6870:e0c7:b0:29e:255e:9551 with SMTP id 586e51a60fabf-2cc9e4ef717mr7632417fac.2.1744043002942; Mon, 07 Apr 2025 09:23:22 -0700 (PDT) MIME-Version: 1.0 References: <3467676.1744041977@sss.pgh.pa.us> In-Reply-To: <3467676.1744041977@sss.pgh.pa.us> From: "David G. Johnston" Date: Mon, 7 Apr 2025 09:22:45 -0700 X-Gm-Features: ATxdqUHTJdBt-UEBYF-Mx46m7GCmWrQVNQ4zTtEButazQAD5eb36mJoyOEiAJMU Message-ID: Subject: =?UTF-8?Q?Re=3A_=C2=A0_Re=3A_Re=3A_Revoke_Connect_Privilege_from_Databas?= =?UTF-8?Q?e_not_working?= To: Tom Lane Cc: "Ing. Marijo Kristo" , PostgreSQL Bug List Content-Type: multipart/alternative; boundary="000000000000a9df77063232a7cb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a9df77063232a7cb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 7, 2025 at 9:06=E2=80=AFAM Tom Lane wrote: > "David G. Johnston" writes: > > On master, confirmed that after this command the privilege: > > test_user=3Dc/test_admin (on database testdb) still exists. That seems > like > > a bug. Its at least a POLA violation and I cannot figure out how to rea= d > > the revoke reference page in a way that explains it. > > 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=3Dc/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. revoke connect on database testdb:v from test_user:v --------------- granted by test_admin:v; ---^^^^^^^^^ So if we stick with status quo behavior we'd need to write the following because the ignoring part is a POLA violation: 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, and the granted by clause is ignored. David J. --000000000000a9df77063232a7cb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 7, 2025 at 9:06=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@g= mail.com> writes:
> On master, confirmed that after this command the privilege:
> test_user=3Dc/test_admin (on database testdb) still exists.=C2=A0 That= seems like
> a bug. Its at least a POLA violation and I cannot figure out how to re= ad
> the revoke reference page in a way that explains it.

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".=C2=A0 So here, what would be revoked is
test_user=3Dc/postgres, which isn't the privilege at issue.
Include GRANTED BY in the REVOKE to override the default
choice of grantor.

The command in questio= n did include "granted by" which is why this is a bug.=C2=A0 The = explicit granted by specification is being ignored if the invoking user is = a superuser.

revoke connect on database testdb:v
=
from test_user:v=C2=A0
---------------
granted by test_a= dmin:v;
---^^^^^^^^^

So if we stick with statu= s quo behavior we'd need to=C2=A0write the following because the ignori= ng part is a POLA violation:

If a superuser chooses to= issue a GRANT or REVOKE command, the command is performed as though it wer= e issued by the owner of the affected object, and the granted by clause is = ignored.

David J.

--000000000000a9df77063232a7cb--