Re: Facility for detecting insecure object naming

On Sat, Aug 11, 2018 at 03:32:23PM -0500, Nico Williams wrote:
> On Sat, Aug 11, 2018 at 12:47:05PM -0700, Noah Misch wrote:
> > -- (3) "SET search_path" with today's code.
> > --
> > -- Security and reliability considerations are the same as (2). Today, this
> > -- reduces performance by suppressing optimizations like inlining.
>
> Out of curiosity, why does this suppress inlining?

The function call machinery, fmgr_security_definer(), is what actually applies
the setting. To inline such functions, one would need to develop a
representation that attaches the setting change to the nodes resulting from
the inlining. When evaluating said nodes, apply the attached setting.

> Anyways, my preference would be to have syntax by which to say: resolve
> at declaration time using the then-in-effect search_path and store
> as-qualified.

Agreed; having that would be great. (I mentioned it as option (7) of
https://postgr.es/m/20180710014308.GA805781@rfd.leadboat.com.) It has
limitations, though:

- Does not help with inexact argument type matches.
- While the applicability to sql-language functions seems clear, other
languages don't benefit as much. You might extend it to a subset of
PL/pgSQL functions, excluding e.g. ones that contain EXECUTE. I see no
chance to help PL/Perl or PL/Python.
- Unlikely to be a good back-patch candidate.

> Another possibility would be to have a way to set a search_path for all
> expressions in a given schema, something like:
>
> SET SCHEMA my_schema DEFAULT search_path = ...;
>
> which would apply to all expressions in schema elements in schema
> "my_schema":
>
> - CHECK expressions
> - INDEX expressions
> - VIEWs and MATERIALIZED VIEWs
> - FUNCTION and STORED PROCEDURE bodies
> - ...
>
> CREATE SCHEMA IF NOT EXISTS my_schema;
>
> SET SCHEMA my_schema DEFAULT search_path = my_schema, my_other_schema;
>
> CREATE OR REPLACE FUNCTION foo() ... AS $$ ... $$;

That's interesting. I suspect we'd first want to make inlining possible.