来源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 extensioncommentDDL blocking for Postgresdefault_version1.0superusertruerelocatablefalsecomment会显示在\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 构建基础设施负责处理其余部分。样板文件处理完毕是时候找点乐子了。一些簿记工作在我们真正开始之前扩展需要存在于一个模式中。该模式中的一些对象需要是公共可访问的。因此我们文件中的第一件事需要如下所示GRANTUSAGEONSCHEMAextschemaTOPUBLIC;USAGE仅意味着模式对象是可见的。除非特别授权否则用户将无法创建对象甚至无法从表中选择。之后我们需要处理配置设置。您可能认为首选是使用会话变量但这是一个微妙的陷阱。这里的问题是纯 SQL 扩展无法访问系统变量的更精细控制点例如将它们限制为超级用户、系统启动、服务重载等。这意味着无法阻止用户通过简单的SET语句覆盖它们。下一个选项是配置表。扩展文档说我们可以注册这些表以便在转储和恢复数据库时保留值并且控制表更新很简单。所以让我们用以下内容开始我们的扩展CREATETABLEextschema.ext_config(nameTEXTPRIMARYKEY,settingTEXTNOTNULL);INSERTINTOextschema.ext_configVALUES(enabled,off),(allow_super,on);SELECTpg_catalog.pg_extension_config_dump(extschema.ext_config,);GRANTSELECTONextschema.ext_configTOPUBLIC;CREATEORREPLACEFUNCTIONextschema.alter_config(p_nameTEXT,p_settingTEXT)RETURNSBOOLEANAS$$BEGINIFp_nameIN(enabled,allow_super)THENUPDATEextschema.ext_configSETsetting(CASEWHENp_settingonTHENonELSEoffEND)WHEREnamep_name;ENDIF;RETURNtrue;END;$$LANGUAGEplpgsql;REVOKEEXECUTEONFUNCTIONextschema.alter_config(TEXT,TEXT)FROMPUBLIC;现在只有超级用户可以配置扩展普通用户仍然需要能够读取配置表因为事件触发器是以该用户身份运行的。无论如何我们现在有了一个方便的配置接口。角色设计下一步是允许某些用户绕过 DDL 限制。最简单的方法是创建一个角色超级用户可以将这些被允许的用户授予该角色。我们还可以在这里处理我们有用的授权/撤销函数CREATEROLE block_ddl_allowed_user;CREATEORREPLACEFUNCTIONextschema.add_ddl_bypass_user(p_userTEXT)RETURNSBOOLEANAS$$BEGINEXECUTEformat(GRANT block_ddl_allowed_user TO %I,p_user);RETURNtrue;END;$$LANGUAGEplpgsql;CREATEORREPLACEFUNCTIONextschema.remove_ddl_bypass_user(p_userTEXT)RETURNSBOOLEANAS$$BEGINEXECUTEformat(REVOKE block_ddl_allowed_user FROM %I,p_user);RETURNtrue;END;$$LANGUAGEplpgsql;REVOKEEXECUTEONFUNCTIONextschema.add_ddl_bypass_user(TEXT)FROMPUBLIC;REVOKEEXECUTEONFUNCTIONextschema.remove_ddl_bypass_user(TEXT)FROMPUBLIC;使用包含扩展名的长名称block_ddl_allowed_user是为了防止名称冲突。这个角色可能尚未被使用并且其目的显而易见。这些函数意味着管理员不需要记住角色名称本身但也不是必需的。最后要添加的是列出绕过用户的视图CREATEVIEWextschema.v_ddl_bypass_usersASSELECTu.rolnameASuser_nameFROMpg_authid xJOINpg_auth_members mon(m.roleidx.oid)JOINpg_authid uon(m.memberu.oid)WHEREx.rolnameblock_ddl_allowed_user;GRANTSELECTONextschema.v_ddl_bypass_usersTOPUBLIC;这是一个你能凭空知道的查询吗可能不是。现在扩展帮你处理了所以你不需要。禁止通行我们扩展的核心是一个 DDL 阻塞器一个在ddl_command_start上触发的事件触发器除非会话用户是超级用户否则它会引发异常。这个阻塞例程的 C 版本比我们在这里构建的要复杂得多。这是我们用于阻塞 DDL 的函数CREATEORREPLACEFUNCTIONextschema.fn_block_ddl()RETURNSevent_triggerAS$$DECLAREenabledTEXT;allow_superTEXT;BEGIN-- 获取我们当前的配置设置SELECTsettingINTOenabledFROMextschema.ext_configWHEREnameenabled;SELECTsettingINTOallow_superFROMextschema.ext_configWHEREnameallow_super;-- 仅在以下情况下阻塞-- 1. 扩展已启用IFenabled!onTHENRETURN;-- 2. 允许超级用户且当前用户是超级用户ELSIF allow_superonAND(SELECTrolsuperFROMpg_catalog.pg_rolesWHERErolnameCURRENT_USER)THENRETURN;-- 3. 用户是 block_ddl_allowed_user 的成员ELSIFEXISTS(SELECT*FROMextschema.v_ddl_bypass_usersWHEREuser_nameCURRENT_USER)THENRETURN;ENDIF;RAISE EXCEPTIONDDL command % denied by block_ddl,tg_tagUSINGHINTConnect 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_startEXECUTEFUNCTIONextschema.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)EXECUTEFUNCTIONextschema.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_triggerWHEREevtnameblock_ddl;evtname|evtevent|evtenabled--------------------------------------------block_ddl|ddl_command_start|Oevtenabled中的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 commandCREATE TABLEdeniedbyblock_ddl HINT:Connectasa superuser,orauserwithblock_ddl_allowed_user access CONTEXT: PL/pgSQLfunctionblock_ddl.fn_block_ddl()line27at RAISE现在 DDL 命令被完全阻止。这应该适用于任何潜在的 DDLCREATEINDEXONscratch(id);ERROR: DDL commandCREATE INDEXdeniedbyblock_ddlALTERTABLEscratchADDCOLUMNnametext;ERROR: DDL commandALTER TABLEdeniedbyblock_ddl我们的绕过系统有效吗SELECTblock_ddl.add_ddl_bypass_user(postgres);CREATETABLEscratch(idint);-- CREATE TABLE显式绕过现在允许 DDL。普通用户呢让我们创建一个用户并再次测试CREATEUSERapp_user;SETROLE app_user;CREATETABLEnope(idint);ERROR: DDL commandCREATE TABLEdeniedbyblock_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。只需要几行额外开销就能获得干净的安装和卸载、版本管理和依赖跟踪。如果您有一批函数、视图或触发器需要部署到环境中的每个数据库请考虑花一个下午的时间将它们包装成一个扩展。您未来的自己以及任何其他继承这些数据库的人可能会感谢您这样做。