38.16. Packaging Related Objects into an Extension
A useful extension to PostgreSQL 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. PostgreSQL calls such a package an extension . To define an extension, you need at least a script file that contains the SQL commands to create the extension's objects, and a control file 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 CREATE EXTENSION command loads the objects into your database.
The main advantage of using an extension, rather than just running the
script to load a bunch of
into your database, is that
understand that the objects of the extension go together. You can
drop all the objects with a single
command (no need to maintain a separate
Even more useful,
knows that it should not
dump the individual member objects of the extension - it will
just include a
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.
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
CREATE OR REPLACE FUNCTION
function), bear in mind that the modified definition will not be dumped
. 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
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
statements. The final set of privileges for each object (if any are set)
will be stored in the
system catalog. When
is used, the
command will be included in the dump, followed
by the set of
statements necessary to set the privileges on the objects to what they were
at the time the dump was taken.
does not currently support extension scripts
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
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
that makes just those
two changes. The
ALTER EXTENSION UPDATE
command 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 of ALTER 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.16.1. Extension Files
command relies on a control
file for each extension, which must be named the same as the extension
with a suffix of
, and must be placed in the
must also be at least one
script file, which follows the
). By default, the script file(s) are also
placed in the
directory; 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 the
file, namely a list of
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:
The directory containing the extension's SQL script file(s). Unless an absolute path is given, the name is relative to the installation's
SHAREDIRdirectory. The default behavior is equivalent to specifying
directory = 'extension'.
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 in
CREATE EXTENSIONfailing if no
VERSIONoption appears, so you generally don't want to do that.
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.
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.
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
MODULE_PATHNAMEis used in
CREATE FUNCTIONcommands for C-language functions, so that the script files do not need to hard-wire the name of the shared library.
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.
If this parameter is
true(which is the default), only superusers can create the extension or update it to a new version. If it is set to
false, just the privileges required to execute the commands in the installation or update script are required.
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.16.2 for more information.
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.16.2 for more information.
In addition to the primary control file
cannot be set in a secondary control file.
script files can contain any SQL commands,
except for transaction control commands (
, etc) and commands that cannot be executed inside a
transaction block (such as
). This is because the
script files are implicitly executed within a transaction block.
script files can also contain lines
, 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
rather than being loaded via
Without that, users might accidentally load the
extension's contents as
objects rather than as an
extension, a state of affairs that's a bit tedious to recover from.
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
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
parameter, but instead use
COMMENT ON EXTENSION
within a script file to set the comment.
38.16.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 setting
relocatable = 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, set
relocatable = 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 before the script is executed. The user can set the target schema using the
If the extension does not support relocation at all, set
relocatable = falsein its control file, and also set
schemato the name of the intended target schema. This will prevent use of the
CREATE EXTENSION, 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
@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 with
initially set to point to the target
schema; that is,
does the equivalent of
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 change
if it wishes,
but that is generally undesirable.
to its previous setting upon completion of
The target schema is determined by the
the control file if that is given, otherwise by the
if that is given, otherwise the
current default object creation schema (the first one in the caller's
). When the control file
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.
If any prerequisite extensions are listed in
in the control file, their target schemas are added to the initial
, following the new
extension's target schema. This allows their objects to be visible to
the new extension's script file.
is automatically appended to
the end of
in 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
during creation of dependent
38.16.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 by pg_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 reload.
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
to include the table's or the sequence's
contents (not its definition) in dumps. To do that, call the function
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
be marked as well.
When the second argument of
an empty string, the entire contents of the table are dumped by
. 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
condition 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 that
is true only
in the rows created by the extension's script.
For sequences, the second argument of
has 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
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
ALTER 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 with
need to be directly marked to dump their state. Marking their parent
relation is not enough for this purpose.
38.16.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 provide
that make the necessary changes to go from
one version to the next. Update scripts have names following the pattern
contains the commands to modify
Given that a suitable update script is available, the command
ALTER EXTENSION UPDATE
will update an installed extension
to the specified new version. The update script is run in the same
provides for installation
scripts: in particular,
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.
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.
The update mechanism can be used to solve an important special case:
collection of objects into an extension.
Before the extension mechanism was added to
(in 9.1), many people wrote
extension modules that simply created assorted unpackaged objects.
Given an existing database containing such objects, how can we convert
the objects into a properly packaged extension? Dropping them and then
doing a plain
is one way, but it's not
desirable if the objects have dependencies (for example, if there are
table columns of a data type created by the extension). The way to fix
this situation is to create an empty extension, then use
to attach each pre-existing object to the extension,
then finally create any new objects that are in the current extension
version but were not in the unpackaged release.
supports this case with its
option, which causes it to not run the
normal installation script for the target version, but instead the update
is up to the extension author, though
is a common convention. If you have multiple
prior versions you need to be able to update into extension style, use
multiple dummy version names to identify them.
is able to execute sequences of update
script files to achieve a requested update. For example, if only
will apply them in sequence if an
update to version
is requested when
doesn't assume anything about the properties
of version names: for example, it does not know whether
. 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
to allow reverting the changes
associated with version
. 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
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('
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
there is no available update path. The path is shown in textual form
separators. You can use
if you prefer an array
38.16.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 a
should be update scripts
10, it was necessary to also create
new script files
that directly build the newer extension versions, or else the newer
versions could not be installed directly, only by
and then updating. That was tedious and
duplicative, but now it's unnecessary, because
can follow update chains automatically.
For example, if only the script
are available then a request to
is honored by running those three
scripts in sequence. The processing is the same as if you'd first
and then updated to
ALTER 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, if
's other control files do not, the
extension's dependency on
will be dropped when updating
to another version.
38.16.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 the
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.
18.104.22.168. 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.
reference page contains advice about
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
If you cannot set the
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
always select an operator using the search path. In their place, use
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
then a call such
could be captured by a
careful that the data types of function and operator parameters exactly
match the declared argument types, using explicit casts where necessary.
22.214.171.124. 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 as
CREATE OPERATOR CLASS
are generally secure,
but beware of any command having a general-purpose expression as a
component. For example,
needs to be
vetted, as does a
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
trust the path provided by
to be secure. Best practice is to temporarily
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
source 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-time
. However, no mechanism currently exists
to require that.
CREATE OR REPLACE
, except in an update script that must change the
definition of a function that is known to be an extension member
already. (Likewise for other
unnecessarily not only has a risk
of accidentally overwriting someone else's function, but it creates a
security hazard since the overwritten function would still be owned by
its original owner, who could modify it.
38.16.7. Extension Example
Here is a complete example of an SQL -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 file
looks 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)::@extschema@.pair;'; CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair); -- "SET search_path" is easy to get right, but qualified names perform better. CREATE FUNCTION lower(pair) RETURNS pair LANGUAGE SQL AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;' SET search_path = pg_temp; CREATE FUNCTION pair_concat(pair, pair) RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k, $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';
The control file
looks like this:
# pair extension comment = 'A key/value pair data type' default_version = '1.0' # cannot be relocatable because of use of @extschema@ relocatable = false
While you hardly need a makefile to install these two files into the
correct directory, you could use a
EXTENSION = pair DATA = pair--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)
This makefile relies on
, which is described
. The command
will install the control and script files into the correct
directory as reported by
Once the files are installed, use the CREATE EXTENSION command to load the objects into any particular database.