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

Greetings,

* Jeff Davis (pgsql@j-davis.com) wrote:
> 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 do think there are some use-cases for it, but agree that it'd be
better to encourage more use of SECURITY DEFINER and one approach to
that might be to have a way for users to explicitly say "don't run code
that isn't mine or a superuser's with my privileges."  Of course, we
need to make sure it's possible to write safe SECURITY DEFINER functions
and to be clear about how to do that to avoid the risk in the other
direction.  We also need to provide some additonal functions along the
lines of "calling_role()" or similar (so that the function can know who
the actual role is that's running the trigger) for the common case of
auditing or needing to know the calling role for RLS or similar.

I don't think we'd be able to get away with just getting rid of SECURITY
INVOKER entirely or even in changing the current way triggers (or
functions in views, etc) are run by default.

> > 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 don't know about trying to define that X things are ok and Y things
are not, that seems like it would be more confusing and difficult to
work with.  Regular SELECT queries that pull data that Bob has access to
but Alice doesn't is a security issue too, were Alice to install a
function that Bob calls which writes that data into a place that Alice
could then access it.  Perhaps if we could allow Bob to say "these
things are ok for Alice's code to access" then it could work ... but if
that's what is going on then the code could run with Alice's permissions
and Bob could use our nice and granular GRANT/RLS system to say what
Alice is allowed to access.

> >  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.

current_role / current_user are certainly common as a default
expression.  I agree that that's more of an edge case that would be nice
to solve in a different way though.  I do think there's some other use
cases for SECURITY INVOKER but not enough folks understand the security
risk associated with it and it'd be good for us to improve on that
situation.

Thanks,

Stephen