将用户id传递给PostgreSQL触发器

Question

我正在使用PostgreSQL 9.1。我的数据库是结构化的，因此我的应用程序使用的是实际的表。对于每个表，都有一个仅存储更改历史的历史记录表。历史记录表包含与实际表相同的字段，加上字段形成一些额外信息，如编辑时间。历史记录表仅由触发器处理。

我有两种触发器：

1. 在INSERT触发器之前，可以在表创建时向其添加一些额外的信息（例如create_time）。
2. UPDATE触发器之前和DELETE触发器之前将旧值从实际表复制到历史表。

问题是我想使用触发器来存储做出这些更改的用户的id。我所说的id是指来自php应用程序的id，而不是PostgreSQL用户id。

有什么合理的方法可以做到这一点吗？

使用INSERT和UPDATE，可以只为实际表添加id的额外字段，并将用户id作为SQL查询的一部分传递给SQL。据我所知，这不适用于删除。

所有触发器的结构如下所示：

CREATE OR REPLACE FUNCTION before_delete_customer() RETURNS trigger AS $BODY$
BEGIN
    INSERT INTO _customer (
        edited_by,
        edit_time,
        field1,
        field2,
        ...,
        fieldN
    ) VALUES (
        -1, // <- This should be user id.
        NOW(),
        OLD.field1,
        OLD.field2,
        ...,
        OLD.fieldN
    );
    RETURN OLD;
END; $BODY$
LANGUAGE plpgsql

Answer 1

另一个选项是在被审计的表中有一个last_updated_user_id。该值可以由PHP/Webapp轻松设置，并且可以在要添加到审计表的News.last_updated_user_id中使用

Answer 2

Set有一个这里没有提到的变体set会话。这很可能是应用程序开发人员通常真正想要的，而不是普通设置或本地设置。

set session trolol.userr = 'Lol';

我的测试触发器设置有点简单，但是这个想法和克雷格·林格的选项2是一样的。

create table lol (
    pk varchar(3) not null primary key,
    createuser varchar(20) not null);


CREATE OR REPLACE function update_created() returns trigger as $$ 
     begin new.createuser := current_setting('trolol.userr'); return new; end; $$ language plpgsql;


create trigger lol_update before update on lol for each row execute procedure update_created();
create trigger lol_insert before insert on lol for each row execute procedure update_created();

在这一点上，我认为这是可以接受的。没有DDL语句，如果由于某种原因意外未设置会话变量，则insert/update将不会成功。

使用DISCARD ALL可能不是一个好主意，因为它会丢弃所有内容。例如，SqlKorma根本不喜欢这样。相反，您可以使用

SET software.theuser TO DEFAULT

我简要考虑了第四种选择。在标准变量集中有“应用程序名称”，可以使用。此解决方案有一些局限性，但也有一些明显的优势，具体取决于上下文。

有关这第四个选项的更多信息，请参阅以下内容：

通过JDBC设置应用程序名称

Postgre留档application_name

Answer 3

选择包括：

>

打开连接时，创建临时表current\u app\u user（用户名文本）；在当前用户（用户名）值（“用户”）中插入 。然后在触发器中，选择当前应用程序用户的用户名以获取当前用户名，可能作为子查询。

在postgresql中。conf为自定义GUC（如my_应用程序）创建一个条目。用户名='未知' 。无论何时创建连接，请运行设置我的应用程序。用户名='the_user' 。然后在触发器中，使用current\u设置（'my\u app.username'）函数获取值。实际上，您正在滥用GUC机制来提供会话变量。阅读适合您的服务器版本的文档，因为自定义GUC在9.2中发生了更改。

调整应用程序，使其具有每个应用程序用户的数据库角色<代码>在执行工作之前为该用户设置角色。这不仅允许您使用内置的current\u user类变量函数选择current\u user ，它还允许您在数据库中强制执行安全性。看这个问题。您可以直接以用户身份登录，而不必使用设置角色，但这往往会使连接池变得困难。

在这三种情况下，您都是连接池，您必须小心地放弃所有连接池 当您返回到池的连接时。（尽管没有记录这样做，放弃所有执行一个重置角色）。

CREATE TABLE tg_demo(blah text);
INSERT INTO tg_demo(blah) VALUES ('spam'),('eggs');

-- Placeholder; will be replaced by demo functions
CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
SELECT 'unknown';
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION tg_demo_trigger() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Current user is: %',get_app_user();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_demo_tg
AFTER INSERT OR UPDATE OR DELETE ON tg_demo 
FOR EACH ROW EXECUTE PROCEDURE tg_demo_trigger();

• 在postgresql.conf的CUSTOMIZED OPTIONS部分，添加一行，如myapp.username='unknown_user'。对于9.2以上的PostgreSQL版本，您还必须设置custom_variable_classes='myapp'。
• 重启PostgreSQL。您现在可以SHOWmyapp.username并获得值unknown_user。

现在，您可以在建立连接时使用SETmyapp.username='the_user'；，或者在BEGIN后使用SET LOCALmyapp.username='the_user'；是事务本地的，这便于池连接。

get\u app\u用户函数定义：

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
    SELECT current_setting('myapp.username');
$$ LANGUAGE sql;

使用事务本地当前用户名的SET LOCAL演示：

regress=> BEGIN;
BEGIN
regress=> SET LOCAL myapp.username = 'test_user';
SET
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: test_user
INSERT 0 1
regress=> COMMIT;
COMMIT
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

如果使用SET而不是SET LOCAL，则在提交/回滚时不会恢复该设置，因此它在整个会话中是持久的。它仍然由放弃所有重置：

regress=> SET myapp.username = 'test';
SET
regress=> SHOW myapp.username;
 myapp.username 
----------------
 test
(1 row)

regress=> DISCARD ALL;
DISCARD ALL
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

另外，请注意，不能将SET或SET LOCAL与服务器端绑定参数一起使用。如果您想使用绑定参数（“准备语句”），请考虑使用函数窗体<代码> SETIONCOFIG（…）<代码>。请参阅系统管理功能

这种方法需要使用触发器（最好是触发器调用的帮助函数），它试图从每个会话应该具有的临时表中读取值。如果找不到临时表，则提供默认值。这可能会有些缓慢。仔细测试。

get_app_user定义：

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
DECLARE
    cur_user text;
BEGIN
    BEGIN
        cur_user := (SELECT username FROM current_app_user);
    EXCEPTION WHEN undefined_table THEN
        cur_user := 'unknown_user';
    END;
    RETURN cur_user;
END;
$$ LANGUAGE plpgsql VOLATILE;

演示：

regress=> CREATE TEMPORARY TABLE current_app_user(username text);
CREATE TABLE
regress=> INSERT INTO current_app_user(username) VALUES ('testuser');
INSERT 0 1
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: testuser
INSERT 0 1
regress=> DISCARD ALL;
DISCARD ALL
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: unknown_user
INSERT 0 1

还有人建议在PostgreSQL中添加“安全会话变量”。这些有点像包变量。截至PostgreSQL 12，该功能尚未包括在内，但请密切注意，如果您需要，请在黑客列表中发言。

对于高级使用，您甚至可以让自己的C扩展注册共享内存区域，并使用读取/写入DSA段中的值的C函数调用在后端之间进行通信。有关详细信息，请参见PostgreSQL编程示例。你需要知识、时间和耐心。