Re: Non-superuser subscription owners

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

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

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

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

Regards,
	Jeff Davis