Postgresql 中文操作指南

38.17. Packaging Related Objects into an Extension #

一个对 PostgreSQL 的有用的扩展通常包括多个 SQL 对象;例如,新的数据类型需要新的函数、新的运算符以及可能的新索引运算符类。最好将所有这些对象收集在一个包中,以便简化数据库管理。PostgreSQL 将这样的包称为 extension 。要定义一个扩展,您至少需要一个 script file ,其中包含创建扩展对象的 SQL 命令,还有一个 control file ,其中指定了扩展本身的几个基本属性。如果扩展包括 C 代码,那么通常还会有一个共享库文件,C 代码已构建到其中。一旦您拥有这些文件,一个简单的 CREATE 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.

使用扩展的主要优点在于,与其仅运行 SQL 脚本将一堆“松散”对象加载到您的数据库中不同,PostgreSQL 随后将了解扩展的对象在一起。您可以使用单个 DROP EXTENSION 命令删除所有对象(无需维护单独的“卸载”脚本)。更有用的是,pg_dump 知道它不应转储扩展的单个成员对象——它将转而只是在转储中包括一个 CREATE EXTENSION 命令。这极大地简化了迁移到可能包含比旧版本更多或不同的对象的扩展的新版本。但请注意,当将这样的转储加载到新数据库时,您必须准备好扩展的控制脚本和其他文件。

The main advantage of using an extension, rather than just running the SQL script to load a bunch of “loose” objects into your database, is that PostgreSQL will then understand that the objects of the extension go together. You can drop all the objects with a single DROP EXTENSION command (no need to maintain a separate “uninstall” script). Even more useful, pg_dump knows that it should not dump the individual member objects of the extension — it will just include a CREATE EXTENSION 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.

PostgreSQL 不会允许删除扩展中包含的单个对象,除非删除整个扩展。另外,虽然您可以更改扩展成员对象(例如,通过函数的 CREATE OR REPLACE FUNCTION),但请记住,修改后的定义不会被 pg_dump 转储。此类更改通常只有在同时在扩展的脚本文件中进行相同更改才有意义。(但是对于包含配置数据的表有特殊规定;请参见 Section 38.17.3。)在实际情况下,通常最好创建一个扩展更新脚本,对扩展成员对象进行更改。

PostgreSQL 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 for a function), bear in mind that the modified definition will not be dumped by pg_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; see Section 38.17.3.) In production situations, it’s generally better to create an extension update script to perform changes to extension member objects.

扩展脚本可以使用 GRANTREVOKE 语句设置属于扩展一部分的对象上的权限。每个对象(如果有设置)的最终权限集将存储在 pg_init_privs 系统目录中。当使用 pg_dump 时, CREATE EXTENSION 命令将包括在转储中,紧随其后的是必需的 GRANTREVOKE 语句集,以在转储时将对象的权限设置为当时的状态。

The extension script may set privileges on objects that are part of the extension, using GRANT and REVOKE statements. The final set of privileges for each object (if any are set) will be stored in the pg_init_privs system catalog. When pg_dump is used, the CREATE EXTENSION command will be included in the dump, followed by the set of GRANT and REVOKE statements necessary to set the privileges on the objects to what they were at the time the dump was taken.

PostgreSQL 目前不支持扩展脚本发出 CREATE POLICYSECURITY LABEL 语句。预计在创建扩展后设置这些语句。pg_dump 创建的转储文件中将包括扩展对象上的所有 RLS 策略和安全标签。

PostgreSQL does not currently support extension scripts issuing CREATE POLICY or SECURITY LABEL 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 pg_dump.

扩展机制还提供了打包修改脚本的规定,用于调整扩展中包含的 SQL 对象的定义。例如,如果某扩展的版本 1.1 添加了一个函数,并更改了另一个函数的内容,与 1.0 相比,扩展作者可以提供一个 update script,仅执行这两个更改。然后可以使用 ALTER EXTENSION UPDATE 命令应用这些更改,并跟踪在给定数据库中实际安装的扩展版本。

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

可以在扩展中成为成员的 SQL 对象的类型显示在 ALTER EXTENSION 的说明中。特别是,诸如数据库、角色和表空间等跨数据库集群的对象不能成为扩展成员,因为扩展仅在一个数据库中已知。(尽管扩展脚本不会被禁止创建这样的对象,但如果创建,它们将不会作为扩展的一部分进行跟踪。)另外请注意,虽然一个表可以是扩展的成员,但它的辅助对象(如索引)不会被直接视为扩展的成员。另一个重要的一点是模式可以属于扩展,但反之则不然:扩展本身有一个不合格的名称,并不存在于任何模式“之中”。但是,扩展的成员对象将酌情属于模式,以适合它们的类型。扩展是否拥有其成员对象所在的模式可能是合适的,也可能不合适。

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.17.1. Extension Files #

