On Wed, Aug 8, 2018 at 1:15 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > that they had to worry about this themselves. Of the various ideas that > we'd kicked around and not been able to finish, the one that seemed most > promising to me was to invent a "function trust" mechanism.
In the interest of giving credit where credit is due, I went back and researched this. I discovered that you were the first one to suggest this idea, and that Noah was the first to produce a completed patch for it. That was in 2013. (Also in 2013, I was arguing that the first thing we should do is fix pg_dump and our in-core tools to be secure against this sort of attack. Noah had a patch for it, in 2013. Five years later, that was indeed the first thing we did. We should have done it back then.) > The things that we hadn't resolved, which is why this didn't get further > than POC stage, were > > (1) What's the mechanism for declaring trust? In this POC, it's just > a GUC that you can set to a list of role names, with $user for yourself > and "public" if you want to trust everybody. It's not clear if that's > good enough, or if we want something a bit more locked-down. Back in 2013, I pointed out that we need to consider the case where the database owner has done ALTER DATABASE .. SET search_path = 'malevolence', hoping that the superuser will log in and do something that allows control to be captured. If trusted_roles is merely a GUC, then the database owner could set that, too. If we want this solution to be water-tight, I think we need a way to make very sure that a user never uses a trust setting configured by someone else. We could implement a special rule (as you proposed back at the time) that limits the ability to ALTER DATABASE .. SET trusted_roles, but I'm a little worried that there may be other ways that one user could end up using a trusted_roles setting configured by somebody else. I wonder whether thoroughly isolating the manner of configuring trust from the GUC system might make it easier to avoid unpredictable interactions. > (2) Is trust transitive? Where and how would the list of trusted roles > change? Arguably, if you call a SECURITY DEFINER function, then once > you've decided that you trust the function owner, actual execution of the > function should use the function owner's list of trusted roles not yours. > With the GUC approach, it'd be necessary for SECURITY DEFINER functions > to implement this with a "SET trusted_roles" clause, much as they now > have to do with search_path. That's possible but it's again not very > non-invasive, so we'd been speculating about automating this more. > If we had, say, a catalog that provided the desired list of trusted roles > for every role, then we could imagine implementing that context change > automatically. Likewise, stuff like autovacuum or REINDEX would want > to run with the table owner's list of trusted roles, but the GUC approach > doesn't really provide enough infrastructure to know what to do there. Yeah, I think these are all good points. It seems natural for trust to be a property of a role, for just the reasons that you mention. However, there does also seem to be a use case for varying it temporarily on a per-session or per-function basis, and I'm not exactly sure how to cater to those needs. I have a feeling that it would be really useful to attach a listed of trusted roles, and for that matter also a search path, to the *lexical scope* of a function. > So we'd kind of decided that the GUC solution wasn't good enough, but > it didn't seem like catalog additions would be feasible as a back-patched > security fix, which is why this didn't go anywhere. But it could work > as a new feature. Check. > Anyway, I had written a small POC that did use a GUC for this, and just > checked function calls without any attempts to switch the active > trusted_roles setting in places like autovacuum. I've rebased it up to > HEAD and here it is. I wonder if Noah would like to rebase and post his version also. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
