summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/extend.sgml
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/extend.sgml')
-rw-r--r--doc/src/sgml/extend.sgml1901
1 files changed, 1901 insertions, 0 deletions
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
new file mode 100644
index 0000000..218940e
--- /dev/null
+++ b/doc/src/sgml/extend.sgml
@@ -0,0 +1,1901 @@
+<!-- doc/src/sgml/extend.sgml -->
+
+ <chapter id="extend">
+ <title>Extending <acronym>SQL</acronym></title>
+
+ <indexterm zone="extend">
+ <primary>extending SQL</primary>
+ </indexterm>
+
+ <para>
+ In the sections that follow, we will discuss how you
+ can extend the <productname>PostgreSQL</productname>
+ <acronym>SQL</acronym> query language by adding:
+
+ <itemizedlist spacing="compact" mark="bullet">
+ <listitem>
+ <para>
+ functions (starting in <xref linkend="xfunc"/>)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ aggregates (starting in <xref linkend="xaggr"/>)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ data types (starting in <xref linkend="xtypes"/>)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ operators (starting in <xref linkend="xoper"/>)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ operator classes for indexes (starting in <xref linkend="xindex"/>)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ packages of related objects (starting in <xref linkend="extend-extensions"/>)
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <sect1 id="extend-how">
+ <title>How Extensibility Works</title>
+
+ <para>
+ <productname>PostgreSQL</productname> is extensible because its operation is
+ catalog-driven. If you are familiar with standard
+ relational database systems, you know that they store information
+ about databases, tables, columns, etc., in what are
+ commonly known as system catalogs. (Some systems call
+ this the data dictionary.) The catalogs appear to the
+ user as tables like any other, but the <acronym>DBMS</acronym> stores
+ its internal bookkeeping in them. One key difference
+ between <productname>PostgreSQL</productname> and standard relational database systems is
+ that <productname>PostgreSQL</productname> stores much more information in its
+ catalogs: not only information about tables and columns,
+ but also information about data types, functions, access
+ methods, and so on. These tables can be modified by
+ the user, and since <productname>PostgreSQL</productname> bases its operation
+ on these tables, this means that <productname>PostgreSQL</productname> can be
+ extended by users. By comparison, conventional
+ database systems can only be extended by changing hardcoded
+ procedures in the source code or by loading modules
+ specially written by the <acronym>DBMS</acronym> vendor.
+ </para>
+
+ <para>
+ The <productname>PostgreSQL</productname> server can moreover
+ incorporate user-written code into itself through dynamic loading.
+ That is, the user can specify an object code file (e.g., a shared
+ library) that implements a new type or function, and
+ <productname>PostgreSQL</productname> will load it as required.
+ Code written in <acronym>SQL</acronym> is even more trivial to add
+ to the server. This ability to modify its operation <quote>on the
+ fly</quote> makes <productname>PostgreSQL</productname> uniquely
+ suited for rapid prototyping of new applications and storage
+ structures.
+ </para>
+ </sect1>
+
+ <sect1 id="extend-type-system">
+ <title>The <productname>PostgreSQL</productname> Type System</title>
+
+ <indexterm zone="extend-type-system">
+ <primary>base type</primary>
+ </indexterm>
+
+ <indexterm zone="extend-type-system">
+ <primary>data type</primary>
+ <secondary>base</secondary>
+ </indexterm>
+
+ <indexterm zone="extend-type-system">
+ <primary>composite type</primary>
+ </indexterm>
+
+ <indexterm zone="extend-type-system">
+ <primary>data type</primary>
+ <secondary>composite</secondary>
+ </indexterm>
+
+ <indexterm zone="extend-type-system">
+ <primary>container type</primary>
+ </indexterm>
+
+ <indexterm zone="extend-type-system">
+ <primary>data type</primary>
+ <secondary>container</secondary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> data types can be divided into base
+ types, container types, domains, and pseudo-types.
+ </para>
+
+ <sect2 id="extend-type-system-base">
+ <title>Base Types</title>
+
+ <para>
+ Base types are those, like <type>integer</type>, that are
+ implemented below the level of the <acronym>SQL</acronym> language
+ (typically in a low-level language such as C). They generally
+ correspond to what are often known as abstract data types.
+ <productname>PostgreSQL</productname> can only operate on such
+ types through functions provided by the user and only understands
+ the behavior of such types to the extent that the user describes
+ them.
+ The built-in base types are described in <xref linkend="datatype"/>.
+ </para>
+
+ <para>
+ Enumerated (enum) types can be considered as a subcategory of base
+ types. The main difference is that they can be created using
+ just <acronym>SQL</acronym> commands, without any low-level programming.
+ Refer to <xref linkend="datatype-enum"/> for more information.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-type-system-container">
+ <title>Container Types</title>
+
+ <para>
+ <productname>PostgreSQL</productname> has three kinds
+ of <quote>container</quote> types, which are types that contain multiple
+ values of other types. These are arrays, composites, and ranges.
+ </para>
+
+ <para>
+ Arrays can hold multiple values that are all of the same type. An array
+ type is automatically created for each base type, composite type, range
+ type, and domain type. But there are no arrays of arrays. So far as
+ the type system is concerned, multi-dimensional arrays are the same as
+ one-dimensional arrays. Refer to <xref linkend="arrays"/> for more
+ information.
+ </para>
+
+ <para>
+ Composite types, or row types, are created whenever the user
+ creates a table. It is also possible to use <xref
+ linkend="sql-createtype"/> to
+ define a <quote>stand-alone</quote> composite type with no associated
+ table. A composite type is simply a list of types with
+ associated field names. A value of a composite type is a row or
+ record of field values. Refer to <xref linkend="rowtypes"/>
+ for more information.
+ </para>
+
+ <para>
+ A range type can hold two values of the same type, which are the lower
+ and upper bounds of the range. Range types are user-created, although
+ a few built-in ones exist. Refer to <xref linkend="rangetypes"/>
+ for more information.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-type-system-domains">
+ <title>Domains</title>
+
+ <para>
+ A domain is based on a particular underlying type and for many purposes
+ is interchangeable with its underlying type. However, a domain can have
+ constraints that restrict its valid values to a subset of what the
+ underlying type would allow. Domains are created using
+ the <acronym>SQL</acronym> command <xref linkend="sql-createdomain"/>.
+ Refer to <xref linkend="domains"/> for more information.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-type-system-pseudo">
+ <title>Pseudo-Types</title>
+
+ <para>
+ There are a few <quote>pseudo-types</quote> for special purposes.
+ Pseudo-types cannot appear as columns of tables or components of
+ container types, but they can be used to declare the argument and
+ result types of functions. This provides a mechanism within the
+ type system to identify special classes of functions. <xref
+ linkend="datatype-pseudotypes-table"/> lists the existing
+ pseudo-types.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-types-polymorphic">
+ <title>Polymorphic Types</title>
+
+ <indexterm zone="extend-types-polymorphic">
+ <primary>polymorphic type</primary>
+ </indexterm>
+
+ <indexterm zone="extend-types-polymorphic">
+ <primary>polymorphic function</primary>
+ </indexterm>
+
+ <indexterm zone="extend-types-polymorphic">
+ <primary>data type</primary>
+ <secondary>polymorphic</secondary>
+ </indexterm>
+
+ <indexterm zone="extend-types-polymorphic">
+ <primary>function</primary>
+ <secondary>polymorphic</secondary>
+ </indexterm>
+
+ <para>
+ Some pseudo-types of special interest are the <firstterm>polymorphic
+ types</firstterm>, which are used to declare <firstterm>polymorphic
+ functions</firstterm>. This powerful feature allows a single function
+ definition to operate on many different data types, with the specific
+ data type(s) being determined by the data types actually passed to it
+ in a particular call. The polymorphic types are shown in
+ <xref linkend="extend-types-polymorphic-table"/>. Some examples of
+ their use appear in <xref linkend="xfunc-sql-polymorphic-functions"/>.
+ </para>
+
+ <table id="extend-types-polymorphic-table">
+ <title>Polymorphic Types</title>
+ <tgroup cols="3">
+ <colspec colname="col1" colwidth="2*"/>
+ <colspec colname="col2" colwidth="1*"/>
+ <colspec colname="col3" colwidth="2*"/>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Family</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><type>anyelement</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any data type</entry>
+ </row>
+
+ <row>
+ <entry><type>anyarray</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any array data type</entry>
+ </row>
+
+ <row>
+ <entry><type>anynonarray</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any non-array data type</entry>
+ </row>
+
+ <row>
+ <entry><type>anyenum</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any enum data type
+ (see <xref linkend="datatype-enum"/>)
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anyrange</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any range data type
+ (see <xref linkend="rangetypes"/>)
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anymultirange</type></entry>
+ <entry>Simple</entry>
+ <entry>Indicates that a function accepts any multirange data type
+ (see <xref linkend="rangetypes"/>)
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anycompatible</type></entry>
+ <entry>Common</entry>
+ <entry>Indicates that a function accepts any data type,
+ with automatic promotion of multiple arguments to a common data type
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anycompatiblearray</type></entry>
+ <entry>Common</entry>
+ <entry>Indicates that a function accepts any array data type,
+ with automatic promotion of multiple arguments to a common data type
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anycompatiblenonarray</type></entry>
+ <entry>Common</entry>
+ <entry>Indicates that a function accepts any non-array data type,
+ with automatic promotion of multiple arguments to a common data type
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anycompatiblerange</type></entry>
+ <entry>Common</entry>
+ <entry>Indicates that a function accepts any range data type,
+ with automatic promotion of multiple arguments to a common data type
+ </entry>
+ </row>
+
+ <row>
+ <entry><type>anycompatiblemultirange</type></entry>
+ <entry>Common</entry>
+ <entry>Indicates that a function accepts any multirange data type,
+ with automatic promotion of multiple arguments to a common data type
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Polymorphic arguments and results are tied to each other and are resolved
+ to specific data types when a query calling a polymorphic function is
+ parsed. When there is more than one polymorphic argument, the actual
+ data types of the input values must match up as described below. If the
+ function's result type is polymorphic, or it has output parameters of
+ polymorphic types, the types of those results are deduced from the
+ actual types of the polymorphic inputs as described below.
+ </para>
+
+ <para>
+ For the <quote>simple</quote> family of polymorphic types, the
+ matching and deduction rules work like this:
+ </para>
+
+ <para>
+ Each position (either argument or return value) declared as
+ <type>anyelement</type> is allowed to have any specific actual
+ data type, but in any given call they must all be the
+ <emphasis>same</emphasis> actual type. Each
+ position declared as <type>anyarray</type> can have any array data type,
+ but similarly they must all be the same type. And similarly,
+ positions declared as <type>anyrange</type> must all be the same range
+ type. Likewise for <type>anymultirange</type>.
+ </para>
+
+ <para>
+ Furthermore, if there are
+ positions declared <type>anyarray</type> and others declared
+ <type>anyelement</type>, the actual array type in the
+ <type>anyarray</type> positions must be an array whose elements are
+ the same type appearing in the <type>anyelement</type> positions.
+ <type>anynonarray</type> is treated exactly the same as <type>anyelement</type>,
+ but adds the additional constraint that the actual type must not be
+ an array type.
+ <type>anyenum</type> is treated exactly the same as <type>anyelement</type>,
+ but adds the additional constraint that the actual type must
+ be an enum type.
+ </para>
+
+ <para>
+ Similarly, if there are positions declared <type>anyrange</type>
+ and others declared <type>anyelement</type> or <type>anyarray</type>,
+ the actual range type in the <type>anyrange</type> positions must be a
+ range whose subtype is the same type appearing in
+ the <type>anyelement</type> positions and the same as the element type
+ of the <type>anyarray</type> positions.
+ If there are positions declared <type>anymultirange</type>,
+ their actual multirange type must contain ranges matching parameters declared
+ <type>anyrange</type> and base elements matching parameters declared
+ <type>anyelement</type> and <type>anyarray</type>.
+ </para>
+
+ <para>
+ Thus, when more than one argument position is declared with a polymorphic
+ type, the net effect is that only certain combinations of actual argument
+ types are allowed. For example, a function declared as
+ <literal>equal(anyelement, anyelement)</literal> will take any two input values,
+ so long as they are of the same data type.
+ </para>
+
+ <para>
+ When the return value of a function is declared as a polymorphic type,
+ there must be at least one argument position that is also polymorphic,
+ and the actual data type(s) supplied for the polymorphic arguments
+ determine the actual
+ result type for that call. For example, if there were not already
+ an array subscripting mechanism, one could define a function that
+ implements subscripting as <literal>subscript(anyarray, integer)
+ returns anyelement</literal>. This declaration constrains the actual first
+ argument to be an array type, and allows the parser to infer the correct
+ result type from the actual first argument's type. Another example
+ is that a function declared as <literal>f(anyarray) returns anyenum</literal>
+ will only accept arrays of enum types.
+ </para>
+
+ <para>
+ In most cases, the parser can infer the actual data type for a
+ polymorphic result type from arguments that are of a different
+ polymorphic type in the same family; for example <type>anyarray</type>
+ can be deduced from <type>anyelement</type> or vice versa.
+ An exception is that a
+ polymorphic result of type <type>anyrange</type> requires an argument
+ of type <type>anyrange</type>; it cannot be deduced
+ from <type>anyarray</type> or <type>anyelement</type> arguments. This
+ is because there could be multiple range types with the same subtype.
+ </para>
+
+ <para>
+ Note that <type>anynonarray</type> and <type>anyenum</type> do not represent
+ separate type variables; they are the same type as
+ <type>anyelement</type>, just with an additional constraint. For
+ example, declaring a function as <literal>f(anyelement, anyenum)</literal>
+ is equivalent to declaring it as <literal>f(anyenum, anyenum)</literal>:
+ both actual arguments have to be the same enum type.
+ </para>
+
+ <para>
+ For the <quote>common</quote> family of polymorphic types, the
+ matching and deduction rules work approximately the same as for
+ the <quote>simple</quote> family, with one major difference: the
+ actual types of the arguments need not be identical, so long as they
+ can be implicitly cast to a single common type. The common type is
+ selected following the same rules as for <literal>UNION</literal> and
+ related constructs (see <xref linkend="typeconv-union-case"/>).
+ Selection of the common type considers the actual types
+ of <type>anycompatible</type> and <type>anycompatiblenonarray</type>
+ inputs, the array element types of <type>anycompatiblearray</type>
+ inputs, the range subtypes of <type>anycompatiblerange</type> inputs,
+ and the multirange subtypes of <type>anycompatiblemultirange</type>
+ inputs. If <type>anycompatiblenonarray</type> is present then the
+ common type is required to be a non-array type. Once a common type is
+ identified, arguments in <type>anycompatible</type>
+ and <type>anycompatiblenonarray</type> positions are automatically
+ cast to that type, and arguments in <type>anycompatiblearray</type>
+ positions are automatically cast to the array type for that type.
+ </para>
+
+ <para>
+ Since there is no way to select a range type knowing only its subtype,
+ use of <type>anycompatiblerange</type> and/or
+ <type>anycompatiblemultirange</type> requires that all arguments declared
+ with that type have the same actual range and/or multirange type, and that
+ that type's subtype agree with the selected common type, so that no casting
+ of the range values is required. As with <type>anyrange</type> and
+ <type>anymultirange</type>, use of <type>anycompatiblerange</type> and
+ <type>anymultirange</type> as a function result type requires that there be
+ an <type>anycompatiblerange</type> or <type>anycompatiblemultirange</type>
+ argument.
+ </para>
+
+ <para>
+ Notice that there is no <type>anycompatibleenum</type> type. Such a
+ type would not be very useful, since there normally are not any
+ implicit casts to enum types, meaning that there would be no way to
+ resolve a common type for dissimilar enum inputs.
+ </para>
+
+ <para>
+ The <quote>simple</quote> and <quote>common</quote> polymorphic
+ families represent two independent sets of type variables. Consider
+ for example
+<programlisting>
+CREATE FUNCTION myfunc(a anyelement, b anyelement,
+ c anycompatible, d anycompatible)
+RETURNS anycompatible AS ...
+</programlisting>
+ In an actual call of this function, the first two inputs must have
+ exactly the same type. The last two inputs must be promotable to a
+ common type, but this type need not have anything to do with the type
+ of the first two inputs. The result will have the common type of the
+ last two inputs.
+ </para>
+
+ <para>
+ A variadic function (one taking a variable number of arguments, as in
+ <xref linkend="xfunc-sql-variadic-functions"/>) can be
+ polymorphic: this is accomplished by declaring its last parameter as
+ <literal>VARIADIC</literal> <type>anyarray</type> or
+ <literal>VARIADIC</literal> <type>anycompatiblearray</type>.
+ For purposes of argument
+ matching and determining the actual result type, such a function behaves
+ the same as if you had written the appropriate number of
+ <type>anynonarray</type> or <type>anycompatiblenonarray</type>
+ parameters.
+ </para>
+ </sect2>
+ </sect1>
+
+ &xfunc;
+ &xaggr;
+ &xtypes;
+ &xoper;
+ &xindex;
+
+
+ <sect1 id="extend-extensions">
+ <title>Packaging Related Objects into an Extension</title>
+
+ <indexterm zone="extend-extensions">
+ <primary>extension</primary>
+ </indexterm>
+
+ <para>
+ A useful extension to <productname>PostgreSQL</productname> typically includes
+ multiple SQL objects; for example, a new data type will require new
+ functions, new operators, and probably new index operator classes.
+ It is helpful to collect all these objects into a single package
+ to simplify database management. <productname>PostgreSQL</productname> calls
+ such a package an <firstterm>extension</firstterm>. To define an extension,
+ you need at least a <firstterm>script file</firstterm> that contains the
+ <acronym>SQL</acronym> commands to create the extension's objects, and a
+ <firstterm>control file</firstterm> that specifies a few basic properties
+ of the extension itself. If the extension includes C code, there
+ will typically also be a shared library file into which the C code
+ has been built. Once you have these files, a simple
+ <link linkend="sql-createextension"><command>CREATE EXTENSION</command></link> command loads the objects into
+ your database.
+ </para>
+
+ <para>
+ The main advantage of using an extension, rather than just running the
+ <acronym>SQL</acronym> script to load a bunch of <quote>loose</quote> objects
+ into your database, is that <productname>PostgreSQL</productname> will then
+ understand that the objects of the extension go together. You can
+ drop all the objects with a single <link linkend="sql-dropextension"><command>DROP EXTENSION</command></link>
+ command (no need to maintain a separate <quote>uninstall</quote> script).
+ Even more useful, <application>pg_dump</application> knows that it should not
+ dump the individual member objects of the extension &mdash; it will
+ just include a <command>CREATE EXTENSION</command> command in dumps, instead.
+ This vastly simplifies migration to a new version of the extension
+ that might contain more or different objects than the old version.
+ Note however that you must have the extension's control, script, and
+ other files available when loading such a dump into a new database.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> will not let you drop an individual object
+ contained in an extension, except by dropping the whole extension.
+ Also, while you can change the definition of an extension member object
+ (for example, via <command>CREATE OR REPLACE FUNCTION</command> for a
+ function), bear in mind that the modified definition will not be dumped
+ by <application>pg_dump</application>. Such a change is usually only sensible if
+ you concurrently make the same change in the extension's script file.
+ (But there are special provisions for tables containing configuration
+ data; see <xref linkend="extend-extensions-config-tables"/>.)
+ In production situations, it's generally better to create an extension
+ update script to perform changes to extension member objects.
+ </para>
+
+ <para>
+ The extension script may set privileges on objects that are part of the
+ extension, using <command>GRANT</command> and <command>REVOKE</command>
+ statements. The final set of privileges for each object (if any are set)
+ will be stored in the
+ <link linkend="catalog-pg-init-privs"><structname>pg_init_privs</structname></link>
+ system catalog. When <application>pg_dump</application> is used, the
+ <command>CREATE EXTENSION</command> command will be included in the dump, followed
+ by the set of <command>GRANT</command> and <command>REVOKE</command>
+ statements necessary to set the privileges on the objects to what they were
+ at the time the dump was taken.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> does not currently support extension scripts
+ issuing <command>CREATE POLICY</command> or <command>SECURITY LABEL</command>
+ statements. These are expected to be set after the extension has been
+ created. All RLS policies and security labels on extension objects will be
+ included in dumps created by <application>pg_dump</application>.
+ </para>
+
+ <para>
+ The extension mechanism also has provisions for packaging modification
+ scripts that adjust the definitions of the SQL objects contained in an
+ extension. For example, if version 1.1 of an extension adds one function
+ and changes the body of another function compared to 1.0, the extension
+ author can provide an <firstterm>update script</firstterm> that makes just those
+ two changes. The <command>ALTER EXTENSION UPDATE</command> command can then
+ be used to apply these changes and track which version of the extension
+ is actually installed in a given database.
+ </para>
+
+ <para>
+ The kinds of SQL objects that can be members of an extension are shown in
+ the description of <link linkend="sql-alterextension"><command>ALTER EXTENSION</command></link>. Notably, objects
+ that are database-cluster-wide, such as databases, roles, and tablespaces,
+ cannot be extension members since an extension is only known within one
+ database. (Although an extension script is not prohibited from creating
+ such objects, if it does so they will not be tracked as part of the
+ extension.) Also notice that while a table can be a member of an
+ extension, its subsidiary objects such as indexes are not directly
+ considered members of the extension.
+ Another important point is that schemas can belong to extensions, but not
+ vice versa: an extension as such has an unqualified name and does not
+ exist <quote>within</quote> any schema. The extension's member objects,
+ however, will belong to schemas whenever appropriate for their object
+ types. It may or may not be appropriate for an extension to own the
+ schema(s) its member objects are within.
+ </para>
+
+ <para>
+ If an extension's script creates any temporary objects (such as temp
+ tables), those objects are treated as extension members for the
+ remainder of the current session, but are automatically dropped at
+ session end, as any temporary object would be. This is an exception
+ to the rule that extension member objects cannot be dropped without
+ dropping the whole extension.
+ </para>
+
+ <sect2 id="extend-extensions-files">
+ <title>Extension Files</title>
+
+ <indexterm>
+ <primary>control file</primary>
+ </indexterm>
+
+ <para>
+ The <command>CREATE EXTENSION</command> command relies on a control
+ file for each extension, which must be named the same as the extension
+ with a suffix of <literal>.control</literal>, and must be placed in the
+ installation's <literal>SHAREDIR/extension</literal> directory. There
+ must also be at least one <acronym>SQL</acronym> script file, which follows the
+ naming pattern
+ <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.sql</literal>
+ (for example, <literal>foo--1.0.sql</literal> for version <literal>1.0</literal> of
+ extension <literal>foo</literal>). By default, the script file(s) are also
+ placed in the <literal>SHAREDIR/extension</literal> directory; but the
+ control file can specify a different directory for the script file(s).
+ </para>
+
+ <para>
+ The file format for an extension control file is the same as for the
+ <filename>postgresql.conf</filename> file, namely a list of
+ <replaceable>parameter_name</replaceable> <literal>=</literal> <replaceable>value</replaceable>
+ assignments, one per line. Blank lines and comments introduced by
+ <literal>#</literal> are allowed. Be sure to quote any value that is not
+ a single word or number.
+ </para>
+
+ <para>
+ A control file can set the following parameters:
+ </para>
+
+ <variablelist>
+ <varlistentry id="extend-extensions-files-directory">
+ <term><varname>directory</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ The directory containing the extension's <acronym>SQL</acronym> script
+ file(s). Unless an absolute path is given, the name is relative to
+ the installation's <literal>SHAREDIR</literal> directory. The
+ default behavior is equivalent to specifying
+ <literal>directory = 'extension'</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-default-version">
+ <term><varname>default_version</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ The default version of the extension (the one that will be installed
+ if no version is specified in <command>CREATE EXTENSION</command>). Although
+ this can be omitted, that will result in <command>CREATE EXTENSION</command>
+ failing if no <literal>VERSION</literal> option appears, so you generally
+ don't want to do that.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-comment">
+ <term><varname>comment</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ A comment (any string) about the extension. The comment is applied
+ when initially creating an extension, but not during extension updates
+ (since that might override user-added comments). Alternatively,
+ the extension's comment can be set by writing
+ a <xref linkend="sql-comment"/> command in the script file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-encoding">
+ <term><varname>encoding</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ The character set encoding used by the script file(s). This should
+ be specified if the script files contain any non-ASCII characters.
+ Otherwise the files will be assumed to be in the database encoding.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-module-pathname">
+ <term><varname>module_pathname</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ The value of this parameter will be substituted for each occurrence
+ of <literal>MODULE_PATHNAME</literal> in the script file(s). If it is not
+ set, no substitution is made. Typically, this is set to
+ <literal>$libdir/<replaceable>shared_library_name</replaceable></literal> and
+ then <literal>MODULE_PATHNAME</literal> is used in <command>CREATE
+ FUNCTION</command> commands for C-language functions, so that the script
+ files do not need to hard-wire the name of the shared library.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-requires">
+ <term><varname>requires</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ A list of names of extensions that this extension depends on,
+ for example <literal>requires = 'foo, bar'</literal>. Those
+ extensions must be installed before this one can be installed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-no-relocate">
+ <term><varname>no_relocate</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ A list of names of extensions that this extension depends on that
+ should be barred from changing their schemas via <command>ALTER
+ EXTENSION ... SET SCHEMA</command>.
+ This is needed if this extension's script references the name
+ of a required extension's schema (using
+ the <literal>@extschema:<replaceable>name</replaceable>@</literal>
+ syntax) in a way that cannot track renames.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-superuser">
+ <term><varname>superuser</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ If this parameter is <literal>true</literal> (which is the default),
+ only superusers can create the extension or update it to a new
+ version (but see also <varname>trusted</varname>, below).
+ If it is set to <literal>false</literal>, just the privileges
+ required to execute the commands in the installation or update script
+ are required.
+ This should normally be set to <literal>true</literal> if any of the
+ script commands require superuser privileges. (Such commands would
+ fail anyway, but it's more user-friendly to give the error up front.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-trusted">
+ <term><varname>trusted</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This parameter, if set to <literal>true</literal> (which is not the
+ default), allows some non-superusers to install an extension that
+ has <varname>superuser</varname> set to <literal>true</literal>.
+ Specifically, installation will be permitted for anyone who has
+ <literal>CREATE</literal> privilege on the current database.
+ When the user executing <command>CREATE EXTENSION</command> is not
+ a superuser but is allowed to install by virtue of this parameter,
+ then the installation or update script is run as the bootstrap
+ superuser, not as the calling user.
+ This parameter is irrelevant if <varname>superuser</varname> is
+ <literal>false</literal>.
+ Generally, this should not be set true for extensions that could
+ allow access to otherwise-superuser-only abilities, such as
+ file system access.
+ Also, marking an extension trusted requires significant extra effort
+ to write the extension's installation and update script(s) securely;
+ see <xref linkend="extend-extensions-security"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-relocatable">
+ <term><varname>relocatable</varname> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ An extension is <firstterm>relocatable</firstterm> if it is possible to move
+ its contained objects into a different schema after initial creation
+ of the extension. The default is <literal>false</literal>, i.e., the
+ extension is not relocatable.
+ See <xref linkend="extend-extensions-relocation"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-extensions-files-schema">
+ <term><varname>schema</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ This parameter can only be set for non-relocatable extensions.
+ It forces the extension to be loaded into exactly the named schema
+ and not any other.
+ The <varname>schema</varname> parameter is consulted only when
+ initially creating an extension, not during extension updates.
+ See <xref linkend="extend-extensions-relocation"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ In addition to the primary control file
+ <literal><replaceable>extension</replaceable>.control</literal>,
+ an extension can have secondary control files named in the style
+ <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.control</literal>.
+ If supplied, these must be located in the script file directory.
+ Secondary control files follow the same format as the primary control
+ file. Any parameters set in a secondary control file override the
+ primary control file when installing or updating to that version of
+ the extension. However, the parameters <varname>directory</varname> and
+ <varname>default_version</varname> cannot be set in a secondary control file.
+ </para>
+
+ <para>
+ An extension's <acronym>SQL</acronym> script files can contain any SQL commands,
+ except for transaction control commands (<command>BEGIN</command>,
+ <command>COMMIT</command>, etc.) and commands that cannot be executed inside a
+ transaction block (such as <command>VACUUM</command>). This is because the
+ script files are implicitly executed within a transaction block.
+ </para>
+
+ <para>
+ An extension's <acronym>SQL</acronym> script files can also contain lines
+ beginning with <literal>\echo</literal>, which will be ignored (treated as
+ comments) by the extension mechanism. This provision is commonly used
+ to throw an error if the script file is fed to <application>psql</application>
+ rather than being loaded via <command>CREATE EXTENSION</command> (see example
+ script in <xref linkend="extend-extensions-example"/>).
+ Without that, users might accidentally load the
+ extension's contents as <quote>loose</quote> objects rather than as an
+ extension, a state of affairs that's a bit tedious to recover from.
+ </para>
+
+ <para>
+ If the extension script contains the
+ string <literal>@extowner@</literal>, that string is replaced with the
+ (suitably quoted) name of the user calling <command>CREATE
+ EXTENSION</command> or <command>ALTER EXTENSION</command>. Typically
+ this feature is used by extensions that are marked trusted to assign
+ ownership of selected objects to the calling user rather than the
+ bootstrap superuser. (One should be careful about doing so, however.
+ For example, assigning ownership of a C-language function to a
+ non-superuser would create a privilege escalation path for that user.)
+ </para>
+
+ <para>
+ While the script files can contain any characters allowed by the specified
+ encoding, control files should contain only plain ASCII, because there
+ is no way for <productname>PostgreSQL</productname> to know what encoding a
+ control file is in. In practice this is only an issue if you want to
+ use non-ASCII characters in the extension's comment. Recommended
+ practice in that case is to not use the control file <varname>comment</varname>
+ parameter, but instead use <command>COMMENT ON EXTENSION</command>
+ within a script file to set the comment.
+ </para>
+
+ </sect2>
+
+ <sect2 id="extend-extensions-relocation">
+ <title>Extension Relocatability</title>
+
+ <para>
+ Users often wish to load the objects contained in an extension into a
+ different schema than the extension's author had in mind. There are
+ three supported levels of relocatability:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ A fully relocatable extension can be moved into another schema
+ at any time, even after it's been loaded into a database.
+ This is done with the <command>ALTER EXTENSION SET SCHEMA</command>
+ command, which automatically renames all the member objects into
+ the new schema. Normally, this is only possible if the extension
+ contains no internal assumptions about what schema any of its
+ objects are in. Also, the extension's objects must all be in one
+ schema to begin with (ignoring objects that do not belong to any
+ schema, such as procedural languages). Mark a fully relocatable
+ extension by setting <literal>relocatable = true</literal> in its control
+ file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ An extension might be relocatable during installation but not
+ afterwards. This is typically the case if the extension's script
+ file needs to reference the target schema explicitly, for example
+ in setting <literal>search_path</literal> properties for SQL functions.
+ For such an extension, set <literal>relocatable = false</literal> in its
+ control file, and use <literal>@extschema@</literal> to refer to the target
+ schema in the script file. All occurrences of this string will be
+ replaced by the actual target schema's name (double-quoted if
+ necessary) before the script is executed. The user can set the
+ target schema using the
+ <literal>SCHEMA</literal> option of <command>CREATE EXTENSION</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the extension does not support relocation at all, set
+ <literal>relocatable = false</literal> in its control file, and also set
+ <literal>schema</literal> to the name of the intended target schema. This
+ will prevent use of the <literal>SCHEMA</literal> option of <command>CREATE
+ EXTENSION</command>, unless it specifies the same schema named in the control
+ file. This choice is typically necessary if the extension contains
+ internal assumptions about its schema name that can't be replaced by
+ uses of <literal>@extschema@</literal>. The <literal>@extschema@</literal>
+ substitution mechanism is available in this case too, although it is
+ of limited use since the schema name is determined by the control file.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ In all cases, the script file will be executed with
+ <xref linkend="guc-search-path"/> initially set to point to the target
+ schema; that is, <command>CREATE EXTENSION</command> does the equivalent of
+ this:
+<programlisting>
+SET LOCAL search_path TO @extschema@, pg_temp;
+</programlisting>
+ This allows the objects created by the script file to go into the target
+ schema. The script file can change <varname>search_path</varname> if it wishes,
+ but that is generally undesirable. <varname>search_path</varname> is restored
+ to its previous setting upon completion of <command>CREATE EXTENSION</command>.
+ </para>
+
+ <para>
+ The target schema is determined by the <varname>schema</varname> parameter in
+ the control file if that is given, otherwise by the <literal>SCHEMA</literal>
+ option of <command>CREATE EXTENSION</command> if that is given, otherwise the
+ current default object creation schema (the first one in the caller's
+ <varname>search_path</varname>). When the control file <varname>schema</varname>
+ parameter is used, the target schema will be created if it doesn't
+ already exist, but in the other two cases it must already exist.
+ </para>
+
+ <para>
+ If any prerequisite extensions are listed in <varname>requires</varname>
+ in the control file, their target schemas are added to the initial
+ setting of <varname>search_path</varname>, following the new
+ extension's target schema. This allows their objects to be visible to
+ the new extension's script file.
+ </para>
+
+ <para>
+ For security, <literal>pg_temp</literal> is automatically appended to
+ the end of <varname>search_path</varname> in all cases.
+ </para>
+
+ <para>
+ Although a non-relocatable extension can contain objects spread across
+ multiple schemas, it is usually desirable to place all the objects meant
+ for external use into a single schema, which is considered the extension's
+ target schema. Such an arrangement works conveniently with the default
+ setting of <varname>search_path</varname> during creation of dependent
+ extensions.
+ </para>
+
+ <para>
+ If an extension references objects belonging to another extension,
+ it is recommended to schema-qualify those references. To do that,
+ write <literal>@extschema:<replaceable>name</replaceable>@</literal>
+ in the extension's script file, where <replaceable>name</replaceable>
+ is the name of the other extension (which must be listed in this
+ extension's <literal>requires</literal> list). This string will be
+ replaced by the name (double-quoted if necessary) of that extension's
+ target schema.
+ Although this notation avoids the need to make hard-wired assumptions
+ about schema names in the extension's script file, its use may embed
+ the other extension's schema name into the installed objects of this
+ extension. (Typically, that happens
+ when <literal>@extschema:<replaceable>name</replaceable>@</literal> is
+ used inside a string literal, such as a function body or
+ a <varname>search_path</varname> setting. In other cases, the object
+ reference is reduced to an OID during parsing and does not require
+ subsequent lookups.) If the other extension's schema name is so
+ embedded, you should prevent the other extension from being relocated
+ after yours is installed, by adding the name of the other extension to
+ this one's <literal>no_relocate</literal> list.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-extensions-config-tables">
+ <title>Extension Configuration Tables</title>
+
+ <para>
+ Some extensions include configuration tables, which contain data that
+ might be added or changed by the user after installation of the
+ extension. Ordinarily, if a table is part of an extension, neither
+ the table's definition nor its content will be dumped by
+ <application>pg_dump</application>. But that behavior is undesirable for a
+ configuration table; any data changes made by the user need to be
+ included in dumps, or the extension will behave differently after a dump
+ and restore.
+ </para>
+
+ <indexterm>
+ <primary>pg_extension_config_dump</primary>
+ </indexterm>
+
+ <para>
+ To solve this problem, an extension's script file can mark a table
+ or a sequence it has created as a configuration relation, which will
+ cause <application>pg_dump</application> to include the table's or the sequence's
+ contents (not its definition) in dumps. To do that, call the function
+ <function>pg_extension_config_dump(regclass, text)</function> after creating the
+ table or the sequence, for example
+<programlisting>
+CREATE TABLE my_config (key text, value text);
+CREATE SEQUENCE my_config_seq;
+
+SELECT pg_catalog.pg_extension_config_dump('my_config', '');
+SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');
+</programlisting>
+ Any number of tables or sequences can be marked this way. Sequences
+ associated with <type>serial</type> or <type>bigserial</type> columns can
+ be marked as well.
+ </para>
+
+ <para>
+ When the second argument of <function>pg_extension_config_dump</function> is
+ an empty string, the entire contents of the table are dumped by
+ <application>pg_dump</application>. This is usually only correct if the table
+ is initially empty as created by the extension script. If there is
+ a mixture of initial data and user-provided data in the table,
+ the second argument of <function>pg_extension_config_dump</function> provides
+ a <literal>WHERE</literal> condition that selects the data to be dumped.
+ For example, you might do
+<programlisting>
+CREATE TABLE my_config (key text, value text, standard_entry boolean);
+
+SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
+</programlisting>
+ and then make sure that <structfield>standard_entry</structfield> is true only
+ in the rows created by the extension's script.
+ </para>
+
+ <para>
+ For sequences, the second argument of <function>pg_extension_config_dump</function>
+ has no effect.
+ </para>
+
+ <para>
+ More complicated situations, such as initially-provided rows that might
+ be modified by users, can be handled by creating triggers on the
+ configuration table to ensure that modified rows are marked correctly.
+ </para>
+
+ <para>
+ You can alter the filter condition associated with a configuration table
+ by calling <function>pg_extension_config_dump</function> again. (This would
+ typically be useful in an extension update script.) The only way to mark
+ a table as no longer a configuration table is to dissociate it from the
+ extension with <command>ALTER EXTENSION ... DROP TABLE</command>.
+ </para>
+
+ <para>
+ Note that foreign key relationships between these tables will dictate the
+ order in which the tables are dumped out by pg_dump. Specifically, pg_dump
+ will attempt to dump the referenced-by table before the referencing table.
+ As the foreign key relationships are set up at CREATE EXTENSION time (prior
+ to data being loaded into the tables) circular dependencies are not
+ supported. When circular dependencies exist, the data will still be dumped
+ out but the dump will not be able to be restored directly and user
+ intervention will be required.
+ </para>
+
+ <para>
+ Sequences associated with <type>serial</type> or <type>bigserial</type> columns
+ need to be directly marked to dump their state. Marking their parent
+ relation is not enough for this purpose.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-extensions-updates">
+ <title>Extension Updates</title>
+
+ <para>
+ One advantage of the extension mechanism is that it provides convenient
+ ways to manage updates to the SQL commands that define an extension's
+ objects. This is done by associating a version name or number with
+ each released version of the extension's installation script.
+ In addition, if you want users to be able to update their databases
+ dynamically from one version to the next, you should provide
+ <firstterm>update scripts</firstterm> that make the necessary changes to go from
+ one version to the next. Update scripts have names following the pattern
+ <literal><replaceable>extension</replaceable>--<replaceable>old_version</replaceable>--<replaceable>target_version</replaceable>.sql</literal>
+ (for example, <literal>foo--1.0--1.1.sql</literal> contains the commands to modify
+ version <literal>1.0</literal> of extension <literal>foo</literal> into version
+ <literal>1.1</literal>).
+ </para>
+
+ <para>
+ Given that a suitable update script is available, the command
+ <command>ALTER EXTENSION UPDATE</command> will update an installed extension
+ to the specified new version. The update script is run in the same
+ environment that <command>CREATE EXTENSION</command> provides for installation
+ scripts: in particular, <varname>search_path</varname> is set up in the same
+ way, and any new objects created by the script are automatically added
+ to the extension. Also, if the script chooses to drop extension member
+ objects, they are automatically dissociated from the extension.
+ </para>
+
+ <para>
+ If an extension has secondary control files, the control parameters
+ that are used for an update script are those associated with the script's
+ target (new) version.
+ </para>
+
+ <para>
+ <command>ALTER EXTENSION</command> is able to execute sequences of update
+ script files to achieve a requested update. For example, if only
+ <literal>foo--1.0--1.1.sql</literal> and <literal>foo--1.1--2.0.sql</literal> are
+ available, <command>ALTER EXTENSION</command> will apply them in sequence if an
+ update to version <literal>2.0</literal> is requested when <literal>1.0</literal> is
+ currently installed.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> doesn't assume anything about the properties
+ of version names: for example, it does not know whether <literal>1.1</literal>
+ follows <literal>1.0</literal>. It just matches up the available version names
+ and follows the path that requires applying the fewest update scripts.
+ (A version name can actually be any string that doesn't contain
+ <literal>--</literal> or leading or trailing <literal>-</literal>.)
+ </para>
+
+ <para>
+ Sometimes it is useful to provide <quote>downgrade</quote> scripts, for
+ example <literal>foo--1.1--1.0.sql</literal> to allow reverting the changes
+ associated with version <literal>1.1</literal>. If you do that, be careful
+ of the possibility that a downgrade script might unexpectedly
+ get applied because it yields a shorter path. The risky case is where
+ there is a <quote>fast path</quote> update script that jumps ahead several
+ versions as well as a downgrade script to the fast path's start point.
+ It might take fewer steps to apply the downgrade and then the fast
+ path than to move ahead one version at a time. If the downgrade script
+ drops any irreplaceable objects, this will yield undesirable results.
+ </para>
+
+ <para>
+ To check for unexpected update paths, use this command:
+<programlisting>
+SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</replaceable>');
+</programlisting>
+ This shows each pair of distinct known version names for the specified
+ extension, together with the update path sequence that would be taken to
+ get from the source version to the target version, or <literal>NULL</literal> if
+ there is no available update path. The path is shown in textual form
+ with <literal>--</literal> separators. You can use
+ <literal>regexp_split_to_array(path,'--')</literal> if you prefer an array
+ format.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-extensions-update-scripts">
+ <title>Installing Extensions Using Update Scripts</title>
+
+ <para>
+ An extension that has been around for awhile will probably exist in
+ several versions, for which the author will need to write update scripts.
+ For example, if you have released a <literal>foo</literal> extension in
+ versions <literal>1.0</literal>, <literal>1.1</literal>, and <literal>1.2</literal>, there
+ should be update scripts <filename>foo--1.0--1.1.sql</filename>
+ and <filename>foo--1.1--1.2.sql</filename>.
+ Before <productname>PostgreSQL</productname> 10, it was necessary to also create
+ new script files <filename>foo--1.1.sql</filename> and <filename>foo--1.2.sql</filename>
+ that directly build the newer extension versions, or else the newer
+ versions could not be installed directly, only by
+ installing <literal>1.0</literal> and then updating. That was tedious and
+ duplicative, but now it's unnecessary, because <command>CREATE
+ EXTENSION</command> can follow update chains automatically.
+ For example, if only the script
+ files <filename>foo--1.0.sql</filename>, <filename>foo--1.0--1.1.sql</filename>,
+ and <filename>foo--1.1--1.2.sql</filename> are available then a request to
+ install version <literal>1.2</literal> is honored by running those three
+ scripts in sequence. The processing is the same as if you'd first
+ installed <literal>1.0</literal> and then updated to <literal>1.2</literal>.
+ (As with <command>ALTER EXTENSION UPDATE</command>, if multiple pathways are
+ available then the shortest is preferred.) Arranging an extension's
+ script files in this style can reduce the amount of maintenance effort
+ needed to produce small updates.
+ </para>
+
+ <para>
+ If you use secondary (version-specific) control files with an extension
+ maintained in this style, keep in mind that each version needs a control
+ file even if it has no stand-alone installation script, as that control
+ file will determine how the implicit update to that version is performed.
+ For example, if <filename>foo--1.0.control</filename> specifies <literal>requires
+ = 'bar'</literal> but <literal>foo</literal>'s other control files do not, the
+ extension's dependency on <literal>bar</literal> will be dropped when updating
+ from <literal>1.0</literal> to another version.
+ </para>
+ </sect2>
+
+ <sect2 id="extend-extensions-security">
+ <title>Security Considerations for Extensions</title>
+
+ <para>
+ Widely-distributed extensions should assume little about the database
+ they occupy. Therefore, it's appropriate to write functions provided
+ by an extension in a secure style that cannot be compromised by
+ search-path-based attacks.
+ </para>
+
+ <para>
+ An extension that has the <varname>superuser</varname> property set to
+ true must also consider security hazards for the actions taken within
+ its installation and update scripts. It is not terribly difficult for
+ a malicious user to create trojan-horse objects that will compromise
+ later execution of a carelessly-written extension script, allowing that
+ user to acquire superuser privileges.
+ </para>
+
+ <para>
+ If an extension is marked <varname>trusted</varname>, then its
+ installation schema can be selected by the installing user, who might
+ intentionally use an insecure schema in hopes of gaining superuser
+ privileges. Therefore, a trusted extension is extremely exposed from a
+ security standpoint, and all its script commands must be carefully
+ examined to ensure that no compromise is possible.
+ </para>
+
+ <para>
+ Advice about writing functions securely is provided in
+ <xref linkend="extend-extensions-security-funcs"/> below, and advice
+ about writing installation scripts securely is provided in
+ <xref linkend="extend-extensions-security-scripts"/>.
+ </para>
+
+ <sect3 id="extend-extensions-security-funcs">
+ <title>Security Considerations for Extension Functions</title>
+
+ <para>
+ SQL-language and PL-language functions provided by extensions are at
+ risk of search-path-based attacks when they are executed, since
+ parsing of these functions occurs at execution time not creation time.
+ </para>
+
+ <para>
+ The <link linkend="sql-createfunction-security"><command>CREATE
+ FUNCTION</command></link> reference page contains advice about
+ writing <literal>SECURITY DEFINER</literal> functions safely. It's
+ good practice to apply those techniques for any function provided by
+ an extension, since the function might be called by a high-privilege
+ user.
+ </para>
+
+ <!-- XXX It's not enough to use qualified names, because one might write a
+ qualified name to an object that itself uses unqualified names. Many
+ information_schema functions have that defect, for example. However,
+ that's a defect in the referenced object, and relatively few queries
+ will be affected. Also, we direct applications to secure search_path
+ when connecting to an untrusted database; if applications do that,
+ they are immune to known attacks even if some extension refers to a
+ defective object. Therefore, guide extension authors as though core
+ PostgreSQL contained no such defect. -->
+ <para>
+ If you cannot set the <varname>search_path</varname> to contain only
+ secure schemas, assume that each unqualified name could resolve to an
+ object that a malicious user has defined. Beware of constructs that
+ depend on <varname>search_path</varname> implicitly; for
+ example, <token>IN</token>
+ and <literal>CASE <replaceable>expression</replaceable> WHEN</literal>
+ always select an operator using the search path. In their place, use
+ <literal>OPERATOR(<replaceable>schema</replaceable>.=) ANY</literal>
+ and <literal>CASE WHEN <replaceable>expression</replaceable></literal>.
+ </para>
+
+ <para>
+ A general-purpose extension usually should not assume that it's been
+ installed into a secure schema, which means that even schema-qualified
+ references to its own objects are not entirely risk-free. For
+ example, if the extension has defined a
+ function <literal>myschema.myfunc(bigint)</literal> then a call such
+ as <literal>myschema.myfunc(42)</literal> could be captured by a
+ hostile function <literal>myschema.myfunc(integer)</literal>. Be
+ careful that the data types of function and operator parameters exactly
+ match the declared argument types, using explicit casts where necessary.
+ </para>
+ </sect3>
+
+ <sect3 id="extend-extensions-security-scripts">
+ <title>Security Considerations for Extension Scripts</title>
+
+ <para>
+ An extension installation or update script should be written to guard
+ against search-path-based attacks occurring when the script executes.
+ If an object reference in the script can be made to resolve to some
+ other object than the script author intended, then a compromise might
+ occur immediately, or later when the mis-defined extension object is
+ used.
+ </para>
+
+ <para>
+ DDL commands such as <command>CREATE FUNCTION</command>
+ and <command>CREATE OPERATOR CLASS</command> are generally secure,
+ but beware of any command having a general-purpose expression as a
+ component. For example, <command>CREATE VIEW</command> needs to be
+ vetted, as does a <literal>DEFAULT</literal> expression
+ in <command>CREATE FUNCTION</command>.
+ </para>
+
+ <para>
+ Sometimes an extension script might need to execute general-purpose
+ SQL, for example to make catalog adjustments that aren't possible via
+ DDL. Be careful to execute such commands with a
+ secure <varname>search_path</varname>; do <emphasis>not</emphasis>
+ trust the path provided by <command>CREATE/ALTER EXTENSION</command>
+ to be secure. Best practice is to temporarily
+ set <varname>search_path</varname> to <literal>'pg_catalog,
+ pg_temp'</literal> and insert references to the extension's
+ installation schema explicitly where needed. (This practice might
+ also be helpful for creating views.) Examples can be found in
+ the <filename>contrib</filename> modules in
+ the <productname>PostgreSQL</productname> source code distribution.
+ </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>.
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="extend-extensions-example">
+ <title>Extension Example</title>
+
+ <para>
+ Here is a complete example of an <acronym>SQL</acronym>-only
+ extension, a two-element composite type that can store any type of value
+ in its slots, which are named <quote>k</quote> and <quote>v</quote>. Non-text
+ values are automatically coerced to text for storage.
+ </para>
+
+ <para>
+ The script file <filename>pair--1.0.sql</filename> looks like this:
+
+<programlisting><![CDATA[
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pair" to load this file. \quit
+
+CREATE TYPE pair AS ( k text, v text );
+
+CREATE FUNCTION pair(text, text)
+RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';
+
+CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);
+
+-- "SET search_path" is easy to get right, but qualified names perform better.
+CREATE FUNCTION lower(pair)
+RETURNS pair LANGUAGE SQL
+AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
+SET search_path = pg_temp;
+
+CREATE FUNCTION pair_concat(pair, pair)
+RETURNS pair LANGUAGE SQL
+AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
+ $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';
+]]>
+</programlisting>
+ </para>
+
+ <para>
+ The control file <filename>pair.control</filename> looks like this:
+
+<programlisting>
+# pair extension
+comment = 'A key/value pair data type'
+default_version = '1.0'
+# cannot be relocatable because of use of @extschema@
+relocatable = false
+</programlisting>
+ </para>
+
+ <para>
+ While you hardly need a makefile to install these two files into the
+ correct directory, you could use a <filename>Makefile</filename> containing this:
+
+<programlisting>
+EXTENSION = pair
+DATA = pair--1.0.sql
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+</programlisting>
+
+ This makefile relies on <acronym>PGXS</acronym>, which is described
+ in <xref linkend="extend-pgxs"/>. The command <literal>make install</literal>
+ will install the control and script files into the correct
+ directory as reported by <application>pg_config</application>.
+ </para>
+
+ <para>
+ Once the files are installed, use the
+ <command>CREATE EXTENSION</command> command to load the objects into
+ any particular database.
+ </para>
+ </sect2>
+ </sect1>
+
+ <sect1 id="extend-pgxs">
+ <title>Extension Building Infrastructure</title>
+
+ <indexterm zone="extend-pgxs">
+ <primary>pgxs</primary>
+ </indexterm>
+
+ <para>
+ If you are thinking about distributing your
+ <productname>PostgreSQL</productname> extension modules, setting up a
+ portable build system for them can be fairly difficult. Therefore
+ the <productname>PostgreSQL</productname> installation provides a build
+ infrastructure for extensions, called <acronym>PGXS</acronym>, so
+ that simple extension modules can be built simply against an
+ already installed server. <acronym>PGXS</acronym> is mainly intended
+ for extensions that include C code, although it can be used for
+ pure-SQL extensions too. Note that <acronym>PGXS</acronym> is not
+ intended to be a universal build system framework that can be used
+ to build any software interfacing to <productname>PostgreSQL</productname>;
+ it simply automates common build rules for simple server extension
+ modules. For more complicated packages, you might need to write your
+ own build system.
+ </para>
+
+ <para>
+ To use the <acronym>PGXS</acronym> infrastructure for your extension,
+ you must write a simple makefile.
+ In the makefile, you need to set some variables
+ and include the global <acronym>PGXS</acronym> makefile.
+ Here is an example that builds an extension module named
+ <literal>isbn_issn</literal>, consisting of a shared library containing
+ some C code, an extension control file, an SQL script, an include file
+ (only needed if other modules might need to access the extension functions
+ without going via SQL), and a documentation text file:
+<programlisting>
+MODULES = isbn_issn
+EXTENSION = isbn_issn
+DATA = isbn_issn--1.0.sql
+DOCS = README.isbn_issn
+HEADERS_isbn_issn = isbn_issn.h
+
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+</programlisting>
+ The last three lines should always be the same. Earlier in the
+ file, you assign variables or add custom
+ <application>make</application> rules.
+ </para>
+
+ <para>
+ Set one of these three variables to specify what is built:
+
+ <variablelist>
+ <varlistentry id="extend-pgxs-modules">
+ <term><varname>MODULES</varname></term>
+ <listitem>
+ <para>
+ list of shared-library objects to be built from source files with same
+ stem (do not include library suffixes in this list)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-module-big">
+ <term><varname>MODULE_big</varname></term>
+ <listitem>
+ <para>
+ a shared library to build from multiple source files
+ (list object files in <varname>OBJS</varname>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-program">
+ <term><varname>PROGRAM</varname></term>
+ <listitem>
+ <para>
+ an executable program to build
+ (list object files in <varname>OBJS</varname>)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ The following variables can also be set:
+
+ <variablelist>
+ <varlistentry id="extend-pgxs-extension">
+ <term><varname>EXTENSION</varname></term>
+ <listitem>
+ <para>
+ extension name(s); for each name you must provide an
+ <literal><replaceable>extension</replaceable>.control</literal> file,
+ which will be installed into
+ <literal><replaceable>prefix</replaceable>/share/extension</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-moduledir">
+ <term><varname>MODULEDIR</varname></term>
+ <listitem>
+ <para>
+ subdirectory of <literal><replaceable>prefix</replaceable>/share</literal>
+ into which DATA and DOCS files should be installed
+ (if not set, default is <literal>extension</literal> if
+ <varname>EXTENSION</varname> is set,
+ or <literal>contrib</literal> if not)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-data">
+ <term><varname>DATA</varname></term>
+ <listitem>
+ <para>
+ random files to install into <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-data-built">
+ <term><varname>DATA_built</varname></term>
+ <listitem>
+ <para>
+ random files to install into
+ <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>,
+ which need to be built first
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-data-tsearch">
+ <term><varname>DATA_TSEARCH</varname></term>
+ <listitem>
+ <para>
+ random files to install under
+ <literal><replaceable>prefix</replaceable>/share/tsearch_data</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-docs">
+ <term><varname>DOCS</varname></term>
+ <listitem>
+ <para>
+ random files to install under
+ <literal><replaceable>prefix</replaceable>/doc/$MODULEDIR</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-headers">
+ <term><varname>HEADERS</varname></term>
+ <term><varname>HEADERS_built</varname></term>
+ <listitem>
+ <para>
+ Files to (optionally build and) install under
+ <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE_big</literal>.
+ </para>
+ <para>
+ Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built</literal>
+ are not removed by the <literal>clean</literal> target; if you want them removed,
+ also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-headers-module">
+ <term><varname>HEADERS_$MODULE</varname></term>
+ <term><varname>HEADERS_built_$MODULE</varname></term>
+ <listitem>
+ <para>
+ Files to install (after building if specified) under
+ <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE</literal>,
+ where <literal>$MODULE</literal> must be a module name used
+ in <literal>MODULES</literal> or <literal>MODULE_big</literal>.
+ </para>
+ <para>
+ Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built_$MODULE</literal>
+ are not removed by the <literal>clean</literal> target; if you want them removed,
+ also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it.
+ </para>
+ <para>
+ It is legal to use both variables for the same module, or any
+ combination, unless you have two module names in the
+ <literal>MODULES</literal> list that differ only by the presence of a
+ prefix <literal>built_</literal>, which would cause ambiguity. In
+ that (hopefully unlikely) case, you should use only the
+ <literal>HEADERS_built_$MODULE</literal> variables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-scripts">
+ <term><varname>SCRIPTS</varname></term>
+ <listitem>
+ <para>
+ script files (not binaries) to install into
+ <literal><replaceable>prefix</replaceable>/bin</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-scripts-built">
+ <term><varname>SCRIPTS_built</varname></term>
+ <listitem>
+ <para>
+ script files (not binaries) to install into
+ <literal><replaceable>prefix</replaceable>/bin</literal>,
+ which need to be built first
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-regress">
+ <term><varname>REGRESS</varname></term>
+ <listitem>
+ <para>
+ list of regression test cases (without suffix), see below
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-regress-opts">
+ <term><varname>REGRESS_OPTS</varname></term>
+ <listitem>
+ <para>
+ additional switches to pass to <application>pg_regress</application>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-isolation">
+ <term><varname>ISOLATION</varname></term>
+ <listitem>
+ <para>
+ list of isolation test cases, see below for more details
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-isolation-opts">
+ <term><varname>ISOLATION_OPTS</varname></term>
+ <listitem>
+ <para>
+ additional switches to pass to
+ <application>pg_isolation_regress</application>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-tap-tests">
+ <term><varname>TAP_TESTS</varname></term>
+ <listitem>
+ <para>
+ switch defining if TAP tests need to be run, see below
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-no-install">
+ <term><varname>NO_INSTALL</varname></term>
+ <listitem>
+ <para>
+ don't define an <literal>install</literal> target, useful for test
+ modules that don't need their build products to be installed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-no-installcheck">
+ <term><varname>NO_INSTALLCHECK</varname></term>
+ <listitem>
+ <para>
+ don't define an <literal>installcheck</literal> target, useful e.g., if tests require special configuration, or don't use <application>pg_regress</application>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-extra-clean">
+ <term><varname>EXTRA_CLEAN</varname></term>
+ <listitem>
+ <para>
+ extra files to remove in <literal>make clean</literal>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-cppflags">
+ <term><varname>PG_CPPFLAGS</varname></term>
+ <listitem>
+ <para>
+ will be prepended to <varname>CPPFLAGS</varname>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-cflags">
+ <term><varname>PG_CFLAGS</varname></term>
+ <listitem>
+ <para>
+ will be appended to <varname>CFLAGS</varname>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-cxxflags">
+ <term><varname>PG_CXXFLAGS</varname></term>
+ <listitem>
+ <para>
+ will be appended to <varname>CXXFLAGS</varname>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-ldflags">
+ <term><varname>PG_LDFLAGS</varname></term>
+ <listitem>
+ <para>
+ will be prepended to <varname>LDFLAGS</varname>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-libs">
+ <term><varname>PG_LIBS</varname></term>
+ <listitem>
+ <para>
+ will be added to <varname>PROGRAM</varname> link line
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-shlib-link">
+ <term><varname>SHLIB_LINK</varname></term>
+ <listitem>
+ <para>
+ will be added to <varname>MODULE_big</varname> link line
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="extend-pgxs-pg-config">
+ <term><varname>PG_CONFIG</varname></term>
+ <listitem>
+ <para>
+ path to <application>pg_config</application> program for the
+ <productname>PostgreSQL</productname> installation to build against
+ (typically just <literal>pg_config</literal> to use the first one in your
+ <varname>PATH</varname>)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ Put this makefile as <literal>Makefile</literal> in the directory
+ which holds your extension. Then you can do
+ <literal>make</literal> to compile, and then <literal>make
+ install</literal> to install your module. By default, the extension is
+ compiled and installed for the
+ <productname>PostgreSQL</productname> installation that
+ corresponds to the first <command>pg_config</command> program
+ found in your <varname>PATH</varname>. You can use a different installation by
+ setting <varname>PG_CONFIG</varname> to point to its
+ <command>pg_config</command> program, either within the makefile
+ or on the <literal>make</literal> command line.
+ </para>
+
+ <para>
+ You can also run <literal>make</literal> in a directory outside the source
+ tree of your extension, if you want to keep the build directory separate.
+ This procedure is also called a
+ <indexterm><primary>VPATH</primary></indexterm><firstterm>VPATH</firstterm>
+ build. Here's how:
+<programlisting>
+mkdir build_dir
+cd build_dir
+make -f /path/to/extension/source/tree/Makefile
+make -f /path/to/extension/source/tree/Makefile install
+</programlisting>
+ </para>
+
+ <para>
+ Alternatively, you can set up a directory for a VPATH build in a similar
+ way to how it is done for the core code. One way to do this is using the
+ core script <filename>config/prep_buildtree</filename>. Once this has been done
+ you can build by setting the <literal>make</literal> variable
+ <varname>VPATH</varname> like this:
+<programlisting>
+make VPATH=/path/to/extension/source/tree
+make VPATH=/path/to/extension/source/tree install
+</programlisting>
+ This procedure can work with a greater variety of directory layouts.
+ </para>
+
+ <para>
+ The scripts listed in the <varname>REGRESS</varname> variable are used for
+ regression testing of your module, which can be invoked by <literal>make
+ installcheck</literal> after doing <literal>make install</literal>. For this to
+ work you must have a running <productname>PostgreSQL</productname> server.
+ The script files listed in <varname>REGRESS</varname> must appear in a
+ subdirectory named <literal>sql/</literal> in your extension's directory.
+ These files must have extension <literal>.sql</literal>, which must not be
+ included in the <varname>REGRESS</varname> list in the makefile. For each
+ test there should also be a file containing the expected output in a
+ subdirectory named <literal>expected/</literal>, with the same stem and
+ extension <literal>.out</literal>. <literal>make installcheck</literal>
+ executes each test script with <application>psql</application>, and compares the
+ resulting output to the matching expected file. Any differences will be
+ written to the file <literal>regression.diffs</literal> in <command>diff
+ -c</command> format. Note that trying to run a test that is missing its
+ expected file will be reported as <quote>trouble</quote>, so make sure you
+ have all expected files.
+ </para>
+
+ <para>
+ The scripts listed in the <varname>ISOLATION</varname> variable are used
+ for tests stressing behavior of concurrent session with your module, which
+ can be invoked by <literal>make installcheck</literal> after doing
+ <literal>make install</literal>. For this to work you must have a
+ running <productname>PostgreSQL</productname> server. The script files
+ listed in <varname>ISOLATION</varname> must appear in a subdirectory
+ named <literal>specs/</literal> in your extension's directory. These files
+ must have extension <literal>.spec</literal>, which must not be included
+ in the <varname>ISOLATION</varname> list in the makefile. For each test
+ there should also be a file containing the expected output in a
+ subdirectory named <literal>expected/</literal>, with the same stem and
+ extension <literal>.out</literal>. <literal>make installcheck</literal>
+ executes each test script, and compares the resulting output to the
+ matching expected file. Any differences will be written to the file
+ <literal>output_iso/regression.diffs</literal> in
+ <command>diff -c</command> format. Note that trying to run a test that is
+ missing its expected file will be reported as <quote>trouble</quote>, so
+ make sure you have all expected files.
+ </para>
+
+ <para>
+ <literal>TAP_TESTS</literal> enables the use of TAP tests. Data from each
+ run is present in a subdirectory named <literal>tmp_check/</literal>.
+ See also <xref linkend="regress-tap"/> for more details.
+ </para>
+
+ <tip>
+ <para>
+ The easiest way to create the expected files is to create empty files,
+ then do a test run (which will of course report differences). Inspect
+ the actual result files found in the <literal>results/</literal>
+ directory (for tests in <literal>REGRESS</literal>), or
+ <literal>output_iso/results/</literal> directory (for tests in
+ <literal>ISOLATION</literal>), then copy them to
+ <literal>expected/</literal> if they match what you expect from the test.
+ </para>
+
+ </tip>
+ </sect1>
+
+ </chapter>