CREATE EXTENSION 命令依赖于每个扩展的一个控制文件,该文件必须与扩展同名,且后缀为 .control ,并且必须放在安装的 SHAREDIR/extension 目录中。还必须至少有一个 SQL 脚本文件,该文件遵循命名模式 _extension —​ version .sql_(例如,针对扩展 foo 的版本 1.0foo—​1.0.sql )。默认情况下,脚本文件也会放在 SHAREDIR/extension 目录中;但控制文件可以为脚本文件指定一个不同的目录。

The CREATE EXTENSION command 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’s SHAREDIR/extension directory. There must also be at least one SQL script file, which follows the naming pattern _extension—​version.sql_ (for example, foo—​1.0.sql for version 1.0 of extension foo). By default, the script file(s) are also placed in the SHAREDIR/extension directory; but the control file can specify a different directory for the script file(s).

扩展控制文件的格式与 postgresql.conf 文件一样,即一个 parameter_name = value 分配的列表(每行一个)。允许使用 # 引入的空行和注释。请务必引用任何并非单字或数字的值。

The file format for an extension control file is the same as for the postgresql.conf file, 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 SQL script file(s). Unless an absolute path is given, the name is relative to the installation’s SHAREDIR directory. The default behavior is equivalent to specifying directory = '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 in CREATE EXTENSION failing if no VERSION option 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_PATHNAME in the script file(s). If it is not set, no substitution is made. Typically, this is set to $libdir/_shared_library_name_ and then MODULE_PATHNAME is used in CREATE FUNCTION commands 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:_name@_ syntax) in a way that cannot track renames.

  • 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 also trusted, below). If it is set to false, just the privileges required to execute the commands in the installation or update script are required. This should normally be set to true 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.)

  • trusted (boolean) #

    • This parameter, if set to true (which is not the default), allows some non-superusers to install an extension that has superuser set to true. Specifically, installation will be permitted for anyone who has CREATE privilege on the current database. When the user executing CREATE EXTENSION 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 superuser is false. 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 schema parameter is consulted only when initially creating an extension, not during extension updates. See Section 38.17.2 for more information.

除了主控制文件 extension.control_, an extension can have secondary control files named in the style _ extension_—​ version .control_ 之外,如果提供了这些文件,它们必须位于脚本文件目录中。辅助控制文件遵循与主控制文件相同的格式。在安装或更新到该扩展版本时,辅助控制文件中设置的任何参数都会覆盖主控制文件。但是,不能在辅助控制文件中设置参数 directorydefault_version

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 parameters directory and default_version cannot be set in a secondary control file.

扩展的 SQL 脚本文件可以包含任何 SQL 命令,但事务控制命令 (BEGINCOMMIT 等)和无法在事务块内执行的命令(例如 VACUUM)除外。这是因为脚本文件在事务块内隐式执行。

An extension’s SQL script 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 as VACUUM). This is because the script files are implicitly executed within a transaction block.

扩展的 SQL 脚本文件还可以包含以 \echo 开头的行,这些行将被扩展机制忽略(视为注释)。此规定通常用于在将脚本文件提供给 psql 而不是通过 CREATE EXTENSION 加载脚本文件时引发错误(请参见 Section 38.17.7 中的示例脚本)。如果没有该规定,用户可能会意外地将扩展的内容作为“松散”对象而不是作为扩展加载,要从这种情形中恢复有点麻烦。

An extension’s SQL script 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 to psql rather than being loaded via CREATE EXTENSION (see example script in Section 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.

如果扩展脚本包含字符串 @extowner@,则该字符串将替换为调用 CREATE EXTENSIONALTER EXTENSION 的用户的名称(使用合适的引号)。通常此功能由标记为受信任的扩展使用,以将所选对象的权限分配给调用用户,而不是引导超级用户。(然而,在这样做时应小心。例如,将 C 语言函数的权限分配给非超级用户会为此用户创建权限提升路径。)

If the extension script contains the string @extowner@, that string is replaced with the (suitably quoted) name of the user calling CREATE EXTENSION or ALTER 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.)

