MIME-Version: 1.0
In-Reply-To: <CA+TgmoZMqsg6-6qN_fuMZTGu=Vdyjv-u9ZgWbEnOTvRE450uvQ@mail.gmail.com>
References: <CAFCRh-8+PGGTuqg=rSKA533D0dqYAgq69UzSqMm67VEW02nZyQ@mail.gmail.com>
 <CAKFQuwYK2Vdnbdaxh9QF_0PYpztg51nc-iqYeiKDfpzek7hTdQ@mail.gmail.com>
 <CAFCRh-8ttK7AexZtZq-vcj+u5e2F93HEs63jrkEH0pq6Gf1TWw@mail.gmail.com>
 <c42d9931-1ec3-4f41-8b4c-a30f5f79e77d@technowledgy.de> <CAKFQuwZL96kB2mR4SG0=Hig21mwv5AhkxjZRGCYoqeYzPBv6Tw@mail.gmail.com>
 <4308abb3-269e-4cee-a48f-c95d49ede6c2@postgrespro.ru> <CAFCRh-_ZVP4emEKSGYJoM2hP657z1ZTq=UVkb7xZCYdByawFKQ@mail.gmail.com>
 <CAFCRh-8d9+GZSXzK=UhJKOq+BTVt9eG0E4Zu6dALo-OaOpunYQ@mail.gmail.com> <CA+TgmoZMqsg6-6qN_fuMZTGu=Vdyjv-u9ZgWbEnOTvRE450uvQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 12 Sep 2024 14:42:32 -0700
Message-ID: <CAKFQuwaJMb24Ygerm+UkHm2zSNQvMC7M+cm2cdteV2_quWdBnA@mail.gmail.com>
Subject: Re: Backward compat issue with v16 around ROLEs
To: Robert Haas <robertmhaas@gmail.com>
Cc: Dominique Devienne <ddevienne@gmail.com>, Pavel Luzanov <p.luzanov@postgrespro.ru>, 
	Wolfgang Walther <walther@technowledgy.de>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ee6cce0621f2fb90"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwaJMb24Ygerm%2BUkHm2zSNQvMC7M%2Bcm2cdteV2_quWdBnA%40mail.gmail.com>
Precedence: bulk

--000000000000ee6cce0621f2fb90
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thursday, September 12, 2024, Robert Haas <robertmhaas@gmail.com> wrote:

> On Thu, Sep 12, 2024 at 3:40=E2=80=AFPM Dominique Devienne <ddevienne@gma=
il.com>
> wrote:
> >
> > Any existing ROLE graph which had "back-edges" (GRANTs) from a ROLE
> > back to the ROLE that created it, valid in pre-v16, becomes invalid in
> v16+.
> > And there's no work-around. Tough luck, take a hike...
> >
>
>
> So I guess I would respectfully disagree with
> the idea that this works on v14 and v16 broke it. It doesn't really
> work on v14, or at least not any better than just using SUPERUSER
> everywhere that you're currently using CREATEROLE. And if you choose
> to do that, I think your example will work pretty much the same way on
> v16 as it does on v14.


After false-starting on a few replies and pondering a bit more that is
basically what I=E2=80=99ve come to conclude as well.  We basically changed=
 things
because this model was deemed dangerous.  I suppose we took little effort
to make it safer in the new regime had anyone decided to use it anyway,
instead figuring that most would separate the main DAG of application roles
from the object owners and role creators.  At least that=E2=80=99s always b=
een my
base design principle.


>
> However, it seems like we might be able to fix this by just making the
> code smarter. Maybe there's a problem that I'm not seeing, but if the
> boss grants a privilege to alice and alice grants it to bob and bob
> grants it back to alice and then the boss revokes the privilege, why
> can't we figure out that alice no longer has a source for that
> privilege *aside from the one involved in the cycle* and undo the
> reciprocal grants that bob and alice made to each other? Right now I
> believe we just ask "is the number of sources that alices has for this
> privilege still greater than zero" which only works if there are no
> cycles but maybe we can do better. We'd probably need to think
> carefully about concurrency issues, though, and whether pg_dump is
> smart enough to handle this case. Also, there are separate code paths
> for role grants and non-role grants, and since I went to a lot of
> trouble to make them work the same way, I'd really prefer it if we
> didn't go back to having them work differently...
>
>
I=E2=80=99m definitely not keen on trying to deal with circularities in the=
 graph.
