登录38.17. Packaging Related Objects into an Extension#
- 38.17.1. Extension Files
- 38.17.2. Extension Relocatability
- 38.17.3. Extension Configuration Tables
- 38.17.4. Extension Updates
- 38.17.5. Installing Extensions Using Update Scripts
- 38.17.6. Security Considerations for Extensions
- 38.17.7. Extension Example
A useful extension toPostgreSQLtypically 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.PostgreSQLcalls such a package anextension. To define an extension, you need at least ascript filethat contains theSQLcommands to create the extension's objects, and acontrol filethat 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 simpleCREATE EXTENSIONcommand loads the objects into your database.
The main advantage of using an extension, rather than just running theSQLscript to load a bunch of“loose”objects into your database, is thatPostgreSQLwill then understand that the objects of the extension go together. You can drop all the objects with a singleDROP EXTENSIONcommand (no need to maintain a separate“uninstall”script). Even more useful,pg_dumpknows that it should not dump the individual member objects of the extension — it will just include aCREATE EXTENSIONcommand 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.
PostgreSQLwill 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, viaCREATE OR REPLACE FUNCTIONfor a function), bear in mind that the modified definition will not be dumped bypg_dump. 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; seeSection 38.17.3.) In production situations, it's generally better to create an extension update script to perform changes to extension member objects.
The extension script may set privileges on objects that are part of the extension, usingGRANTandREVOKEstatements. The final set of privileges for each object (if any are set) will be stored in thepg_init_privssystem catalog. Whenpg_dumpis used, theCREATE EXTENSIONcommand will be included in the dump, followed by the set ofGRANTandREVOKEstatements necessary to set the privileges on the objects to what they were at the time the dump was taken.
PostgreSQLdoes not currently support extension scripts issuingCREATE POLICYorSECURITY LABELstatements. 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 bypg_dump.
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 anupdate scriptthat makes just those two changes. TheALTER EXTENSION UPDATEcommand can then be used to apply these changes and track which version of the extension is actually installed in a given database.
The kinds of SQL objects that can be members of an extension are shown in the description ofALTER EXTENSION. 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“within”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.
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.
38.17.1. Extension Files#
TheCREATE EXTENSIONcommand relies on a control file for each extension, which must be named the same as the extension with a suffix of.control, and must be placed in the installation'sSHAREDIR/extensiondirectory. There must also be at least oneSQLscript file, which follows the naming pattern*extension*--*version*.sql(for example,foo--1.0.sqlfor version1.0of extensionfoo). By default, the script file(s) are also placed in theSHAREDIR/extensiondirectory; but the control file can specify a different directory for the script file(s).
The file format for an extension control file is the same as for thepostgresql.conffile, namely a list of*parameter_name=value*assignments, one per line. Blank lines and comments introduced by#are allowed. Be sure to quote any value that is not a single word or number.
A control file can set the following parameters:
directory(string) #The directory containing the extension's script file(s). Unless an absolute path is given, the name is relative to the installation's
SHAREDIRdirectory. The default behavior is equivalent to specifyingdirectory = 'extension'.default_version(string) #The default version of the extension (the one that will be installed if no version is specified in
CREATE EXTENSION). Although this can be omitted, that will result inCREATE EXTENSIONfailing if noVERSIONoption appears, so you generally don't want to do that.comment(string) #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 COMMENT command in the script file.
encoding(string) #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.
module_pathname(string) #The value of this parameter will be substituted for each occurrence of
MODULE_PATHNAMEin the script file(s). If it is not set, no substitution is made. Typically, this is set to$libdir/and thenshared_library_nameMODULE_PATHNAMEis used inCREATE FUNCTIONcommands for C-language functions, so that the script files do not need to hard-wire the name of the shared library.requires(string) #A list of names of extensions that this extension depends on, for example
requires = 'foo, bar'. Those extensions must be installed before this one can be installed.no_relocate(string) #A list of names of extensions that this extension depends on that should be barred from changing their schemas via
ALTER EXTENSION ... SET SCHEMA. This is needed if this extension's script references the name of a required extension's schema (using the@extschema:syntax) in a way that cannot track renames.name@superuser(boolean) #If this parameter is
true(which is the default), only superusers can create the extension or update it to a new version (but see alsotrusted, below). If it is set tofalse, just the privileges required to execute the commands in the installation or update script are required. This should normally be set totrueif 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.)trusted(boolean) #This parameter, if set to
true(which is not the default), allows some non-superusers to install an extension that hassuperuserset totrue. Specifically, installation will be permitted for anyone who hasCREATEprivilege on the current database. When the user executingCREATE EXTENSIONis 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 ifsuperuserisfalse. 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 Section 38.17.6.relocatable(boolean) #An extension is relocatable if it is possible to move its contained objects into a different schema after initial creation of the extension. The default is
false, i.e., the extension is not relocatable. See Section 38.17.2 for more information.schema(string) #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
schemaparameter is consulted only when initially creating an extension, not during extension updates. See Section 38.17.2 for more information.
In addition to the primary control file*extension*.control, an extension can have secondary control files named in the style*extension*--*version*.control. 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 parametersdirectoryanddefault_versioncannot be set in a secondary control file.
An extension'sSQLscript files can contain any SQL commands, except for transaction control commands (BEGIN,COMMIT, etc.) and commands that cannot be executed inside a transaction block (such asVACUUM). This is because the script files are implicitly executed within a transaction block.
An extension'sSQLscript files can also contain lines beginning with\echo, 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 topsqlrather than being loaded viaCREATE EXTENSION(see example script inSection 38.17.7). Without that, users might accidentally load the extension's contents as“loose”objects rather than as an extension, a state of affairs that's a bit tedious to recover from.
If the extension script contains the string@extowner@, that string is replaced with the (suitably quoted) name of the user callingCREATE EXTENSIONorALTER EXTENSION. 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.)
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 forPostgreSQLto 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 filecommentparameter, but instead useCOMMENT ON EXTENSIONwithin a script file to set the comment.
38.17.2. Extension Relocatability#
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:
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
ALTER EXTENSION SET SCHEMAcommand, 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 settingrelocatable = truein its control file.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
search_pathproperties for SQL functions. For such an extension, setrelocatable = falsein its control file, and use@extschema@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 (double-quoted if necessary) before the script is executed. The user can set the target schema using theSCHEMAoption ofCREATE EXTENSION.If the extension does not support relocation at all, set
relocatable = falsein its control file, and also setschemato the name of the intended target schema. This will prevent use of theSCHEMAoption ofCREATE EXTENSION, unless it specifies the same schema named in the control file. This choice is typically necessary if the extension contains internal assumptions about its schema name that can't be replaced by uses of@extschema@. The@extschema@substitution mechanism is available in this case too, although it is of limited use since the schema name is determined by the control file.
In all cases, the script file will be executed withsearch_pathinitially set to point to the target schema; that is,CREATE EXTENSIONdoes the equivalent of this:
SET LOCAL search_path TO @extschema@, pg_temp;This allows the objects created by the script file to go into the target schema. The script file can changesearch_pathif it wishes, but that is generally undesirable.search_pathis restored to its previous setting upon completion ofCREATE EXTENSION.
The target schema is determined by theschemaparameter in the control file if that is given, otherwise by theSCHEMAoption ofCREATE EXTENSIONif that is given, otherwise the current default object creation schema (the first one in the caller'ssearch_path). When the control fileschemaparameter is used, the target schema will be created if it doesn't already exist, but in the other two cases it must already exist.
If any prerequisite extensions are listed inrequiresin the control file, their target schemas are added to the initial setting ofsearch_path, following the new extension's target schema. This allows their objects to be visible to the new extension's script file.
For security,pg_tempis automatically appended to the end ofsearch_pathin all cases.
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 ofsearch_pathduring creation of dependent extensions.
If an extension references objects belonging to another extension, it is recommended to schema-qualify those references. To do that, write@extschema:*name*@in the extension's script file, where*name*is the name of the other extension (which must be listed in this extension'srequireslist). This string will be replaced by the name (double-quoted if necessary) of that extension's target schema. Although this notation avoids the need to make hard-wired assumptions about schema names in the extension's script file, its use may embed the other extension's schema name into the installed objects of this extension. (Typically, that happens when@extschema:*name*@is used inside a string literal, such as a function body or asearch_pathsetting. In other cases, the object reference is reduced to an OID during parsing and does not require subsequent lookups.) If the other extension's schema name is so embedded, you should prevent the other extension from being relocated after yours is installed, by adding the name of the other extension to this one'sno_relocatelist.
38.17.3. Extension Configuration Tables#
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 bypg_dump. 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.
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 causepg_dumpto include the table's or the sequence's contents (not its definition) in dumps. To do that, call the functionpg_extension_config_dump(regclass, text)after creating the table or the sequence, for example
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', '');Any number of tables or sequences can be marked this way. Sequences associated withserialorbigserialcolumns can be marked as well.
When the second argument ofpg_extension_config_dumpis an empty string, the entire contents of the table are dumped bypg_dump. 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 ofpg_extension_config_dumpprovides aWHEREcondition that selects the data to be dumped. For example, you might do
CREATE TABLE my_config (key text, value text, standard_entry boolean);
SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');and then make sure thatstandard_entryis true only in the rows created by the extension's script.
For sequences, the second argument ofpg_extension_config_dumphas no effect.
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.
You can alter the filter condition associated with a configuration table by callingpg_extension_config_dumpagain. (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 withALTER EXTENSION ... DROP TABLE.
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.
Sequences associated withserialorbigserialcolumns need to be directly marked to dump their state. Marking their parent relation is not enough for this purpose.
38.17.4. Extension Updates#
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 provideupdate scriptsthat make the necessary changes to go from one version to the next. Update scripts have names following the pattern*extension*--*old_version*--*target_version*.sql(for example,foo--1.0--1.1.sqlcontains the commands to modify version1.0of extensionfoointo version1.1).
Given that a suitable update script is available, the commandALTER EXTENSION UPDATEwill update an installed extension to the specified new version. The update script is run in the same environment thatCREATE EXTENSIONprovides for installation scripts: in particular,search_pathis 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.
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.
ALTER EXTENSIONis able to execute sequences of update script files to achieve a requested update. For example, if onlyfoo--1.0--1.1.sqlandfoo--1.1--2.0.sqlare available,ALTER EXTENSIONwill apply them in sequence if an update to version2.0is requested when1.0is currently installed.
PostgreSQLdoesn't assume anything about the properties of version names: for example, it does not know whether1.1follows1.0. 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--or leading or trailing-.)
Sometimes it is useful to provide“downgrade”scripts, for examplefoo--1.1--1.0.sqlto allow reverting the changes associated with version1.1. 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“fast path”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.
To check for unexpected update paths, use this command:
SELECT * FROM pg_extension_update_paths('extension_name');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, orNULLif there is no available update path. The path is shown in textual form with--separators. You can useregexp_split_to_array(path,'--')if you prefer an array format.
38.17.5. Installing Extensions Using Update Scripts#
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 afooextension in versions1.0,1.1, and1.2, there should be update scriptsfoo--1.0--1.1.sqlandfoo--1.1--1.2.sql. BeforePostgreSQL10, it was necessary to also create new script filesfoo--1.1.sqlandfoo--1.2.sqlthat directly build the newer extension versions, or else the newer versions could not be installed directly, only by installing1.0and then updating. That was tedious and duplicative, but now it's unnecessary, becauseCREATE EXTENSIONcan follow update chains automatically. For example, if only the script filesfoo--1.0.sql,foo--1.0--1.1.sql, andfoo--1.1--1.2.sqlare available then a request to install version1.2is honored by running those three scripts in sequence. The processing is the same as if you'd first installed1.0and then updated to1.2. (As withALTER EXTENSION UPDATE, 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.
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, iffoo--1.0.controlspecifiesrequires = 'bar'butfoo's other control files do not, the extension's dependency onbarwill be dropped when updating from1.0to another version.
38.17.6. Security Considerations for Extensions#
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.
An extension that has thesuperuserproperty 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.
If an extension is markedtrusted, 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.
Advice about writing functions securely is provided inSection 38.17.6.1below, and advice about writing installation scripts securely is provided inSection 38.17.6.2.
38.17.6.1. Security Considerations for Extension Functions#
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.
TheCREATE FUNCTIONreference page contains advice about writingSECURITY DEFINERfunctions 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.
If you cannot set thesearch_pathto 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 onsearch_pathimplicitly; for example,INandCASE*expression*WHENalways select an operator using the search path. In their place, useOPERATOR(*schema*.=) ANYandCASE WHEN*expression*.
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 functionmyschema.myfunc(bigint)then a call such asmyschema.myfunc(42)could be captured by a hostile functionmyschema.myfunc(integer). Be careful that the data types of function and operator parameters exactly match the declared argument types, using explicit casts where necessary.
38.17.6.2. Security Considerations for Extension Scripts#
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.
DDL commands such asCREATE FUNCTIONandCREATE OPERATOR CLASSare generally secure, but beware of any command having a general-purpose expression as a component. For example,CREATE VIEWneeds to be vetted, as does aDEFAULTexpression inCREATE FUNCTION.
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 securesearch_path; donottrust the path provided byCREATE/ALTER EXTENSIONto be secure. Best practice is to temporarily setsearch_pathto'pg_catalog, pg_temp'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 thecontribmodules in thePostgreSQLsource code distribution.
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-timesearch_path. 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 inpg_catalog.
38.17.7. Extension Example#
Here is a complete example of anSQL-only extension, a two-element composite type that can store any type of value in its slots, which are named“k”and“v”. Non-text values are automatically coerced to text for storage.
The script filepair--1.0.sqllooks like this:
-- 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)::@[email protected];';
CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);
-- "SET search_path" is easy to get right, but qualified names perform better.
CREATE FUNCTION lower(pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW(lower($1.k), lower($1.v))::@[email protected];'
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)::@[email protected];';The control filepair.controllooks like this:
# pair extension
comment = 'A key/value pair data type'
default_version = '1.0'
# cannot be relocatable because of use of @extschema@
relocatable = falseWhile you hardly need a makefile to install these two files into the correct directory, you could use aMakefilecontaining this:
EXTENSION = pair
DATA = pair--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)This makefile relies onPGXS, which is described inSection 38.18. The commandmake installwill install the control and script files into the correct directory as reported bypg_config.
Once the files are installed, use theCREATE EXTENSIONcommand to load the objects into any particular database.