将用户 ID 传递给 PostgreSQL 触发器 [英] Passing user id to PostgreSQL triggers

问题描述

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

I'm using PostgreSQL 9.1. My database is structured so that there is actual tables that my application uses. For every table there is history table that stores only change history. History tables contain same fields that actual tables plus fields form some extra information eg. edit time. History tables are only handled by triggers.

我有两种触发器:

1. Before INSERT 触发器用于在创建表时向表添加一些额外信息(例如 create_time).
2. Before UPDATE 触发器和 before DELETE 触发器将旧值从实际表复制到历史表.

1. Before INSERT trigger to add some extra information to tables when they are created (eg. create_time).
2. Before UPDATE trigger and before DELETE triggers to copy old values from actual table to history table.

问题是我想使用触发器来存储进行这些更改的用户的 ID.id 是指来自 php 应用程序的 id，而不是 PostgreSQL 用户 id.

Problem is that I'd like to use triggers to store also the id of user who made those changes. And by id I mean id from php application, not PostgreSQL user id.

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

Is there any reasonable way to do that?

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

With INSERT and UPDATE it could be possible to just add extra field for id to actual tables and pass user id to SQL as part of SQL query. As far as I know this doesn't work with DELETE.

所有触发器的结构如下:

All triggers are structured as follows:

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

推荐答案

选项包括:

• 当你打开一个连接时，CREATE TEMPORARY TABLE current_app_user(username text);INSERT INTO current_app_user(username) VALUES ('the_user');.然后在您的触发器中，SELECT username FROM current_app_user 以获取当前用户名，可能作为子查询.

• When you open a connection, CREATE TEMPORARY TABLE current_app_user(username text); INSERT INTO current_app_user(username) VALUES ('the_user');. Then in your trigger, SELECT username FROM current_app_user to get the current username, possibly as a subquery.

在 postgresql.conf 中为 自定义 GUC 像 my_app.username = 'unknown';.每当你创建一个连接 run SET my_app.username = 'the_user';.然后在触发器中，使用 current_setting('my_app.username') 函数 获取值.实际上，您正在滥用 GUC 机制来提供会话变量.阅读适合您的服务器版本的文档，因为自定义 GUC 在 9.2 中发生了变化.

In postgresql.conf create an entry for a custom GUC like my_app.username = 'unknown';. Whenever you create a connection run SET my_app.username = 'the_user';. Then in triggers, use the current_setting('my_app.username') function to obtain the value. Effectively, you're abusing the GUC machinery to provide session variables. Read the documentation appropriate to your server version, as custom GUCs changed in 9.2.

调整您的应用程序，使其具有适用于每个应用程序用户的数据库角色.SET ROLE 在开始工作之前给那个用户.这不仅允许您使用内置的 current_user 类似变量的函数来SELECT current_user;，它还允许您在数据库中实施安全性.请参阅这个问题.您可以直接以用户身份登录，而不是使用 SET ROLE，但这往往会使连接池变得困难.

Adjust your application so that it has database roles for every application user. SET ROLE to that user before doing work. This not only lets you use the built-in current_user variable-like function to SELECT current_user;, it also allows you to enforce security in the database. See this question. You could log in directly as the user instead of using SET ROLE, but that tends to make connection pooling hard.

在所有三种情况下，您都在使用连接池，您必须小心 DISCARD ALL; 当您返回到池的连接时.(虽然它没有被记录为这样做，DISCARD ALL 做了一个 RESET ROLE).

In both all three cases you're connection pooling you must be careful to DISCARD ALL; when you return a connection to the pool. (Though it is not documented as doing so, DISCARD ALL does a RESET ROLE).

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();

使用 GUC:

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

Using a GUC:

• In the CUSTOMIZED OPTIONS section of postgresql.conf, add a line like myapp.username = 'unknown_user'. On PostgreSQL versions older than 9.2 you also have to set custom_variable_classes = 'myapp'.
• Restart PostgreSQL. You will now be able to SHOW myapp.username and get the value unknown_user.

现在您可以在建立连接时使用 SET myapp.username = 'the_user';，或者在 SET LOCAL myapp.username = 'the_user'; 之后使用 code>BEGIN如果你希望它是事务本地的，那么就可以将其设置为事务，这对于池化连接来说很方便.

Now you can use SET myapp.username = 'the_user'; when you establish a connection, or alternately SET LOCAL myapp.username = 'the_user'; after BEGINning a transaction if you want it to be transaction-local, which is convenient for pooled connections.

get_app_user 函数定义:

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

使用SET LOCAL作为交易本地当前用户名的演示:

Demo using SET LOCAL for transaction-local current username:

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 设置不会在提交/回滚时恢复，因此它在整个会话中都是持久的.它仍然被DISCARD ALL重置:

If you use SET instead of SET LOCAL the setting won't get reverted at commit/rollback time, so it's persistent across the session. It is still reset by DISCARD ALL:

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 与服务器端绑定参数一起使用.如果要使用绑定参数(准备好的语句")，请考虑使用函数形式 set_config(...).请参阅系统管理功能

Also, note that you can't use SET or SET LOCAL with server-side bind parameters. If you want to use bind parameters ("prepared statements"), consider using the function form set_config(...). See system adminstration functions

此方法需要使用触发器(或由触发器调用的辅助函数，最好是)尝试从每个会话应该具有的临时表中读取值.如果找不到临时表，则提供默认值.这可能有点慢.仔细测试.

This approach requires the use of a trigger (or helper function called by a trigger, preferably) that tries to read a value from a temporary table every session should have. If the temporary table cannot be found, a default value is supplied. This is likely to be somewhat slow. Test carefully.

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 开始，该功能尚未包含在内，但如果您需要，请留意并在黑客列表中发言.

Secure session variables

There's also a proposal to add "secure session variables" to PostgreSQL. These are a bit like package variables. As of PostgreSQL 12 the feature has not been included, but keep an eye out and speak up on the hackers list if this is something you need.

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

For advanced uses you can even have your own C extension register a shared memory area and communicate between backends using C function calls that read/write values in a DSA segment. See the PostgreSQL programming examples for details. You'll need C knowledge, time, and patience.

这篇关于将用户 ID 传递给 PostgreSQL 触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！