Facility for detecting insecure object naming - Mailing list pgsql-hackers

Commit e09144e documented the rules for writing safe qualified names, but
those rules are tedious to apply in practice.  Spotting the defects in this
function definition (from an unpublished draft intended for
https://postgr.es/m/20180710014308.GA805781@rfd.leadboat.com) is, I think, too
hard:

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

If hackers and non-core extension authors are to write such code, let's make
it easier to check the work.  Different classes of code need different checks.
In each class, qualified function and operator names referring to untrusted
schemas need an exact match of function parameters, including any VARIADIC.
Class-specific rules:

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.

I believe PostgreSQL can apply each class's rules given a list of trusted
schemas and a flag to enable the checks.  Class (b) naturally degenerates to
class (a) if every schema of search_path appears in the whitelist.  To check
class-(c) code, issue "SET search_path = not_in_whitelist, pg_temp,
pg_catalog, ..."  before the test queries.  That's something of a hack, but I
didn't think of a non-hack that I liked better.

Should this feature warn about "SELECT 'earth()'::pg_catalog.regprocedure"
under the conditions that would make it warn about "SELECT earth()"?  Should
it offer the option to warn or not warn?  Some uses of reg*,
e.g. appendQualifiedRelation(), would consider those to be false positives.

Then there's the question of exact UI naming.  Some possibilities:

  SET lint_trusted_schemas = pg_catalog, admin
  SET lint = reg*, exact_match, qualified_name
  SET lint = all
  SET lint = ''

  SET lint_trusted_schemas = pg_catalog, admin
  SET lint_name_security = on

  SET name_security_warning_trusted_schemas = pg_catalog, admin
  SET name_security_warning = on

  SET name_security_warnings_trusted_schemas = pg_catalog, admin
  SET warnings = reg*, exact_match, qualified_name

Preferences, other ideas?