On Wed, Mar 1, 2023 at 1:13 PM Jeff Davis <pg...@j-davis.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
