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 1tzcPD-004L6v-GR for pgsql-sql@arkaria.postgresql.org; Tue, 01 Apr 2025 14:15:55 +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 1tzcPC-00314w-26 for pgsql-sql@arkaria.postgresql.org; Tue, 01 Apr 2025 14:15:54 +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 1tzcPB-00314o-O3 for pgsql-sql@lists.postgresql.org; Tue, 01 Apr 2025 14:15:53 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tzcP9-002MA3-2m for pgsql-sql@lists.postgresql.org; Tue, 01 Apr 2025 14:15:52 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-601ff283d70so1671529eaf.3 for ; Tue, 01 Apr 2025 07:15:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743516951; x=1744121751; 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=dpu68y5L/Yz1jjG2e5u4AFowKR1A+GrfFQoizA64sys=; b=SXeN2cUy0bSXX7Z1+UDzigLWfuUuItzdzjTFOnxt/5BtaGz2QXcBDuW60YJ56DATvZ wjIAkeoMuzP7E1/RtBBYpcsDaBJnAYjos27YYPoypupljOtPi3sMawzAFS2vAE2ld/sE k2oQYeJKXc0Ge3kUO2B9UBl1L71iG1Kkwu4aH2wyb5u1DKJoMSD3TjyTxpX84oKHhJ3R 09ATy847CPXeHITZZzKU4hjHhFgio/wfYAEWfbEPgbK4Rqc7UG1Me5W+mxsAOS3XGqZ8 X7aF2UpR9VqrIRVR7N2t3flJSNKlzZZMv3qxCmqPRN0bcS9k9unQ88yFg7fDk/Sy5cPN vQ4g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743516951; x=1744121751; 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=dpu68y5L/Yz1jjG2e5u4AFowKR1A+GrfFQoizA64sys=; b=CYK/wj9HjxiK+L9fxe1bnx401mjQejFAhJrJOUHHpB0JCaNInbNQ+ZNJ+0dK7ZNzPW 9p3pJSJMRmjmuyT8lm9Gm5gB6524plHG9v7SPr/I5IrUmWnSqkjKvhKPg4CuSXpu/Dzv VzpKK+V70nk9UW6ig9dieJXDyf9svd7FOGluP6GDNGNcHXniYnX/82rWWPt+mj8ITxMJ k6QCJJ4fEe4dwRDCLQ6kAVp3rbxZbtvJgV3g4dNK+tWMvYJFA2UA2qu94n5q0crfQ+ee 0BP9fu9obg1Dlga1JGmbvnHo62i2W1AG4Z+3iISCl0HsdAZhJ1cnyrLG5ZFyRHz5JCqt tdVA== X-Gm-Message-State: AOJu0Yz/mmtHWjAF4QsUpufynfQpSspkjCR5LbX1maNx8lUEfF2FH4q3 4io8lWFXg3qxxnMDWc99S2cKwFPG54k23GPEFjPEAF2tbsB4CbHKk1xZ0SJt1owi51xAsywl9Ak znTHZVQb4gMygNQfQEasyS8F3/VU= X-Gm-Gg: ASbGncsiMT62rQ364bzgh6LwjO/sWc6uIbt7bpRnVvIOQMlNGCVMDrCRf8prZdkb17q oxnGNfMqas+GPKQlbrt67Z+iE6btQXu6WQOHlBQ/M1ChLZ4zRiAznlH1y02jY21sb5wCpO7ZnuF 4cxGSLHuloCcDnr/BSO2qfuODO X-Google-Smtp-Source: AGHT+IFYS/SsV3tLj5qWx0LYIYuA0El5bezw1vBKi/y2e6sa4KmweitdfEw85pBXSZiFXwSfxbO3HHBvQV+ec+M2K9Y= X-Received: by 2002:a05:6808:309e:b0:3fe:aecb:5c49 with SMTP id 5614622812f47-3ff0f53b4b1mr8368937b6e.21.1743516951100; Tue, 01 Apr 2025 07:15:51 -0700 (PDT) MIME-Version: 1.0 References: <18873-c148b32c0e501cc0@postgresql.org> <6C13A1CC-3841-4A5E-BC78-C8F9C5B120BB@icloud.com> In-Reply-To: <6C13A1CC-3841-4A5E-BC78-C8F9C5B120BB@icloud.com> From: "David G. Johnston" Date: Tue, 1 Apr 2025 07:15:13 -0700 X-Gm-Features: AQ5f1Jo6NToEWVEkf65NR2Rc0EAjRhfZ3wZ8bltloB0LUCv8kpupXfRD7xI6yXU Message-ID: Subject: Re: Revoke Connect Privilege from Database not working To: "Ing. Marijo Kristo" Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000087ca490631b82cba" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000087ca490631b82cba Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 1, 2025 at 4:59=E2=80=AFAM Ing. Marijo Kristo wrote: > > > > "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"=3Dc/vault_adm= in > > > Same happens when trying to revoke with the vault admin user: > > > > disp_db=3D# select current_user; > > current_user > > -------------- > > vault_admin > > (1 row) > > > > disp_db=3D# revoke connect on database "disp_db" from > > "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"; > > REVOKE > > disp_db=3D# drop user > > "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"; > > ERROR: role "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00= " > > cannot be dropped because some objects depend on it > > DETAIL: privileges for database disp_db > > If you include the "granted by" clause when you perform revoke everything usually just works. "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." [1= ] The fact vault_admin is superuser overrides the fact that it is their specific grant that is trying to be revoked. David J. [1] https://www.postgresql.org/docs/current/sql-revoke.html --00000000000087ca490631b82cba Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 1, 2025 at 4:59=E2=80=AFAM Ing. Marijo Kristo = <marijo.kristo@icloud.com> wrote:

> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"= =3Dc/vault_admin=C2=A0 =C2=A0

> Same happens when trying to revok= e with the vault admin user:
>
> disp_db=3D# select current_user;
> current_user
> --------------
> vault_admin
> (1 row)
>
> disp_db=3D# revoke connect on database "disp_db" from
> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";=
> REVOKE
> disp_db=3D# drop user
> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";=
> ERROR:=C2=A0 role "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_= 06_30+00:00"
> cannot be dropped because some objects depend on it
> DETAIL:=C2=A0 privileges for database disp_db


If you include the "granted by" clause when you per= form revoke everything usually just works.

"If a = superuser chooses to issue a GRANT or REVOKE command, the command is perfor= med as though it were issued by the owner of the affected object." [1]=

The fact vault_admin is superuser overrides the fact = that it is their specific grant that is trying to be revoked.
--00000000000087ca490631b82cba--