codekingpro/portable-devtools
115k
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 ~> (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>