DeepSeek总结的无需编译器：编写纯 SQL 的 Postgres 扩展

来源：https://www.pgedge.com/blog/no-compiler-required-writing-sql-only-postgres-extensions

无需编译器：编写纯 SQL 的 Postgres 扩展

作者:Shaun Thomas
日期:2026 年 5 月 8 日

最近在圣何塞举办的 2026 年 Postgres 会议上，我做了一个题为“让我们构建一个 Postgres 扩展！”的演讲。由于整个演讲主要聚焦于编写 C 扩展，同时探索 Postgres 源代码，所以我只是顺便提到了纯 SQL 扩展。但在 Postgres 社区中，哪种人更常见？C 开发者，还是懂 SQL 的人？

事实证明，你可以利用函数、触发器、视图、表和许多其他 Postgres 原生功能做很多事情。扩展系统并不关心其内容是编译的 C 代码还是纯 SQL。它只需要一个控制文件、一个 SQL 脚本和一个可选的 Makefile 来帮助安装。

因此，让我们完全用 SQL 构建一个相对简单的扩展。

我们想要什么？

首先，我们需要一个计划。这个扩展到底应该做什么？我之前写过一篇关于用 C 扩展阻塞 DDL 的文章，为什么不使用 SQL 重新审视这个例子呢？

由于这是纯 SQL，我们可以毫不费力地添加其他有用的元素，例如：

• 一个启用或禁用扩展的设置。
• 一个允许或阻止超级用户执行 DDL 的设置。
• 一个允许其成员绕过 DDL 限制的角色。
• 一个将用户添加到绕过角色的函数。
• 一个将用户从绕过角色中移除的函数。
• 一个查看哪些用户在绕过角色中的视图。
• 一个实际阻止 DDL 尝试的事件触发器。

我们不是在构建一个简单的事件触发器来阻止 DDL 执行，而是在构建一个 DDL 执行管理套件。这应该有望展示纯 SQL 实现的能力有多强。

三个文件和一个梦想

每个 Postgres 扩展，无论复杂程度如何，都可以归结为相同的基本结构：

• 一个描述扩展的控制文件。
• 一个用于创建表、视图、函数等的SQL 脚本。
• 一个可选的Makefile，用于将 SQL 脚本和控制文件复制到正确的位置。与 C 项目不同，纯 SQL 扩展没有构建步骤，因为没有什么需要编译。

这是我们的项目目录结构：

block_ddl/ ├── block_ddl--1.0.sql ├── block_ddl.control └── Makefile

让我们从控制文件开始。它告诉 Postgres 扩展的名称、版本、描述以及一些行为标志的设置。我们的控制文件如下所示：

# block_ddl extensioncomment='DDL blocking for Postgres'default_version='1.0'superuser=truerelocatable=false

• comment会显示在\dx和pg_extension目录视图中。
• default_version告诉 Postgres 当有人运行CREATE EXTENSION block_ddl而未指定版本时，加载哪个 SQL 脚本。
• superuser = true标志意味着只有超级用户可以安装或更新此扩展。这是默认设置，但明确指定更好。
• relocatable = false标志值得简要解释。可重定位扩展可以在安装后通过ALTER EXTENSION ... SET SCHEMA在模式之间移动。我们的扩展不能，因为 SQL 脚本使用@extschema@替换标记在内部引用了特定的模式。在安装期间定义模式是可行的（也是推荐的），但之后不行。

接下来是 Makefile。对于 C 扩展，Makefile 负责协调编译和链接。对于纯 SQL 扩展，它只需将控制文件和 SQL 文件复制到 Postgres 存放扩展的库文件夹。整个文件内容如下：

EXTENSION = block_ddl DATA = block_ddl--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS)

通常还有一个MODULES行来指定要编译的 C 源文件。没有它，make install就只是将控制文件和 SQL 脚本复制到正确的目录。PGXS 构建基础设施负责处理其余部分。

样板文件处理完毕，是时候找点乐子了。

一些簿记工作

在我们真正开始之前，扩展需要存在于一个模式中。该模式中的一些对象需要是公共可访问的。因此，我们文件中的第一件事需要如下所示：

GRANTUSAGEONSCHEMA@extschema@TOPUBLIC;

USAGE仅意味着模式对象是可见的。除非特别授权，否则用户将无法创建对象，甚至无法从表中选择。

之后，我们需要处理配置设置。您可能认为首选是使用会话变量，但这是一个微妙的陷阱。这里的问题是纯 SQL 扩展无法访问系统变量的更精细控制点，例如将它们限制为超级用户、系统启动、服务重载等。这意味着无法阻止用户通过简单的SET语句覆盖它们。

下一个选项是配置表。扩展文档说我们可以注册这些表，以便在转储和恢复数据库时保留值，并且控制表更新很简单。所以让我们用以下内容开始我们的扩展：

CREATETABLE@extschema@.ext_config(nameTEXTPRIMARYKEY,settingTEXTNOTNULL);INSERTINTO@extschema@.ext_configVALUES('enabled','off'),('allow_super','on');SELECTpg_catalog.pg_extension_config_dump('@extschema@.ext_config','');GRANTSELECTON@extschema@.ext_configTOPUBLIC;CREATEORREPLACEFUNCTION@extschema@.alter_config(p_nameTEXT,p_settingTEXT)RETURNSBOOLEANAS$$BEGINIFp_nameIN('enabled','allow_super')THENUPDATE@extschema@.ext_configSETsetting=(CASEWHENp_setting='on'THEN'on'ELSE'off'END)WHEREname=p_name;ENDIF;RETURNtrue;END;$$LANGUAGEplpgsql;REVOKEEXECUTEONFUNCTION@extschema@.alter_config(TEXT,TEXT)FROMPUBLIC;

现在只有超级用户可以配置扩展！普通用户仍然需要能够读取配置表，因为事件触发器是以该用户身份运行的。无论如何，我们现在有了一个方便的配置接口。

角色设计

下一步是允许某些用户绕过 DDL 限制。最简单的方法是创建一个角色，超级用户可以将这些被允许的用户授予该角色。我们还可以在这里处理我们有用的授权/撤销函数：

CREATEROLE block_ddl_allowed_user;CREATEORREPLACEFUNCTION@extschema@.add_ddl_bypass_user(p_userTEXT)RETURNSBOOLEANAS$$BEGINEXECUTEformat('GRANT block_ddl_allowed_user TO %I',p_user);RETURNtrue;END;$$LANGUAGEplpgsql;CREATEORREPLACEFUNCTION@extschema@.remove_ddl_bypass_user(p_userTEXT)RETURNSBOOLEANAS$$BEGINEXECUTEformat('REVOKE block_ddl_allowed_user FROM %I',p_user);RETURNtrue;END;$$LANGUAGEplpgsql;REVOKEEXECUTEONFUNCTION@extschema@.add_ddl_bypass_user(TEXT)FROMPUBLIC;REVOKEEXECUTEONFUNCTION@extschema@.remove_ddl_bypass_user(TEXT)FROMPUBLIC;

使用包含扩展名的长名称block_ddl_allowed_user是为了防止名称冲突。这个角色可能尚未被使用，并且其目的显而易见。这些函数意味着管理员不需要记住角色名称本身，但也不是必需的。

最后要添加的是列出绕过用户的视图：

CREATEVIEW@extschema@.v_ddl_bypass_usersASSELECTu.rolnameASuser_nameFROMpg_authid xJOINpg_auth_members mon(m.roleid=x.oid)JOINpg_authid uon(m.member=u.oid)WHEREx.rolname='block_ddl_allowed_user';GRANTSELECTON@extschema@.v_ddl_bypass_usersTOPUBLIC;

这是一个你能凭空知道的查询吗？可能不是。现在扩展帮你处理了，所以你不需要。

禁止通行

我们扩展的核心是一个 DDL 阻塞器：一个在ddl_command_start上触发的事件触发器，除非会话用户是超级用户，否则它会引发异常。这个阻塞例程的 C 版本比我们在这里构建的要复杂得多。

