Re: pgsql: Add new GUC createrole_self_grant.

On Tue, Jan 10, 2023 at 8:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [ squint ... ] Are you sure it's not a security *hazard*, though?

I think you have to squint pretty hard to find a security hazard here.
The effect of this GUC is to control the set of privileges that a
CREATEROLE user automatically grants to themselves. But granting
yourself privileges does not normally lead to any sort of security
privilege. It's not completely impossible, I believe. For example,
suppose that I, as a CREATEROLE user who is not a superuser, execute
"CREATE ROLE shifty". I then set up a schema that shifty can access
and I cannot. I then put that schema into my search_path despite the
fact that I haven't given myself access to it. Now, depending on the
value of this setting, I might either implicitly inherit shifty's
privileges, or I might not. So, if I was expecting that I wouldn't,
and I do, then I have now created a situation where if I do more dumb
things I could execute some shifty code that lets that shifty user
take over my account.

But, you know, if I'm that dumb, I could also hit myself in the head
with a hammer and the shifty guy could use the fact that I'm
unconscious to fish the sticky note out of my wallet where,
presumably, I keep my database password.

The bigger point here, I think, is that this GUC only controls default
privileges -- and we already have a system for default privileges that
allows any user to give away privileges on virtually any object that
they create to anyone. Nothing about that system is superuser-only.
This system is far more restricted in its scope. It only allows you to
give privileges to yourself, not anyone else, and only if you're a
CREATEROLE user who is not a SUPERUSER. It seems a bit crazy to say
that it's not a hazard for Alice to automatically grant every
permission in the book to Emil every time she creates a table or
schema or type or sequence or a function, but it is a hazard if Bob
can grant INHERIT and SET to himself on roles that he creates.

That said, in my original design, this was controlled via a different
mechanism which was superuser-only. I was informed that made no sense,
so I changed it. Now here we are.

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