Re: POC for a function trust mechanism

On Wed, Aug  8, 2018 at 01:15:38PM -0400, Tom Lane wrote:
> This is sort of a counter-proposal to Noah's discussion of search path
> security checking in <20180805080441.GH1688868@rfd.leadboat.com>.
> (There's no technical reason we couldn't do both things, but I think
> this'd be more useful to most people.)

Yes, the query from Noah below confirmed that schema qualification just
isn't a realistic approach:

    CREATE FUNCTION latitude(earth)
    RETURNS float8
    LANGUAGE SQL
    IMMUTABLE STRICT
    PARALLEL SAFE
    AS 'SELECT CASE
    WHEN @cube_schema(at)(dot)cube_ll_coord($1, 3) OPERATOR(pg_catalog./)
    @extschema(at)(dot)earth() OPERATOR(pg_catalog.<) -1 THEN -90::pg_catalog.float8
    WHEN @cube_schema(at)(dot)cube_ll_coord($1, 3) OPERATOR(pg_catalog./)
    @extschema(at)(dot)earth() OPERATOR(pg_catalog.>) 1 THEN 90::pg_catalog.float8
    ELSE pg_catalog.degrees(pg_catalog.asin(@cube_schema(at)(dot)cube_ll_coord($1, 3)
    OPERATOR(pg_catalog./) @extschema(at)(dot)earth()))
    END';

Of course, with the limitations of backpatching and security-only
discussion, that was the best we could do in the past.

> The core idea here is to prevent security problems not by changing an
> application's rules for operator/function name resolution, but by
> detecting an attempted compromise and preventing the trojan-horse code
> from being executed.  Essentially, a user or application is to declare
> a list of roles that it trusts functions owned by, and the system will
> then refuse to execute functions owned by other not-trusted roles.
> So, if $badguy creates a trojan-horse operator and manages to capture
> a call from your SQL code, he'll nonetheless not be able to execute
> code as you.

Yes, this is the only reasonable approach I can think of.

> To reduce the overhead of the mechanism and chance of unintentionally
> breaking things, superuser-owned functions (particularly, all built-in
> functions) are always trusted by everybody.  A superuser who wants to
> become you can do so trivially, with no need for a trojan horse, so
> this restriction isn't creating any new security hole.

Agreed.

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

Yes, works for me.

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

I can't think of any other places we do transitive permissions, except
for role membership.  I don't see the logic in adding such transitivity
to function/operator calls, or even a per-function GUC.  I assume most
sites have a small number of extensions installed by a predefined group
of users, usually superusers. If there is a larger group, a group role
should be created and those people put in the role, and the group role
trusted.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +