Re: Converting contrib SQL functions to new style - Mailing list pgsql-hackers

I happened to notice that there's more that we can do to harden
contrib modules: the transform modules for hstore and ltree
currently have disclaimers about having to install them in the
same schema as the underlying modules.  AFAICS that can be
fixed trivially now, by using the @extschema:name@ mechanism.

In the attached I just modified the 1.0 installation scripts
rather than making update scripts.  As for the problem with
earthdistance's CREATE DOMAIN command, there's no way for
an update script to rescue an already-trojaned definition,
and the script change does not affect the results in any
non-compromised case.

(The transform modules for bool and jsonb don't need to be
touched, since those data types are in pg_catalog which
will always be the front of the search path.)

I also noticed some text in extend.sgml that needs to be
updated.

            regards, tom lane

diff --git a/contrib/hstore_plperl/hstore_plperl--1.0.sql b/contrib/hstore_plperl/hstore_plperl--1.0.sql
index af743c8733..2837f3719f 100644
--- a/contrib/hstore_plperl/hstore_plperl--1.0.sql
+++ b/contrib/hstore_plperl/hstore_plperl--1.0.sql
@@ -7,11 +7,11 @@ CREATE FUNCTION hstore_to_plperl(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME';

-CREATE FUNCTION plperl_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plperl_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME';

-CREATE TRANSFORM FOR hstore LANGUAGE plperl (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plperl (
     FROM SQL WITH FUNCTION hstore_to_plperl(internal),
     TO SQL WITH FUNCTION plperl_to_hstore(internal)
 );
diff --git a/contrib/hstore_plperl/hstore_plperlu--1.0.sql b/contrib/hstore_plperl/hstore_plperlu--1.0.sql
index 7c3bc86eba..7f3119a7b2 100644
--- a/contrib/hstore_plperl/hstore_plperlu--1.0.sql
+++ b/contrib/hstore_plperl/hstore_plperlu--1.0.sql
@@ -7,11 +7,11 @@ CREATE FUNCTION hstore_to_plperlu(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'hstore_to_plperl';

-CREATE FUNCTION plperlu_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plperlu_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'plperl_to_hstore';

-CREATE TRANSFORM FOR hstore LANGUAGE plperlu (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plperlu (
     FROM SQL WITH FUNCTION hstore_to_plperlu(internal),
     TO SQL WITH FUNCTION plperlu_to_hstore(internal)
 );
diff --git a/contrib/hstore_plpython/hstore_plpython3u--1.0.sql b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
index 0b410ab183..3508232241 100644
--- a/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
+++ b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
@@ -7,13 +7,13 @@ CREATE FUNCTION hstore_to_plpython3(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'hstore_to_plpython';

-CREATE FUNCTION plpython3_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plpython3_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'plpython_to_hstore';

-CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plpython3u (
     FROM SQL WITH FUNCTION hstore_to_plpython3(internal),
     TO SQL WITH FUNCTION plpython3_to_hstore(internal)
 );

-COMMENT ON TRANSFORM FOR hstore LANGUAGE plpython3u IS 'transform between hstore and Python dict';
+COMMENT ON TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plpython3u IS 'transform between hstore and Python dict';
diff --git a/contrib/ltree_plpython/ltree_plpython3u--1.0.sql b/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
index 09ada3c7e8..14f73518d6 100644
--- a/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
+++ b/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
@@ -7,6 +7,6 @@ CREATE FUNCTION ltree_to_plpython3(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'ltree_to_plpython';

-CREATE TRANSFORM FOR ltree LANGUAGE plpython3u (
+CREATE TRANSFORM FOR @extschema:ltree@.ltree LANGUAGE plpython3u (
     FROM SQL WITH FUNCTION ltree_to_plpython3(internal)
 );
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 218940ee5c..ba492ca27c 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -1348,15 +1348,11 @@ SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</replaceabl
      </para>

      <para>
-      Cross-extension references are extremely difficult to make fully
-      secure, partially because of uncertainty about which schema the other
-      extension is in.  The hazards are reduced if both extensions are
-      installed in the same schema, because then a hostile object cannot be
-      placed ahead of the referenced extension in the installation-time
-      <varname>search_path</varname>.  However, no mechanism currently exists
-      to require that.  For now, best practice is to not mark an extension
-      trusted if it depends on another one, unless that other one is always
-      installed in <literal>pg_catalog</literal>.
+      Secure cross-extension references typically require schema-qualification
+      of the names of the other extension's objects, using the
+      <literal>@extschema:<replaceable>name</replaceable>@</literal>
+      syntax, in addition to careful matching of argument types for functions
+      and operators.
      </para>
     </sect3>
    </sect2>
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index 7d93e49e91..44325e0bba 100644
--- a/doc/src/sgml/hstore.sgml
+++ b/doc/src/sgml/hstore.sgml
@@ -946,15 +946,6 @@ ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
    extension for PL/Python is called <literal>hstore_plpython3u</literal>.
    If you use it, <type>hstore</type> values are mapped to Python dictionaries.
   </para>
-
-  <caution>
-   <para>
-    It is strongly recommended that the transform extensions be installed in
-    the same schema as <filename>hstore</filename>.  Otherwise there are
-    installation-time security hazards if a transform extension's schema
-    contains objects defined by a hostile user.
-   </para>
-  </caution>
  </sect2>

  <sect2 id="hstore-authors">
diff --git a/doc/src/sgml/ltree.sgml b/doc/src/sgml/ltree.sgml
index 9584105b03..1c3543303f 100644
--- a/doc/src/sgml/ltree.sgml
+++ b/doc/src/sgml/ltree.sgml
@@ -841,15 +841,6 @@ ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.
    creating a function, <type>ltree</type> values are mapped to Python lists.
    (The reverse is currently not supported, however.)
   </para>
-
-  <caution>
-   <para>
-    It is strongly recommended that the transform extension be installed in
-    the same schema as <filename>ltree</filename>.  Otherwise there are
-    installation-time security hazards if a transform extension's schema
-    contains objects defined by a hostile user.
-   </para>
-  </caution>
  </sect2>

  <sect2 id="ltree-authors">