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 1rSgef-00FGN3-TH for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 17:03:14 +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 1rSgee-006c1o-7k for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 17:03:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSged-006c1g-V8 for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 17:03:12 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rSgeb-003VNl-M0 for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 17:03:11 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-598a5448ef5so3535761eaf.0 for ; Wed, 24 Jan 2024 09:03:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706115787; x=1706720587; 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=+fpL7rWlNpw2621/LKgUWU7H1QPzbT2IGuuWcskChBk=; b=eRQ3T8ul1w4zuLJTLv849uouIRU6bAT5Fr79Vi6WzjR9cz1h4XN4XH4wGOseju2eZ0 6NKii+D2WZWAb3Djy/GLdbcJNKuuE+/fR4i3PJFSh++OUYgFPm93H60kM2jocNCQDdVc OK2REz23paMFzjddRQvPFB9IF4VJNA8pBujoJh7O1crRxEB4mRFb3dFwOG1EMEyJVEGe 2OF55eb07yBbJItAyhhKEvQEDgbVcb7rcMPwBqQshHHfSqf83//1I7OmTFOsp+jlaNBk Yk7I+oDfI6ms/2HV9rKkcya1/2gm4iAUoBB7zmjZdgkpSuPpvFURLzMEWFST0qOOfXM+ RFSw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706115787; x=1706720587; 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=+fpL7rWlNpw2621/LKgUWU7H1QPzbT2IGuuWcskChBk=; b=FomxhuU+BoOVISPCQ5o/3+EYRskCFjvmhx18rrFY/t8tMNNuNpjv400tdhzMybgSuM rZaZQFlngf1IUFPxHh2vtTMjEstZ/vLUOu/k2NwvA9JZ8lG6u0z83+CYiZFaC9dzK7nt 99j2hbqXHiBJ9JgBtwJSgkFEOOpPJf01EEtdY71eWlGU+njjrI9sojqOTDPjahCB302v zJs7RTu9vF1dVU0zCmJZA4OwEt/REdBgj4ZBjpQ1QwBMhOdNDX7sxp8eqz2rGo2UDTdT y2+LkZpyHdhEWjNxsTW+LU6o117ZUVQz3AyjmHl3LWa3X/MXa07o7vh/a3TneWg8x13I W+vQ== X-Gm-Message-State: AOJu0Yxl8l3YEB9mWtrrFs6hpC0cG0gAjxdB1HEvl6Cxtyj0iwbZd4ey WUX8xMt4rzOyZDM47TPRDq/8w704eClWSD+kqaKDbvWrryCYdEq3U7tuoO4LaNYewMEMAeE4kwa lmVG2CoieCsRwzJMLCjRVF2RyMG4= X-Google-Smtp-Source: AGHT+IHJWdAtciQWQGKKVCA4iUy/7R9uVzVVH0Y/kxttE/8bDumwzcJakto2spU15btB2j7FyGxBi0YEeryYzBqbayk= X-Received: by 2002:a4a:c54a:0:b0:599:9e45:a700 with SMTP id j10-20020a4ac54a000000b005999e45a700mr1452038ooq.6.1706115787227; Wed, 24 Jan 2024 09:03:07 -0800 (PST) MIME-Version: 1.0 References: <202401241656.hbks4btwvjnf@alvherre.pgsql> In-Reply-To: <202401241656.hbks4btwvjnf@alvherre.pgsql> From: "David G. Johnston" Date: Wed, 24 Jan 2024 10:02:31 -0700 Message-ID: Subject: Re: SQL command : ALTER DATABASE OWNER TO To: Alvaro Herrera Cc: Laurenz Albe , gparc@free.fr, Daniel Gustafsson , pgsql-docs Content-Type: multipart/alternative; boundary="0000000000007188bf060fb409bb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007188bf060fb409bb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 24, 2024 at 9:56=E2=80=AFAM Alvaro Herrera wrote: > On 2024-Jan-24, Laurenz Albe wrote: > > > The permissions are transferred to the new owner, so the old owner > doesn't > > have any privileges on the object (and, in your case, cannot connect to > > the database any more). > > However, if the old owner had a pg_hba.conf line that allowed them in, > and the new owner doesn't, then they're now both locked out of the > database with no recourse. > > The OP doesn't actually care about inherited permissions, just the stated ones. That said, I do think there is a problem here: postgres=3D# select current_user; -[ RECORD 1 ]+------- current_user | davidj postgres=3D# revoke all on database newdb2 from public; REVOKE postgres=3D# \l newdb2 List of databases -[ RECORD 1 ]-----+------------------------ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale | ICU Rules | Access privileges | testowner=3DCTc/testowner postgres=3D# grant all on database newdb2 to testowner; -- as I am logged in as davidj this grant should actually happen, with davidj as the grantor -- the grants that materialize from ownership has the owning role as the grantor -- it is only those that should be removed upon reassigning ownership GRANT postgres=3D# \l newdb2 List of databases -[ RECORD 1 ]-----+------------------------ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale | ICU Rules | Access privileges | testowner=3DCTc/testowner -- I expect to see "testowner=3DCTc/davidj" here as well David J. --0000000000007188bf060fb409bb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 24, 2024 at 9:56=E2=80=AFAM Alvaro Herrera <= ;alvherre@alvh.no-ip.org>= wrote:
On 2024-Jan-24, Laurenz Albe wrote:

> The permissions are transferred to the new owner, so the old owner doe= sn't
> have any privileges on the object (and, in your case, cannot connect t= o
> the database any more).

However, if the old owner had a pg_hba.conf line that allowed them in,
and the new owner doesn't, then they're now both locked out of the<= br> database with no recourse.


The OP doe= sn't actually care about inherited permissions, just the stated ones.= =C2=A0 That said, I do think there is a problem=C2=A0here:

postgres=3D# select current_user;
-[ RECORD 1 ]+-------
current= _user | davidj

postgres=3D# revoke all on database= =C2=A0newdb2 from public;
REVOKE
postgres=3D# \l newdb2
List of d= atabases
-[ RECORD 1 ]-----+------------------------
Name =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| newdb2
Owner =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | testowner
Encoding =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| UTF8
Locale Provider =C2=A0 | libc
Collate =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8
Ctype =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | en_US.UTF-8
ICU Locale =C2=A0 =C2=A0 =C2=A0 =C2=A0|
ICU = Rules =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Access privileges | testowner=3DCTc/= testowner

postgres=3D# grant all on database newdb2 to testowner;
-- as I am logged in as davidj this grant should actually happen, wit= h davidj=C2=A0as the grantor
-- the grants that materialize from ow= nership has the owning role as the grantor
-- it is only those that= should be removed upon reassigning ownership

GRANTpostgres=3D# \l newdb2
List of databases
-[ RECORD 1 ]-----+-------= -----------------
Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= newdb2
Owner =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | testowner
E= ncoding =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| UTF8
Locale Provider =C2=A0 = | libc
Collate =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8
Ctype= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8
ICU Locale =C2= =A0 =C2=A0 =C2=A0 =C2=A0|
ICU Rules =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Acc= ess privileges | testowner=3DCTc/testowner

-- I expect to see "= testowner=3DCTc/davidj" here as well

David J.

=
--0000000000007188bf060fb409bb--