将用户 ID 传递给 PostgreSQL 触发器

Question

提问by pipo

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.

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

I have 2 kind of triggers:

我有两种触发器：

Before INSERTtrigger to add some extra information to tables when they are created (eg. create_time).
Before UPDATEtrigger and before DELETEtriggers to copy old values from actual table to history table.

Before INSERT触发器在创建时向表添加一些额外信息（例如 create_time）。
Before UPDATE触发器和before DELETE触发器将旧值从实际表复制到历史表。

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.

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

Is there any reasonable way to do that?

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

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.

使用 INSERT 和 UPDATE 可以将 id 的额外字段添加到实际表中，并将用户 id 作为 SQL 查询的一部分传递给 SQL。据我所知，这不适用于 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

Answer 1

回答by Craig Ringer

Options include:

选项包括：

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_userto get the current username, possibly as a subquery.
In postgresql.confcreate an entry for a custom GUClike 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')functionto 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.
Adjust your application so that it has database roles for every application user. SET ROLEto that user before doing work. This not only lets you use the built-in current_uservariable-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.

打开连接时，CREATE TEMPORARY TABLE current_app_user(username text); INSERT INTO current_app_user(username) VALUES ('the_user');. 然后在您的触发器中，SELECT username FROM current_app_user获取当前用户名，可能作为子查询。
在postgresql.conf为自定义 GUC创建一个条目，如my_app.username = 'unknown';. 每当您创建连接时，运行SET my_app.username = 'the_user';. 然后在触发器中，使用current_setting('my_app.username')函数获取值。实际上，您正在滥用 GUC 机制来提供会话变量。阅读适合您的服务器版本的文档，因为自定义 GUC 在 9.2 中发生了变化。
调整您的应用程序，使其具有适用于每个应用程序用户的数据库角色。SET ROLE在工作之前给那个用户。这不仅允许您使用内置的current_user类似变量的函数 to SELECT current_user;，还允许您在数据库中强制执行安全性。看到这个问题。您可以直接以用户身份登录而不是使用SET 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 ALLdoes a RESET ROLE).

在这三种情况下，您都在使用连接池，DISCARD ALL;当您将连接返回到池时必须小心。（虽然它没有记录为这样做，DISCARD ALL但确实如此RESET ROLE）。

Common setup for demos:

演示的常见设置：

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

Using a GUC:

使用 GUC：

In the CUSTOMIZED OPTIONSsection 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.usernameand get the value unknown_user.

在的CUSTOMIZED OPTIONS部分中postgresql.conf，添加一行，如myapp.username = 'unknown_user'。在早于 9.2 的 PostgreSQL 版本上，您还必须设置custom_variable_classes = 'myapp'.
重启 PostgreSQL。您现在将能够SHOW myapp.username获得价值unknown_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.

现在您可以SET myapp.username = 'the_user';在建立连接时使用，或者如果您希望它是事务本地的，也可以SET LOCAL myapp.username = 'the_user';在BEGINning 事务之后使用，这对于池连接很方便。

The get_app_userfunction definition:

该get_app_user函数的定义：

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

Demo using SET LOCALfor transaction-local current username:

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)

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

如果您使用SET而不是SET LOCAL设置不会在提交/回滚时恢复，因此它在整个会话中是持久的。它仍然通过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)

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

另请注意，您不能使用SET或SET LOCAL与服务器端绑定参数一起使用。如果要使用绑定参数（“准备好的语句”），请考虑使用函数 form set_config(...)。查看系统管理功能

Using a temporary table

使用临时表

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.

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

The get_app_user()definition:

该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;

Demo:

演示：

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

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.

还有一个建议是向 PostgreSQL 添加“安全会话变量”。这些有点像包变量。从 PostgreSQL 12 开始，该功能尚未包含在内，但如果您需要，请留意并在黑客列表中发言。

Advanced: your own extension with shared memory area

高级：您自己的共享内存区扩展

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.

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

Answer 2

回答by lokori

set has a variant set session not mentioned here. It's most likely what application developers usually really want instead of plain set or set local.

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

set session trolol.userr = 'Lol';

My test trigger setup was a bit simpler, but the idea is the same as Craig Ringer's option 2.

我的测试触发器设置稍微简单一些，但其想法与 Craig Ringer 的选项 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();

I find this quite acceptable at this point. No DDL statements and the insert/update will not succeed if the session variable is accidentally not set for some reason.

我觉得这在这一点上是可以接受的。如果由于某种原因意外没有设置会话变量，则没有 DDL 语句并且插入/更新将不会成功。

Using DISCARD ALLmay not be a good idea as it discards everything. For example SqlKorma does not like this at all. Instead you could just reset the variable using

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

SET software.theuser TO DEFAULT

There was a fourth option I briefly considered. In the standard set of variables there is "application_name" which could be used. This solution has some limitations, but also some clear advantages depending on the context.

我简要考虑了第四种选择。在标准变量集中，可以使用“application_name”。这种解决方案有一些限制，但根据上下文也有一些明显的优势。

For more information on this fourth option refer to these:

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

setting application_name through JDBC

通过 JDBC 设置 application_name

postgre documentation on application_name

关于 application_name 的 postgre 文档

Answer 3

回答by DavidC

Another option is to have a last_updated_user_idin the table being audited. This value can be set by the PHP/Webapp easily and will be available in the NEW.last_updated_user_idto be added to the audit table

另一种选择是last_updated_user_id在被审计的表中有一个。这个值可以很容易地由 PHP/Webapp 设置，并且可以在NEW.last_updated_user_id被添加到审计表中使用

将用户 ID 传递给 PostgreSQL 触发器

提问by pipo

回答by Craig Ringer

Common setup for demos:

演示的常见设置：

Using a GUC:

使用 GUC：

Using a temporary table

使用临时表

Secure session variables

安全会话变量

Advanced: your own extension with shared memory area

高级：您自己的共享内存区扩展

回答by lokori

回答by DavidC

相关推荐

最近更新

标签

将用户 ID 传递给 PostgreSQL 触发器

提问by pipo

回答by Craig Ringer

Common setup for demos:

演示的常见设置：

Using a GUC:

使用 GUC：

Using a temporary table

使用临时表

Secure session variables

安全会话变量

Advanced: your own extension with shared memory area

高级：您自己的共享内存区扩展

回答by lokori

回答by DavidC

相关推荐

postgresql 比较数组的相等性，忽略元素的顺序

如何在现有数据库中安装 postgresql-contrib 模块？

PostgreSQL：在哪里和不在那里

如何更改 postgresql 中的外键值？

相关推荐

最近更新

标签