Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRbQm-0000vy-2B for pgsql-docs@arkaria.postgresql.org; Tue, 08 Mar 2022 15:07:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRbQk-0002Z7-PG for pgsql-docs@arkaria.postgresql.org; Tue, 08 Mar 2022 15:07:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRbQk-0002X8-G3 for pgsql-docs@lists.postgresql.org; Tue, 08 Mar 2022 15:07:18 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nRbQe-0007RC-0x for pgsql-docs@lists.postgresql.org; Tue, 08 Mar 2022 15:07:18 +0000 Received: by mail-oi1-x22d.google.com with SMTP id k2so19114268oia.2 for ; Tue, 08 Mar 2022 07:07:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=vnvNrKwgmiscJlwSEE4TmJlnctwULuwM1Hsk7Arnlxw=; b=jpr7gwIZwhKv6BR6jtutdMYNMZ1cmZJxCiFbBXFJrOUDE4N+mPPf/ENtcYFJRjLLP8 HC/nFwveVN9lCbDqqh6+7kradDlci9BYaF6a9YJnv8aWw1T8pfWRVmTm8+t0SKWuBSUh MHN/pjSA7xSsQ0fyJX5HejXryVSwRJeme8Y9ZKhTRR5CMrVdTY3Bm5vb2IYSySwgiYmI Mk29JojSyW+74iEUwho25UgL6oqlR9LqzZd5FJK2nUdMi4iKyZ18mpDgXWpcZvCQiV2f JRG0N+HZLBI4l7AmPZZmIu21LokxAhYmEj79a8NMGXTXKXXhV81Zyjl3kBJWtk/0Zmvn A10A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=vnvNrKwgmiscJlwSEE4TmJlnctwULuwM1Hsk7Arnlxw=; b=aNDhPlYa6wrdTk8iQBKE0nD5oj4o6vpS/HiTP7/Y3lwCTtSeVU3YqqeZAv8J3sQYrG 0KA7MNnJv/n/2pBlyUhTGwYm6sixGE3nEYaDLE1aXsQe9sdzpym0W1rLon6X4oUnsvlL /owQxm9y1oWHPQLvLHKH/BSIvpckqX/Ykpcn1AtF558DKajX39qCA27x1OfTTg8J7dup KsITNyhuY2DtowO2Z9H7wxfYpv6Lem/LFvQjeWwr9Ohvt/hPXsUjqU2CU/DCIRONf8Wc 4SoAhPrbP+/CltqQFDFubs/QlYUhtjptuyx3U7WZZF/vVUQcDloLiK187y67bNhYqplM 3d8A== X-Gm-Message-State: AOAM530xTGOOIRl/utKTpygAk9VQoP1wCbMm+KVO1637A738i/H1zm/2 9MiwQ6uEIGTNFLQGkdebQuCg9qRAYzeiOoG72CKiGB0j X-Google-Smtp-Source: ABdhPJyDp5jwA+NHZ0RV0whY+NjfH12YdlHaTsawd9i+/4yKr5/KdWfzwqVBaPzySGMcDaJt22eU79YgOiN/6mhc/SU= X-Received: by 2002:a05:6808:1204:b0:2d9:a01a:48d0 with SMTP id a4-20020a056808120400b002d9a01a48d0mr2951589oil.283.1646752029957; Tue, 08 Mar 2022 07:07:09 -0800 (PST) MIME-Version: 1.0 References: <1216810578.281812820.1646732222284.JavaMail.root@zimbra15-e2.priv.proxad.net> <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> In-Reply-To: From: "David G. Johnston" Date: Tue, 8 Mar 2022 08:06:53 -0700 Message-ID: Subject: Re: SQL command : ALTER DATABASE OWNER TO To: Bruce Momjian Cc: gparc@free.fr, Pg Docs Content-Type: multipart/alternative; boundary="000000000000c7601405d9b65590" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c7601405d9b65590 Content-Type: text/plain; charset="UTF-8" On Tue, Mar 8, 2022 at 7:39 AM Bruce Momjian wrote: > On Tue, Mar 8, 2022 at 10:50:38AM +0100, gparc@free.fr wrote: > > > > Hello, > > > > for this "ALTER DATABASE" form, it should be mentioned that after > execution of the command, > > the old database owner loses all his privileges on it (even connection) > although it might > > still owns schemas or objects (tables, index,...) inside it. > > > > Thanks in advance to add this important precision. > > Uh, the original owner is not the owner anymore, so why would they > assume they can reconnect, unless there is some other permission > specified for them. > > Agreed. The proposed solution simply addresses a single symptom of what may be a misunderstanding about how the system works (i.e., that an object can only have a single owner, and, each privilege is specific to an object and does not confer any implied privileges on container objects - schemas and databases namely). If there is a suggestion to improve the core misunderstandings that is something to consider. Ideally in a central place about permissions in general and not in the specific ALTER DATABASE command. Given that the default behavior of PostgreSQL is to grant CONNECT via PUBLIC, removing ownership of a database from a role does not, by default, remove their connect privilege. David J. --000000000000c7601405d9b65590 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Mar 8, 2022 at 7:39 AM Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Mar=C2=A0 8, 2022 at 10:50:38AM +0100, gparc@free.fr wrote:
>
> Hello,
>
> for this "ALTER DATABASE" form, it should be mentioned that = after execution of the command,
> the old database owner loses all his privileges on it (even connection= ) although it might
> still owns schemas or objects (tables, index,...) inside it.
>
> Thanks in advance to add this important precision.

Uh, the original owner is not the owner anymore, so why would they
assume they can reconnect, unless there is some other permission
specified for them.


Agreed.=C2= =A0 The proposed solution simply addresses a single symptom of what may be = a misunderstanding about how the system works (i.e., that an object can onl= y have a single owner, and, each privilege is specific to an object and doe= s not confer any implied=C2=A0privileges on container objects - schemas and= databases namely).

If there is a suggestion to improv= e the core misunderstandings that is something to consider.=C2=A0 Ideally i= n a central place about permissions in general and not in the specific ALTE= R DATABASE command.

Given that the default behavior of= PostgreSQL is to grant CONNECT via PUBLIC, removing ownership of a databas= e from a role does not, by default, remove their connect privilege.

David J.


--000000000000c7601405d9b65590--