diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
| commit | 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch) | |
| tree | 0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/extend-extensions.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-15-upstream.tar.xz postgresql-15-upstream.zip | |
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/extend-extensions.html')
| -rw-r--r-- | doc/src/sgml/html/extend-extensions.html | 626 |
1 files changed, 626 insertions, 0 deletions
diff --git a/doc/src/sgml/html/extend-extensions.html b/doc/src/sgml/html/extend-extensions.html new file mode 100644 index 0000000..15d0157 --- /dev/null +++ b/doc/src/sgml/html/extend-extensions.html @@ -0,0 +1,626 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 15.4 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</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.20.11">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#id-1.8.3.20.14">38.17.4. Extension Updates</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.20.15">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> + A useful extension to <span class="productname">PostgreSQL</span> typically includes + multiple SQL objects; for example, a new data type will require new + functions, new operators, and probably new index operator classes. + It is helpful to collect all these objects into a single package + to simplify database management. <span class="productname">PostgreSQL</span> calls + such a package an <em class="firstterm">extension</em>. To define an extension, + you need at least a <em class="firstterm">script file</em> that contains the + <acronym class="acronym">SQL</acronym> commands to create the extension's objects, and a + <em class="firstterm">control file</em> that specifies a few basic properties + of the extension itself. If the extension includes C code, there + will typically also be a shared library file into which the C code + has been built. Once you have these files, a simple + <a class="link" href="sql-createextension.html" title="CREATE EXTENSION"><code class="command">CREATE EXTENSION</code></a> command loads the objects into + your database. + </p><p> + The main advantage of using an extension, rather than just running the + <acronym class="acronym">SQL</acronym> script to load a bunch of <span class="quote">“<span class="quote">loose</span>”</span> objects + into your database, is that <span class="productname">PostgreSQL</span> will then + understand that the objects of the extension go together. You can + drop all the objects with a single <a class="link" href="sql-dropextension.html" title="DROP EXTENSION"><code class="command">DROP EXTENSION</code></a> + command (no need to maintain a separate <span class="quote">“<span class="quote">uninstall</span>”</span> script). + Even more useful, <span class="application">pg_dump</span> knows that it should not + dump the individual member objects of the extension — it will + just include a <code class="command">CREATE EXTENSION</code> command in dumps, instead. + This vastly simplifies migration to a new version of the extension + that might contain more or different objects than the old version. + Note however that you must have the extension's control, script, and + other files available when loading such a dump into a new database. + </p><p> + <span class="productname">PostgreSQL</span> will not let you drop an individual object + contained in an extension, except by dropping the whole extension. + Also, while you can change the definition of an extension member object + (for example, via <code class="command">CREATE OR REPLACE FUNCTION</code> for a + function), bear in mind that the modified definition will not be dumped + by <span class="application">pg_dump</span>. Such a change is usually only sensible if + you concurrently make the same change in the extension's script file. + (But there are special provisions for tables containing configuration + data; see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES" title="38.17.3. Extension Configuration Tables">Section 38.17.3</a>.) + In production situations, it's generally better to create an extension + update script to perform changes to extension member objects. + </p><p> + The extension script may set privileges on objects that are part of the + extension, using <code class="command">GRANT</code> and <code class="command">REVOKE</code> + statements. The final set of privileges for each object (if any are set) + will be stored in the + <a class="link" href="catalog-pg-init-privs.html" title="53.28. pg_init_privs"><code class="structname">pg_init_privs</code></a> + system catalog. When <span class="application">pg_dump</span> is used, the + <code class="command">CREATE EXTENSION</code> command will be included in the dump, followed + by the set of <code class="command">GRANT</code> and <code class="command">REVOKE</code> + statements necessary to set the privileges on the objects to what they were + at the time the dump was taken. + </p><p> + <span class="productname">PostgreSQL</span> does not currently support extension scripts + issuing <code class="command">CREATE POLICY</code> or <code class="command">SECURITY LABEL</code> + statements. These are expected to be set after the extension has been + created. All RLS policies and security labels on extension objects will be + included in dumps created by <span class="application">pg_dump</span>. + </p><p> + The extension mechanism also has provisions for packaging modification + scripts that adjust the definitions of the SQL objects contained in an + extension. For example, if version 1.1 of an extension adds one function + and changes the body of another function compared to 1.0, the extension + author can provide an <em class="firstterm">update script</em> that makes just those + two changes. The <code class="command">ALTER EXTENSION UPDATE</code> command can then + be used to apply these changes and track which version of the extension + is actually installed in a given database. + </p><p> + The kinds of SQL objects that can be members of an extension are shown in + the description of <a class="link" href="sql-alterextension.html" title="ALTER EXTENSION"><code class="command">ALTER EXTENSION</code></a>. Notably, objects + that are database-cluster-wide, such as databases, roles, and tablespaces, + cannot be extension members since an extension is only known within one + database. (Although an extension script is not prohibited from creating + such objects, if it does so they will not be tracked as part of the + extension.) Also notice that while a table can be a member of an + extension, its subsidiary objects such as indexes are not directly + considered members of the extension. + Another important point is that schemas can belong to extensions, but not + vice versa: an extension as such has an unqualified name and does not + exist <span class="quote">“<span class="quote">within</span>”</span> any schema. The extension's member objects, + however, will belong to schemas whenever appropriate for their object + types. It may or may not be appropriate for an extension to own the + schema(s) its member objects are within. + </p><p> + If an extension's script creates any temporary objects (such as temp + tables), those objects are treated as extension members for the + remainder of the current session, but are automatically dropped at + session end, as any temporary object would be. This is an exception + to the rule that extension member objects cannot be dropped without + dropping the whole extension. + </p><div class="sect2" id="id-1.8.3.20.11"><div class="titlepage"><div><div><h3 class="title">38.17.1. Extension Files</h3></div></div></div><a id="id-1.8.3.20.11.2" class="indexterm"></a><p> + The <code class="command">CREATE EXTENSION</code> command relies on a control + file for each extension, which must be named the same as the extension + with a suffix of <code class="literal">.control</code>, and must be placed in the + installation's <code class="literal">SHAREDIR/extension</code> directory. There + must also be at least one <acronym class="acronym">SQL</acronym> script file, which follows the + naming pattern + <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.sql</code> + (for example, <code class="literal">foo--1.0.sql</code> for version <code class="literal">1.0</code> of + extension <code class="literal">foo</code>). By default, the script file(s) are also + placed in the <code class="literal">SHAREDIR/extension</code> directory; but the + control file can specify a different directory for the script file(s). + </p><p> + The file format for an extension control file is the same as for the + <code class="filename">postgresql.conf</code> file, namely a list of + <em class="replaceable"><code>parameter_name</code></em> <code class="literal">=</code> <em class="replaceable"><code>value</code></em> + assignments, one per line. Blank lines and comments introduced by + <code class="literal">#</code> are allowed. Be sure to quote any value that is not + a single word or number. + </p><p> + A control file can set the following parameters: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="varname">directory</code> (<code class="type">string</code>)</span></dt><dd><p> + The directory containing the extension's <acronym class="acronym">SQL</acronym> script + file(s). Unless an absolute path is given, the name is relative to + the installation's <code class="literal">SHAREDIR</code> directory. The + default behavior is equivalent to specifying + <code class="literal">directory = 'extension'</code>. + </p></dd><dt><span class="term"><code class="varname">default_version</code> (<code class="type">string</code>)</span></dt><dd><p> + The default version of the extension (the one that will be installed + if no version is specified in <code class="command">CREATE EXTENSION</code>). Although + this can be omitted, that will result in <code class="command">CREATE EXTENSION</code> + failing if no <code class="literal">VERSION</code> option appears, so you generally + don't want to do that. + </p></dd><dt><span class="term"><code class="varname">comment</code> (<code class="type">string</code>)</span></dt><dd><p> + A comment (any string) about the extension. The comment is applied + when initially creating an extension, but not during extension updates + (since that might override user-added comments). Alternatively, + the extension's comment can be set by writing + a <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a> command in the script file. + </p></dd><dt><span class="term"><code class="varname">encoding</code> (<code class="type">string</code>)</span></dt><dd><p> + The character set encoding used by the script file(s). This should + be specified if the script files contain any non-ASCII characters. + Otherwise the files will be assumed to be in the database encoding. + </p></dd><dt><span class="term"><code class="varname">module_pathname</code> (<code class="type">string</code>)</span></dt><dd><p> + The value of this parameter will be substituted for each occurrence + of <code class="literal">MODULE_PATHNAME</code> in the script file(s). If it is not + set, no substitution is made. Typically, this is set to + <code class="literal">$libdir/<em class="replaceable"><code>shared_library_name</code></em></code> and + then <code class="literal">MODULE_PATHNAME</code> is used in <code class="command">CREATE + FUNCTION</code> commands for C-language functions, so that the script + files do not need to hard-wire the name of the shared library. + </p></dd><dt><span class="term"><code class="varname">requires</code> (<code class="type">string</code>)</span></dt><dd><p> + A list of names of extensions that this extension depends on, + for example <code class="literal">requires = 'foo, bar'</code>. Those + extensions must be installed before this one can be installed. + </p></dd><dt><span class="term"><code class="varname">superuser</code> (<code class="type">boolean</code>)</span></dt><dd><p> + If this parameter is <code class="literal">true</code> (which is the default), + only superusers can create the extension or update it to a new + version (but see also <code class="varname">trusted</code>, below). + If it is set to <code class="literal">false</code>, just the privileges + required to execute the commands in the installation or update script + are required. + This should normally be set to <code class="literal">true</code> if any of the + script commands require superuser privileges. (Such commands would + fail anyway, but it's more user-friendly to give the error up front.) + </p></dd><dt><span class="term"><code class="varname">trusted</code> (<code class="type">boolean</code>)</span></dt><dd><p> + This parameter, if set to <code class="literal">true</code> (which is not the + default), allows some non-superusers to install an extension that + has <code class="varname">superuser</code> set to <code class="literal">true</code>. + Specifically, installation will be permitted for anyone who has + <code class="literal">CREATE</code> privilege on the current database. + When the user executing <code class="command">CREATE EXTENSION</code> is not + a superuser but is allowed to install by virtue of this parameter, + then the installation or update script is run as the bootstrap + superuser, not as the calling user. + This parameter is irrelevant if <code class="varname">superuser</code> is + <code class="literal">false</code>. + Generally, this should not be set true for extensions that could + allow access to otherwise-superuser-only abilities, such as + file system access. + Also, marking an extension trusted requires significant extra effort + to write the extension's installation and update script(s) securely; + see <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY" title="38.17.6. Security Considerations for Extensions">Section 38.17.6</a>. + </p></dd><dt><span class="term"><code class="varname">relocatable</code> (<code class="type">boolean</code>)</span></dt><dd><p> + An extension is <em class="firstterm">relocatable</em> if it is possible to move + its contained objects into a different schema after initial creation + of the extension. The default is <code class="literal">false</code>, i.e., the + extension is not relocatable. + 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. + </p></dd><dt><span class="term"><code class="varname">schema</code> (<code class="type">string</code>)</span></dt><dd><p> + This parameter can only be set for non-relocatable extensions. + It forces the extension to be loaded into exactly the named schema + and not any other. + The <code class="varname">schema</code> parameter is consulted only when + initially creating an extension, not during extension updates. + 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. + </p></dd></dl></div><p> + In addition to the primary control file + <code class="literal"><em class="replaceable"><code>extension</code></em>.control</code>, + an extension can have secondary control files named in the style + <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.control</code>. + If supplied, these must be located in the script file directory. + Secondary control files follow the same format as the primary control + file. Any parameters set in a secondary control file override the + primary control file when installing or updating to that version of + the extension. However, the parameters <code class="varname">directory</code> and + <code class="varname">default_version</code> cannot be set in a secondary control file. + </p><p> + An extension's <acronym class="acronym">SQL</acronym> script files can contain any SQL commands, + except for transaction control commands (<code class="command">BEGIN</code>, + <code class="command">COMMIT</code>, etc.) and commands that cannot be executed inside a + transaction block (such as <code class="command">VACUUM</code>). This is because the + script files are implicitly executed within a transaction block. + </p><p> + An extension's <acronym class="acronym">SQL</acronym> script files can also contain lines + beginning with <code class="literal">\echo</code>, which will be ignored (treated as + comments) by the extension mechanism. This provision is commonly used + to throw an error if the script file is fed to <span class="application">psql</span> + rather than being loaded via <code class="command">CREATE EXTENSION</code> (see example + script in <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE" title="38.17.7. Extension Example">Section 38.17.7</a>). + Without that, users might accidentally load the + extension's contents as <span class="quote">“<span class="quote">loose</span>”</span> objects rather than as an + extension, a state of affairs that's a bit tedious to recover from. + </p><p> + If the extension script contains the + string <code class="literal">@extowner@</code>, that string is replaced with the + (suitably quoted) name of the user calling <code class="command">CREATE + EXTENSION</code> or <code class="command">ALTER EXTENSION</code>. Typically + this feature is used by extensions that are marked trusted to assign + ownership of selected objects to the calling user rather than the + bootstrap superuser. (One should be careful about doing so, however. + For example, assigning ownership of a C-language function to a + non-superuser would create a privilege escalation path for that user.) + </p><p> + While the script files can contain any characters allowed by the specified + encoding, control files should contain only plain ASCII, because there + is no way for <span class="productname">PostgreSQL</span> to know what encoding a + control file is in. In practice this is only an issue if you want to + use non-ASCII characters in the extension's comment. Recommended + practice in that case is to not use the control file <code class="varname">comment</code> + parameter, but instead use <code class="command">COMMENT ON EXTENSION</code> + within a script file to set the comment. + </p></div><div class="sect2" id="EXTEND-EXTENSIONS-RELOCATION"><div class="titlepage"><div><div><h3 class="title">38.17.2. Extension Relocatability</h3></div></div></div><p> + Users often wish to load the objects contained in an extension into a + different schema than the extension's author had in mind. There are + three supported levels of relocatability: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + A fully relocatable extension can be moved into another schema + at any time, even after it's been loaded into a database. + This is done with the <code class="command">ALTER EXTENSION SET SCHEMA</code> + command, which automatically renames all the member objects into + the new schema. Normally, this is only possible if the extension + contains no internal assumptions about what schema any of its + objects are in. Also, the extension's objects must all be in one + schema to begin with (ignoring objects that do not belong to any + schema, such as procedural languages). Mark a fully relocatable + extension by setting <code class="literal">relocatable = true</code> in its control + file. + </p></li><li class="listitem"><p> + An extension might be relocatable during installation but not + afterwards. This is typically the case if the extension's script + file needs to reference the target schema explicitly, for example + in setting <code class="literal">search_path</code> properties for SQL functions. + For such an extension, set <code class="literal">relocatable = false</code> in its + control file, and use <code class="literal">@extschema@</code> to refer to the target + schema in the script file. All occurrences of this string will be + replaced by the actual target schema's name before the script is + executed. The user can set the target schema using the + <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>. + </p></li><li class="listitem"><p> + If the extension does not support relocation at all, set + <code class="literal">relocatable = false</code> in its control file, and also set + <code class="literal">schema</code> to the name of the intended target schema. This + will prevent use of the <code class="literal">SCHEMA</code> option of <code class="command">CREATE + EXTENSION</code>, unless it specifies the same schema named in the control + file. This choice is typically necessary if the extension contains + internal assumptions about schema names that can't be replaced by + uses of <code class="literal">@extschema@</code>. The <code class="literal">@extschema@</code> + substitution mechanism is available in this case too, although it is + of limited use since the schema name is determined by the control file. + </p></li></ul></div><p> + In all cases, the script file will be executed with + <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> initially set to point to the target + schema; that is, <code class="command">CREATE EXTENSION</code> does the equivalent of + this: +</p><pre class="programlisting"> +SET LOCAL search_path TO @extschema@, pg_temp; +</pre><p> + This allows the objects created by the script file to go into the target + schema. The script file can change <code class="varname">search_path</code> if it wishes, + but that is generally undesirable. <code class="varname">search_path</code> is restored + to its previous setting upon completion of <code class="command">CREATE EXTENSION</code>. + </p><p> + The target schema is determined by the <code class="varname">schema</code> parameter in + the control file if that is given, otherwise by the <code class="literal">SCHEMA</code> + option of <code class="command">CREATE EXTENSION</code> if that is given, otherwise the + current default object creation schema (the first one in the caller's + <code class="varname">search_path</code>). When the control file <code class="varname">schema</code> + parameter is used, the target schema will be created if it doesn't + already exist, but in the other two cases it must already exist. + </p><p> + If any prerequisite extensions are listed in <code class="varname">requires</code> + in the control file, their target schemas are added to the initial + setting of <code class="varname">search_path</code>, following the new + extension's target schema. This allows their objects to be visible to + the new extension's script file. + </p><p> + For security, <code class="literal">pg_temp</code> is automatically appended to + the end of <code class="varname">search_path</code> in all cases. + </p><p> + Although a non-relocatable extension can contain objects spread across + multiple schemas, it is usually desirable to place all the objects meant + for external use into a single schema, which is considered the extension's + target schema. Such an arrangement works conveniently with the default + setting of <code class="varname">search_path</code> during creation of dependent + extensions. + </p></div><div class="sect2" id="EXTEND-EXTENSIONS-CONFIG-TABLES"><div class="titlepage"><div><div><h3 class="title">38.17.3. Extension Configuration Tables</h3></div></div></div><p> + Some extensions include configuration tables, which contain data that + might be added or changed by the user after installation of the + extension. Ordinarily, if a table is part of an extension, neither + the table's definition nor its content will be dumped by + <span class="application">pg_dump</span>. But that behavior is undesirable for a + configuration table; any data changes made by the user need to be + included in dumps, or the extension will behave differently after a dump + and restore. + </p><a id="id-1.8.3.20.13.3" class="indexterm"></a><p> + To solve this problem, an extension's script file can mark a table + or a sequence it has created as a configuration relation, which will + cause <span class="application">pg_dump</span> to include the table's or the sequence's + contents (not its definition) in dumps. To do that, call the function + <code class="function">pg_extension_config_dump(regclass, text)</code> after creating the + table or the sequence, for example +</p><pre class="programlisting"> +CREATE TABLE my_config (key text, value text); +CREATE SEQUENCE my_config_seq; + +SELECT pg_catalog.pg_extension_config_dump('my_config', ''); +SELECT pg_catalog.pg_extension_config_dump('my_config_seq', ''); +</pre><p> + Any number of tables or sequences can be marked this way. Sequences + associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns can + be marked as well. + </p><p> + When the second argument of <code class="function">pg_extension_config_dump</code> is + an empty string, the entire contents of the table are dumped by + <span class="application">pg_dump</span>. This is usually only correct if the table + is initially empty as created by the extension script. If there is + a mixture of initial data and user-provided data in the table, + the second argument of <code class="function">pg_extension_config_dump</code> provides + a <code class="literal">WHERE</code> condition that selects the data to be dumped. + For example, you might do +</p><pre class="programlisting"> +CREATE TABLE my_config (key text, value text, standard_entry boolean); + +SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry'); +</pre><p> + and then make sure that <code class="structfield">standard_entry</code> is true only + in the rows created by the extension's script. + </p><p> + For sequences, the second argument of <code class="function">pg_extension_config_dump</code> + has no effect. + </p><p> + More complicated situations, such as initially-provided rows that might + be modified by users, can be handled by creating triggers on the + configuration table to ensure that modified rows are marked correctly. + </p><p> + You can alter the filter condition associated with a configuration table + by calling <code class="function">pg_extension_config_dump</code> again. (This would + typically be useful in an extension update script.) The only way to mark + a table as no longer a configuration table is to dissociate it from the + extension with <code class="command">ALTER EXTENSION ... DROP TABLE</code>. + </p><p> + Note that foreign key relationships between these tables will dictate the + order in which the tables are dumped out by pg_dump. Specifically, pg_dump + will attempt to dump the referenced-by table before the referencing table. + As the foreign key relationships are set up at CREATE EXTENSION time (prior + to data being loaded into the tables) circular dependencies are not + supported. When circular dependencies exist, the data will still be dumped + out but the dump will not be able to be restored directly and user + intervention will be required. + </p><p> + Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns + need to be directly marked to dump their state. Marking their parent + relation is not enough for this purpose. + </p></div><div class="sect2" id="id-1.8.3.20.14"><div class="titlepage"><div><div><h3 class="title">38.17.4. Extension Updates</h3></div></div></div><p> + One advantage of the extension mechanism is that it provides convenient + ways to manage updates to the SQL commands that define an extension's + objects. This is done by associating a version name or number with + each released version of the extension's installation script. + In addition, if you want users to be able to update their databases + dynamically from one version to the next, you should provide + <em class="firstterm">update scripts</em> that make the necessary changes to go from + one version to the next. Update scripts have names following the pattern + <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> + (for example, <code class="literal">foo--1.0--1.1.sql</code> contains the commands to modify + version <code class="literal">1.0</code> of extension <code class="literal">foo</code> into version + <code class="literal">1.1</code>). + </p><p> + Given that a suitable update script is available, the command + <code class="command">ALTER EXTENSION UPDATE</code> will update an installed extension + to the specified new version. The update script is run in the same + environment that <code class="command">CREATE EXTENSION</code> provides for installation + scripts: in particular, <code class="varname">search_path</code> is set up in the same + way, and any new objects created by the script are automatically added + to the extension. Also, if the script chooses to drop extension member + objects, they are automatically dissociated from the extension. + </p><p> + If an extension has secondary control files, the control parameters + that are used for an update script are those associated with the script's + target (new) version. + </p><p> + <code class="command">ALTER EXTENSION</code> is able to execute sequences of update + script files to achieve a requested update. For example, if only + <code class="literal">foo--1.0--1.1.sql</code> and <code class="literal">foo--1.1--2.0.sql</code> are + available, <code class="command">ALTER EXTENSION</code> will apply them in sequence if an + update to version <code class="literal">2.0</code> is requested when <code class="literal">1.0</code> is + currently installed. + </p><p> + <span class="productname">PostgreSQL</span> doesn't assume anything about the properties + of version names: for example, it does not know whether <code class="literal">1.1</code> + follows <code class="literal">1.0</code>. It just matches up the available version names + and follows the path that requires applying the fewest update scripts. + (A version name can actually be any string that doesn't contain + <code class="literal">--</code> or leading or trailing <code class="literal">-</code>.) + </p><p> + Sometimes it is useful to provide <span class="quote">“<span class="quote">downgrade</span>”</span> scripts, for + example <code class="literal">foo--1.1--1.0.sql</code> to allow reverting the changes + associated with version <code class="literal">1.1</code>. If you do that, be careful + of the possibility that a downgrade script might unexpectedly + get applied because it yields a shorter path. The risky case is where + there is a <span class="quote">“<span class="quote">fast path</span>”</span> update script that jumps ahead several + versions as well as a downgrade script to the fast path's start point. + It might take fewer steps to apply the downgrade and then the fast + path than to move ahead one version at a time. If the downgrade script + drops any irreplaceable objects, this will yield undesirable results. + </p><p> + To check for unexpected update paths, use this command: +</p><pre class="programlisting"> +SELECT * FROM pg_extension_update_paths('<em class="replaceable"><code>extension_name</code></em>'); +</pre><p> + This shows each pair of distinct known version names for the specified + extension, together with the update path sequence that would be taken to + get from the source version to the target version, or <code class="literal">NULL</code> if + there is no available update path. The path is shown in textual form + with <code class="literal">--</code> separators. You can use + <code class="literal">regexp_split_to_array(path,'--')</code> if you prefer an array + format. + </p></div><div class="sect2" id="id-1.8.3.20.15"><div class="titlepage"><div><div><h3 class="title">38.17.5. Installing Extensions Using Update Scripts</h3></div></div></div><p> + An extension that has been around for awhile will probably exist in + several versions, for which the author will need to write update scripts. + For example, if you have released a <code class="literal">foo</code> extension in + versions <code class="literal">1.0</code>, <code class="literal">1.1</code>, and <code class="literal">1.2</code>, there + should be update scripts <code class="filename">foo--1.0--1.1.sql</code> + and <code class="filename">foo--1.1--1.2.sql</code>. + Before <span class="productname">PostgreSQL</span> 10, it was necessary to also create + new script files <code class="filename">foo--1.1.sql</code> and <code class="filename">foo--1.2.sql</code> + that directly build the newer extension versions, or else the newer + versions could not be installed directly, only by + installing <code class="literal">1.0</code> and then updating. That was tedious and + duplicative, but now it's unnecessary, because <code class="command">CREATE + EXTENSION</code> can follow update chains automatically. + For example, if only the script + files <code class="filename">foo--1.0.sql</code>, <code class="filename">foo--1.0--1.1.sql</code>, + and <code class="filename">foo--1.1--1.2.sql</code> are available then a request to + install version <code class="literal">1.2</code> is honored by running those three + scripts in sequence. The processing is the same as if you'd first + installed <code class="literal">1.0</code> and then updated to <code class="literal">1.2</code>. + (As with <code class="command">ALTER EXTENSION UPDATE</code>, if multiple pathways are + available then the shortest is preferred.) Arranging an extension's + script files in this style can reduce the amount of maintenance effort + needed to produce small updates. + </p><p> + If you use secondary (version-specific) control files with an extension + maintained in this style, keep in mind that each version needs a control + file even if it has no stand-alone installation script, as that control + file will determine how the implicit update to that version is performed. + For example, if <code class="filename">foo--1.0.control</code> specifies <code class="literal">requires + = 'bar'</code> but <code class="literal">foo</code>'s other control files do not, the + extension's dependency on <code class="literal">bar</code> will be dropped when updating + from <code class="literal">1.0</code> to another version. + </p></div><div class="sect2" id="EXTEND-EXTENSIONS-SECURITY"><div class="titlepage"><div><div><h3 class="title">38.17.6. Security Considerations for Extensions</h3></div></div></div><p> + Widely-distributed extensions should assume little about the database + they occupy. Therefore, it's appropriate to write functions provided + by an extension in a secure style that cannot be compromised by + search-path-based attacks. + </p><p> + An extension that has the <code class="varname">superuser</code> property set to + true must also consider security hazards for the actions taken within + its installation and update scripts. It is not terribly difficult for + a malicious user to create trojan-horse objects that will compromise + later execution of a carelessly-written extension script, allowing that + user to acquire superuser privileges. + </p><p> + If an extension is marked <code class="varname">trusted</code>, then its + installation schema can be selected by the installing user, who might + intentionally use an insecure schema in hopes of gaining superuser + privileges. Therefore, a trusted extension is extremely exposed from a + security standpoint, and all its script commands must be carefully + examined to ensure that no compromise is possible. + </p><p> + Advice about writing functions securely is provided in + <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 advice + about writing installation scripts securely is provided in + <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>. + </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</h4></div></div></div><p> + SQL-language and PL-language functions provided by extensions are at + risk of search-path-based attacks when they are executed, since + parsing of these functions occurs at execution time not creation time. + </p><p> + The <a class="link" href="sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" title="Writing SECURITY DEFINER Functions Safely"><code class="command">CREATE + FUNCTION</code></a> reference page contains advice about + writing <code class="literal">SECURITY DEFINER</code> functions safely. It's + good practice to apply those techniques for any function provided by + an extension, since the function might be called by a high-privilege + user. + </p><p> + If you cannot set the <code class="varname">search_path</code> to contain only + secure schemas, assume that each unqualified name could resolve to an + object that a malicious user has defined. Beware of constructs that + depend on <code class="varname">search_path</code> implicitly; for + example, <code class="token">IN</code> + and <code class="literal">CASE <em class="replaceable"><code>expression</code></em> WHEN</code> + always select an operator using the search path. In their place, use + <code class="literal">OPERATOR(<em class="replaceable"><code>schema</code></em>.=) ANY</code> + and <code class="literal">CASE WHEN <em class="replaceable"><code>expression</code></em></code>. + </p><p> + A general-purpose extension usually should not assume that it's been + installed into a secure schema, which means that even schema-qualified + references to its own objects are not entirely risk-free. For + example, if the extension has defined a + function <code class="literal">myschema.myfunc(bigint)</code> then a call such + as <code class="literal">myschema.myfunc(42)</code> could be captured by a + hostile function <code class="literal">myschema.myfunc(integer)</code>. Be + careful that the data types of function and operator parameters exactly + match the declared argument types, using explicit casts where necessary. + </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</h4></div></div></div><p> + An extension installation or update script should be written to guard + against search-path-based attacks occurring when the script executes. + If an object reference in the script can be made to resolve to some + other object than the script author intended, then a compromise might + occur immediately, or later when the mis-defined extension object is + used. + </p><p> + DDL commands such as <code class="command">CREATE FUNCTION</code> + and <code class="command">CREATE OPERATOR CLASS</code> are generally secure, + but beware of any command having a general-purpose expression as a + component. For example, <code class="command">CREATE VIEW</code> needs to be + vetted, as does a <code class="literal">DEFAULT</code> expression + in <code class="command">CREATE FUNCTION</code>. + </p><p> + Sometimes an extension script might need to execute general-purpose + SQL, for example to make catalog adjustments that aren't possible via + DDL. Be careful to execute such commands with a + secure <code class="varname">search_path</code>; do <span class="emphasis"><em>not</em></span> + trust the path provided by <code class="command">CREATE/ALTER EXTENSION</code> + to be secure. Best practice is to temporarily + set <code class="varname">search_path</code> to <code class="literal">'pg_catalog, + pg_temp'</code> and insert references to the extension's + installation schema explicitly where needed. (This practice might + also be helpful for creating views.) Examples can be found in + the <code class="filename">contrib</code> modules in + the <span class="productname">PostgreSQL</span> source code distribution. + </p><p> + Cross-extension references are extremely difficult to make fully + secure, partially because of uncertainty about which schema the other + extension is in. The hazards are reduced if both extensions are + installed in the same schema, because then a hostile object cannot be + placed ahead of the referenced extension in the installation-time + <code class="varname">search_path</code>. However, no mechanism currently exists + to require that. For now, best practice is to not mark an extension + trusted if it depends on another one, unless that other one is always + installed in <code class="literal">pg_catalog</code>. + </p></div></div><div class="sect2" id="EXTEND-EXTENSIONS-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">38.17.7. Extension Example</h3></div></div></div><p> + Here is a complete example of an <acronym class="acronym">SQL</acronym>-only + extension, a two-element composite type that can store any type of value + 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 + values are automatically coerced to text for storage. + </p><p> + The script file <code class="filename">pair--1.0.sql</code> looks like this: + +</p><pre class="programlisting"> +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pair" to load this file. \quit + +CREATE TYPE pair AS ( k text, v text ); + +CREATE FUNCTION pair(text, text) +RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;'; + +CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair); + +-- "SET search_path" is easy to get right, but qualified names perform better. +CREATE FUNCTION lower(pair) +RETURNS pair LANGUAGE SQL +AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;' +SET search_path = pg_temp; + +CREATE FUNCTION pair_concat(pair, pair) +RETURNS pair LANGUAGE SQL +AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k, + $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;'; + +</pre><p> + </p><p> + The control file <code class="filename">pair.control</code> looks like this: + +</p><pre class="programlisting"> +# pair extension +comment = 'A key/value pair data type' +default_version = '1.0' +# cannot be relocatable because of use of @extschema@ +relocatable = false +</pre><p> + </p><p> + While you hardly need a makefile to install these two files into the + correct directory, you could use a <code class="filename">Makefile</code> containing this: + +</p><pre class="programlisting"> +EXTENSION = pair +DATA = pair--1.0.sql + +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +</pre><p> + + This makefile relies on <acronym class="acronym">PGXS</acronym>, which is described + 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> + will install the control and script files into the correct + directory as reported by <span class="application">pg_config</span>. + </p><p> + Once the files are installed, use the + <code class="command">CREATE EXTENSION</code> command to load the objects into + any particular database. + </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 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.18. Extension Building Infrastructure</td></tr></table></div></body></html>
\ No newline at end of file |