Re: Facility for detecting insecure object naming

On Wed, Aug 08, 2018 at 12:00:45PM +0530, Robert Haas wrote:
> On Sun, Aug 5, 2018 at 1:34 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > If hackers and non-core extension authors are to write such code, let's make
> > it easier to check the work.
>
> +1. Better still would be to invent a way to remove the need for such
> onerous qualification, but I don't have a good idea.

Agreed. Thanks for thinking about it.

> > a. SQL intended to run under secure search_path. No class-specific rules.
> > src/bin code is an example of this class, and this is the only secure class
> > for end-user applications.
> >
> > b. SQL intended for a particular search_path, possibly untrusted. Unqualified
> > names need an exact match. Use a qualified name for any object whose
> > schema appears in search_path later than some untrusted schema. Examples
> > of this class include extension scripts, pre-CVE-2018-1058 pg_dump, some
> > functions with "SET search_path", and many casual end-user applications.
> >
> > c. SQL intended to work the same under every search_path setting. Do not use
> > any unqualified name. Most pg_catalog and contrib functions, but not those
> > having a "SET search_path" annotation, are examples of this class.
>
> If I understand correctly, the only difference between (b) and (c) is
> that references to an object in the very first schema in the search
> path could be unqualified; in most cases, that would be pg_catalog.

In (b), there's no bound on the number of schemas for which qualification is
optional. Consider search_path=pg_temp,pg_catalog,admin,"$user",public with
you trusting each of those schemas except "public". The rules of (b) then do
not require any qualified names, though unqualified names shall arrange an
exact match of argument types. On the other hand, with
search_path=public,equally_public, one shall qualify all names of objects
located in equally_public.

> So I guess what we need is a UI that lets you say either:
>
> - Don't tell me about anything, or
> - Tell me about all unqualified references, or
> - Tell me about all unqualified references except those that latch
> onto an object in <list of schemas>

"SELECT exp(1.0)" merits a warning under search_path=public,pg_catalog but not
under search_path=pg_catalog,public. Thus, it's more subtle than your last
bullet. That's why I envisioned the user declaring which schemas to trust.
The system uses that and the actual search_path to choose warnings.

> Personally, I'm not entirely sold on having that third capability. I
> guess it's valuable, but the second one seems like the much more
> valuable thing.

That's fair. Even without pursuing that, it's valuable to know the list of
trusted schemas so we can relax about exact argument type matches when the
function is in a trusted schema. For example, pg_catalog.exp(1) is safe
because no hostile user can create pg_catalog.exp(int4).