summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/extend-extensions.html
blob: 6a3377108b35d7885deeabaa6d85e012d0f37ea9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
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.6 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 ~&gt; (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.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.18. Extension Building Infrastructure</td></tr></table></div></body></html>