虽然脚本文件可以包含指定编码允许的任何字符,但控制文件应该只包含纯 ASCII,因为 PostgreSQL 无法知道控制文件采用的是什么编码。实际上,只有当您要在扩展注释中使用非 ASCII 字符时,此项才成为问题。在该情况下,建议的做法是不使用控制文件 comment 参数,而是使用 COMMENT ON EXTENSION 在脚本文件中设置注释。

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 PostgreSQL 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 comment parameter, but instead use COMMENT ON EXTENSION within 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:

在所有情况下,脚本文件都将用最初设置为指向目标模式的 search_path 执行;也就是说,CREATE EXTENSION 执行等效于此操作:

In all cases, the script file will be executed with search_path initially set to point to the target schema; that is, CREATE EXTENSION does the equivalent of this:

SET LOCAL search_path TO @extschema@, pg_temp;

这允许由脚本文件创建的对象进入目标模式。脚本文件可以更改 search_path,如果希望的话,但这通常是不需要的。search_path 在完成 CREATE EXTENSION 后将恢复到它的前一设置。

This allows the objects created by the script file to go into the target schema. The script file can change search_path if it wishes, but that is generally undesirable. search_path is restored to its previous setting upon completion of CREATE EXTENSION.

目标模式取决于控制文件中(如果已给定)中的 schema 参数,否则取决于 CREATE EXTENSIONSCHEMA 选项(如果已给定),否则当前默认对象创建模式(调用者 search_path 中的第一个)。当使用 schema 参数的控制文件时,如果目标模式不存在则创建该模式,但在其他两种情况下它必须已经存在。

The target schema is determined by the schema parameter in the control file if that is given, otherwise by the SCHEMA option of CREATE EXTENSION if that is given, otherwise the current default object creation schema (the first one in the caller’s search_path). When the control file schema 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.

如果任何先决条件扩展在控制文件的 requires 中列出,则它们的模式添加到 search_path 的初始设置中,遵循新扩展的模式。这允许它们的对象对新扩展的脚本文件可见。

If any prerequisite extensions are listed in requires in the control file, their target schemas are added to the initial setting of search_path, following the new extension’s target schema. This allows their objects to be visible to the new extension’s script file.

为了安全性,pg_temp 在所有情况下都自动附加到 search_path 的末尾。

For security, pg_temp is automatically appended to the end of search_path in all cases.

虽然不可重定位扩展可以包含跨多个模式展开的对象,但通常需要将所有用于外部使用的对象放在单个模式中,该模式被认为是扩展的目标模式。在创建依赖扩展期间,这样的布置与 search_path 的默认设置配合得很好。

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 search_path during creation of dependent extensions.

如果一个扩展引用另一个扩展的对象,则建议对这些引用进行模式限定。要执行此操作,请在扩展的脚本文件中写入 @extschema:_name@,其中 _name_另一个扩展的名称(该扩展必须在此扩展的 _requires 列表中列出)。此字符串将被用该扩展的目标模式的名称(如有必要则用双引号引起来)所替换。虽然此符号避免了在扩展的脚本文件中针对模式名称进行硬连线假设的需求,但其使用可能会将其他扩展的模式名称嵌入到此扩展的已安装对象中。(通常,当 @extschema:_name@_ 在字符串文本中使用时,例如函数主体或 search_path 设置中时会发生这种情况。在其他情况下,在解析期间对象引用缩减为 OID,并且不需要后续查找)。如果另一个扩展的模式名称如此嵌入,则应该通过将另一个扩展的名称添加到此名称的 no_relocate 列表中,来防止在安装完自己的扩展之后另一个扩展被重定位。

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’s requires list). 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 a search_path setting. 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’s no_relocate list.

38.17.3. Extension Configuration Tables #

某些扩展包括配置表,其中包含可能在安装扩展之后由用户添加或更改的数据。通常,如果一个表是扩展的一部分,则表定义和内容都不会被 pg_dump 转储。但是,这种行为对于配置表来说是不需要的;用户作出的任何数据更改需要包含在转储中,否则扩展在转储和恢复之后的行为会不同。

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

为了解决此问题,扩展的脚本文件可以将创建的表或序列标记为配置关系,这将导致 pg_dump 在转储中包括表或序列的内容(而非定义)。要执行此操作,请在创建表或序列之后调用函数 pg_extension_config_dump(regclass, text),例如:

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 pg_dump to include the table’s or the sequence’s contents (not its definition) in dumps. To do that, call the function pg_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', '');

