Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On Sat, 2023-08-19 at 11:59 -0700, Andres Freund wrote:
> If you install a bunch of extensions into public - very very
> common from what I have seen - you can't really remove public from
> the search
> path. Which then basically makes all approaches of resolving any of
> the
> security issues via search path pretty toothless.

Toothless only if (a) untrusted users have CREATE privileges in the
public schema, which is no longer the default; and (b) you're writing a
function that accesses extension objects installed in the public
schema.

While those may be normal things to do, there are a lot of times when
those things aren't true. I speculate that it's far more common to
write functions that only use pg_catalog objects (e.g. the "+"
operator, some string manipulation, etc.) and basic control flow.

There's a lot of value in making those simple cases secure-by-default.
We are already moving users towards a readable-but-not-writable public
schema as a best practice, so if we also move to something like SEARCH
SYSTEM as a best practice, then that will help a LOT of users.

> >
> I don't think that really works in practice, due to the very common
> practice
> of installing extensions into the same schema as the application.
> Then that
> schema needs to be in search path (if you don't want to schema
> qualify
> everything), which leaves you wide open.

...

> >
> myextension is typically public. Which means that there's zero
> protection due
> to such a search path.

You mentioned this three times so I must be missing something. Why is
it "wide open" and "zero protection"? If the schema is not world-
writable, then aren't attacks a lot harder to pull off?

> >
> I think the more common attack paths are things like tricking
> extension
> scripts into evaluating arbitrary code, to gain "real superuser"
> privileges.

Extension scripts are a separate beast. I do see some potential avenues
of attack, but I don't see how your approach of resolving schemas early
would help.

> Search path does not reliably protect things involving "type
> matching". If you
> have a better fitting cast, or a function call with parameters that
> won't need
> coercion, later in search path, they'll win, even if there's another
> fit
> earlier on.

You need to trust the schemas in your search_path.

> If we instead store something that avoids the need for such search,
> the
> "better fitting cast" logic wouldn't add these kind of security
> issues
> anymore.

I don't disagree, but I don't understand the approach in detail (i.e. I
couldn't write it up as a proposal). For instance, what would the
pg_dump output look like?

And even if we had that in place, I think we'd still want a better way
to control the search_path.

> >
> Hm, I'm not quite sure I follow on what exactly you see as different
> here.

From what I understand, Robert's approach is to fully parse the
commands and resolve to specific OIDs (necessitating dependencies,
etc.); while your approach resolves to fully-qualified names but not
OIDs (and needing no dependencies).

I don't understand either proposal entirely, so perhaps I'm on the
wrong track here, but I feel like Robert's approach is more "normal"
and easy to document whereas your approach is more "creative" and
perhaps hard to document.

Both approaches (resolving to names and resolving to OIDs) seem pretty
far away, so I'm still very much inclined to nudge users toward safer
best practices with search_path. I think SEARCH SYSTEM is a good start
there and doable for 17.

Regards,
Jeff Davis