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 1sQxjQ-00CYcO-TR for pgsql-general@arkaria.postgresql.org; Mon, 08 Jul 2024 23:25:17 +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 1sQxjO-00AXN4-33 for pgsql-general@arkaria.postgresql.org; Mon, 08 Jul 2024 23:25:14 +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 1sQxjN-00AXMw-KK for pgsql-general@lists.postgresql.org; Mon, 08 Jul 2024 23:25:13 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sQxjK-0016mv-Qu for pgsql-general@postgresql.org; Mon, 08 Jul 2024 23:25:12 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-5c661e75ff6so1640457eaf.2 for ; Mon, 08 Jul 2024 16:25:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720481110; x=1721085910; darn=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=fP1S4idV4K+dGMdS+upG8nyqDSaN7YPFT1cXJyywyl4=; b=ZFRnT+ugKyg4rrcsJdLiTtwHNXUo3gJo6FtX5nmX7iKK15sBOyxaT5wEpPwfN/DliR SCWkYwMeDGrrNzb9fM8ufsMQbcjZfxZrU5UmU3JHrnowszvlPYb+XSS7H9snuQ99c/4u FdsKjfp2Vnizn8KmpmmLUKzdMG4NFE76j7fykhUMBU1bhspGpD7409lDRaJqInStt1GH Li47b2XTWMBOmoFMV2rQR2MZlgxQrrQE2orOFk9zMmu5oFrvJkp06HoypfcIFRR/lT4N T7r01TcKkE6EYMJhh8ZK92Itl2HJrq5dhtYeONZeMhdiLpDdHonxM/b/RmWshglSxuym Ir/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720481110; x=1721085910; 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=fP1S4idV4K+dGMdS+upG8nyqDSaN7YPFT1cXJyywyl4=; b=QIiQk8O75Jv2iRsa5jYPi6GE29xPkTjlYtV+1WN5Hp3kCIKf/ngbRxC1VikLYV1W+r RfWHFNw3yCZIB8WF53RWcvRJbKHJmIJF1088tfaa+I89Ek5jlMZy/I/0cDUxknleD0uM FN10y8fmNgy8xkS1yVng95ZW5xoMcfXWVJe22bGTyJbotU/F8gnUXJGRE75t6MEpDFEH 5UXx0KZW6GTzG7QAb9mD8Uc+GC9+yqoyQfjiipEpSiL3HkmeSpXSMWDTl8PP6ApuGixN J6OC5f9Y8n60O7TLeMctsyxsZCLg0z8rq15aP1x83iq6BhDK+BVV8+Xt46Ii/ZZIkcpZ NhXw== X-Forwarded-Encrypted: i=1; AJvYcCUlNCV5ssv3mkRVeH+Rtd2V1Nmqx10tgGYWmNuRmdW8ljdr6DnnX5yrfodwCWwjucU/Wu/CXpG9OoiFuC4WmZfqYjegQOuPDEbbXBRG X-Gm-Message-State: AOJu0YyqL8E5+98QAvotMbNTTthHCTVJycTulLhidE6WN6YOJ3TkF1F5 Z0db8jDyai6iKZnMxvL6Gt9ZFhcCC053HJfpeAS+31vh78CllNTgVkWBipQiMCBU/+ypLyPNtir RH02vTs//Mr6d0FR29I+G1BJmlU4= X-Google-Smtp-Source: AGHT+IHJwYy0MyxJICtgxqi59VBICSqYvMjhuuQL8CVEpjLxDCfYj21QjByakizAFXpqQ9tvTWU7FDowzz0EUOVeGYU= X-Received: by 2002:a05:6871:7997:b0:254:fb0c:cd6a with SMTP id 586e51a60fabf-25eaec531bemr684950fac.48.1720481110002; Mon, 08 Jul 2024 16:25:10 -0700 (PDT) MIME-Version: 1.0 References: <69A2A7BD-F8CA-4067-B229-B5F9FC6A884F@thebuild.com> <2e3e4ddb-52b5-49b2-b363-00e3f12a83a0@postgrespro.ru> <1214992.1720473388@sss.pgh.pa.us> <1221566.1720476530@sss.pgh.pa.us> <1227391.1720479480@sss.pgh.pa.us> In-Reply-To: <1227391.1720479480@sss.pgh.pa.us> From: "David G. Johnston" Date: Mon, 8 Jul 2024 16:24:32 -0700 Message-ID: Subject: Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE To: Tom Lane Cc: Robert Haas , Pavel Luzanov , Christophe Pettus , pgsql-general Content-Type: multipart/alternative; boundary="000000000000679554061cc4b95b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000679554061cc4b95b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 8, 2024 at 3:58=E2=80=AFPM Tom Lane wrote: > I'd argue that INHERIT TRUE should be required. The point of SET TRUE > with INHERIT FALSE is that you must *explicitly* do SET ROLE or > equivalent in order to have access to the privileges of the referenced > role. I think that blast radius is too large for v16. Arguable for v17. All pre-v-16 roles encountered during upgrade will have SET true and those roles are able to login today but a non-trivial number I would expect to be unable to if we require INHERIT. It would be nicer to do away with the default connect grant to public, expect the group role to have the connect privilege on the desired database, and let the user role inherit that in lieu of granting it out explicitly, if we are into v18 feature requests. By the time the newbie stops using postgres superuser for everything they can add a connect grant here or there. There is no opportunity to issue SET ROLE before logging in, > but ISTM that means you don't get the privilege, not that you > magically get it without asking. Otherwise, why did we build this > additional level of grant specificity at all? > Mostly for the pre-defined roles, and just general explicitness. So inherit could be per-role-membership instead per-role. HBA group specification rules wasn't a motivating drive if it was considered at all. Unbreaking CREATEROLE. An analogous case is that having > some privileges on table(s) in some schema does not give you the right > to bypass needing USAGE on the schema in order to access those tables. > That sounds like the Connect privilege noted above. While +role could be seen as a sImple text-expander for pg_hba.conf, not a privilege check in its own right. We don't explicitly exclude users lacking the login attribute from the 'all' specification. They are allowed to match and then their login attribute and database-specific connect privilege is checked. David J. --000000000000679554061cc4b95b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jul 8, 2024 at 3:58=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
<= /div>
I'd argue that INHERIT TRUE should be required.=C2=A0 The = point of SET TRUE
with INHERIT FALSE is that you must *explicitly* do SET ROLE or
equivalent in order to have access to the privileges of the referenced
role.

