Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Suivre 1
Ajouter aux favoris 0
Wiki 捐赠
gooderp18绿色标准版
Vous ne pouvez pas sélectionner plus de 25 sujets Les noms de sujets doivent commencer par une lettre ou un nombre, peuvent contenir des tirets ('-') et peuvent comporter jusqu'à 35 caractères.
Branche: master
Branches Tags
${ item.name }
Créer la branche ${ searchTerm }
de 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/extend-extensions.html

617 lignes
47KB
Brut Lien permanent Annotations Historique 

  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>37.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 V1.79.1" /><link rel="prev" href="xindex.html" title="37.16. Interfacing Extensions to Indexes" /><link rel="next" href="extend-pgxs.html" title="37.18. Extension Building Infrastructure" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.17. Packaging Related Objects into an Extension</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xindex.html" title="37.16. Interfacing Extensions to Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 37. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="extend-pgxs.html" title="37.18. Extension Building Infrastructure">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="EXTEND-EXTENSIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.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">37.17.1. Extension Files</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION">37.17.2. Extension Relocatability</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES">37.17.3. Extension Configuration Tables</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.20.14">37.17.4. Extension Updates</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#id-1.8.3.20.15">37.17.5. Installing Extensions Using Update Scripts</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY">37.17.6. Security Considerations for Extensions</a></span></dt><dt><span class="sect2"><a href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE">37.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="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> command loads the objects into

  16.     your database.

  17.    </p><p>

  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="xref" href="sql-dropextension.html" title="DROP EXTENSION"><span class="refentrytitle">DROP EXTENSION</span></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.

  31.    </p><p>

  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="37.17.3. Extension Configuration Tables">Section 37.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.

  43.    </p><p>

  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)

  47.     will be stored in the

  48.     <a class="link" href="catalog-pg-init-privs.html" title="51.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.

  54.    </p><p>

  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>.

  60.    </p><p>

  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.

  69.    </p><p>

  70.     The kinds of SQL objects that can be members of an extension are shown in

  71.     the description of <a class="xref" href="sql-alterextension.html" title="ALTER EXTENSION"><span class="refentrytitle">ALTER EXTENSION</span></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.

  85.    </p><p>

  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="id-1.8.3.20.11"><div class="titlepage"><div><div><h3 class="title">37.17.1. Extension Files</h3></div></div></div><a id="id-1.8.3.20.11.2" class="indexterm"></a><p>

  93.      The <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> 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

  98.      naming pattern

  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).

  104.     </p><p>

  105.      The file format for an extension control file is the same as for the

  106.      <code class="filename">postgresql.conf</code> file, namely a list of

  107.      <em class="replaceable"><code>parameter_name</code></em> <code class="literal">=</code> <em class="replaceable"><code>value</code></em>

  108.      assignments, one per line.  Blank lines and comments introduced by

  109.      <code class="literal">#</code> are allowed.  Be sure to quote any value that is not

  110.      a single word or number.

  111.     </p><p>

  112.      A control file can set the following parameters:

  113.     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="varname">directory</code> (<code class="type">string</code>)</span></dt><dd><p>

  114.         The directory containing the extension's <acronym class="acronym">SQL</acronym> script

  115.         file(s).  Unless an absolute path is given, the name is relative to

  116.         the installation's <code class="literal">SHAREDIR</code> directory.  The

  117.         default behavior is equivalent to specifying

  118.         <code class="literal">directory = 'extension'</code>.

  119.        </p></dd><dt><span class="term"><code class="varname">default_version</code> (<code class="type">string</code>)</span></dt><dd><p>

  120.         The default version of the extension (the one that will be installed

  121.         if no version is specified in <code class="command">CREATE EXTENSION</code>).  Although

  122.         this can be omitted, that will result in <code class="command">CREATE EXTENSION</code>

  123.         failing if no <code class="literal">VERSION</code> option appears, so you generally

  124.         don't want to do that.

  125.        </p></dd><dt><span class="term"><code class="varname">comment</code> (<code class="type">string</code>)</span></dt><dd><p>

  126.         A comment (any string) about the extension.  The comment is applied

  127.         when initially creating an extension, but not during extension updates

  128.         (since that might override user-added comments).  Alternatively,

  129.         the extension's comment can be set by writing

  130.         a <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a> command in the script file.

  131.        </p></dd><dt><span class="term"><code class="varname">encoding</code> (<code class="type">string</code>)</span></dt><dd><p>

  132.         The character set encoding used by the script file(s).  This should

  133.         be specified if the script files contain any non-ASCII characters.

  134.         Otherwise the files will be assumed to be in the database encoding.

  135.        </p></dd><dt><span class="term"><code class="varname">module_pathname</code> (<code class="type">string</code>)</span></dt><dd><p>

  136.         The value of this parameter will be substituted for each occurrence

  137.         of <code class="literal">MODULE_PATHNAME</code> in the script file(s).  If it is not

  138.         set, no substitution is made.  Typically, this is set to

  139.         <code class="literal">$libdir/<em class="replaceable"><code>shared_library_name</code></em></code> and

  140.         then <code class="literal">MODULE_PATHNAME</code> is used in <code class="command">CREATE

  141.         FUNCTION</code> commands for C-language functions, so that the script

  142.         files do not need to hard-wire the name of the shared library.

  143.        </p></dd><dt><span class="term"><code class="varname">requires</code> (<code class="type">string</code>)</span></dt><dd><p>

  144.         A list of names of extensions that this extension depends on,

  145.         for example <code class="literal">requires = 'foo, bar'</code>.  Those

  146.         extensions must be installed before this one can be installed.

  147.        </p></dd><dt><span class="term"><code class="varname">superuser</code> (<code class="type">boolean</code>)</span></dt><dd><p>

  148.         If this parameter is <code class="literal">true</code> (which is the default),

  149.         only superusers can create the extension or update it to a new

  150.         version.  If it is set to <code class="literal">false</code>, just the privileges

  151.         required to execute the commands in the installation or update script

  152.         are required.

  153.        </p></dd><dt><span class="term"><code class="varname">relocatable</code> (<code class="type">boolean</code>)</span></dt><dd><p>

  154.         An extension is <em class="firstterm">relocatable</em> if it is possible to move

  155.         its contained objects into a different schema after initial creation

  156.         of the extension.  The default is <code class="literal">false</code>, i.e. the

  157.         extension is not relocatable.

  158.         See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="37.17.2. Extension Relocatability">Section 37.17.2</a> for more information.

  159.        </p></dd><dt><span class="term"><code class="varname">schema</code> (<code class="type">string</code>)</span></dt><dd><p>

  160.         This parameter can only be set for non-relocatable extensions.

  161.         It forces the extension to be loaded into exactly the named schema

  162.         and not any other.

  163.         The <code class="varname">schema</code> parameter is consulted only when

  164.         initially creating an extension, not during extension updates.

  165.         See <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION" title="37.17.2. Extension Relocatability">Section 37.17.2</a> for more information.

  166.        </p></dd></dl></div><p>

  167.      In addition to the primary control file

  168.      <code class="literal"><em class="replaceable"><code>extension</code></em>.control</code>,

  169.      an extension can have secondary control files named in the style

  170.      <code class="literal"><em class="replaceable"><code>extension</code></em>--<em class="replaceable"><code>version</code></em>.control</code>.

  171.      If supplied, these must be located in the script file directory.

  172.      Secondary control files follow the same format as the primary control

  173.      file.  Any parameters set in a secondary control file override the

  174.      primary control file when installing or updating to that version of

  175.      the extension.  However, the parameters <code class="varname">directory</code> and

  176.      <code class="varname">default_version</code> cannot be set in a secondary control file.

  177.     </p><p>

  178.      An extension's <acronym class="acronym">SQL</acronym> script files can contain any SQL commands,

  179.      except for transaction control commands (<code class="command">BEGIN</code>,

  180.      <code class="command">COMMIT</code>, etc) and commands that cannot be executed inside a

  181.      transaction block (such as <code class="command">VACUUM</code>).  This is because the

  182.      script files are implicitly executed within a transaction block.

  183.     </p><p>

  184.      An extension's <acronym class="acronym">SQL</acronym> script files can also contain lines

  185.      beginning with <code class="literal">\echo</code>, which will be ignored (treated as

  186.      comments) by the extension mechanism.  This provision is commonly used

  187.      to throw an error if the script file is fed to <span class="application">psql</span>

  188.      rather than being loaded via <code class="command">CREATE EXTENSION</code> (see example

  189.      script in <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE" title="37.17.7. Extension Example">Section 37.17.7</a>).

  190.      Without that, users might accidentally load the

  191.      extension's contents as <span class="quote">“<span class="quote">loose</span>”</span> objects rather than as an

  192.      extension, a state of affairs that's a bit tedious to recover from.

  193.     </p><p>

  194.      While the script files can contain any characters allowed by the specified

  195.      encoding, control files should contain only plain ASCII, because there

  196.      is no way for <span class="productname">PostgreSQL</span> to know what encoding a

  197.      control file is in.  In practice this is only an issue if you want to

  198.      use non-ASCII characters in the extension's comment.  Recommended

  199.      practice in that case is to not use the control file <code class="varname">comment</code>

  200.      parameter, but instead use <code class="command">COMMENT ON EXTENSION</code>

  201.      within a script file to set the comment.

  202.     </p></div><div class="sect2" id="EXTEND-EXTENSIONS-RELOCATION"><div class="titlepage"><div><div><h3 class="title">37.17.2. Extension Relocatability</h3></div></div></div><p>

  203.      Users often wish to load the objects contained in an extension into a

  204.      different schema than the extension's author had in mind.  There are

  205.      three supported levels of relocatability:

  206.     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  207.        A fully relocatable extension can be moved into another schema

  208.        at any time, even after it's been loaded into a database.

  209.        This is done with the <code class="command">ALTER EXTENSION SET SCHEMA</code>

  210.        command, which automatically renames all the member objects into

  211.        the new schema.  Normally, this is only possible if the extension

  212.        contains no internal assumptions about what schema any of its

  213.        objects are in.  Also, the extension's objects must all be in one

  214.        schema to begin with (ignoring objects that do not belong to any

  215.        schema, such as procedural languages).  Mark a fully relocatable

  216.        extension by setting <code class="literal">relocatable = true</code> in its control

  217.        file.

  218.       </p></li><li class="listitem"><p>

  219.        An extension might be relocatable during installation but not

  220.        afterwards.  This is typically the case if the extension's script

  221.        file needs to reference the target schema explicitly, for example

  222.        in setting <code class="literal">search_path</code> properties for SQL functions.

  223.        For such an extension, set <code class="literal">relocatable = false</code> in its

  224.        control file, and use <code class="literal">@extschema@</code> to refer to the target

  225.        schema in the script file.  All occurrences of this string will be

  226.        replaced by the actual target schema's name before the script is

  227.        executed.  The user can set the target schema using the

  228.        <code class="literal">SCHEMA</code> option of <code class="command">CREATE EXTENSION</code>.

  229.       </p></li><li class="listitem"><p>

  230.        If the extension does not support relocation at all, set

  231.        <code class="literal">relocatable = false</code> in its control file, and also set

  232.        <code class="literal">schema</code> to the name of the intended target schema.  This

  233.        will prevent use of the <code class="literal">SCHEMA</code> option of <code class="command">CREATE

  234.        EXTENSION</code>, unless it specifies the same schema named in the control

  235.        file.  This choice is typically necessary if the extension contains

  236.        internal assumptions about schema names that can't be replaced by

  237.        uses of <code class="literal">@extschema@</code>.  The <code class="literal">@extschema@</code>

  238.        substitution mechanism is available in this case too, although it is

  239.        of limited use since the schema name is determined by the control file.

  240.       </p></li></ul></div><p>

  241.      In all cases, the script file will be executed with

  242.      <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> initially set to point to the target

  243.      schema; that is, <code class="command">CREATE EXTENSION</code> does the equivalent of

  244.      this:

  245. </p><pre class="programlisting">

  246. SET LOCAL search_path TO @extschema@, pg_temp;

  247. </pre><p>

  248.      This allows the objects created by the script file to go into the target

  249.      schema.  The script file can change <code class="varname">search_path</code> if it wishes,

  250.      but that is generally undesirable.  <code class="varname">search_path</code> is restored

  251.      to its previous setting upon completion of <code class="command">CREATE EXTENSION</code>.

  252.     </p><p>

  253.      The target schema is determined by the <code class="varname">schema</code> parameter in

  254.      the control file if that is given, otherwise by the <code class="literal">SCHEMA</code>

  255.      option of <code class="command">CREATE EXTENSION</code> if that is given, otherwise the

  256.      current default object creation schema (the first one in the caller's

  257.      <code class="varname">search_path</code>).  When the control file <code class="varname">schema</code>

  258.      parameter is used, the target schema will be created if it doesn't

  259.      already exist, but in the other two cases it must already exist.

  260.     </p><p>

  261.      If any prerequisite extensions are listed in <code class="varname">requires</code>

  262.      in the control file, their target schemas are added to the initial

  263.      setting of <code class="varname">search_path</code>, following the new

  264.      extension's target schema.  This allows their objects to be visible to

  265.      the new extension's script file.

  266.     </p><p>

  267.      For security, <code class="literal">pg_temp</code> is automatically appended to

  268.      the end of <code class="varname">search_path</code> in all cases.

  269.     </p><p>

  270.      Although a non-relocatable extension can contain objects spread across

  271.      multiple schemas, it is usually desirable to place all the objects meant

  272.      for external use into a single schema, which is considered the extension's

  273.      target schema.  Such an arrangement works conveniently with the default

  274.      setting of <code class="varname">search_path</code> during creation of dependent

  275.      extensions.

  276.     </p></div><div class="sect2" id="EXTEND-EXTENSIONS-CONFIG-TABLES"><div class="titlepage"><div><div><h3 class="title">37.17.3. Extension Configuration Tables</h3></div></div></div><p>

  277.      Some extensions include configuration tables, which contain data that

  278.      might be added or changed by the user after installation of the

  279.      extension.  Ordinarily, if a table is part of an extension, neither

  280.      the table's definition nor its content will be dumped by

  281.      <span class="application">pg_dump</span>.  But that behavior is undesirable for a

  282.      configuration table; any data changes made by the user need to be

  283.      included in dumps, or the extension will behave differently after a dump

  284.      and reload.

  285.     </p><a id="id-1.8.3.20.13.3" class="indexterm"></a><p>

  286.      To solve this problem, an extension's script file can mark a table

  287.      or a sequence it has created as a configuration relation, which will

  288.      cause <span class="application">pg_dump</span> to include the table's or the sequence's

  289.      contents (not its definition) in dumps.  To do that, call the function

  290.      <code class="function">pg_extension_config_dump(regclass, text)</code> after creating the

  291.      table or the sequence, for example

  292. </p><pre class="programlisting">

  293. CREATE TABLE my_config (key text, value text);

  294. CREATE SEQUENCE my_config_seq;

  295. 

  296. SELECT pg_catalog.pg_extension_config_dump('my_config', '');

  297. SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');

  298. </pre><p>

  299.      Any number of tables or sequences can be marked this way. Sequences

  300.      associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns can

  301.      be marked as well.

  302.     </p><p>

  303.      When the second argument of <code class="function">pg_extension_config_dump</code> is

  304.      an empty string, the entire contents of the table are dumped by

  305.      <span class="application">pg_dump</span>.  This is usually only correct if the table

  306.      is initially empty as created by the extension script.  If there is

  307.      a mixture of initial data and user-provided data in the table,

  308.      the second argument of <code class="function">pg_extension_config_dump</code> provides

  309.      a <code class="literal">WHERE</code> condition that selects the data to be dumped.

  310.      For example, you might do

  311. </p><pre class="programlisting">

  312. CREATE TABLE my_config (key text, value text, standard_entry boolean);

  313. 

  314. SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');

  315. </pre><p>

  316.      and then make sure that <code class="structfield">standard_entry</code> is true only

  317.      in the rows created by the extension's script.

  318.     </p><p>

  319.      For sequences, the second argument of <code class="function">pg_extension_config_dump</code>

  320.      has no effect.

  321.     </p><p>

  322.      More complicated situations, such as initially-provided rows that might

  323.      be modified by users, can be handled by creating triggers on the

  324.      configuration table to ensure that modified rows are marked correctly.

  325.     </p><p>

  326.      You can alter the filter condition associated with a configuration table

  327.      by calling <code class="function">pg_extension_config_dump</code> again.  (This would

  328.      typically be useful in an extension update script.)  The only way to mark

  329.      a table as no longer a configuration table is to dissociate it from the

  330.      extension with <code class="command">ALTER EXTENSION ... DROP TABLE</code>.

  331.     </p><p>

  332.      Note that foreign key relationships between these tables will dictate the

  333.      order in which the tables are dumped out by pg_dump.  Specifically, pg_dump

  334.      will attempt to dump the referenced-by table before the referencing table.

  335.      As the foreign key relationships are set up at CREATE EXTENSION time (prior

  336.      to data being loaded into the tables) circular dependencies are not

  337.      supported.  When circular dependencies exist, the data will still be dumped

  338.      out but the dump will not be able to be restored directly and user

  339.      intervention will be required.

  340.     </p><p>

  341.      Sequences associated with <code class="type">serial</code> or <code class="type">bigserial</code> columns

  342.      need to be directly marked to dump their state. Marking their parent

  343.      relation is not enough for this purpose.

  344.     </p></div><div class="sect2" id="id-1.8.3.20.14"><div class="titlepage"><div><div><h3 class="title">37.17.4. Extension Updates</h3></div></div></div><p>

  345.      One advantage of the extension mechanism is that it provides convenient

  346.      ways to manage updates to the SQL commands that define an extension's

  347.      objects.  This is done by associating a version name or number with

  348.      each released version of the extension's installation script.

  349.      In addition, if you want users to be able to update their databases

  350.      dynamically from one version to the next, you should provide

  351.      <em class="firstterm">update scripts</em> that make the necessary changes to go from

  352.      one version to the next.  Update scripts have names following the pattern

  353.      <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>

  354.      (for example, <code class="literal">foo--1.0--1.1.sql</code> contains the commands to modify

  355.      version <code class="literal">1.0</code> of extension <code class="literal">foo</code> into version

  356.      <code class="literal">1.1</code>).

  357.     </p><p>

  358.      Given that a suitable update script is available, the command

  359.      <code class="command">ALTER EXTENSION UPDATE</code> will update an installed extension

  360.      to the specified new version.  The update script is run in the same

  361.      environment that <code class="command">CREATE EXTENSION</code> provides for installation

  362.      scripts: in particular, <code class="varname">search_path</code> is set up in the same

  363.      way, and any new objects created by the script are automatically added

  364.      to the extension.  Also, if the script chooses to drop extension member

  365.      objects, they are automatically dissociated from the extension.

  366.     </p><p>

  367.      If an extension has secondary control files, the control parameters

  368.      that are used for an update script are those associated with the script's

  369.      target (new) version.

  370.     </p><p>

  371.      The update mechanism can be used to solve an important special case:

  372.      converting a <span class="quote">“<span class="quote">loose</span>”</span> collection of objects into an extension.

  373.      Before the extension mechanism was added to

  374.      <span class="productname">PostgreSQL</span> (in 9.1), many people wrote

  375.      extension modules that simply created assorted unpackaged objects.

  376.      Given an existing database containing such objects, how can we convert

  377.      the objects into a properly packaged extension?  Dropping them and then

  378.      doing a plain <code class="command">CREATE EXTENSION</code> is one way, but it's not

  379.      desirable if the objects have dependencies (for example, if there are

  380.      table columns of a data type created by the extension).  The way to fix

  381.      this situation is to create an empty extension, then use <code class="command">ALTER

  382.      EXTENSION ADD</code> to attach each pre-existing object to the extension,

  383.      then finally create any new objects that are in the current extension

  384.      version but were not in the unpackaged release.  <code class="command">CREATE

  385.      EXTENSION</code> supports this case with its <code class="literal">FROM</code> <em class="replaceable"><code>old_version</code></em> option, which causes it to not run the

  386.      normal installation script for the target version, but instead the update

  387.      script named

  388.      <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>.

  389.      The choice of the dummy version name to use as <em class="replaceable"><code>old_version</code></em> is up to the extension author, though

  390.      <code class="literal">unpackaged</code> is a common convention.  If you have multiple

  391.      prior versions you need to be able to update into extension style, use

  392.      multiple dummy version names to identify them.

  393.     </p><p>

  394.      <code class="command">ALTER EXTENSION</code> is able to execute sequences of update

  395.      script files to achieve a requested update.  For example, if only

  396.      <code class="literal">foo--1.0--1.1.sql</code> and <code class="literal">foo--1.1--2.0.sql</code> are

  397.      available, <code class="command">ALTER EXTENSION</code> will apply them in sequence if an

  398.      update to version <code class="literal">2.0</code> is requested when <code class="literal">1.0</code> is

  399.      currently installed.

  400.     </p><p>

  401.      <span class="productname">PostgreSQL</span> doesn't assume anything about the properties

  402.      of version names: for example, it does not know whether <code class="literal">1.1</code>

  403.      follows <code class="literal">1.0</code>.  It just matches up the available version names

  404.      and follows the path that requires applying the fewest update scripts.

  405.      (A version name can actually be any string that doesn't contain

  406.      <code class="literal">--</code> or leading or trailing <code class="literal">-</code>.)

  407.     </p><p>

  408.      Sometimes it is useful to provide <span class="quote">“<span class="quote">downgrade</span>”</span> scripts, for

  409.      example <code class="literal">foo--1.1--1.0.sql</code> to allow reverting the changes

  410.      associated with version <code class="literal">1.1</code>.  If you do that, be careful

  411.      of the possibility that a downgrade script might unexpectedly

  412.      get applied because it yields a shorter path.  The risky case is where

  413.      there is a <span class="quote">“<span class="quote">fast path</span>”</span> update script that jumps ahead several

  414.      versions as well as a downgrade script to the fast path's start point.

  415.      It might take fewer steps to apply the downgrade and then the fast

  416.      path than to move ahead one version at a time.  If the downgrade script

  417.      drops any irreplaceable objects, this will yield undesirable results.

  418.     </p><p>

  419.      To check for unexpected update paths, use this command:

  420. </p><pre class="programlisting">

  421. SELECT * FROM pg_extension_update_paths('<em class="replaceable"><code>extension_name</code></em>');

  422. </pre><p>

  423.      This shows each pair of distinct known version names for the specified

  424.      extension, together with the update path sequence that would be taken to

  425.      get from the source version to the target version, or <code class="literal">NULL</code> if

  426.      there is no available update path.  The path is shown in textual form

  427.      with <code class="literal">--</code> separators.  You can use

  428.      <code class="literal">regexp_split_to_array(path,'--')</code> if you prefer an array

  429.      format.

  430.     </p></div><div class="sect2" id="id-1.8.3.20.15"><div class="titlepage"><div><div><h3 class="title">37.17.5. Installing Extensions Using Update Scripts</h3></div></div></div><p>

  431.      An extension that has been around for awhile will probably exist in

  432.      several versions, for which the author will need to write update scripts.

  433.      For example, if you have released a <code class="literal">foo</code> extension in

  434.      versions <code class="literal">1.0</code>, <code class="literal">1.1</code>, and <code class="literal">1.2</code>, there

  435.      should be update scripts <code class="filename">foo--1.0--1.1.sql</code>

  436.      and <code class="filename">foo--1.1--1.2.sql</code>.

  437.      Before <span class="productname">PostgreSQL</span> 10, it was necessary to also create

  438.      new script files <code class="filename">foo--1.1.sql</code> and <code class="filename">foo--1.2.sql</code>

  439.      that directly build the newer extension versions, or else the newer

  440.      versions could not be installed directly, only by

  441.      installing <code class="literal">1.0</code> and then updating.  That was tedious and

  442.      duplicative, but now it's unnecessary, because <code class="command">CREATE

  443.      EXTENSION</code> can follow update chains automatically.

  444.      For example, if only the script

  445.      files <code class="filename">foo--1.0.sql</code>, <code class="filename">foo--1.0--1.1.sql</code>,

  446.      and <code class="filename">foo--1.1--1.2.sql</code> are available then a request to

  447.      install version <code class="literal">1.2</code> is honored by running those three

  448.      scripts in sequence.  The processing is the same as if you'd first

  449.      installed <code class="literal">1.0</code> and then updated to <code class="literal">1.2</code>.

  450.      (As with <code class="command">ALTER EXTENSION UPDATE</code>, if multiple pathways are

  451.      available then the shortest is preferred.)  Arranging an extension's

  452.      script files in this style can reduce the amount of maintenance effort

  453.      needed to produce small updates.

  454.     </p><p>

  455.      If you use secondary (version-specific) control files with an extension

  456.      maintained in this style, keep in mind that each version needs a control

  457.      file even if it has no stand-alone installation script, as that control

  458.      file will determine how the implicit update to that version is performed.

  459.      For example, if <code class="filename">foo--1.0.control</code> specifies <code class="literal">requires

  460.      = 'bar'</code> but <code class="literal">foo</code>'s other control files do not, the

  461.      extension's dependency on <code class="literal">bar</code> will be dropped when updating

  462.      from <code class="literal">1.0</code> to another version.

  463.     </p></div><div class="sect2" id="EXTEND-EXTENSIONS-SECURITY"><div class="titlepage"><div><div><h3 class="title">37.17.6. Security Considerations for Extensions</h3></div></div></div><p>

  464.      Widely-distributed extensions should assume little about the database

  465.      they occupy.  Therefore, it's appropriate to write functions provided

  466.      by an extension in a secure style that cannot be compromised by

  467.      search-path-based attacks.

  468.     </p><p>

  469.      An extension that has the <code class="varname">superuser</code> property set to

  470.      true must also consider security hazards for the actions taken within

  471.      its installation and update scripts.  It is not terribly difficult for

  472.      a malicious user to create trojan-horse objects that will compromise

  473.      later execution of a carelessly-written extension script, allowing that

  474.      user to acquire superuser privileges.

  475.     </p><p>

  476.      Advice about writing functions securely is provided in

  477.      <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-FUNCS" title="37.17.6.1. Security Considerations for Extension Functions">Section 37.17.6.1</a> below, and advice

  478.      about writing installation scripts securely is provided in

  479.      <a class="xref" href="extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-SCRIPTS" title="37.17.6.2. Security Considerations for Extension Scripts">Section 37.17.6.2</a>.

  480.     </p><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-FUNCS"><div class="titlepage"><div><div><h4 class="title">37.17.6.1. Security Considerations for Extension Functions</h4></div></div></div><p>

  481.       SQL-language and PL-language functions provided by extensions are at

  482.       risk of search-path-based attacks when they are executed, since

  483.       parsing of these functions occurs at execution time not creation time.

  484.      </p><p>

  485.       The <a class="link" href="sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" title="Writing SECURITY DEFINER Functions Safely"><code class="command">CREATE

  486.       FUNCTION</code></a> reference page contains advice about

  487.       writing <code class="literal">SECURITY DEFINER</code> functions safely.  It's

  488.       good practice to apply those techniques for any function provided by

  489.       an extension, since the function might be called by a high-privilege

  490.       user.

  491.      </p><p>

  492.       If you cannot set the <code class="varname">search_path</code> to contain only

  493.       secure schemas, assume that each unqualified name could resolve to an

  494.       object that a malicious user has defined.  Beware of constructs that

  495.       depend on <code class="varname">search_path</code> implicitly; for

  496.       example, <code class="token">IN</code>

  497.       and <code class="literal">CASE <em class="replaceable"><code>expression</code></em> WHEN</code>

  498.       always select an operator using the search path.  In their place, use

  499.       <code class="literal">OPERATOR(<em class="replaceable"><code>schema</code></em>.=) ANY</code>

  500.       and <code class="literal">CASE WHEN <em class="replaceable"><code>expression</code></em></code>.

  501.      </p><p>

  502.       A general-purpose extension usually should not assume that it's been

  503.       installed into a secure schema, which means that even schema-qualified

  504.       references to its own objects are not entirely risk-free.  For

  505.       example, if the extension has defined a

  506.       function <code class="literal">myschema.myfunc(bigint)</code> then a call such

  507.       as <code class="literal">myschema.myfunc(42)</code> could be captured by a

  508.       hostile function <code class="literal">myschema.myfunc(integer)</code>.  Be

  509.       careful that the data types of function and operator parameters exactly

  510.       match the declared argument types, using explicit casts where necessary.

  511.      </p></div><div class="sect3" id="EXTEND-EXTENSIONS-SECURITY-SCRIPTS"><div class="titlepage"><div><div><h4 class="title">37.17.6.2. Security Considerations for Extension Scripts</h4></div></div></div><p>

  512.       An extension installation or update script should be written to guard

  513.       against search-path-based attacks occurring when the script executes.

  514.       If an object reference in the script can be made to resolve to some

  515.       other object than the script author intended, then a compromise might

  516.       occur immediately, or later when the mis-defined extension object is

  517.       used.

  518.      </p><p>

  519.       DDL commands such as <code class="command">CREATE FUNCTION</code>

  520.       and <code class="command">CREATE OPERATOR CLASS</code> are generally secure,

  521.       but beware of any command having a general-purpose expression as a

  522.       component.  For example, <code class="command">CREATE VIEW</code> needs to be

  523.       vetted, as does a <code class="literal">DEFAULT</code> expression

  524.       in <code class="command">CREATE FUNCTION</code>.

  525.      </p><p>

  526.       Sometimes an extension script might need to execute general-purpose

  527.       SQL, for example to make catalog adjustments that aren't possible via

  528.       DDL.  Be careful to execute such commands with a

  529.       secure <code class="varname">search_path</code>; do <span class="emphasis"><em>not</em></span>

  530.       trust the path provided by <code class="command">CREATE/ALTER EXTENSION</code>

  531.       to be secure.  Best practice is to temporarily

  532.       set <code class="varname">search_path</code> to <code class="literal">'pg_catalog,

  533.       pg_temp'</code> and insert references to the extension's

  534.       installation schema explicitly where needed.  (This practice might

  535.       also be helpful for creating views.)  Examples can be found in

  536.       the <code class="filename">contrib</code> modules in

  537.       the <span class="productname">PostgreSQL</span> source code distribution.

  538.      </p><p>

  539.       Cross-extension references are extremely difficult to make fully

  540.       secure, partially because of uncertainty about which schema the other

  541.       extension is in.  The hazards are reduced if both extensions are

  542.       installed in the same schema, because then a hostile object cannot be

  543.       placed ahead of the referenced extension in the installation-time

  544.       <code class="varname">search_path</code>.  However, no mechanism currently exists

  545.       to require that.

  546.      </p><p>

  547.       Do <span class="emphasis"><em>not</em></span> use <code class="command">CREATE OR REPLACE

  548.       FUNCTION</code>, except in an update script that must change the

  549.       definition of a function that is known to be an extension member

  550.       already.  (Likewise for other <code class="literal">OR REPLACE</code> options.)

  551.       Using <code class="literal">OR REPLACE</code> unnecessarily not only has a risk

  552.       of accidentally overwriting someone else's function, but it creates a

  553.       security hazard since the overwritten function would still be owned by

  554.       its original owner, who could modify it.

  555.      </p></div></div><div class="sect2" id="EXTEND-EXTENSIONS-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">37.17.7. Extension Example</h3></div></div></div><p>

  556.      Here is a complete example of an <acronym class="acronym">SQL</acronym>-only

  557.      extension, a two-element composite type that can store any type of value

  558.      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

  559.      values are automatically coerced to text for storage.

  560.     </p><p>

  561.      The script file <code class="filename">pair--1.0.sql</code> looks like this:

  562. 

  563. </p><pre class="programlisting">

  564. -- complain if script is sourced in psql, rather than via CREATE EXTENSION

  565. \echo Use "CREATE EXTENSION pair" to load this file. \quit

  566. 

  567. CREATE TYPE pair AS ( k text, v text );

  568. 

  569. CREATE FUNCTION pair(text, text)

  570. RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';

  571. 

  572. CREATE OPERATOR ~&gt; (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);

  573. 

  574. -- "SET search_path" is easy to get right, but qualified names perform better.

  575. CREATE FUNCTION lower(pair)

  576. RETURNS pair LANGUAGE SQL

  577. AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'

  578. SET search_path = pg_temp;

  579. 

  580. CREATE FUNCTION pair_concat(pair, pair)

  581. RETURNS pair LANGUAGE SQL

  582. AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,

  583.                $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

  584. 

  585. </pre><p>

  586.     </p><p>

  587.      The control file <code class="filename">pair.control</code> looks like this:

  588. 

  589. </p><pre class="programlisting">

  590. # pair extension

  591. comment = 'A key/value pair data type'

  592. default_version = '1.0'

  593. # cannot be relocatable because of use of @extschema@

  594. relocatable = false

  595. </pre><p>

  596.     </p><p>

  597.      While you hardly need a makefile to install these two files into the

  598.      correct directory, you could use a <code class="filename">Makefile</code> containing this:

  599. 

  600. </p><pre class="programlisting">

  601. EXTENSION = pair

  602. DATA = pair--1.0.sql

  603. 

  604. PG_CONFIG = pg_config

  605. PGXS := $(shell $(PG_CONFIG) --pgxs)

  606. include $(PGXS)

  607. </pre><p>

  608. 

  609.      This makefile relies on <acronym class="acronym">PGXS</acronym>, which is described

  610.      in <a class="xref" href="extend-pgxs.html" title="37.18. Extension Building Infrastructure">Section 37.18</a>.  The command <code class="literal">make install</code>

  611.      will install the control and script files into the correct

  612.      directory as reported by <span class="application">pg_config</span>.

  613.     </p><p>

  614.      Once the files are installed, use the

  615.      <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a> command to load the objects into

  616.      any particular database.

  617.     </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="extend-pgxs.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.16. Interfacing Extensions to Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 37.18. Extension Building Infrastructure</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1