Re: Non-superuser subscription owners

Greetings,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> On Mon, 2023-02-27 at 14:10 -0500, Stephen Frost wrote:
> > 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." 
>
> I tried that:
>
> https://www.postgresql.org/message-id/75b0dbb55e9febea54c441efff8012a6d2cb5bd7.camel@j-davis.com
>
> but Andres pointed out some problems with my implementation. They
> didn't seem easily fixable, but perhaps with more effort it could work
> (run all the expressions as security definer, as well?).

Presumably. Ultimately, I tend to agree it won't be easy. That doesn't
mean it's not a worthwhile effort.

> > 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.
>
> Agreed. Perhaps we can force search_path to be set for SECURITY
> DEFINER, and require that the temp schema be explicitly included rather
> than the current "must be at the end". We could also provide a way to
> turn public access off in the same statement, so that you don't need to
> use a transaction block to keep the function private.

We do pretty strongly encourage a search_path setting for SECURITY
DEFINER today.. That said, I'm not against pushing on that harder. The
issue about temporary schemas is a more difficult issue... but frankly,
I'd like an option to say "no temporary schemas should be allowed in my
search path" when it comes to a security definer function.

> > 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 didn't propose anything radical. I'm just trying to get some
> agreement that SECURITY INVOKER is central to a lot of our security
> woes, and that we should be treating it with skepticism on a
> fundamental level.

Sure, but if we want to make progress then we have to provide a
direction for folks to go in that's both secure and convenient.

> Individual proposals for how to get away from SECURITY INVOKER should
> be evaluated on their merits (i.e. don't break a bunch of stuff).

Of course. That said ... we don't want to spend a lot of time
going in a direction that won't bear fruit; I'm hopeful that this
direction will though.

Thanks,

Stephen