I think that blast radius is too l= arge for v16.=C2=A0 Arguable for v17.=C2=A0 All pre-v-16 roles encountered = during upgrade will have SET true and those roles are able to login today b= ut a non-trivial number I would expect to be unable to if we require INHERI= T.

It would be nicer to do away with=C2=A0the de= fault connect grant to public, expect the group role to have the connect pr= ivilege on the desired database, and let the user role inherit that in lieu= of granting it out explicitly, if we are into v18 feature requests.=C2=A0 = By the time the newbie stops using postgres superuser for everything they c= an add a connect grant here or there.

There is no opportunity to issue SET ROLE = before logging in,
but ISTM that means you don't get the privilege, not that you
magically get it without asking.=C2=A0 Otherwise, why did we build this
additional level of grant specificity at all?

Mostly for the pre-defined roles, and just general explicitness.=C2=A0= So inherit could be per-role-membership instead per-role.=C2=A0 HBA group = specification rules wasn't a motivating drive if it was considered at a= ll.=C2=A0 Unbreaking CREATEROLE.

An analogous case is that having
some privileges on table(s) in some schema does not give you the right
to bypass needing USAGE on the schema in order to access those tables.
<= /blockquote>

That sounds like the Connect privilege noted = above.=C2=A0 While +role could be seen as a sImple text-expander for pg_hba= .conf, not a privilege check in its own right.=C2=A0 We don't explicitl= y exclude users lacking the login attribute from the 'all' specific= ation.=C2=A0 They are allowed to match and then their login attribute and d= atabase-specific connect privilege is checked.

David J= .

--000000000000679554061cc4b95b--