Передача идентификатора пользователя в триггеры PostgreSQL

Я использую PostgreSQL 9.1. Моя база данных структурирована так, что есть реальные таблицы, которые использует мое приложение. Для каждой таблицы есть таблица истории, в которой хранится только история изменений. Таблицы истории содержат те же поля, что фактические таблицы плюс поля образуют некоторую дополнительную информацию, например. редактировать время Таблицы истории обрабатываются только триггерами.

У меня есть 2 вида триггеров:

  1. Before INSERT триггер для добавления дополнительной информации в таблицы при их создании (например, create_time).
  2. Before UPDATE вызвать и before DELETE триггеры для копирования старых значений из фактической таблицы в таблицу истории.

Проблема в том, что я хотел бы использовать триггеры для хранения идентификатора пользователя, который внес эти изменения. И под идентификатором я подразумеваю идентификатор из приложения php, а не идентификатор пользователя PostgreSQL.

Есть ли разумный способ сделать это?

С INSERT и UPDATE можно было бы просто добавить дополнительное поле для идентификатора в реальные таблицы и передать идентификатор пользователя в SQL как часть запроса SQL. Насколько я знаю, это не работает с DELETE.

Все триггеры структурированы следующим образом:

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
Источник

2 ответа

Решение

Варианты включают в себя:

  • Когда вы открываете соединение, 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 переменная функция SELECT current_user; Это также позволяет обеспечить безопасность в базе данных. Смотрите этот вопрос. Вы можете войти в систему как пользователь вместо использования SET ROLE, но это имеет тенденцию усложнять пул соединений.

В обоих трех случаях вы используете пул соединений, вы должны быть осторожны с DISCARD ALL; когда вы вернете соединение в пул. ( Хотя это не задокументировано, DISCARD ALL делает 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:

  • в CUSTOMIZED OPTIONS раздел postgresql.conf, добавьте строку как myapp.username = 'unknown_user', В версиях PostgreSQL старше 9.2 вы также должны установить custom_variable_classes = 'myapp',
  • Перезапустите PostgreSQL. Теперь вы сможете SHOW myapp.username и получить значение unknown_user,

Теперь вы можете использовать SET myapp.username = 'the_user'; когда вы устанавливаете соединение, или поочередно SET LOCAL myapp.username = 'the_user'; после BEGIN ning транзакция, если вы хотите, чтобы она была локальной для транзакции, что удобно для соединений в пуле.

get_app_user определение функции:

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 параметр не будет возвращен во время фиксации / отката, поэтому он сохраняется в течение всего сеанса. Это все еще сбрасывается 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)

Использование временной таблицы

Этот подход требует использования триггера (или вспомогательной функции, вызываемой триггером, предпочтительно), который пытается прочитать значение из временной таблицы, которую должен иметь каждый сеанс. Если временная таблица не может быть найдена, предоставляется значение по умолчанию. Это, вероятно, будет несколько медленным. Проверьте внимательно.

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
Источник

У набора есть сессия набора вариантов, не упомянутая здесь. Скорее всего, что разработчики приложений обычно действительно хотят вместо простого или локального набора.

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, и вставка / обновление не будут выполнены, если переменная сеанса случайно не установлена ​​по какой-либо причине.

С помощью DISCARD ALL не может быть хорошей идеей, поскольку это отбрасывает все. Например SqlKorma не нравится это вообще. Вместо этого вы можете просто сбросить переменную, используя

SET software.theuser TO DEFAULT

Был четвертый вариант, который я кратко рассмотрел. В стандартном наборе переменных есть "application_name", которое можно использовать. Это решение имеет некоторые ограничения, но также и некоторые явные преимущества в зависимости от контекста.

Для получения дополнительной информации об этом четвертом варианте обратитесь к этим:

установка application_name через JDBC

Postgre документация на имя_приложения

Источник

Другой вариант - иметь last_updated_user_idв проверяемой таблице. Это значение может быть легко установлено PHP/Webapp и будет доступно вNEW.last_updated_user_id быть добавленным в таблицу аудита

Источник

Я думаю, что на момент написания этого ответа удовлетворительного решения не было. В итоге я использовал set_config в промежуточном программном обеспечении nodejs.

app.use((req, res, next)=>{
    db.query("SELECT set_config('myapp._user_id', $1, false)", [req.session.user.id]);
    next();
});

После отладки я обнаружил, что он установлен глобально для всех пользователей. (Состояние гонки может легко возникнуть) И вы не можете использовать SET LOCAL, потому что вам нужно НАЧАТЬ транзакцию при запуске запроса и COMMIT/ROLLBACK, когда запрос завершается.

Источник
Другие вопросы по тегам