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>
Subject: Re: Converting contrib SQL functions to new style
In-reply-to: 
 <CA+TgmoaF_DKSPRnbkakkOufWMFpOBsA8fwE-mF0fF7aeH20zqg@mail.gmail.com>
References: <3395418.1618352794@sss.pgh.pa.us>
 <20210414020827.GA1232417@rfd.leadboat.com>
 <3441172.1618369873@sss.pgh.pa.us>
 <20210414125811.GB1263822@rfd.leadboat.com>
 <CA+TgmoaF_DKSPRnbkakkOufWMFpOBsA8fwE-mF0fF7aeH20zqg@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Wed, 14 Apr 2021 09:55:56 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3489826.1618411777.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 14 Apr 2021 10:49:37 -0400
Message-ID: <3489827.1618411777@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3489827.1618411777%40sss.pgh.pa.us>
Precedence: bulk

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 hazardo=
us.
>> Today, typical SQL commands in extension creation scripts don't activat=
e
>> inexact argument type matching.  You were careful to make each script c=
lear
>> 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