可以通过这种方式标记任意数量的表或序列。与 serialbigserial 列关联的序列也可以标记。

Any number of tables or sequences can be marked this way. Sequences associated with serial or bigserial columns can be marked as well.

pg_extension_config_dump 的第二个自变量是空字符串时,pg_dump 会转储表的整个内容。这通常仅在扩展脚本创建的表最初为空时才会正确。如果表中包含初始数据和用户提供的数据的混合,则 pg_extension_config_dump 的第二个自变量提供了选择要转储的数据的 WHERE 条件。例如,您可能会执行:

When the second argument of pg_extension_config_dump is an empty string, the entire contents of the table are dumped by pg_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 of pg_extension_config_dump provides a WHERE 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');

然后确保 standard_entry 仅在扩展脚本创建的行中为真。

and then make sure that standard_entry is true only in the rows created by the extension’s script.

对于序列,pg_extension_config_dump 的第二个自变量不起作用。

For sequences, the second argument of pg_extension_config_dump 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.

您可以通过再次调用 pg_extension_config_dump 更改与配置表关联的过滤条件。(这通常在扩展更新脚本中很有用)。标记表不再是配置表的唯一方法是使用 ALTER EXTENSION …​ DROP TABLE 将其与扩展分离。

You can alter the filter condition associated with a configuration table by calling pg_extension_config_dump 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 ALTER EXTENSION …​ DROP TABLE.

请注意,这些表之间的外键关系将决定 pg_dump 转储表的顺序。具体来说,pg_dump 会尝试在引用表之前转储被引用表。由于外键关系是在 CREATE EXTENSION 时间设置的(在将数据加载到表之前),因此不支持循环依赖关系。当存在循环依赖关系时,数据仍将被转储,但转储无法直接恢复,并且需要用户干预。

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.

serialbigserial 列关联的序列需要直接标记为转储其状态。标记其父关系不足以实现此目的。

Sequences associated with serial or bigserial columns need to be directly marked to dump their state. Marking their parent relation is not enough for this purpose.

38.17.4. Extension Updates #

扩展机制的一个优点是,它提供了管理用于定义扩展对象的 SQL 命令的更新的便捷方法。这是通过将版本名称或数字与扩展安装脚本的每个已发布版本相关联来完成的。此外,如果您希望用户能够动态地将他们的数据库从一个版本更新到下一个版本,您应该提供 update scripts ,以进行从一个版本过渡到另一个版本所需的更改。更新脚本的名称遵循模式 _extension —​ old_version —​ target_version .sql_(例如, foo—​1.0—​1.1.sql 包含将扩展 foo 的版本 1.0 修改为版本 1.1 的命令)。

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 update scripts that 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.sql contains the commands to modify version 1.0 of extension foo into version 1.1).

在有合适的更新脚本的情况下,命令 ALTER EXTENSION UPDATE 将安装的扩展更新到指定的新版本。更新脚本在 CREATE EXTENSION 为安装脚本提供的相同环境中运行:特别是,search_path 以相同的方式设置,脚本创建的任何新对象都将自动添加到扩展中。另外,如果脚本选择放弃扩展成员对象,它们将自动与此扩展分离。

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 environment that CREATE EXTENSION provides for installation scripts: in particular, search_path 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.

ALTER EXTENSION 可以执行更新脚本文件序列以实现请求的更新。例如,如果只有 foo—​1.0—​1.1.sqlfoo—​1.1—​2.0.sql 可用,在当前已安装 1.0 的情况下,当要求将程序升级到版本 2.0 时,ALTER EXTENSION 将顺序应用这些脚本。

ALTER EXTENSION is able to execute sequences of update script files to achieve a requested update. For example, if only foo—​1.0—​1.1.sql and foo—​1.1—​2.0.sql are available, ALTER EXTENSION will apply them in sequence if an update to version 2.0 is requested when 1.0 is currently installed.

PostgreSQL 不对版本名称的属性做任何假设:例如,它不知道 1.1 是否紧随 1.0 之后。它只是匹配可用的版本名称,并沿着需要应用最少更新脚本的路径前进。(版本名称实际上可以是任何不包含 —​ 或开头或结尾 - 的字符串。)

PostgreSQL doesn’t assume anything about the properties of version names: for example, it does not know whether 1.1 follows 1.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 -.)