I get that we broke a working model here but I=E2=80=99m still not seeing w=
hy that
model is one we would strive to accommodate in a green-field situation.

A user can delegate away some ability to a role they create but cannot make
it so roles they create can pretend to be their creator.  The main thing I
haven=E2=80=99t looked into is if alice delegates createrole to bob and the=
n
removes bob does she assume all of the roles bob created or must there be
an explicit reassigned owned executed?

David J.

--000000000000ee6cce0621f2fb90
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thursday, September 12, 2024, Robert Haas &lt;<a href=3D"mailto:robertmh=
aas@gmail.com">robertmhaas@gmail.com</a>&gt; wrote:<br><blockquote class=3D=
"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding=
-left:1ex">On Thu, Sep 12, 2024 at 3:40=E2=80=AFPM Dominique Devienne &lt;<=
a href=3D"mailto:ddevienne@gmail.com">ddevienne@gmail.com</a>&gt; wrote:<br=
>
&gt;<br>
&gt; Any existing ROLE graph which had &quot;back-edges&quot; (GRANTs) from=
 a ROLE<br>
&gt; back to the ROLE that created it, valid in pre-v16, becomes invalid in=
 v16+.<br>
&gt; And there&#39;s no work-around. Tough luck, take a hike...<br>
&gt;<br><br>
<br>So I guess I would respectfully disagree with<br>
the idea that this works on v14 and v16 broke it. It doesn&#39;t really<br>
work on v14, or at least not any better than just using SUPERUSER<br>
everywhere that you&#39;re currently using CREATEROLE. And if you choose<br=
>
to do that, I think your example will work pretty much the same way on<br>
v16 as it does on v14.</blockquote><div><br></div><div>After false-starting=
 on a few replies and pondering a bit more that is basically what I=E2=80=
=99ve come to conclude as well.=C2=A0 We basically changed things because t=
his model was deemed dangerous.=C2=A0 I suppose we took little effort to ma=
ke it safer in the new regime had anyone decided to use it anyway, instead =
figuring that most would separate the main DAG of application roles from th=
e object owners and role creators.=C2=A0 At least that=E2=80=99s always bee=
n my base design principle.</div><div>=C2=A0</div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex">
<br>However, it seems like we might be able to fix this by just making the<=
br>
code smarter. Maybe there&#39;s a problem that I&#39;m not seeing, but if t=
he<br>
boss grants a privilege to alice and alice grants it to bob and bob<br>
grants it back to alice and then the boss revokes the privilege, why<br>
can&#39;t we figure out that alice no longer has a source for that<br>
privilege *aside from the one involved in the cycle* and undo the<br>
reciprocal grants that bob and alice made to each other? Right now I<br>
believe we just ask &quot;is the number of sources that alices has for this=
<br>
privilege still greater than zero&quot; which only works if there are no<br=
>
cycles but maybe we can do better. We&#39;d probably need to think<br>
carefully about concurrency issues, though, and whether pg_dump is<br>
smart enough to handle this case. Also, there are separate code paths<br>
for role grants and non-role grants, and since I went to a lot of<br>
trouble to make them work the same way, I&#39;d really prefer it if we<br>
didn&#39;t go back to having them work differently...<br><br>
</blockquote><div><br></div><div>I=E2=80=99m definitely not keen on trying =
to deal with circularities in the graph.=C2=A0 I get that we broke a workin=
g model here but I=E2=80=99m still not seeing why that model is one we woul=
d strive to accommodate in a green-field situation.</div><div><br></div><di=
v>A user can delegate away some ability to a role they create but cannot ma=
ke it so roles they create can pretend to be their creator.=C2=A0 The main =
thing I haven=E2=80=99t looked into is if alice delegates createrole to bob=
 and then removes bob does she assume all of the roles bob created or must =
there be an explicit reassigned owned executed?</div><div><br></div><div>Da=
vid J.</div><div><br></div>

--000000000000ee6cce0621f2fb90--