这是我们用于阻塞 DDL 的函数：

CREATEORREPLACEFUNCTION@extschema@.fn_block_ddl()RETURNSevent_triggerAS$$DECLAREenabledTEXT;allow_superTEXT;BEGIN-- 获取我们当前的配置设置SELECTsettingINTOenabledFROM@extschema@.ext_configWHEREname='enabled';SELECTsettingINTOallow_superFROM@extschema@.ext_configWHEREname='allow_super';-- 仅在以下情况下阻塞：-- 1. 扩展已启用IFenabled!='on'THENRETURN;-- 2. 允许超级用户且当前用户是超级用户ELSIF allow_super='on'AND(SELECTrolsuperFROMpg_catalog.pg_rolesWHERErolname=CURRENT_USER)THENRETURN;-- 3. 用户是 block_ddl_allowed_user 的成员ELSIFEXISTS(SELECT*FROM@extschema@.v_ddl_bypass_usersWHEREuser_name=CURRENT_USER)THENRETURN;ENDIF;RAISE EXCEPTION'DDL command "%" denied by block_ddl',tg_tagUSINGHINT='Connect as a superuser, ''or a user with block_ddl_allowed_user access';END;$$LANGUAGEplpgsql;

RETURNS event_trigger声明使此函数有资格与CREATE EVENT TRIGGER一起使用。这是一种特殊的返回类型，向 Postgres 指示如何调用该函数。

超级用户检查查询pg_catalog.pg_roles以获取current_user。这允许超级用户出于测试目的模拟其他用户，并且可能阻止意外的 DDL 执行，前提是他们先执行SET ROLE some_other_user。最后的检查是针对我们创建的v_ddl_bypass_users视图。我们可能会想使用pg_has_role信息函数来实现这一点，但该函数显示的是有效权限，而不是实际成员资格。超级用户拥有所有权限，因此如果我们不显式验证角色成员资格，他们会自动通过此检查。

函数就位后，创建事件触发器只需一行代码来调用该函数：

CREATEEVENTTRIGGERblock_ddlONddl_command_startEXECUTEFUNCTION@extschema@.fn_block_ddl();

ddl_command_start事件在任何 DDL 命令执行之前触发。如果我们的函数此时引发异常，命令将永远不会运行。简单易行。

在 Postgres 看来，什么算作“DDL”？实际上，相当多。ddl_command_start事件会为CREATE、ALTER、DROP、GRANT、REVOKE、COMMENT、REINDEX、REFRESH MATERIALIZED VIEW、SECURITY LABEL和SELECT INTO触发。它不会为针对数据库、角色、表空间，或者具有讽刺意味的是，针对事件触发器本身的命令触发。

我们也可以使用WHEN子句过滤特定的命令标签：

CREATEEVENTTRIGGERblock_ddlONddl_command_startWHENTAGIN('CREATE TABLE','DROP TABLE','ALTER TABLE')EXECUTEFUNCTION@extschema@.fn_block_ddl();

但这还有什么乐趣呢？

试运行

是时候看看这东西是否真能工作了。首先，安装扩展文件：

$cdblock_ddl $sudomakeinstall

这会将block_ddl.control和block_ddl--1.0.sql复制到扩展目录。现在连接到一个数据库并创建扩展：

CREATESCHEMAblock_ddl;CREATEEXTENSION block_ddlWITHSCHEMAblock_ddl;\dx block_ddl Listofinstalled extensions Name|Version|Defaultversion|Schema|Description-----------+---------+-----------------+-----------+---------------------------block_ddl|1.0|1.0|block_ddl|DDL blockingforPostgres

扩展已安装。让我们验证事件触发器是否就位：

SELECTevtname,evtevent,evtenabledFROMpg_event_triggerWHEREevtname='block_ddl';evtname|evtevent|evtenabled-----------+---------------------+------------block_ddl|ddl_command_start|O

evtenabled中的O表示“origin”，这是默认的启用状态（在除复制之外的所有上下文中触发）。是时候了！