有时提供“降级”脚本非常有用,例如 foo—​1.1—​1.0.sql 可用来恢复与版本 1.1 关联的更改。如果您这样做,请注意,降级脚本可能会意外应用,因为它产生了一条更短的路径。危险的情况是存在“快速路径”更新脚本,它可以跨越多个版本,还存在到“快速路径”起点的降级脚本。应用降级脚本然后应用快速路径比一次推进一个版本要耗费更少的步骤。如果降级脚本删除了任何不可替代的对象,这将产生不良的结果。

Sometimes it is useful to provide “downgrade” scripts, for example foo—​1.1—​1.0.sql to allow reverting the changes associated with version 1.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');

这会显示指定的扩展的每对不同的已知版本名称,以及从源版本到目标版本要采取的更新路径序列,或者在没有可用更新路径的情况下显示 NULL。路径以文本形式显示,并带有 —​ 分隔符。如果您更喜欢数组格式,可以使用 regexp_split_to_array(path,'--')

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 NULL if there is no available update path. The path is shown in textual form with —​ separators. You can use regexp_split_to_array(path,'--') if you prefer an array format.

38.17.5. Installing Extensions Using Update Scripts #

一个已经存在一段时间了的扩展程序可能存在多个版本,其作者需要为这些版本编写更新脚本。例如,如果您已发布版本 1.01.11.2foo 扩展,则应该有更新脚本 foo—​1.0—​1.1.sqlfoo—​1.1—​1.2.sql。在 PostgreSQL 10 之前,还需要创建新脚本文件 foo—​1.1.sqlfoo—​1.2.sql,它们直接构建较新的扩展版本,否则无法直接安装较新版本,只能通过安装 1.0 然后更新来实现。这既繁琐又重复,但现在不必这样做,因为 CREATE EXTENSION 可以自动遵循更新链。例如,如果只有脚本文件 foo—​1.0.sqlfoo—​1.0—​1.1.sqlfoo—​1.1—​1.2.sql 可用,那么通过按顺序运行这三个脚本可以满足安装版本 1.2 的请求。处理方式与先安装 1.0 然后更新到 1.2 相同。(与 ALTER EXTENSION UPDATE 一样,如果有多个路径可用,则首选最短路径。)以这种样式安排扩展的脚本文件可以减少生成小更新所需的维护工作量。

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 foo extension in versions 1.0, 1.1, and 1.2, there should be update scripts foo—​1.0—​1.1.sql and foo—​1.1—​1.2.sql. Before PostgreSQL 10, it was necessary to also create new script files foo—​1.1.sql and foo—​1.2.sql that directly build the newer extension versions, or else the newer versions could not be installed directly, only by installing 1.0 and then updating. That was tedious and duplicative, but now it’s unnecessary, because CREATE EXTENSION can follow update chains automatically. For example, if only the script files foo—​1.0.sql, foo—​1.0—​1.1.sql, and foo—​1.1—​1.2.sql are available then a request to install version 1.2 is honored by running those three scripts in sequence. The processing is the same as if you’d first installed 1.0 and then updated to 1.2. (As with 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.

如果您使用辅助(特定版本)控制文件与以这种样式维护的扩展,请记住,即使版本没有独立的安装脚本,每个版本也需要一个控制文件,因为该控制文件将决定如何执行对该版本的隐式更新。例如,如果 foo—​1.0.control 指定了 requires = 'bar',但 foo 的其他控制文件没有指定,那么在从 1.0 更新到另一个版本时,扩展对 bar 的依赖关系将被删除。

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 foo—​1.0.control specifies requires = 'bar' but foo's other control files do not, the extension’s dependency on bar will be dropped when updating from 1.0 to 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.

superuser 属性设置为 true 的扩展还必须考虑在其安装和更新脚本中执行的操作的安全隐患。对于恶意用户来说,创建可以破坏后来执行的粗心编写的扩展脚本的特洛伊木马对象并不是非常困难,这允许该用户获取超级用户权限。

An extension that has the superuser 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.

如果扩展被标记为 trusted,则其安装架构可以选择安装用户,该用户可能故意使用不安全的架构以获取超级用户权限。因此,从安全的角度来看,受信扩展的漏洞非常多,必须仔细检查其所有脚本命令以确保不可能被破坏。

If an extension is marked trusted, 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.

关于安全编写函数的建议在下文 Section 38.17.6.1 中提供,关于安全编写安装脚本的建议在下文 Section 38.17.6.2 中提供。

Advice about writing functions securely is provided in Section 38.17.6.1 below, and advice about writing installation scripts securely is provided in Section 38.17.6.2.

38.17.6.1. Security Considerations for Extension Functions #

扩展提供的 SQL 语言和 PL 语言函数在执行时面临搜索路径攻击风险,因为这些函数的解析发生在执行时而非创建时。

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.

CREATE FUNCTION 参考页面包含有关安全编写 SECURITY DEFINER 函数的建议。最好为扩展提供的任何函数应用这些技术,因为函数可能会被高权限用户调用。

The CREATE FUNCTION reference page contains advice about writing SECURITY DEFINER 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.

如果您不能将 search_path 设置为仅包含安全模式,请假设每个不合格的名称都可以解析为恶意用户已定义的对象。注意依赖于 search_path 的结构;例如, INCASE _expression WHEN_ 始终使用搜索路径选择运算符。使用 OPERATOR(_schema .=) ANY_ 和 CASE WHEN _expression_ 代替它们。

If you cannot set the search_path 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 search_path implicitly; for example, IN and CASE _expression WHEN_ always select an operator using the search path. In their place, use OPERATOR(_schema.=) ANY_ and CASE WHEN _expression_.

通用扩展通常不应该假设它已安装到安全架构中,这意味着即使是对其自身对象的架构限定引用也不是完全没有风险的。例如,如果扩展定义了一个函数 myschema.myfunc(bigint),那么像 myschema.myfunc(42) 这样的调用可能会被敌对函数 myschema.myfunc(integer) 捕获。注意函数和运算符参数的数据类型与声明的参数类型完全匹配,需要时使用显式转换。

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 myschema.myfunc(bigint) then a call such as myschema.myfunc(42) could be captured by a hostile function myschema.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.

CREATE FUNCTIONCREATE OPERATOR CLASS 这样的 DDL 命令通常是安全的,但要注意任何具有通用表达式作为组件的命令。例如,CREATE VIEW 需要被审查, CREATE FUNCTION 中的 DEFAULT 表达式也是如此。

DDL commands such as CREATE FUNCTION and CREATE OPERATOR CLASS are generally secure, but beware of any command having a general-purpose expression as a component. For example, CREATE VIEW needs to be vetted, as does a DEFAULT expression in CREATE FUNCTION.

有时扩展脚本可能需要执行通用 SQL,例如对通过 DDL 无法实现的目录进行调整。小心地使用一个安全的 search_path 来执行这样的命令;相信 CREATE/ALTER EXTENSION 提供的路径是安全的。最佳做法是将 search_path 暂时设置为 'pg_catalog, pg_temp' 并根据需要在扩展的安装架构中显式插入引用。(这种做法对创建视图也可能很有帮助。)可以在 PostgreSQL 源代码分发版中的 contrib 模块中找到示例。

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 search_path; do not trust the path provided by CREATE/ALTER EXTENSION to be secure. Best practice is to temporarily set search_path to '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 the contrib modules in the PostgreSQL source code distribution.

交叉扩展引用难以完全实现安全,部分原因是对于其他扩展所在架构的不明确性。如果两个扩展安装在同一架构中,则危险会降低,因为这样在安装时 search_path 无法放置恶意对象在引用扩展前面。但是,目前不存在要求这样做的机制。目前,最佳做法是不将扩展标记为受信任,如果它依赖另一个扩展,除非另一个扩展始终安装在 pg_catalog 中。

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 search_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 in pg_catalog.

38.17.7. Extension Example #

以下是对 SQL 专用扩展的一个完整示例,一个两元素复合类型,可在其称为“k”和“v”的槽中存储任何类型的的值。非文本值自动强制转换为文本以便存储。

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.

脚本文件 pair—​1.0.sql 类似于这样:

The script file pair—​1.0.sql 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;';

控制文件 pair.control 类似于这样:

The control file pair.control 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

虽然几乎不需要 makefile 将这两个文件安装到正确的目录中,但可以使用 Makefile(其中包含此内容):

While you hardly need a makefile to install these two files into the correct directory, you could use a Makefile containing this:

EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

此 makefile 依赖于 PGXS,PGXS 在 Section 38.18 中已进行描述。命令 make install 将把控制和脚本文件安装到 pg_config 报告的正确目录中。

This makefile relies on PGXS, which is described in Section 38.18. The command make install will install the control and script files into the correct directory as reported by pg_config.

安装文件后,使用 CREATE EXTENSION 命令将对象加载到任何特定数据库中。

Once the files are installed, use the CREATE EXTENSION command to load the objects into any particular database.