Horton

Re: Converting contrib SQL functions to new style

Noah Misch <noah@leadboat.com>

View thread View on postgr.es
From: Noah Misch <noah@leadboat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Date: 2021-04-14T12:58:11Z
Lists: pgsql-hackers 
On Tue, Apr 13, 2021 at 11:11:13PM -0400, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > On Tue, Apr 13, 2021 at 06:26:34PM -0400, Tom Lane wrote:
> >> Attached are some draft patches to convert almost all of the
> >> contrib modules' SQL functions to use SQL-standard function bodies.
> >> The point of this is to remove the residual search_path security
> >> hazards that we couldn't fix in commits 7eeb1d986 et al.  Since
> >> a SQL-style function body is fully parsed at creation time,
> >> its object references are not subject to capture by the run-time
> >> search path.
> 
> > Are there any inexact matches in those function/operator calls?  Will that
> > matter more or less than it does today?
> 
> I can't claim to have looked closely for inexact matches.  It should
> matter less than today, since there's a hazard only during creation
> (with a somewhat-controlled search path) and not during use.  But
> that doesn't automatically eliminate the issue.

Once CREATE EXTENSION is over, things are a great deal safer under this
proposal, as you say.  I suspect it makes CREATE EXTENSION more hazardous.
Today, typical SQL commands in extension creation scripts don't activate
inexact argument type matching.  You were careful to make each script clear
the search_path around commands deviating from that (commit 7eeb1d9).  I think
"CREATE FUNCTION plus1dot1(int) RETURNS numeric LANGUAGE SQL RETURN $1 + 1.1;"
in a trusted extension script would constitute a security vulnerability, since
it can lock in the wrong operator.

Commits

  1. Use @extschema:name@ notation in contrib transform modules.

  2. pg_freespacemap: Fix declaration of pg_freespace(regclass)

  3. contrib/pageinspect: Use SQL-standard function bodies.

  4. contrib/xml2: Use SQL-standard function bodies.

  5. contrib/citext: Use SQL-standard function bodies.

  6. contrib/earthdistance: Use SQL-standard function bodies.

  7. contrib/lo: Use SQL-standard function bodies

  8. xml2: Add tests for functions xpath_nodeset() and xpath_list()

  9. contrib/lo: Add test for function lo_oid()

  10. pg_freespacemap: Use SQL-standard function bodies

  11. Add @extschema:name@ and no_relocate options to extensions.

  12. Make contrib modules' installation scripts more secure.