Re: Non-superuser subscription owners - Mailing list pgsql-hackers

On Mon, Feb 27, 2023 at 1:25 PM Jeff Davis <pgsql@j-davis.com> wrote:
> I think you are saying that we should still run Alice's code with the
> privileges of Bob, but somehow make that safe(r) for Bob. Is that
> right?

Yeah. That's the idea I was floating, at least.

> That sounds hard, and I'm still stuck at the "why" question. Why do we
> want to run Alice's code with Bob's permissions?
>
> The answers I have so far are abstract. For instance, maybe it's a
> clever SRF that takes table names as inputs and you want people to only
> be able to use the clever SRF with tables they have privileges on. But
> that's not what most functions do, and it's certainly not what most
> default expressions do.

I guess I have a pretty hard time imagining that we can just
obliterate SECURITY INVOKER entirely. It seems fundamentally
reasonable to me that Alice might want to make some code available to
be executed in the form of a function or procedure but without
offering to execute it with her own privileges. But I think maybe
you're asking a different question, which is whether when the code is
attached to a table we ought to categorically switch to the table
owner before executing it. I'm less sure about the answer to that
question. We already take the position that VACUUM always runs as the
table owner, and while VACUUM runs index expressions but not for
example triggers, why not just be consistent and run all code that is
tied to the table as the table owner, all the time?

Maybe that's the right thing to do, but I think it would inevitably
break some things for some users. Alice might choose to write her
triggers or default expressions in ways that rely on them running with
Bob's permissions in any number of ways. For instance, maybe those
functions issue a SELECT query against an RLS-enabled table, such that
the answer depends on whose privileges are used to run the query. More
simply, she might refer to CURRENT_ROLE, say to record who inserted
any particular row into her table, which seems like a totally
reasonable thing to want to do. If she was feeling really clever, she
might even have designed queries that she's using inside those
triggers or default expressions to fail if Bob doesn't have enough
permissions to do some particular modification that he's attempting,
and thus block certain kinds of access to her own tables. That would
be pretty weird and perhaps too clever by half, but the point is that
the current behavior is probably known to many, many users and we
really can't know what they've done that depends on that. If we change
any behavior here, some people are going to notice those changes, and
they may not like them.

To put that another way, we're not talking about a black-and-white
security vulnerability here, like a buffer overrun that allows for
arbitrary code execution. We're talking about a set of semantics that
seem to be somewhat fragile and vulnerable to spawning security
problems. Nobody wants those security problems, for sure. But it
doesn't follow that nobody is relying on the semantics.

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