Re: Non-superuser subscription owners

On Wed, Mar 1, 2023 at 1:13 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I don't think it's magic, as I said above. But I assume that your more
> general point is that if we take some responsibility away from the
> invoker and place it on the definer, then it creates room for new kinds
> of problems. And I agree.
>
> The point of moving responsibility to the definer is that the definer
> can actually do something to protect themselves (nail down search_path,
> restrict USAGE privs, and avoid dynamic SQL); whereas the invoker is
> nearly helpless.

I think there's some truth to that allegation, but I think it largely
comes from the fact that we've given very little thought or attention
to this problem. We have a section in the documentation on writing
SECURITY DEFINER functions safely because we've known for a long time
that it's dangerous and we've provided some (imperfect) tools for
dealing with it, like allowing a SET search_path clause to be attached
to a function definition. We have no comparable documentation section
about SECURITY INVOKER because we haven't historically taken that
seriously as a security hazard and we have no tools to make it safe.
But we could, as with what I'm proposing here, or the user/function
trust mechanism previously proposed by Noah, or various other ideas
that we might have.

I don't like the idea of saying that we're not going to try to invent
anything new and just push people into using the stuff we already
have. The stuff we have for security SECURITY DEFINER functions is not
very good. True, it's better than what we have for protecting against
the risks inherent in SECURITY INVOKER, but that's not saying much:
anything at all is better than nothing. But it's easy to forget a SET
search_path clause on one of your functions, or to include something
in that search path that's not actually safe, or to have a problem
that isn't blocked by just setting search_path. Also, not that it's
the most important consideration here, but putting a SET clause on
your functions is really kind of expensive if what the function does
is trivial, which if you're using it in an index expression or a
default expression, will often be the case. I don't want to pretend
like I have all the answers here, but I find it really hard to believe
that pushing people to do the same kind of nonsense that's currently
required when writing a SECURITY DEFINER function for a lot of their
other functions as well is going to be a win. I think it will probably
suck.

To be fair, it's possible that there's no solution to this class of
problems that *doesn't* suck, but I think we should look a lot harder
before coming to that conclusion. I've come to agree with your
contention that we're not taking the hazards of SECURITY INVOKER
nearly seriously enough, but I think you're underestimating the
hazards that SECURITY DEFINER poses, and overestimating how easy it is
to avoid them.

--
Robert Haas
EDB: http://www.enterprisedb.com