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 1u1oYL-008Wm6-6A for pgsql-bugs@arkaria.postgresql.org; Mon, 07 Apr 2025 15:38:25 +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 1u1oYJ-000McS-Gv for pgsql-bugs@arkaria.postgresql.org; Mon, 07 Apr 2025 15:38:23 +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 1u1oYJ-000McJ-4c for pgsql-bugs@lists.postgresql.org; Mon, 07 Apr 2025 15:38:23 +0000 Received: from mail-oa1-x29.google.com ([2001:4860:4864:20::29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u1oYH-003Upg-1w for pgsql-bugs@lists.postgresql.org; Mon, 07 Apr 2025 15:38:22 +0000 Received: by mail-oa1-x29.google.com with SMTP id 586e51a60fabf-2d0364e9231so298256fac.1 for ; Mon, 07 Apr 2025 08:38:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744040301; x=1744645101; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=8S4H0UCgmyNlpPz5BJGGitcTej3jEdDCWlFzAiKdoi8=; b=EIGG4hrT+ryCNm5J2nLxP24vVOvx4+Snfn9uefO8rBLDCQ+C9eKLFfYszEFWGZJimI Ft2bEM4WP86KgnyVoiRCutKA3T25UUsVaAzezE2Y+hTVqWRPPItAsCKI/u/I8zJn1S21 OQNWhgPzk+ehZV9VfN2hWiWqOdoQYD6zXcA3bGuhwXYwENpbD1mIXdpmKGc/DcMtNGcV OKtnEdmOZP2vE4P+rs2svuDC3XCRi2nYDmUmxqWXP5Gt3mr5ROWxj7+oYqx2qa1KH2t3 DY6M8auQh9t2oj8kKb0BZv0vLuQCbB9AXRGGJ1kDwkAX4LU3S4Y3N/D4nmNGm2e8LAMk VktQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744040301; x=1744645101; h=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=8S4H0UCgmyNlpPz5BJGGitcTej3jEdDCWlFzAiKdoi8=; b=vaSBJ4jNuJQWtS4GsYqm2rib5xjn2LMzp1VmNjnM5kChsMTYUMGAgmT2W87AcowiVs Zzf2o8hFGEvZVnVJNVjy+aeFHLUzk65vhLKZFr4sftNlzx1LXXaLQJTCGhkJVxfB8Gez oY04ZtEgj1zRyj09io36ifsdlIaxVDwbneivMYUMU3WrXO/y+YazTbwYo60+Axbrmbkz tqmqYEk2a9I/s5MyUQ1BkdS7wrI9WO2PaIKerQ7o6jW9YdJiFxwbokmZQ90vxeBaeZrF bk26XdKxTbcJB0MykkNV4T1ikO6A2p/oEWv6qts0+JxVXaF/2ATNSgk4KH9bRUmihjqL 4eNA== X-Forwarded-Encrypted: i=1; AJvYcCWPiMXcl+M/IK5e1XQmQLjNOd52pIaZL9RfWI+7lHDdQyWJx52UDz0VnxoTcHT0AOLwuo+q+/aD+0Uf@lists.postgresql.org X-Gm-Message-State: AOJu0YwJXBZb2U6zZpjDT4q5cOJdbjdBzXTFqlcQNDaTkMO38TiWHJXj nc+RsLFY3ruZuw/ErpD16CSOCwu1ZbHFJrEtFtRC4hFsugIj1eQIlr/MZKzEqX93uPvnwWgHCbI hvsHDZnZfZ5wV6lmp+Zlu0IXBD4k= X-Gm-Gg: ASbGncvn5Jr5xGCVqieTGGYrm3Dd/530J67SMPjPFo2YWTVJUX6hkqUvR2TCt2Oz1fP m1aZpmMjI5P6Fty3jbGL5cMxhuPsZYov9kXJyYi5z4LrUbV8YfV8QCf1tNQZQBNgc8s5Gu9KTHF JbLeSXennR5exaC+Sys3CUj2B9 X-Google-Smtp-Source: AGHT+IH2xz5m8kdvea4VpM5nXZyMC6+3s/wOYyRrOcoWDAmnPIQDEE9nDCcizcPh7k03i5Z6b5Z+ekq5q4cp4gqoORo= X-Received: by 2002:a05:6870:ae07:b0:2c2:542b:bce4 with SMTP id 586e51a60fabf-2cd32e3432cmr5298333fac.8.1744040300706; Mon, 07 Apr 2025 08:38:20 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Mon, 7 Apr 2025 08:37:43 -0700 X-Gm-Features: ATxdqUHEoOIqSgTJekTHiQAWbAgkPZM6YpSEbpmNbBPQPEvi8ssghOX5qvu7EIo 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: "Ing. Marijo Kristo" , PostgreSQL Bug List Content-Type: multipart/alternative; boundary="0000000000009901470632320682" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009901470632320682 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 7, 2025 at 7:27=E2=80=AFAM Ing. Marijo Kristo wrote: > Hi, > here is a full reproducer. Also revoking with the granted by clause does > not work. > > #clean initialization > postgres=3D# create database testdb owner postgres; > CREATE DATABASE > postgres=3D# create user test_admin createrole; > CREATE ROLE > postgres=3D# alter user test_admin with password 'test1234'; > ALTER ROLE > postgres=3D# grant connect on database testdb to test_admin with grant > option; > GRANT > > #create user and grant connect privilege with test_admin > postgres=3D# set role test_admin; > SET > postgres=3D> create user test_user password 'testuserpw'; > CREATE ROLE > postgres=3D> grant connect on database testdb to test_user; > GRANT > > #generate the failure by granting test_admin superuser privileges > postgres=3D> reset role; > RESET > postgres=3D# alter user test_admin superuser; > ALTER ROLE > postgres=3D# set role test_admin; > SET > postgres=3D# revoke connect on database testdb from test_user; > REVOKE > postgres=3D# drop user test_user; > ERROR: role "test_user" cannot be dropped because some objects depend on > it > DETAIL: privileges for database testdb > > #test also with "granted by clause" > postgres=3D# revoke connect on database testdb from test_user granted by > "test_admin"; > REVOKE > On master, confirmed that after this command the privilege: test_user=3Dc/test_admin (on database testdb) still exists. That seems lik= e a bug. Its at least a POLA violation and I cannot figure out how to read the revoke reference page in a way that explains it. David J. # revokescript.psql create database testdb:v; create user test_admin:v createrole; grant connect on database testdb:v to test_admin:v with grant option; set role test_admin:v; create user test_user:v password 'testuserpw'; grant connect on database testdb:v to test_user:v; reset role; alter user test_admin:v superuser; set role test_admin:v; revoke connect on database testdb:v from test_user:v granted by test_admin:v; \l+ testdb:v drop user test_user:v; > psql postgres --file revokescript.psql -v v=3D1 --0000000000009901470632320682 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 7, 2025 at 7:27=E2=80=AFAM Ing. Marijo Kristo = <marijo.kristo@icloud.com> wrote:
=
GRANT

#create user and grant connect priv= ilege with test_admin
postgres=3D# set role test_admin;
SET
postgres=3D> create user test_user password &= #39;testuserpw';
CREATE ROLE
postgres=3D>= ; grant connect on database testdb to test_user;
GRANT

#generate the failure by granting test_admin superus= er privileges
postgres=3D> reset role;
RESET=
postgres=3D# alter user test_admin superuser;
= ALTER ROLE
postgres=3D# set role test_admin;
SE= T
postgres=3D# revoke connect on database testdb from test_us= er;
REVOKE
postgres=3D# drop user test_user;
ERROR:=C2=A0 role "test_user" cannot be dropped becau= se some objects depend on it
DETAIL:=C2=A0 privileges for dat= abase testdb

#test also with "granted by = clause"
postgres=3D# revoke connect on database testdb f= rom test_user granted by "test_admin";
REVOKE

--0000000000009901470632320682--