Re: POC for a function trust mechanism

On Wed, Aug 8, 2018 at 1:15 PM, Tom Lane <tgl@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