Re: [PATCH v2] use has_privs_for_role for predefined roles

On Sun, Feb 6, 2022 at 12:24 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
> > I'd like to pick this patch up and see it through to commit/push.
> > Presumably that will include back-patching to all supported pg versions.
> > Before I go through the effort to back-patch, does anyone want to argue
> > that this should *not* be back-patched?
>
> Hm, I'm -0.5 or so. I think changing security-related behaviors
> in a stable branch is a hard sell unless you are closing a security
> hole. This is a fine improvement for HEAD but I'm inclined to
> leave the back branches alone.

I think the threshold to back-patch a clear behavior change is pretty
high, so I do not think it should be back-patched.

I am also not convinced that a sufficient argument has been made for
changing this in master. This isn't the only thread where NOINHERIT
has come up lately, and I have to admit that I'm not a fan. Let's
suppose that I have two users, let's say sunita and sri. In the real
world, Sunita is Sri's manager and needs to be able to perform actions
as Sri when Sri is out of the office, but it isn't desirable for
Sunita to have Sri's privileges in all situations all the time. So we
mark role sunita as NOINHERIT and grant sri to sunita. Then it turns
out that Sunita also needs to be COPY TO/FROM PROGRAM, so we give her
pg_execute_server_program. Now, if she can't exercise this privilege
without setting role to the prefined role, that's bad, isn't it? I
mean, we want her to be able to copy between *her* tables and various
shell commands, not the tables owned by pg_execute_server_program, of
which there are presumably none.

It seems to me that the INHERIT role flag isn't very well-considered.
Inheritance, or the lack of it, ought to be decided separately for
each inherited role. However, that would be a major architectural
change. But in the absence of that, it seems clearly better for
predefined roles to disregard INHERIT and just always grant the rights
they are intended to give. Because if we don't do that, then we end up
with people having to SET ROLE to the predefined role and perform
actions directly as that role, which seems like it can't be what we
want. I almost feel like we ought to be looking for ways of preventing
people from doing SET ROLE to a predefined role altogether, not
encouraging them to do it.

--
Robert Haas
EDB: http://www.enterprisedb.com