Horton

Re: Converting contrib SQL functions to new style

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Noah Misch <noah@leadboat.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Date: 2021-04-14T14:49:37Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 14, 2021 at 8:58 AM Noah Misch <noah@leadboat.com> wrote:
>> 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.

> I don't understand how that can happen, unless we've failed to secure
> the search_path. And, if we've failed to secure the search_path, I
> think we are in a lot of trouble no matter what else we do.

The situation of interest is where you are trying to install an extension
into a schema that also contains malicious objects.  We've managed to make
most of the commands you might use in an extension script secure against
that situation, and Noah wants to hold SQL-function creation to that same
standard.

My concern in this patch is rendering SQL functions safe against untrusted
search_path at *time of use*, which is really an independent security
concern.

If you're willing to assume there's nothing untrustworthy in your
search_path, then there's no issue and nothing to fix.  Unfortunately,
that seems like a rather head-in-the-sand standpoint.

			regards, tom lane

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.