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>36.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="36.16. Interfacing Extensions to Indexes" /><link rel="next" href="extend-pgxs.html" title="36.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">36.17. Packaging Related Objects into an Extension</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xindex.html" title="36.16. Interfacing Extensions to Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 36. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="36.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">36.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">36.17.1. Extension Files</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION">36.17.2. Extension Relocatability</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES">36.17.3. Extension Configuration Tables</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-UPDATES">36.17.4. Extension Updates</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-UPDATE-SCRIPTS">36.17.5. Installing Extensions Using Update Scripts</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY">36.17.6. Security Considerations for Extensions</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE">36.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 includes
4 multiple SQL objects; for example, a new data type will require new
5 functions, new operators, and probably new index operator classes.
6 It is helpful to collect all these objects into a single package
7 to simplify database management. <span class="productname">PostgreSQL</span> calls
8 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 the
10 <acronym class="acronym">SQL</acronym> commands to create the extension's objects, and a
11 <em class="firstterm">control file</em> that specifies a few basic properties
12 of the extension itself. If the extension includes C code, there
13 will typically also be a shared library file into which the C code
14 has been built. Once you have these files, a simple
15 <a class="link" href="sql-createextension.html" title="CREATE EXTENSION"><code class="command">CREATE EXTENSION</code></a> command loads the objects into
18 The main advantage of using an extension, rather than just running the
19 <acronym class="acronym">SQL</acronym> script to load a bunch of <span class="quote">“<span class="quote">loose</span>”</span> objects
20 into your database, is that <span class="productname">PostgreSQL</span> will then
21 understand that the objects of the extension go together. You can
22 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 not
25 dump the individual member objects of the extension — it will
26 just include a <code class="command">CREATE EXTENSION</code> command in dumps, instead.
27 This vastly simplifies migration to a new version of the extension
28 that might contain more or different objects than the old version.
29 Note however that you must have the extension's control, script, and
30 other files available when loading such a dump into a new database.
32 <span class="productname">PostgreSQL</span> will not let you drop an individual object
33 contained in an extension, except by dropping the whole extension.
34 Also, while you can change the definition of an extension member object
35 (for example, via <code class="command">CREATE OR REPLACE FUNCTION</code> for a
36 function), bear in mind that the modified definition will not be dumped
37 by <span class="application">pg_dump</span>. Such a change is usually only sensible if
38 you concurrently make the same change in the extension's script file.
39 (But there are special provisions for tables containing configuration
40 data; see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES" title="36.17.3. Extension Configuration Tables">Section 36.17.3</a>.)
41 In production situations, it's generally better to create an extension
42 update script to perform changes to extension member objects.
44 The extension script may set privileges on objects that are part of the
45 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)
48 <a class="link" href="catalog-pg-init-privs.html" title="52.28. pg_init_privs"><code class="structname">pg_init_privs</code></a>
49 system catalog. When <span class="application">pg_dump</span> is used, the
50 <code class="command">CREATE EXTENSION</code> command will be included in the dump, followed
51 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 were
53 at the time the dump was taken.
55 <span class="productname">PostgreSQL</span> does not currently support extension scripts
56 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 been
58 created. All RLS policies and security labels on extension objects will be
59 included in dumps created by <span class="application">pg_dump</span>.
61 The extension mechanism also has provisions for packaging modification
62 scripts that adjust the definitions of the SQL objects contained in an
63 extension. For example, if version 1.1 of an extension adds one function
64 and changes the body of another function compared to 1.0, the extension
65 author can provide an <em class="firstterm">update script</em> that makes just those
66 two changes. The <code class="command">ALTER EXTENSION UPDATE</code> command can then
67 be used to apply these changes and track which version of the extension
68 is actually installed in a given database.
70 The kinds of SQL objects that can be members of an extension are shown in
71 the description of <a class="link" href="sql-alterextension.html" title="ALTER EXTENSION"><code class="command">ALTER EXTENSION</code></a>. Notably, objects
72 that are database-cluster-wide, such as databases, roles, and tablespaces,
73 cannot be extension members since an extension is only known within one
74 database. (Although an extension script is not prohibited from creating
75 such objects, if it does so they will not be tracked as part of the
76 extension.) Also notice that while a table can be a member of an
77 extension, its subsidiary objects such as indexes are not directly
78 considered members of the extension.
79 Another important point is that schemas can belong to extensions, but not
80 vice versa: an extension as such has an unqualified name and does not
81 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 object
83 types. It may or may not be appropriate for an extension to own the
84 schema(s) its member objects are within.
86 If an extension's script creates any temporary objects (such as temp
87 tables), those objects are treated as extension members for the
88 remainder of the current session, but are automatically dropped at
89 session end, as any temporary object would be. This is an exception
90 to the rule that extension member objects cannot be dropped without
91 dropping the whole extension.
92 </p><div class="sect2" id="EXTEND-EXTENSIONS-FILES"><div class="titlepage"><div><div><h3 class="title">36.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 control
94 file for each extension, which must be named the same as the extension
95 with a suffix of <code class="literal">.control</code>, and must be placed in the
96 installation's <code class="literal">SHAREDIR/extension</code> directory. There
97 must also be at least one <acronym class="acronym">SQL</acronym> script file, which follows the
99 <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> of
101 extension <code class="literal">foo</code>). By default, the script file(s) are also
102 placed in the <code class="literal">SHAREDIR/extension</code> directory; but the
103 control file can specify a different directory for the script file(s).
105 Additional locations for extension control files can be configured using
106 the parameter <a class="xref" href="runtime-config-client.html#GUC-EXTENSION-CONTROL-PATH">extension_control_path</a>.
108 The file format for an extension control file is the same as for the
109 <code class="filename">postgresql.conf</code> file, namely a list of
110 <em class="replaceable"><code>parameter_name</code></em> <code class="literal">=</code> <em class="replaceable"><code>value</code></em>
111 assignments, one per line. Blank lines and comments introduced by
112 <code class="literal">#</code> are allowed. Be sure to quote any value that is not
113 a single word or number.
115 A control file can set the following parameters:
116 </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>
117 The directory containing the extension's <acronym class="acronym">SQL</acronym> script
118 file(s). Unless an absolute path is given, the name is relative to
119 the directory where the control file was found. By default,
120 the script files are looked for in the same directory where the
121 control file was found.
122 </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>
123 The default version of the extension (the one that will be installed
124 if no version is specified in <code class="command">CREATE EXTENSION</code>). Although
125 this can be omitted, that will result in <code class="command">CREATE EXTENSION</code>
126 failing if no <code class="literal">VERSION</code> option appears, so you generally
127 don't want to do that.
128 </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>
129 A comment (any string) about the extension. The comment is applied
130 when initially creating an extension, but not during extension updates
131 (since that might override user-added comments). Alternatively,
132 the extension's comment can be set by writing
133 a <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a> command in the script file.
134 </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>
135 The character set encoding used by the script file(s). This should
136 be specified if the script files contain any non-ASCII characters.
137 Otherwise the files will be assumed to be in the database encoding.
138 </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>
139 The value of this parameter will be substituted for each occurrence
140 of <code class="literal">MODULE_PATHNAME</code> in the script file(s). If it is not
141 set, no substitution is made. Typically, this is set to just
142 <code class="literal"><em class="replaceable"><code>shared_library_name</code></em></code> and
143 then <code class="literal">MODULE_PATHNAME</code> is used in <code class="command">CREATE
144 FUNCTION</code> commands for C-language functions, so that the script
145 files do not need to hard-wire the name of the shared library.
146 </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>
147 A list of names of extensions that this extension depends on,
148 for example <code class="literal">requires = 'foo, bar'</code>. Those
149 extensions must be installed before this one can be installed.
150 </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>
151 A list of names of extensions that this extension depends on that
152 should be barred from changing their schemas via <code class="command">ALTER
153 EXTENSION ... SET SCHEMA</code>.
154 This is needed if this extension's script references the name
155 of a required extension's schema (using
156 the <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code>
157 syntax) in a way that cannot track renames.
158 </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>
159 If this parameter is <code class="literal">true</code> (which is the default),
160 only superusers can create the extension or update it to a new
161 version (but see also <code class="varname">trusted</code>, below).
162 If it is set to <code class="literal">false</code>, just the privileges
163 required to execute the commands in the installation or update script
165 This should normally be set to <code class="literal">true</code> if any of the
166 script commands require superuser privileges. (Such commands would
167 fail anyway, but it's more user-friendly to give the error up front.)
168 </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>
169 This parameter, if set to <code class="literal">true</code> (which is not the
170 default), allows some non-superusers to install an extension that
171 has <code class="varname">superuser</code> set to <code class="literal">true</code>.
172 Specifically, installation will be permitted for anyone who has
173 <code class="literal">CREATE</code> privilege on the current database.
174 When the user executing <code class="command">CREATE EXTENSION</code> is not
175 a superuser but is allowed to install by virtue of this parameter,
176 then the installation or update script is run as the bootstrap
177 superuser, not as the calling user.
178 This parameter is irrelevant if <code class="varname">superuser</code> is
179 <code class="literal">false</code>.
180 Generally, this should not be set true for extensions that could
181 allow access to otherwise-superuser-only abilities, such as
183 Also, marking an extension trusted requires significant extra effort
184 to write the extension's installation and update script(s) securely;
185 see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY" title="36.17.6. Security Considerations for Extensions">Section 36.17.6</a>.
186 </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>
187 An extension is <em class="firstterm">relocatable</em> if it is possible to move
188 its contained objects into a different schema after initial creation
189 of the extension. The default is <code class="literal">false</code>, i.e., the
190 extension is not relocatable.
191 See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="36.17.2. Extension Relocatability">Section 36.17.2</a> for more information.
192 </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>
193 This parameter can only be set for non-relocatable extensions.
194 It forces the extension to be loaded into exactly the named schema
196 The <code class="varname">schema</code> parameter is consulted only when
197 initially creating an extension, not during extension updates.
198 See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="36.17.2. Extension Relocatability">Section 36.17.2</a> for more information.
199 </p></dd></dl></div><p>
200 In addition to the primary control file
201 <code class="literal"><em class="replaceable"><code>extension</code></em>.control</code>,
202 an extension can have secondary control files named in the style
203 <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.control</code>.
204 If supplied, these must be located in the script file directory.
205 Secondary control files follow the same format as the primary control
206 file. Any parameters set in a secondary control file override the
207 primary control file when installing or updating to that version of
208 the extension. However, the parameters <code class="varname">directory</code> and
209 <code class="varname">default_version</code> cannot be set in a secondary control file.
211 An extension's <acronym class="acronym">SQL</acronym> script files can contain any SQL commands,
212 except for transaction control commands (<code class="command">BEGIN</code>,
213 <code class="command">COMMIT</code>, etc.) and commands that cannot be executed inside a
214 transaction block (such as <code class="command">VACUUM</code>). This is because the
215 script files are implicitly executed within a transaction block.
217 An extension's <acronym class="acronym">SQL</acronym> script files can also contain lines
218 beginning with <code class="literal">\echo</code>, which will be ignored (treated as
219 comments) by the extension mechanism. This provision is commonly used
220 to throw an error if the script file is fed to <span class="application">psql</span>
221 rather than being loaded via <code class="command">CREATE EXTENSION</code> (see example
222 script in <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE" title="36.17.7. Extension Example">Section 36.17.7</a>).
223 Without that, users might accidentally load the
224 extension's contents as <span class="quote">“<span class="quote">loose</span>”</span> objects rather than as an
225 extension, a state of affairs that's a bit tedious to recover from.
227 If the extension script contains the
228 string <code class="literal">@extowner@</code>, that string is replaced with the
229 (suitably quoted) name of the user calling <code class="command">CREATE
230 EXTENSION</code> or <code class="command">ALTER EXTENSION</code>. Typically
231 this feature is used by extensions that are marked trusted to assign
232 ownership of selected objects to the calling user rather than the
233 bootstrap superuser. (One should be careful about doing so, however.
234 For example, assigning ownership of a C-language function to a
235 non-superuser would create a privilege escalation path for that user.)
237 While the script files can contain any characters allowed by the specified
238 encoding, control files should contain only plain ASCII, because there
239 is no way for <span class="productname">PostgreSQL</span> to know what encoding a
240 control file is in. In practice this is only an issue if you want to
241 use non-ASCII characters in the extension's comment. Recommended
242 practice in that case is to not use the control file <code class="varname">comment</code>
243 parameter, but instead use <code class="command">COMMENT ON EXTENSION</code>
244 within a script file to set the comment.
245 </p></div><div class="sect2" id="EXTEND-EXTENSIONS-RELOCATION"><div class="titlepage"><div><div><h3 class="title">36.17.2. Extension Relocatability <a href="#EXTEND-EXTENSIONS-RELOCATION" class="id_link">#</a></h3></div></div></div><p>
246 Users often wish to load the objects contained in an extension into a
247 different schema than the extension's author had in mind. There are
248 three supported levels of relocatability:
249 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
250 A fully relocatable extension can be moved into another schema
251 at any time, even after it's been loaded into a database.
252 This is done with the <code class="command">ALTER EXTENSION SET SCHEMA</code>
253 command, which automatically renames all the member objects into
254 the new schema. Normally, this is only possible if the extension
255 contains no internal assumptions about what schema any of its
256 objects are in. Also, the extension's objects must all be in one
257 schema to begin with (ignoring objects that do not belong to any
258 schema, such as procedural languages). Mark a fully relocatable
259 extension by setting <code class="literal">relocatable = true</code> in its control
261 </p></li><li class="listitem"><p>
262 An extension might be relocatable during installation but not
263 afterwards. This is typically the case if the extension's script
264 file needs to reference the target schema explicitly, for example
265 in setting <code class="literal">search_path</code> properties for SQL functions.
266 For such an extension, set <code class="literal">relocatable = false</code> in its
267 control file, and use <code class="literal">@extschema@</code> to refer to the target
268 schema in the script file. All occurrences of this string will be
269 replaced by the actual target schema's name (double-quoted if
270 necessary) before the script is executed. The user can set the
271 target schema using the
272 <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>.
273 </p></li><li class="listitem"><p>
274 If the extension does not support relocation at all, set
275 <code class="literal">relocatable = false</code> in its control file, and also set
276 <code class="literal">schema</code> to the name of the intended target schema. This
277 will prevent use of the <code class="literal">SCHEMA</code> option of <code class="command">CREATE
278 EXTENSION</code>, unless it specifies the same schema named in the control
279 file. This choice is typically necessary if the extension contains
280 internal assumptions about its schema name that can't be replaced by
281 uses of <code class="literal">@extschema@</code>. The <code class="literal">@extschema@</code>
282 substitution mechanism is available in this case too, although it is
283 of limited use since the schema name is determined by the control file.
284 </p></li></ul></div><p>
285 In all cases, the script file will be executed with
286 <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> initially set to point to the target
287 schema; that is, <code class="command">CREATE EXTENSION</code> does the equivalent of
289 </p><pre class="programlisting">
290 SET LOCAL search_path TO @extschema@, pg_temp;
292 This allows the objects created by the script file to go into the target
293 schema. The script file can change <code class="varname">search_path</code> if it wishes,
294 but that is generally undesirable. <code class="varname">search_path</code> is restored
295 to its previous setting upon completion of <code class="command">CREATE EXTENSION</code>.
297 The target schema is determined by the <code class="varname">schema</code> parameter in
298 the control file if that is given, otherwise by the <code class="literal">SCHEMA</code>
299 option of <code class="command">CREATE EXTENSION</code> if that is given, otherwise the
300 current default object creation schema (the first one in the caller's
301 <code class="varname">search_path</code>). When the control file <code class="varname">schema</code>
302 parameter is used, the target schema will be created if it doesn't
303 already exist, but in the other two cases it must already exist.
305 If any prerequisite extensions are listed in <code class="varname">requires</code>
306 in the control file, their target schemas are added to the initial
307 setting of <code class="varname">search_path</code>, following the new
308 extension's target schema. This allows their objects to be visible to
309 the new extension's script file.
311 For security, <code class="literal">pg_temp</code> is automatically appended to
312 the end of <code class="varname">search_path</code> in all cases.
314 Although a non-relocatable extension can contain objects spread across
315 multiple schemas, it is usually desirable to place all the objects meant
316 for external use into a single schema, which is considered the extension's
317 target schema. Such an arrangement works conveniently with the default
318 setting of <code class="varname">search_path</code> during creation of dependent
321 If an extension references objects belonging to another extension,
322 it is recommended to schema-qualify those references. To do that,
323 write <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code>
324 in the extension's script file, where <em class="replaceable"><code>name</code></em>
325 is the name of the other extension (which must be listed in this
326 extension's <code class="literal">requires</code> list). This string will be
327 replaced by the name (double-quoted if necessary) of that extension's
329 Although this notation avoids the need to make hard-wired assumptions
330 about schema names in the extension's script file, its use may embed
331 the other extension's schema name into the installed objects of this
332 extension. (Typically, that happens
333 when <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code> is
334 used inside a string literal, such as a function body or
335 a <code class="varname">search_path</code> setting. In other cases, the object
336 reference is reduced to an OID during parsing and does not require
337 subsequent lookups.) If the other extension's schema name is so
338 embedded, you should prevent the other extension from being relocated
339 after yours is installed, by adding the name of the other extension to
340 this one's <code class="literal">no_relocate</code> list.
341 </p></div><div class="sect2" id="EXTEND-EXTENSIONS-CONFIG-TABLES"><div class="titlepage"><div><div><h3 class="title">36.17.3. Extension Configuration Tables <a href="#EXTEND-EXTENSIONS-CONFIG-TABLES" class="id_link">#</a></h3></div></div></div><p>
342 Some extensions include configuration tables, which contain data that
343 might be added or changed by the user after installation of the
344 extension. Ordinarily, if a table is part of an extension, neither
345 the table's definition nor its content will be dumped by
346 <span class="application">pg_dump</span>. But that behavior is undesirable for a
347 configuration table; any data changes made by the user need to be
348 included in dumps, or the extension will behave differently after a dump
350 </p><a id="id-1.8.3.20.13.3" class="indexterm"></a><p>
351 To solve this problem, an extension's script file can mark a table
352 or a sequence it has created as a configuration relation, which will
353 cause <span class="application">pg_dump</span> to include the table's or the sequence's
354 contents (not its definition) in dumps. To do that, call the function
355 <code class="function">pg_extension_config_dump(regclass, text)</code> after creating the
356 table or the sequence, for example
357 </p><pre class="programlisting">
358 CREATE TABLE my_config (key text, value text);
359 CREATE SEQUENCE my_config_seq;
361 SELECT pg_catalog.pg_extension_config_dump('my_config', '');
362 SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');
364 Any number of tables or sequences can be marked this way. Sequences
365 associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns can
368 When the second argument of <code class="function">pg_extension_config_dump</code> is
369 an empty string, the entire contents of the table are dumped by
370 <span class="application">pg_dump</span>. This is usually only correct if the table
371 is initially empty as created by the extension script. If there is
372 a mixture of initial data and user-provided data in the table,
373 the second argument of <code class="function">pg_extension_config_dump</code> provides
374 a <code class="literal">WHERE</code> condition that selects the data to be dumped.
375 For example, you might do
376 </p><pre class="programlisting">
377 CREATE TABLE my_config (key text, value text, standard_entry boolean);
379 SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
381 and then make sure that <code class="structfield">standard_entry</code> is true only
382 in the rows created by the extension's script.
384 For sequences, the second argument of <code class="function">pg_extension_config_dump</code>
387 More complicated situations, such as initially-provided rows that might
388 be modified by users, can be handled by creating triggers on the
389 configuration table to ensure that modified rows are marked correctly.
391 You can alter the filter condition associated with a configuration table
392 by calling <code class="function">pg_extension_config_dump</code> again. (This would
393 typically be useful in an extension update script.) The only way to mark
394 a table as no longer a configuration table is to dissociate it from the
395 extension with <code class="command">ALTER EXTENSION ... DROP TABLE</code>.
397 Note that foreign key relationships between these tables will dictate the
398 order in which the tables are dumped out by pg_dump. Specifically, pg_dump
399 will attempt to dump the referenced-by table before the referencing table.
400 As the foreign key relationships are set up at CREATE EXTENSION time (prior
401 to data being loaded into the tables) circular dependencies are not
402 supported. When circular dependencies exist, the data will still be dumped
403 out but the dump will not be able to be restored directly and user
404 intervention will be required.
406 Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns
407 need to be directly marked to dump their state. Marking their parent
408 relation is not enough for this purpose.
409 </p></div><div class="sect2" id="EXTEND-EXTENSIONS-UPDATES"><div class="titlepage"><div><div><h3 class="title">36.17.4. Extension Updates <a href="#EXTEND-EXTENSIONS-UPDATES" class="id_link">#</a></h3></div></div></div><p>
410 One advantage of the extension mechanism is that it provides convenient
411 ways to manage updates to the SQL commands that define an extension's
412 objects. This is done by associating a version name or number with
413 each released version of the extension's installation script.
414 In addition, if you want users to be able to update their databases
415 dynamically from one version to the next, you should provide
416 <em class="firstterm">update scripts</em> that make the necessary changes to go from
417 one version to the next. Update scripts have names following the pattern
418 <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>
419 (for example, <code class="literal">foo--1.0--1.1.sql</code> contains the commands to modify
420 version <code class="literal">1.0</code> of extension <code class="literal">foo</code> into version
421 <code class="literal">1.1</code>).
423 Given that a suitable update script is available, the command
424 <code class="command">ALTER EXTENSION UPDATE</code> will update an installed extension
425 to the specified new version. The update script is run in the same
426 environment that <code class="command">CREATE EXTENSION</code> provides for installation
427 scripts: in particular, <code class="varname">search_path</code> is set up in the same
428 way, and any new objects created by the script are automatically added
429 to the extension. Also, if the script chooses to drop extension member
430 objects, they are automatically dissociated from the extension.
432 If an extension has secondary control files, the control parameters
433 that are used for an update script are those associated with the script's
434 target (new) version.
436 <code class="command">ALTER EXTENSION</code> is able to execute sequences of update
437 script files to achieve a requested update. For example, if only
438 <code class="literal">foo--1.0--1.1.sql</code> and <code class="literal">foo--1.1--2.0.sql</code> are
439 available, <code class="command">ALTER EXTENSION</code> will apply them in sequence if an
440 update to version <code class="literal">2.0</code> is requested when <code class="literal">1.0</code> is
443 <span class="productname">PostgreSQL</span> doesn't assume anything about the properties
444 of version names: for example, it does not know whether <code class="literal">1.1</code>
445 follows <code class="literal">1.0</code>. It just matches up the available version names
446 and follows the path that requires applying the fewest update scripts.
447 (A version name can actually be any string that doesn't contain
448 <code class="literal">--</code> or leading or trailing <code class="literal">-</code>.)
450 Sometimes it is useful to provide <span class="quote">“<span class="quote">downgrade</span>”</span> scripts, for
451 example <code class="literal">foo--1.1--1.0.sql</code> to allow reverting the changes
452 associated with version <code class="literal">1.1</code>. If you do that, be careful
453 of the possibility that a downgrade script might unexpectedly
454 get applied because it yields a shorter path. The risky case is where
455 there is a <span class="quote">“<span class="quote">fast path</span>”</span> update script that jumps ahead several
456 versions as well as a downgrade script to the fast path's start point.
457 It might take fewer steps to apply the downgrade and then the fast
458 path than to move ahead one version at a time. If the downgrade script
459 drops any irreplaceable objects, this will yield undesirable results.
461 To check for unexpected update paths, use this command:
462 </p><pre class="programlisting">
463 SELECT * FROM pg_extension_update_paths('<em class="replaceable"><code>extension_name</code></em>');
465 This shows each pair of distinct known version names for the specified
466 extension, together with the update path sequence that would be taken to
467 get from the source version to the target version, or <code class="literal">NULL</code> if
468 there is no available update path. The path is shown in textual form
469 with <code class="literal">--</code> separators. You can use
470 <code class="literal">regexp_split_to_array(path,'--')</code> if you prefer an array
472 </p></div><div class="sect2" id="EXTEND-EXTENSIONS-UPDATE-SCRIPTS"><div class="titlepage"><div><div><h3 class="title">36.17.5. Installing Extensions Using Update Scripts <a href="#EXTEND-EXTENSIONS-UPDATE-SCRIPTS" class="id_link">#</a></h3></div></div></div><p>
473 An extension that has been around for awhile will probably exist in
474 several versions, for which the author will need to write update scripts.
475 For example, if you have released a <code class="literal">foo</code> extension in
476 versions <code class="literal">1.0</code>, <code class="literal">1.1</code>, and <code class="literal">1.2</code>, there
477 should be update scripts <code class="filename">foo--1.0--1.1.sql</code>
478 and <code class="filename">foo--1.1--1.2.sql</code>.
479 Before <span class="productname">PostgreSQL</span> 10, it was necessary to also create
480 new script files <code class="filename">foo--1.1.sql</code> and <code class="filename">foo--1.2.sql</code>
481 that directly build the newer extension versions, or else the newer
482 versions could not be installed directly, only by
483 installing <code class="literal">1.0</code> and then updating. That was tedious and
484 duplicative, but now it's unnecessary, because <code class="command">CREATE
485 EXTENSION</code> can follow update chains automatically.
486 For example, if only the script
487 files <code class="filename">foo--1.0.sql</code>, <code class="filename">foo--1.0--1.1.sql</code>,
488 and <code class="filename">foo--1.1--1.2.sql</code> are available then a request to
489 install version <code class="literal">1.2</code> is honored by running those three
490 scripts in sequence. The processing is the same as if you'd first
491 installed <code class="literal">1.0</code> and then updated to <code class="literal">1.2</code>.
492 (As with <code class="command">ALTER EXTENSION UPDATE</code>, if multiple pathways are
493 available then the shortest is preferred.) Arranging an extension's
494 script files in this style can reduce the amount of maintenance effort
495 needed to produce small updates.
497 If you use secondary (version-specific) control files with an extension
498 maintained in this style, keep in mind that each version needs a control
499 file even if it has no stand-alone installation script, as that control
500 file will determine how the implicit update to that version is performed.
501 For example, if <code class="filename">foo--1.0.control</code> specifies <code class="literal">requires
502 = 'bar'</code> but <code class="literal">foo</code>'s other control files do not, the
503 extension's dependency on <code class="literal">bar</code> will be dropped when updating
504 from <code class="literal">1.0</code> to another version.
505 </p></div><div class="sect2" id="EXTEND-EXTENSIONS-SECURITY"><div class="titlepage"><div><div><h3 class="title">36.17.6. Security Considerations for Extensions <a href="#EXTEND-EXTENSIONS-SECURITY" class="id_link">#</a></h3></div></div></div><p>
506 Widely-distributed extensions should assume little about the database
507 they occupy. Therefore, it's appropriate to write functions provided
508 by an extension in a secure style that cannot be compromised by
509 search-path-based attacks.
511 An extension that has the <code class="varname">superuser</code> property set to
512 true must also consider security hazards for the actions taken within
513 its installation and update scripts. It is not terribly difficult for
514 a malicious user to create trojan-horse objects that will compromise
515 later execution of a carelessly-written extension script, allowing that
516 user to acquire superuser privileges.
518 If an extension is marked <code class="varname">trusted</code>, then its
519 installation schema can be selected by the installing user, who might
520 intentionally use an insecure schema in hopes of gaining superuser
521 privileges. Therefore, a trusted extension is extremely exposed from a
522 security standpoint, and all its script commands must be carefully
523 examined to ensure that no compromise is possible.
525 Advice about writing functions securely is provided in
526 <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-FUNCS" title="36.17.6.1. Security Considerations for Extension Functions">Section 36.17.6.1</a> below, and advice
527 about writing installation scripts securely is provided in
528 <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-SCRIPTS" title="36.17.6.2. Security Considerations for Extension Scripts">Section 36.17.6.2</a>.
529 </p><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-FUNCS"><div class="titlepage"><div><div><h4 class="title">36.17.6.1. Security Considerations for Extension Functions <a href="#EXTEND-EXTENSIONS-SECURITY-FUNCS" class="id_link">#</a></h4></div></div></div><p>
530 SQL-language and PL-language functions provided by extensions are at
531 risk of search-path-based attacks when they are executed, since
532 parsing of these functions occurs at execution time not creation time.
534 The <a class="link" href="sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" title="Writing SECURITY DEFINER Functions Safely"><code class="command">CREATE
535 FUNCTION</code></a> reference page contains advice about
536 writing <code class="literal">SECURITY DEFINER</code> functions safely. It's
537 good practice to apply those techniques for any function provided by
538 an extension, since the function might be called by a high-privilege
541 If you cannot set the <code class="varname">search_path</code> to contain only
542 secure schemas, assume that each unqualified name could resolve to an
543 object that a malicious user has defined. Beware of constructs that
544 depend on <code class="varname">search_path</code> implicitly; for
545 example, <code class="token">IN</code>
546 and <code class="literal">CASE <em class="replaceable"><code>expression</code></em> WHEN</code>
547 always select an operator using the search path. In their place, use
548 <code class="literal">OPERATOR(<em class="replaceable"><code>schema</code></em>.=) ANY</code>
549 and <code class="literal">CASE WHEN <em class="replaceable"><code>expression</code></em></code>.
551 A general-purpose extension usually should not assume that it's been
552 installed into a secure schema, which means that even schema-qualified
553 references to its own objects are not entirely risk-free. For
554 example, if the extension has defined a
555 function <code class="literal">myschema.myfunc(bigint)</code> then a call such
556 as <code class="literal">myschema.myfunc(42)</code> could be captured by a
557 hostile function <code class="literal">myschema.myfunc(integer)</code>. Be
558 careful that the data types of function and operator parameters exactly
559 match the declared argument types, using explicit casts where necessary.
560 </p></div><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-SCRIPTS"><div class="titlepage"><div><div><h4 class="title">36.17.6.2. Security Considerations for Extension Scripts <a href="#EXTEND-EXTENSIONS-SECURITY-SCRIPTS" class="id_link">#</a></h4></div></div></div><p>
561 An extension installation or update script should be written to guard
562 against search-path-based attacks occurring when the script executes.
563 If an object reference in the script can be made to resolve to some
564 other object than the script author intended, then a compromise might
565 occur immediately, or later when the mis-defined extension object is
568 DDL commands such as <code class="command">CREATE FUNCTION</code>
569 and <code class="command">CREATE OPERATOR CLASS</code> are generally secure,
570 but beware of any command having a general-purpose expression as a
571 component. For example, <code class="command">CREATE VIEW</code> needs to be
572 vetted, as does a <code class="literal">DEFAULT</code> expression
573 in <code class="command">CREATE FUNCTION</code>.
575 Sometimes an extension script might need to execute general-purpose
576 SQL, for example to make catalog adjustments that aren't possible via
577 DDL. Be careful to execute such commands with a
578 secure <code class="varname">search_path</code>; do <span class="emphasis"><em>not</em></span>
579 trust the path provided by <code class="command">CREATE/ALTER EXTENSION</code>
580 to be secure. Best practice is to temporarily
581 set <code class="varname">search_path</code> to <code class="literal">pg_catalog,
582 pg_temp</code> and insert references to the extension's
583 installation schema explicitly where needed. (This practice might
584 also be helpful for creating views.) Examples can be found in
585 the <code class="filename">contrib</code> modules in
586 the <span class="productname">PostgreSQL</span> source code distribution.
588 Secure cross-extension references typically require schema-qualification
589 of the names of the other extension's objects, using the
590 <code class="literal">@extschema:<em class="replaceable"><code>name</code></em>@</code>
591 syntax, in addition to careful matching of argument types for functions
593 </p></div></div><div class="sect2" id="EXTEND-EXTENSIONS-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">36.17.7. Extension Example <a href="#EXTEND-EXTENSIONS-EXAMPLE" class="id_link">#</a></h3></div></div></div><p>
594 Here is a complete example of an <acronym class="acronym">SQL</acronym>-only
595 extension, a two-element composite type that can store any type of value
596 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-text
597 values are automatically coerced to text for storage.
599 The script file <code class="filename">pair--1.0.sql</code> looks like this:
601 </p><pre class="programlisting">
602 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
603 \echo Use "CREATE EXTENSION pair" to load this file. \quit
605 CREATE TYPE pair AS ( k text, v text );
607 CREATE FUNCTION pair(text, text)
608 RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';
610 CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);
612 -- "SET search_path" is easy to get right, but qualified names perform better.
613 CREATE FUNCTION lower(pair)
614 RETURNS pair LANGUAGE SQL
615 AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
616 SET search_path = pg_temp;
618 CREATE FUNCTION pair_concat(pair, pair)
619 RETURNS pair LANGUAGE SQL
620 AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
621 $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';
625 The control file <code class="filename">pair.control</code> looks like this:
627 </p><pre class="programlisting">
629 comment = 'A key/value pair data type'
630 default_version = '1.0'
631 # cannot be relocatable because of use of @extschema@
635 While you hardly need a makefile to install these two files into the
636 correct directory, you could use a <code class="filename">Makefile</code> containing this:
638 </p><pre class="programlisting">
642 PG_CONFIG = pg_config
643 PGXS := $(shell $(PG_CONFIG) --pgxs)
647 This makefile relies on <acronym class="acronym">PGXS</acronym>, which is described
648 in <a class="xref" href="extend-pgxs.html" title="36.18. Extension Building Infrastructure">Section 36.18</a>. The command <code class="literal">make install</code>
649 will install the control and script files into the correct
650 directory as reported by <span class="application">pg_config</span>.
652 Once the files are installed, use the
653 <code class="command">CREATE EXTENSION</code> command to load the objects into
654 any particular database.
655 </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="36.16. Interfacing Extensions to Indexes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="36.18. Extension Building Infrastructure">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.16. Interfacing Extensions to Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.18. Extension Building Infrastructure</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob