Re: Non-superuser subscription owners

On Mon, 2023-02-27 at 16:13 -0500, Robert Haas wrote:
> On Mon, Feb 27, 2023 at 1:25 PM Jeff Davis <pgsql(at)j-davis(dot)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.

Isn't that a hard problem; maybe impossible?

>
> I guess I have a pretty hard time imagining that we can just
> obliterate SECURITY INVOKER entirely.

Of course not.

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

It also seems fundamentally reasonable that if someone grants you
privileges on one of their tables, it might be safe to access it.

I'm sure there are a few use cases for SECURITY INVOKER, but they are
quite narrow.

Perhaps most frustratingly, even if none of the users on a system has
any use case for SECURITY INVOKER, they still must all live in fear of
accessing each others' tables, because at any time a SECURITY INVOKER
function could be attached to one of the tables.

I feel like we are giving up mainstream utility and safety in exchange
for contrived or exceptional cases. That's not a good trade.

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

I'd also extend this to default expressions and other code that can be
executed implicitly.

> Maybe that's the right thing to do

If it's the right place to go, then I think we should consider
reasonable steps to take in that direction that don't cause unnecessary
breakage.

> , but I think it would inevitably
> break some things for some users.

Not all steps would be breaking changes, and a lot of those steps are
things we should do anyway. We could make it easier to write safe
SECURITY DEFINER functions, provide more tools for users to opt-out of
executing SECURITY INVOKER code, provide a way for superusers to safely
drop privileges, document the problems with security invoker and what
to do about them, etc.

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

Sure, breakage is possible, and we should mitigate it.

But we also shouldn't exaggerate it -- for instance, others have
proposed that we run code as the table owner for logical subscriptions,
and that's going to break things in the same way. Arguably, if we are
going to break something, it's better to break it consistently rather
than one subsystem at a time.

Back to the $SUBJECT, if we allow non-superusers to run subscriptions,
and the subscription runs the code as the table owner, that might also
lead to some weird behavior for triggers that rely on SECURITY INVOKER
semantics.

Regards,
Jeff Davis