使用postgres扩展作为非特权用户

Question

问题

我有以下使用moddatetime扩展的触发函数：

/* BEFORE UPDATE trigger - Updates "entity_version.updated_on", setting it to the current UTC time. */
CREATE EXTENSION IF NOT EXISTS moddatetime;  -- Needs superuser privileges!
DROP TRIGGER IF EXISTS trig_entity_version_before_update
ON entity_version;
CREATE TRIGGER trig_entity_version_before_update
BEFORE UPDATE
ON entity_version
FOR EACH ROW EXECUTE PROCEDURE moddatetime(updated_on);  -- updated_on is TIMESTAMP type

触发器工作得很好，但问题是第一行(CREATE EXTENSION(需要超级用户权限。由于这些数据库将由用户(通过脚本(创建，我不希望这些数据库和触发器的用户拥有超级用户访问权限。

我尝试过的

作为第一步，以超级用户的身份运行脚本可以很好地工作，正如您所期望的那样。

当然，下一步将把作为超级用户创建扩展与触发器创建脚本分开。但是，如果我在没有CREATE EXTENSION行的情况下运行上面的脚本，我会得到以下错误：

function moddatetime() does not exist

考虑到脚本从未声明moddatetime，但我在文档中找不到如何在不使用CREATE EXTENSION的情况下将扩展定义为可用，我认为这是有道理的。肯定有一种方法可以在不创建扩展的情况下导入或使用它吗？类似于：

USING EXTENSION moddatetime;  -- Does something like this exist?
... rest of the trigger script ...

Answer 1

如果你想使用扩展，你需要安装它。

PostgreSQL v13引入了可信扩展的概念，具有CREATE权限的非超级用户可以安装该扩展，但该扩展不在其中。所以升级并不能解决问题。

您可以作为超级用户连接到template1并在那里创建扩展。然后它将自动出现在所有新创建的数据库中。

但坦率地说，我不认为这有什么意义。使用常规PL/pgSQL触发器也可以做同样的事情——只是性能不会那么好。

Answer 2

根据@laurenz的建议，我刚刚停止为这个特定的用例使用扩展。幸运的是，编写一个函数来做扩展所做的事情相对来说是微不足道的。我将把它包括在这里，以备有人专门寻找moddatetime的替代品时参考。

DROP FUNCTION IF EXISTS fn_entity_version_before_update() CASCADE;
CREATE FUNCTION fn_entity_version_before_update() RETURNS TRIGGER LANGUAGE PLPGSQL AS
$fn_body$
BEGIN
NEW.updated_on = NOW() AT TIME ZONE 'utc';
RETURN NEW;
END;
$fn_body$;
DROP TRIGGER IF EXISTS trig_entity_version_before_update
ON entity_version;
CREATE TRIGGER trig_entity_version_before_update
BEFORE UPDATE
ON entity_version
FOR EACH ROW EXECUTE PROCEDURE fn_entity_version_before_update();