测试阻塞器

默认情况下，阻塞是关闭的。让我们通过创建一个临时表来确认：

CREATETABLEscratch(idint);-- CREATE TABLEDROPTABLEscratch;-- DROP TABLE

没有报错。现在让我们启用阻塞器：

SELECTblock_ddl.alter_config('enabled','on');

然后再次测试：

CREATETABLEscratch(idint);-- CREATE TABLE

仍然有效。默认情况下，超级用户可以免费通行。让我们堵上这个漏洞：

SELECTblock_ddl.alter_config('allow_super','off');CREATETABLEscratch(idint);ERROR: DDL command"CREATE TABLE"deniedbyblock_ddl HINT:Connectasa superuser,orauserwithblock_ddl_allowed_user access CONTEXT: PL/pgSQLfunctionblock_ddl.fn_block_ddl()line27at RAISE

现在 DDL 命令被完全阻止。这应该适用于任何潜在的 DDL：

CREATEINDEXONscratch(id);ERROR: DDL command"CREATE INDEX"deniedbyblock_ddlALTERTABLEscratchADDCOLUMNnametext;ERROR: DDL command"ALTER TABLE"deniedbyblock_ddl

我们的绕过系统有效吗？

SELECTblock_ddl.add_ddl_bypass_user('postgres');CREATETABLEscratch(idint);-- CREATE TABLE

显式绕过现在允许 DDL。普通用户呢？让我们创建一个用户并再次测试：

CREATEUSERapp_user;SETROLE app_user;CREATETABLEnope(idint);ERROR: DDL command"CREATE TABLE"deniedbyblock_ddl

完全符合预期！

注意事项

纯 SQL 扩展功能强大，但它们不能完全替代 C。在您决定采用哪种方法之前，需要了解一些权衡。

• GUC 安全差距。在这个扩展的 C 版本中，GUC 使用PGC_SUSET上下文注册，这意味着只有超级用户可以更改它。在我们纯 SQL 版本中，block_ddl.enabled将是一个自定义参数，任何会话都可以修改。我们不得不通过使用配置表来为此设计一个有些迂回的解决方案。如果存在某种为扩展注册真正变量的 SQL 接口，这就没有必要了。
• 事件触发器盲点。一些 DDL 命令根本不会触发事件触发器。对数据库、角色、表空间以及事件触发器本身的操作是豁免的。像CREATE DATABASE或ALTER ROLE这样的操作完全豁免。这就是 Postgres 的内置权限系统（或pg_hba.conf限制）应该承担重任的地方。再次强调，C 扩展可以访问我们 SQL 版本只能梦想的功能。
• 没有后台工作进程或钩子。C 扩展可以注册后台工作进程、拦截查询计划、挂接到执行器，并在基础层面修改服务器行为。纯 SQL 扩展完全在 SQL 层内运行。如果您的用例涉及任何这些更深层次的功能，那么 C 是唯一的选择。

对于其他一切呢？函数、触发器、事件触发器、视图、类型、域、操作符、聚合、表等等都可以存在于纯 SQL 扩展中。这涵盖了相当多的领域。

总结

Postgres 扩展系统通常被认为需要 C 专业知识、编译器工具链和对服务器内部机制的深入理解。只有当您需要深入内部时，情况才确实如此。如果您曾经编写过一系列实用函数，并希望可以通过一条命令安装它们，那么您已经在考虑扩展了。打包的意义正在于此。

我们的block_ddl扩展演示了自定义配置表、角色、函数、视图和事件触发器。所有这些都是任何 Postgres 用户已经知道的标准 SQL 原语。唯一的新增部分是最小化的控制文件和 Makefile。只需要几行额外开销，就能获得干净的安装和卸载、版本管理和依赖跟踪。

如果您有一批函数、视图或触发器需要部署到环境中的每个数据库，请考虑花一个下午的时间将它们包装成一个扩展。您未来的自己，以及任何其他继承这些数据库的人，可能会感谢您这样做。