Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
extend-extensions.html656 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>38.17. Packaging Related Objects into an Extension</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="xindex.html" title="38.16. Interfacing Extensions to Indexes" /><link rel="next" href="extend-pgxs.html" title="38.18. Extension Building Infrastructure" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">38.17. Packaging Related Objects into an Extension</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="38.18. Extension Building Infrastructure">Next</a></td></tr></table><hr /></div><div class="sect1" id="EXTEND-EXTENSIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">38.17. Packaging Related Objects into an Extension <a href="#EXTEND-EXTENSIONS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-FILES">38.17.1. Extension Files</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION">38.17.2. Extension Relocatability</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES">38.17.3. Extension Configuration Tables</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-UPDATES">38.17.4. Extension Updates</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-UPDATE-SCRIPTS">38.17.5. Installing Extensions Using Update Scripts</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY">38.17.6. Security Considerations for Extensions</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE">38.17.7. Extension Example</a></span></dt></dl></div><a id="id-1.8.3.20.2" class="indexterm"></a><p>3    A useful extension to <span class="productname">PostgreSQL</span> typically includes4    multiple SQL objects; for example, a new data type will require new5    functions, new operators, and probably new index operator classes.6    It is helpful to collect all these objects into a single package7    to simplify database management.  <span class="productname">PostgreSQL</span> calls8    such a package an <em class="firstterm">extension</em>.  To define an extension,9    you need at least a <em class="firstterm">script file</em> that contains the10    <acronym class="acronym">SQL</acronym> commands to create the extension's objects, and a11    <em class="firstterm">control file</em> that specifies a few basic properties12    of the extension itself.  If the extension includes C code, there13    will typically also be a shared library file into which the C code14    has been built.  Once you have these files, a simple15    <a class="link" href="sql-createextension.html" title="CREATE EXTENSION"><code class="command">CREATE EXTENSION</code></a> command loads the objects into16    your database.17   </p><p>18    The main advantage of using an extension, rather than just running the19    <acronym class="acronym">SQL</acronym> script to load a bunch of <span class="quote">“<span class="quote">loose</span>”</span> objects20    into your database, is that <span class="productname">PostgreSQL</span> will then21    understand that the objects of the extension go together.  You can22    drop all the objects with a single <a class="link" href="sql-dropextension.html" title="DROP EXTENSION"><code class="command">DROP EXTENSION</code></a>23    command (no need to maintain a separate <span class="quote">“<span class="quote">uninstall</span>”</span> script).24    Even more useful, <span class="application">pg_dump</span> knows that it should not25    dump the individual member objects of the extension — it will26    just include a <code class="command">CREATE EXTENSION</code> command in dumps, instead.27    This vastly simplifies migration to a new version of the extension28    that might contain more or different objects than the old version.29    Note however that you must have the extension's control, script, and30    other files available when loading such a dump into a new database.31   </p><p>32    <span class="productname">PostgreSQL</span> will not let you drop an individual object33    contained in an extension, except by dropping the whole extension.34    Also, while you can change the definition of an extension member object35    (for example, via <code class="command">CREATE OR REPLACE FUNCTION</code> for a36    function), bear in mind that the modified definition will not be dumped37    by <span class="application">pg_dump</span>.  Such a change is usually only sensible if38    you concurrently make the same change in the extension's script file.39    (But there are special provisions for tables containing configuration40    data; see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES" title="38.17.3. Extension Configuration Tables">Section 38.17.3</a>.)41    In production situations, it's generally better to create an extension42    update script to perform changes to extension member objects.43   </p><p>44    The extension script may set privileges on objects that are part of the45    extension, using <code class="command">GRANT</code> and <code class="command">REVOKE</code>46    statements.  The final set of privileges for each object (if any are set)47    will be stored in the48    <a class="link" href="catalog-pg-init-privs.html" title="53.28. pg_init_privs"><code class="structname">pg_init_privs</code></a>49    system catalog.  When <span class="application">pg_dump</span> is used, the50    <code class="command">CREATE EXTENSION</code> command will be included in the dump, followed51    by the set of <code class="command">GRANT</code> and <code class="command">REVOKE</code>52    statements necessary to set the privileges on the objects to what they were53    at the time the dump was taken.54   </p><p>55    <span class="productname">PostgreSQL</span> does not currently support extension scripts56    issuing <code class="command">CREATE POLICY</code> or <code class="command">SECURITY LABEL</code>57    statements.  These are expected to be set after the extension has been58    created.  All RLS policies and security labels on extension objects will be59    included in dumps created by <span class="application">pg_dump</span>.60   </p><p>61    The extension mechanism also has provisions for packaging modification62    scripts that adjust the definitions of the SQL objects contained in an63    extension.  For example, if version 1.1 of an extension adds one function64    and changes the body of another function compared to 1.0, the extension65    author can provide an <em class="firstterm">update script</em> that makes just those66    two changes.  The <code class="command">ALTER EXTENSION UPDATE</code> command can then67    be used to apply these changes and track which version of the extension68    is actually installed in a given database.69   </p><p>70    The kinds of SQL objects that can be members of an extension are shown in71    the description of <a class="link" href="sql-alterextension.html" title="ALTER EXTENSION"><code class="command">ALTER EXTENSION</code></a>.  Notably, objects72    that are database-cluster-wide, such as databases, roles, and tablespaces,73    cannot be extension members since an extension is only known within one74    database.  (Although an extension script is not prohibited from creating75    such objects, if it does so they will not be tracked as part of the76    extension.)  Also notice that while a table can be a member of an77    extension, its subsidiary objects such as indexes are not directly78    considered members of the extension.79    Another important point is that schemas can belong to extensions, but not80    vice versa: an extension as such has an unqualified name and does not81    exist <span class="quote">“<span class="quote">within</span>”</span> any schema.  The extension's member objects,82    however, will belong to schemas whenever appropriate for their object83    types.  It may or may not be appropriate for an extension to own the84    schema(s) its member objects are within.85   </p><p>86    If an extension's script creates any temporary objects (such as temp87    tables), those objects are treated as extension members for the88    remainder of the current session, but are automatically dropped at89    session end, as any temporary object would be.  This is an exception90    to the rule that extension member objects cannot be dropped without91    dropping the whole extension.92   </p><div class="sect2" id="EXTEND-EXTENSIONS-FILES"><div class="titlepage"><div><div><h3 class="title">38.17.1. Extension Files <a href="#EXTEND-EXTENSIONS-FILES" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.20.11.2" class="indexterm"></a><p>93     The <code class="command">CREATE EXTENSION</code> command relies on a control94     file for each extension, which must be named the same as the extension95     with a suffix of <code class="literal">.control</code>, and must be placed in the96     installation's <code class="literal">SHAREDIR/extension</code> directory.  There97     must also be at least one <acronym class="acronym">SQL</acronym> script file, which follows the98     naming pattern99     <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.sql</code>100     (for example, <code class="literal">foo--1.0.sql</code> for version <code class="literal">1.0</code> of101     extension <code class="literal">foo</code>).  By default, the script file(s) are also102     placed in the <code class="literal">SHAREDIR/extension</code> directory; but the103     control file can specify a different directory for the script file(s).104    </p><p>105     The file format for an extension control file is the same as for the106     <code class="filename">postgresql.conf</code> file, namely a list of107     <em class="replaceable"><code>parameter_name</code></em> <code class="literal">=</code> <em class="replaceable"><code>value</code></em>108     assignments, one per line.  Blank lines and comments introduced by109     <code class="literal">#</code> are allowed.  Be sure to quote any value that is not110     a single word or number.111    </p><p>112     A control file can set the following parameters:113    </p><div class="variablelist"><dl class="variablelist"><dt id="EXTEND-EXTENSIONS-FILES-DIRECTORY"><span class="term"><code class="varname">directory</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-DIRECTORY" class="id_link">#</a></dt><dd><p>114        The directory containing the extension's <acronym class="acronym">SQL</acronym> script115        file(s).  Unless an absolute path is given, the name is relative to116        the installation's <code class="literal">SHAREDIR</code> directory.  The117        default behavior is equivalent to specifying118        <code class="literal">directory = 'extension'</code>.119       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-DEFAULT-VERSION"><span class="term"><code class="varname">default_version</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-DEFAULT-VERSION" class="id_link">#</a></dt><dd><p>120        The default version of the extension (the one that will be installed121        if no version is specified in <code class="command">CREATE EXTENSION</code>).  Although122        this can be omitted, that will result in <code class="command">CREATE EXTENSION</code>123        failing if no <code class="literal">VERSION</code> option appears, so you generally124        don't want to do that.125       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-COMMENT"><span class="term"><code class="varname">comment</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-COMMENT" class="id_link">#</a></dt><dd><p>126        A comment (any string) about the extension.  The comment is applied127        when initially creating an extension, but not during extension updates128        (since that might override user-added comments).  Alternatively,129        the extension's comment can be set by writing130        a <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a> command in the script file.131       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-ENCODING"><span class="term"><code class="varname">encoding</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-ENCODING" class="id_link">#</a></dt><dd><p>132        The character set encoding used by the script file(s).  This should133        be specified if the script files contain any non-ASCII characters.134        Otherwise the files will be assumed to be in the database encoding.135       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-MODULE-PATHNAME"><span class="term"><code class="varname">module_pathname</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-MODULE-PATHNAME" class="id_link">#</a></dt><dd><p>136        The value of this parameter will be substituted for each occurrence137        of <code class="literal">MODULE_PATHNAME</code> in the script file(s).  If it is not138        set, no substitution is made.  Typically, this is set to139        <code class="literal">$libdir/<em class="replaceable"><code>shared_library_name</code></em></code> and140        then <code class="literal">MODULE_PATHNAME</code> is used in <code class="command">CREATE141        FUNCTION</code> commands for C-language functions, so that the script142        files do not need to hard-wire the name of the shared library.143       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-REQUIRES"><span class="term"><code class="varname">requires</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-REQUIRES" class="id_link">#</a></dt><dd><p>144        A list of names of extensions that this extension depends on,145        for example <code class="literal">requires = 'foo, bar'</code>.  Those146        extensions must be installed before this one can be installed.147       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-NO-RELOCATE"><span class="term"><code class="varname">no_relocate</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-NO-RELOCATE" class="id_link">#</a></dt><dd><p>148        A list of names of extensions that this extension depends on that149        should be barred from changing their schemas via <code class="command">ALTER150        EXTENSION ... SET SCHEMA</code>.151        This is needed if this extension's script references the name152        of a required extension's schema (using153        the <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code>154        syntax) in a way that cannot track renames.155       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-SUPERUSER"><span class="term"><code class="varname">superuser</code> (<code class="type">boolean</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-SUPERUSER" class="id_link">#</a></dt><dd><p>156        If this parameter is <code class="literal">true</code> (which is the default),157        only superusers can create the extension or update it to a new158        version (but see also <code class="varname">trusted</code>, below).159        If it is set to <code class="literal">false</code>, just the privileges160        required to execute the commands in the installation or update script161        are required.162        This should normally be set to <code class="literal">true</code> if any of the163        script commands require superuser privileges.  (Such commands would164        fail anyway, but it's more user-friendly to give the error up front.)165       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-TRUSTED"><span class="term"><code class="varname">trusted</code> (<code class="type">boolean</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-TRUSTED" class="id_link">#</a></dt><dd><p>166        This parameter, if set to <code class="literal">true</code> (which is not the167        default), allows some non-superusers to install an extension that168        has <code class="varname">superuser</code> set to <code class="literal">true</code>.169        Specifically, installation will be permitted for anyone who has170        <code class="literal">CREATE</code> privilege on the current database.171        When the user executing <code class="command">CREATE EXTENSION</code> is not172        a superuser but is allowed to install by virtue of this parameter,173        then the installation or update script is run as the bootstrap174        superuser, not as the calling user.175        This parameter is irrelevant if <code class="varname">superuser</code> is176        <code class="literal">false</code>.177        Generally, this should not be set true for extensions that could178        allow access to otherwise-superuser-only abilities, such as179        file system access.180        Also, marking an extension trusted requires significant extra effort181        to write the extension's installation and update script(s) securely;182        see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY" title="38.17.6. Security Considerations for Extensions">Section 38.17.6</a>.183       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-RELOCATABLE"><span class="term"><code class="varname">relocatable</code> (<code class="type">boolean</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-RELOCATABLE" class="id_link">#</a></dt><dd><p>184        An extension is <em class="firstterm">relocatable</em> if it is possible to move185        its contained objects into a different schema after initial creation186        of the extension.  The default is <code class="literal">false</code>, i.e., the187        extension is not relocatable.188        See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="38.17.2. Extension Relocatability">Section 38.17.2</a> for more information.189       </p></dd><dt id="EXTEND-EXTENSIONS-FILES-SCHEMA"><span class="term"><code class="varname">schema</code> (<code class="type">string</code>)</span> <a href="#EXTEND-EXTENSIONS-FILES-SCHEMA" class="id_link">#</a></dt><dd><p>190        This parameter can only be set for non-relocatable extensions.191        It forces the extension to be loaded into exactly the named schema192        and not any other.193        The <code class="varname">schema</code> parameter is consulted only when194        initially creating an extension, not during extension updates.195        See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="38.17.2. Extension Relocatability">Section 38.17.2</a> for more information.196       </p></dd></dl></div><p>197     In addition to the primary control file198     <code class="literal"><em class="replaceable"><code>extension</code></em>.control</code>,199     an extension can have secondary control files named in the style200     <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.control</code>.201     If supplied, these must be located in the script file directory.202     Secondary control files follow the same format as the primary control203     file.  Any parameters set in a secondary control file override the204     primary control file when installing or updating to that version of205     the extension.  However, the parameters <code class="varname">directory</code> and206     <code class="varname">default_version</code> cannot be set in a secondary control file.207    </p><p>208     An extension's <acronym class="acronym">SQL</acronym> script files can contain any SQL commands,209     except for transaction control commands (<code class="command">BEGIN</code>,210     <code class="command">COMMIT</code>, etc.) and commands that cannot be executed inside a211     transaction block (such as <code class="command">VACUUM</code>).  This is because the212     script files are implicitly executed within a transaction block.213    </p><p>214     An extension's <acronym class="acronym">SQL</acronym> script files can also contain lines215     beginning with <code class="literal">\echo</code>, which will be ignored (treated as216     comments) by the extension mechanism.  This provision is commonly used217     to throw an error if the script file is fed to <span class="application">psql</span>218     rather than being loaded via <code class="command">CREATE EXTENSION</code> (see example219     script in <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE" title="38.17.7. Extension Example">Section 38.17.7</a>).220     Without that, users might accidentally load the221     extension's contents as <span class="quote">“<span class="quote">loose</span>”</span> objects rather than as an222     extension, a state of affairs that's a bit tedious to recover from.223    </p><p>224     If the extension script contains the225     string <code class="literal">@extowner@</code>, that string is replaced with the226     (suitably quoted) name of the user calling <code class="command">CREATE227     EXTENSION</code> or <code class="command">ALTER EXTENSION</code>.  Typically228     this feature is used by extensions that are marked trusted to assign229     ownership of selected objects to the calling user rather than the230     bootstrap superuser.  (One should be careful about doing so, however.231     For example, assigning ownership of a C-language function to a232     non-superuser would create a privilege escalation path for that user.)233    </p><p>234     While the script files can contain any characters allowed by the specified235     encoding, control files should contain only plain ASCII, because there236     is no way for <span class="productname">PostgreSQL</span> to know what encoding a237     control file is in.  In practice this is only an issue if you want to238     use non-ASCII characters in the extension's comment.  Recommended239     practice in that case is to not use the control file <code class="varname">comment</code>240     parameter, but instead use <code class="command">COMMENT ON EXTENSION</code>241     within a script file to set the comment.242    </p></div><div class="sect2" id="EXTEND-EXTENSIONS-RELOCATION"><div class="titlepage"><div><div><h3 class="title">38.17.2. Extension Relocatability <a href="#EXTEND-EXTENSIONS-RELOCATION" class="id_link">#</a></h3></div></div></div><p>243     Users often wish to load the objects contained in an extension into a244     different schema than the extension's author had in mind.  There are245     three supported levels of relocatability:246    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>247       A fully relocatable extension can be moved into another schema248       at any time, even after it's been loaded into a database.249       This is done with the <code class="command">ALTER EXTENSION SET SCHEMA</code>250       command, which automatically renames all the member objects into251       the new schema.  Normally, this is only possible if the extension252       contains no internal assumptions about what schema any of its253       objects are in.  Also, the extension's objects must all be in one254       schema to begin with (ignoring objects that do not belong to any255       schema, such as procedural languages).  Mark a fully relocatable256       extension by setting <code class="literal">relocatable = true</code> in its control257       file.258      </p></li><li class="listitem"><p>259       An extension might be relocatable during installation but not260       afterwards.  This is typically the case if the extension's script261       file needs to reference the target schema explicitly, for example262       in setting <code class="literal">search_path</code> properties for SQL functions.263       For such an extension, set <code class="literal">relocatable = false</code> in its264       control file, and use <code class="literal">@extschema@</code> to refer to the target265       schema in the script file.  All occurrences of this string will be266       replaced by the actual target schema's name (double-quoted if267       necessary) before the script is executed.  The user can set the268       target schema using the269       <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>.270      </p></li><li class="listitem"><p>271       If the extension does not support relocation at all, set272       <code class="literal">relocatable = false</code> in its control file, and also set273       <code class="literal">schema</code> to the name of the intended target schema.  This274       will prevent use of the <code class="literal">SCHEMA</code> option of <code class="command">CREATE275       EXTENSION</code>, unless it specifies the same schema named in the control276       file.  This choice is typically necessary if the extension contains277       internal assumptions about its schema name that can't be replaced by278       uses of <code class="literal">@extschema@</code>.  The <code class="literal">@extschema@</code>279       substitution mechanism is available in this case too, although it is280       of limited use since the schema name is determined by the control file.281      </p></li></ul></div><p>282     In all cases, the script file will be executed with283     <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> initially set to point to the target284     schema; that is, <code class="command">CREATE EXTENSION</code> does the equivalent of285     this:286</p><pre class="programlisting">287SET LOCAL search_path TO @extschema@, pg_temp;288</pre><p>289     This allows the objects created by the script file to go into the target290     schema.  The script file can change <code class="varname">search_path</code> if it wishes,291     but that is generally undesirable.  <code class="varname">search_path</code> is restored292     to its previous setting upon completion of <code class="command">CREATE EXTENSION</code>.293    </p><p>294     The target schema is determined by the <code class="varname">schema</code> parameter in295     the control file if that is given, otherwise by the <code class="literal">SCHEMA</code>296     option of <code class="command">CREATE EXTENSION</code> if that is given, otherwise the297     current default object creation schema (the first one in the caller's298     <code class="varname">search_path</code>).  When the control file <code class="varname">schema</code>299     parameter is used, the target schema will be created if it doesn't300     already exist, but in the other two cases it must already exist.301    </p><p>302     If any prerequisite extensions are listed in <code class="varname">requires</code>303     in the control file, their target schemas are added to the initial304     setting of <code class="varname">search_path</code>, following the new305     extension's target schema.  This allows their objects to be visible to306     the new extension's script file.307    </p><p>308     For security, <code class="literal">pg_temp</code> is automatically appended to309     the end of <code class="varname">search_path</code> in all cases.310    </p><p>311     Although a non-relocatable extension can contain objects spread across312     multiple schemas, it is usually desirable to place all the objects meant313     for external use into a single schema, which is considered the extension's314     target schema.  Such an arrangement works conveniently with the default315     setting of <code class="varname">search_path</code> during creation of dependent316     extensions.317    </p><p>318     If an extension references objects belonging to another extension,319     it is recommended to schema-qualify those references.  To do that,320     write <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code>321     in the extension's script file, where <em class="replaceable"><code>name</code></em>322     is the name of the other extension (which must be listed in this323     extension's <code class="literal">requires</code> list).  This string will be324     replaced by the name (double-quoted if necessary) of that extension's325     target schema.326     Although this notation avoids the need to make hard-wired assumptions327     about schema names in the extension's script file, its use may embed328     the other extension's schema name into the installed objects of this329     extension.  (Typically, that happens330     when <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code> is331     used inside a string literal, such as a function body or332     a <code class="varname">search_path</code> setting.  In other cases, the object333     reference is reduced to an OID during parsing and does not require334     subsequent lookups.)  If the other extension's schema name is so335     embedded, you should prevent the other extension from being relocated336     after yours is installed, by adding the name of the other extension to337     this one's <code class="literal">no_relocate</code> list.338    </p></div><div class="sect2" id="EXTEND-EXTENSIONS-CONFIG-TABLES"><div class="titlepage"><div><div><h3 class="title">38.17.3. Extension Configuration Tables <a href="#EXTEND-EXTENSIONS-CONFIG-TABLES" class="id_link">#</a></h3></div></div></div><p>339     Some extensions include configuration tables, which contain data that340     might be added or changed by the user after installation of the341     extension.  Ordinarily, if a table is part of an extension, neither342     the table's definition nor its content will be dumped by343     <span class="application">pg_dump</span>.  But that behavior is undesirable for a344     configuration table; any data changes made by the user need to be345     included in dumps, or the extension will behave differently after a dump346     and restore.347    </p><a id="id-1.8.3.20.13.3" class="indexterm"></a><p>348     To solve this problem, an extension's script file can mark a table349     or a sequence it has created as a configuration relation, which will350     cause <span class="application">pg_dump</span> to include the table's or the sequence's351     contents (not its definition) in dumps.  To do that, call the function352     <code class="function">pg_extension_config_dump(regclass, text)</code> after creating the353     table or the sequence, for example354</p><pre class="programlisting">355CREATE TABLE my_config (key text, value text);356CREATE SEQUENCE my_config_seq;357 358SELECT pg_catalog.pg_extension_config_dump('my_config', '');359SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');360</pre><p>361     Any number of tables or sequences can be marked this way. Sequences362     associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns can363     be marked as well.364    </p><p>365     When the second argument of <code class="function">pg_extension_config_dump</code> is366     an empty string, the entire contents of the table are dumped by367     <span class="application">pg_dump</span>.  This is usually only correct if the table368     is initially empty as created by the extension script.  If there is369     a mixture of initial data and user-provided data in the table,370     the second argument of <code class="function">pg_extension_config_dump</code> provides371     a <code class="literal">WHERE</code> condition that selects the data to be dumped.372     For example, you might do373</p><pre class="programlisting">374CREATE TABLE my_config (key text, value text, standard_entry boolean);375 376SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');377</pre><p>378     and then make sure that <code class="structfield">standard_entry</code> is true only379     in the rows created by the extension's script.380    </p><p>381     For sequences, the second argument of <code class="function">pg_extension_config_dump</code>382     has no effect.383    </p><p>384     More complicated situations, such as initially-provided rows that might385     be modified by users, can be handled by creating triggers on the386     configuration table to ensure that modified rows are marked correctly.387    </p><p>388     You can alter the filter condition associated with a configuration table389     by calling <code class="function">pg_extension_config_dump</code> again.  (This would390     typically be useful in an extension update script.)  The only way to mark391     a table as no longer a configuration table is to dissociate it from the392     extension with <code class="command">ALTER EXTENSION ... DROP TABLE</code>.393    </p><p>394     Note that foreign key relationships between these tables will dictate the395     order in which the tables are dumped out by pg_dump.  Specifically, pg_dump396     will attempt to dump the referenced-by table before the referencing table.397     As the foreign key relationships are set up at CREATE EXTENSION time (prior398     to data being loaded into the tables) circular dependencies are not399     supported.  When circular dependencies exist, the data will still be dumped400     out but the dump will not be able to be restored directly and user401     intervention will be required.402    </p><p>403     Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns404     need to be directly marked to dump their state. Marking their parent405     relation is not enough for this purpose.406    </p></div><div class="sect2" id="EXTEND-EXTENSIONS-UPDATES"><div class="titlepage"><div><div><h3 class="title">38.17.4. Extension Updates <a href="#EXTEND-EXTENSIONS-UPDATES" class="id_link">#</a></h3></div></div></div><p>407     One advantage of the extension mechanism is that it provides convenient408     ways to manage updates to the SQL commands that define an extension's409     objects.  This is done by associating a version name or number with410     each released version of the extension's installation script.411     In addition, if you want users to be able to update their databases412     dynamically from one version to the next, you should provide413     <em class="firstterm">update scripts</em> that make the necessary changes to go from414     one version to the next.  Update scripts have names following the pattern415     <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>old_version</code></em>--<em class="replaceable"><code>target_version</code></em>.sql</code>416     (for example, <code class="literal">foo--1.0--1.1.sql</code> contains the commands to modify417     version <code class="literal">1.0</code> of extension <code class="literal">foo</code> into version418     <code class="literal">1.1</code>).419    </p><p>420     Given that a suitable update script is available, the command421     <code class="command">ALTER EXTENSION UPDATE</code> will update an installed extension422     to the specified new version.  The update script is run in the same423     environment that <code class="command">CREATE EXTENSION</code> provides for installation424     scripts: in particular, <code class="varname">search_path</code> is set up in the same425     way, and any new objects created by the script are automatically added426     to the extension.  Also, if the script chooses to drop extension member427     objects, they are automatically dissociated from the extension.428    </p><p>429     If an extension has secondary control files, the control parameters430     that are used for an update script are those associated with the script's431     target (new) version.432    </p><p>433     <code class="command">ALTER EXTENSION</code> is able to execute sequences of update434     script files to achieve a requested update.  For example, if only435     <code class="literal">foo--1.0--1.1.sql</code> and <code class="literal">foo--1.1--2.0.sql</code> are436     available, <code class="command">ALTER EXTENSION</code> will apply them in sequence if an437     update to version <code class="literal">2.0</code> is requested when <code class="literal">1.0</code> is438     currently installed.439    </p><p>440     <span class="productname">PostgreSQL</span> doesn't assume anything about the properties441     of version names: for example, it does not know whether <code class="literal">1.1</code>442     follows <code class="literal">1.0</code>.  It just matches up the available version names443     and follows the path that requires applying the fewest update scripts.444     (A version name can actually be any string that doesn't contain445     <code class="literal">--</code> or leading or trailing <code class="literal">-</code>.)446    </p><p>447     Sometimes it is useful to provide <span class="quote">“<span class="quote">downgrade</span>”</span> scripts, for448     example <code class="literal">foo--1.1--1.0.sql</code> to allow reverting the changes449     associated with version <code class="literal">1.1</code>.  If you do that, be careful450     of the possibility that a downgrade script might unexpectedly451     get applied because it yields a shorter path.  The risky case is where452     there is a <span class="quote">“<span class="quote">fast path</span>”</span> update script that jumps ahead several453     versions as well as a downgrade script to the fast path's start point.454     It might take fewer steps to apply the downgrade and then the fast455     path than to move ahead one version at a time.  If the downgrade script456     drops any irreplaceable objects, this will yield undesirable results.457    </p><p>458     To check for unexpected update paths, use this command:459</p><pre class="programlisting">460SELECT * FROM pg_extension_update_paths('<em class="replaceable"><code>extension_name</code></em>');461</pre><p>462     This shows each pair of distinct known version names for the specified463     extension, together with the update path sequence that would be taken to464     get from the source version to the target version, or <code class="literal">NULL</code> if465     there is no available update path.  The path is shown in textual form466     with <code class="literal">--</code> separators.  You can use467     <code class="literal">regexp_split_to_array(path,'--')</code> if you prefer an array468     format.469    </p></div><div class="sect2" id="EXTEND-EXTENSIONS-UPDATE-SCRIPTS"><div class="titlepage"><div><div><h3 class="title">38.17.5. Installing Extensions Using Update Scripts <a href="#EXTEND-EXTENSIONS-UPDATE-SCRIPTS" class="id_link">#</a></h3></div></div></div><p>470     An extension that has been around for awhile will probably exist in471     several versions, for which the author will need to write update scripts.472     For example, if you have released a <code class="literal">foo</code> extension in473     versions <code class="literal">1.0</code>, <code class="literal">1.1</code>, and <code class="literal">1.2</code>, there474     should be update scripts <code class="filename">foo--1.0--1.1.sql</code>475     and <code class="filename">foo--1.1--1.2.sql</code>.476     Before <span class="productname">PostgreSQL</span> 10, it was necessary to also create477     new script files <code class="filename">foo--1.1.sql</code> and <code class="filename">foo--1.2.sql</code>478     that directly build the newer extension versions, or else the newer479     versions could not be installed directly, only by480     installing <code class="literal">1.0</code> and then updating.  That was tedious and481     duplicative, but now it's unnecessary, because <code class="command">CREATE482     EXTENSION</code> can follow update chains automatically.483     For example, if only the script484     files <code class="filename">foo--1.0.sql</code>, <code class="filename">foo--1.0--1.1.sql</code>,485     and <code class="filename">foo--1.1--1.2.sql</code> are available then a request to486     install version <code class="literal">1.2</code> is honored by running those three487     scripts in sequence.  The processing is the same as if you'd first488     installed <code class="literal">1.0</code> and then updated to <code class="literal">1.2</code>.489     (As with <code class="command">ALTER EXTENSION UPDATE</code>, if multiple pathways are490     available then the shortest is preferred.)  Arranging an extension's491     script files in this style can reduce the amount of maintenance effort492     needed to produce small updates.493    </p><p>494     If you use secondary (version-specific) control files with an extension495     maintained in this style, keep in mind that each version needs a control496     file even if it has no stand-alone installation script, as that control497     file will determine how the implicit update to that version is performed.498     For example, if <code class="filename">foo--1.0.control</code> specifies <code class="literal">requires499     = 'bar'</code> but <code class="literal">foo</code>'s other control files do not, the500     extension's dependency on <code class="literal">bar</code> will be dropped when updating501     from <code class="literal">1.0</code> to another version.502    </p></div><div class="sect2" id="EXTEND-EXTENSIONS-SECURITY"><div class="titlepage"><div><div><h3 class="title">38.17.6. Security Considerations for Extensions <a href="#EXTEND-EXTENSIONS-SECURITY" class="id_link">#</a></h3></div></div></div><p>503     Widely-distributed extensions should assume little about the database504     they occupy.  Therefore, it's appropriate to write functions provided505     by an extension in a secure style that cannot be compromised by506     search-path-based attacks.507    </p><p>508     An extension that has the <code class="varname">superuser</code> property set to509     true must also consider security hazards for the actions taken within510     its installation and update scripts.  It is not terribly difficult for511     a malicious user to create trojan-horse objects that will compromise512     later execution of a carelessly-written extension script, allowing that513     user to acquire superuser privileges.514    </p><p>515     If an extension is marked <code class="varname">trusted</code>, then its516     installation schema can be selected by the installing user, who might517     intentionally use an insecure schema in hopes of gaining superuser518     privileges.  Therefore, a trusted extension is extremely exposed from a519     security standpoint, and all its script commands must be carefully520     examined to ensure that no compromise is possible.521    </p><p>522     Advice about writing functions securely is provided in523     <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-FUNCS" title="38.17.6.1. Security Considerations for Extension Functions">Section 38.17.6.1</a> below, and advice524     about writing installation scripts securely is provided in525     <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-SCRIPTS" title="38.17.6.2. Security Considerations for Extension Scripts">Section 38.17.6.2</a>.526    </p><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-FUNCS"><div class="titlepage"><div><div><h4 class="title">38.17.6.1. Security Considerations for Extension Functions <a href="#EXTEND-EXTENSIONS-SECURITY-FUNCS" class="id_link">#</a></h4></div></div></div><p>527      SQL-language and PL-language functions provided by extensions are at528      risk of search-path-based attacks when they are executed, since529      parsing of these functions occurs at execution time not creation time.530     </p><p>531      The <a class="link" href="sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" title="Writing SECURITY DEFINER Functions Safely"><code class="command">CREATE532      FUNCTION</code></a> reference page contains advice about533      writing <code class="literal">SECURITY DEFINER</code> functions safely.  It's534      good practice to apply those techniques for any function provided by535      an extension, since the function might be called by a high-privilege536      user.537     </p><p>538      If you cannot set the <code class="varname">search_path</code> to contain only539      secure schemas, assume that each unqualified name could resolve to an540      object that a malicious user has defined.  Beware of constructs that541      depend on <code class="varname">search_path</code> implicitly; for542      example, <code class="token">IN</code>543      and <code class="literal">CASE <em class="replaceable"><code>expression</code></em> WHEN</code>544      always select an operator using the search path.  In their place, use545      <code class="literal">OPERATOR(<em class="replaceable"><code>schema</code></em>.=) ANY</code>546      and <code class="literal">CASE WHEN <em class="replaceable"><code>expression</code></em></code>.547     </p><p>548      A general-purpose extension usually should not assume that it's been549      installed into a secure schema, which means that even schema-qualified550      references to its own objects are not entirely risk-free.  For551      example, if the extension has defined a552      function <code class="literal">myschema.myfunc(bigint)</code> then a call such553      as <code class="literal">myschema.myfunc(42)</code> could be captured by a554      hostile function <code class="literal">myschema.myfunc(integer)</code>.  Be555      careful that the data types of function and operator parameters exactly556      match the declared argument types, using explicit casts where necessary.557     </p></div><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-SCRIPTS"><div class="titlepage"><div><div><h4 class="title">38.17.6.2. Security Considerations for Extension Scripts <a href="#EXTEND-EXTENSIONS-SECURITY-SCRIPTS" class="id_link">#</a></h4></div></div></div><p>558      An extension installation or update script should be written to guard559      against search-path-based attacks occurring when the script executes.560      If an object reference in the script can be made to resolve to some561      other object than the script author intended, then a compromise might562      occur immediately, or later when the mis-defined extension object is563      used.564     </p><p>565      DDL commands such as <code class="command">CREATE FUNCTION</code>566      and <code class="command">CREATE OPERATOR CLASS</code> are generally secure,567      but beware of any command having a general-purpose expression as a568      component.  For example, <code class="command">CREATE VIEW</code> needs to be569      vetted, as does a <code class="literal">DEFAULT</code> expression570      in <code class="command">CREATE FUNCTION</code>.571     </p><p>572      Sometimes an extension script might need to execute general-purpose573      SQL, for example to make catalog adjustments that aren't possible via574      DDL.  Be careful to execute such commands with a575      secure <code class="varname">search_path</code>; do <span class="emphasis"><em>not</em></span>576      trust the path provided by <code class="command">CREATE/ALTER EXTENSION</code>577      to be secure.  Best practice is to temporarily578      set <code class="varname">search_path</code> to <code class="literal">'pg_catalog,579      pg_temp'</code> and insert references to the extension's580      installation schema explicitly where needed.  (This practice might581      also be helpful for creating views.)  Examples can be found in582      the <code class="filename">contrib</code> modules in583      the <span class="productname">PostgreSQL</span> source code distribution.584     </p><p>585      Cross-extension references are extremely difficult to make fully586      secure, partially because of uncertainty about which schema the other587      extension is in.  The hazards are reduced if both extensions are588      installed in the same schema, because then a hostile object cannot be589      placed ahead of the referenced extension in the installation-time590      <code class="varname">search_path</code>.  However, no mechanism currently exists591      to require that.  For now, best practice is to not mark an extension592      trusted if it depends on another one, unless that other one is always593      installed in <code class="literal">pg_catalog</code>.594     </p></div></div><div class="sect2" id="EXTEND-EXTENSIONS-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">38.17.7. Extension Example <a href="#EXTEND-EXTENSIONS-EXAMPLE" class="id_link">#</a></h3></div></div></div><p>595     Here is a complete example of an <acronym class="acronym">SQL</acronym>-only596     extension, a two-element composite type that can store any type of value597     in its slots, which are named <span class="quote">“<span class="quote">k</span>”</span> and <span class="quote">“<span class="quote">v</span>”</span>.  Non-text598     values are automatically coerced to text for storage.599    </p><p>600     The script file <code class="filename">pair--1.0.sql</code> looks like this:601 602</p><pre class="programlisting">603-- complain if script is sourced in psql, rather than via CREATE EXTENSION604\echo Use "CREATE EXTENSION pair" to load this file. \quit605 606CREATE TYPE pair AS ( k text, v text );607 608CREATE FUNCTION pair(text, text)609RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';610 611CREATE OPERATOR ~&gt; (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);612 613-- "SET search_path" is easy to get right, but qualified names perform better.614CREATE FUNCTION lower(pair)615RETURNS pair LANGUAGE SQL616AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'617SET search_path = pg_temp;618 619CREATE FUNCTION pair_concat(pair, pair)620RETURNS pair LANGUAGE SQL621AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,622               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';623 624</pre><p>625    </p><p>626     The control file <code class="filename">pair.control</code> looks like this:627 628</p><pre class="programlisting">629# pair extension630comment = 'A key/value pair data type'631default_version = '1.0'632# cannot be relocatable because of use of @extschema@633relocatable = false634</pre><p>635    </p><p>636     While you hardly need a makefile to install these two files into the637     correct directory, you could use a <code class="filename">Makefile</code> containing this:638 639</p><pre class="programlisting">640EXTENSION = pair641DATA = pair--1.0.sql642 643PG_CONFIG = pg_config644PGXS := $(shell $(PG_CONFIG) --pgxs)645include $(PGXS)646</pre><p>647 648     This makefile relies on <acronym class="acronym">PGXS</acronym>, which is described649     in <a class="xref" href="extend-pgxs.html" title="38.18. Extension Building Infrastructure">Section 38.18</a>.  The command <code class="literal">make install</code>650     will install the control and script files into the correct651     directory as reported by <span class="application">pg_config</span>.652    </p><p>653     Once the files are installed, use the654     <code class="command">CREATE EXTENSION</code> command to load the objects into655     any particular database.656    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="38.18. Extension Building Infrastructure">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.16. Interfacing Extensions to Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.18. Extension Building Infrastructure</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai