Postgresql 中文操作指南

38.17. Packaging Related Objects into an Extension #

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

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

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

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

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

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

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

如果扩展的脚本创建任何临时对象（例如临时表），则这些对象在当前会话的剩余时间内将被视为扩展成员，但会在会话结束时自动删除，就像任何临时对象一样。这是扩展成员对象不能在不删除整个扩展的情况下被删除的规则的一个例外。

38.17.1. Extension Files #

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

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

控制文件可以设置以下参数：

  • directory (string) #

    • 包含扩展的 SQL 脚本文件的目录。除非给出了绝对路径，否则该名称是相对于安装的 SHAREDIR 目录的。默认行为等效于指定 directory = 'extension' 。

  • default_version (string) #

    • 扩展的默认版本（如果在 CREATE EXTENSION 中未指定版本，则安装该版本）。虽然可以省略它，但这将导致在未出现 VERSION 选项的情况下 CREATE EXTENSION 失败，所以您通常不希望这样做。

  • comment (string) #

    • 对扩展的评论（任何字符串）。评论在最初创建扩展时应用，但不会在扩展更新期间应用（因为这样会覆盖用户添加的评论）。或者，可以通过在脚本文件中编写 COMMENT 命令来设置扩展的评论。

  • encoding (string) #

    • 脚本文件使用的字符集编码。如果脚本文件包含任何非 ASCII 字符，则应指定此项。否则，文件将被假定为使用数据库编码。

  • module_pathname (string) #

    • 此参数的值将替换脚本文件中的每次出现 MODULE_PATHNAME 。如果未设置，则不进行替换。通常，这被设置为 $libdir/_shared_library_name_ ，然后 MODULE_PATHNAME 在面向 C 语言函数的 CREATE FUNCTION 命令中使用，以便脚本文件不必硬编码共享库的名称。

  • requires (string) #

    • 依赖此扩展的扩展的名称列表，例如 requires = 'foo, bar'。在安装此扩展之前，必须安装那些扩展。

  • no_relocate (string) #

    • 本扩展所依赖的扩展的名称列表，这些扩展应被禁止通过 ALTER EXTENSION …​ SET SCHEMA 更改它们的模式。如果本扩展的脚本以无法跟踪重命名的形式引用所需扩展的模式的名称（使用 @extschema:_name @_ 语法），则需要这样做。

  • superuser (boolean) #

    • 如果此参数是 true （这是默认设置），只有超级用户可以创建扩展或将其更新到新版本（但另请参见 trusted ，如下）。如果将其设置为 false ，则只需要执行安装或更新脚本中的命令所需的权限。如果任何脚本命令需要超级用户权限，则通常应将其设置为 true 。（这些命令无论如何都会失败，但最好在前面给出错误。）

  • trusted (boolean) #

    • 如果将此参数设置为 true （这不是默认值），它允许一些非超级用户安装将 superuser 设置为 true 的扩展。具体来说，它将允许拥有当前数据库的 CREATE 权限的任何人进行安装。当执行 CREATE EXTENSION 的用户不是超级用户，但被允许通过此参数进行安装时，安装或更新脚本将作为引导超级用户运行，而不是作为调用用户运行。如果 superuser 为 false ，则此参数无关紧要。通常，这不应为可能允许访问原本只能由超级用户访问的能力（如文件系统访问）的扩展设置 true。还有，将扩展标记为受信任需要付出大量的额外努力才能安全地编写扩展的安装和更新脚本；参阅 Section 38.17.6 。

  • relocatable (boolean) #

    • 如果扩展的包含对象可以在最初创建扩展后移动到不同的架构中，则扩展是 relocatable。默认值为 false，即该扩展不可重新定位。请参阅 Section 38.17.2以获取更多信息。

  • schema (string) #

    • 此参数只能为不可重新定位的扩展设置。它强制将扩展加载到完全指定的模式中，而不是任何其他模式。schema 参数仅在最初创建扩展时才会查阅，而不会在扩展更新期间查阅。请参阅 Section 38.17.2以获取更多信息。

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

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

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

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

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

38.17.2. Extension Relocatability #

用户通常希望将扩展中包含的对象加载到与扩展作者设想的不同的模式中。支持以下三个级别可重定位：

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

SET LOCAL search_path TO @extschema@, pg_temp;

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

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

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

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

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

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

38.17.3. Extension Configuration Tables #

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

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

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', '');

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

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

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 仅在扩展脚本创建的行中为真。

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

可以通过针对配置表创建触发器来处理更复杂的情况，例如可能由用户修改的初始提供的行，以确保正确标记修改的行。

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

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

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

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 的命令）。

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

如果扩展有辅助控制文件，那么用于更新脚本的控制参数是与脚本目标（新）版本相关联的参数。

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

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

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

要检查意外更新路径，请使用此命令：

SELECT * FROM pg_extension_update_paths('extension_name');

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

38.17.5. Installing Extensions Using Update Scripts #

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

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

38.17.6. Security Considerations for Extensions #

广泛分发的扩展应该对其所在的数据库做很少的假设。因此，以安全样式编写扩展提供的函数非常合适，而搜索路径攻击无法破坏这种样式。

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

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

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

38.17.6.1. Security Considerations for Extension Functions #

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

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

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

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

38.17.6.2. Security Considerations for Extension Scripts #

应该编写扩展安装或更新脚本以防范在脚本执行时发生的搜索路径攻击。如果脚本中的对象引用可以解析为脚本作者意图之外的其他对象，那么可能会立即发生破坏，或者在错误定义的扩展对象被使用时稍后发生破坏。

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

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

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

38.17.7. Extension Example #

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

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

-- 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 类似于这样：

# 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（其中包含此内容）：

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 报告的正确目录中。

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