Re: Non-superuser subscription owners

On Mon, 2023-02-27 at 10:45 -0500, Robert Haas wrote:

> Suppose Alice owns a table and attaches a trigger to it. If
> Bob inserts into that table, I think we have to run the trigger,
> because Alice is entitled to assume that, for example, any BEFORE
> triggers she might have defined that block certain kinds of inserts
> are actually going to block those inserts; any constraints that she
> has applied to the table are going to be enforced against all new
> rows; and any default expressions she supplies are actually going to
> work.

True, but I still find myself suspending my disbelief. Which of these
use cases make sense for SECURITY INVOKER?

> I think Bob has to be OK with those things too; otherwise, he
> just shouldn't insert anything into the table.

Right, but why should Bob's privileges be needed to do any of those
things? Any difference in privileges, for those use cases, could only
either get in the way of achieving Alice's goals, or cause a security
problem for Bob.

> But Bob doesn't have to be OK with Alice's code changing the session
> state, or executing DML or DDL with his permissions.

What's left? Should Bob be OK with Alice's code using his permissions
for anything?

> I wonder if
> that's where we should be trying to insert restrictions here. Right
> now, we think of SECURITY_RESTRICTED_OPERATION as a way to prevent a
> function or procedure that runs under a different user ID than the
> session user from poisoning the session state. But I'm thinking that
> maybe the problem isn't really with code running under a different
> user ID. It's with running code *provided by* a different user ID.
> Maybe we should stop thinking about the security context as something
> that you set when you switch to running as a different user ID, and
> start thinking about it as something that needs to be set based on
> the
> relationship between the user that provided the code and the session
> user. If they're not the same, some restrictions are probably
> appropriate, except I think in the case where the user who provided
> the code can become the session user anyway.

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?

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.

Regards,
Jeff Davis