将用户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.

我有2种触发器:

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.

有什么合理的方法吗?

使用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';).每当您创建连接时，运行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.

在这三种情况下，您都是连接池，必须小心

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';，或者在BEGIN设置事务后也可以选择SET LOCAL myapp.username = 'the_user';，如果您希望它是本地事务的话，这对于合并连接很方便.

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屋！