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

Ответ 1

Параметры включают:

• Когда вы открываете соединение, 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 для предоставления переменных сеанса. Прочтите документацию, соответствующую вашей версии сервера, так как измененные пользовательские GUCs в 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 при транзакции, если вы хотите, чтобы она была локальной транзакцией, что удобно для объединенных подключений.

Определение функции 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, установка не будет возвращена в момент фиксации/откат, поэтому она будет постоянной в течение сеанса. Он по-прежнему reset на 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

Ответ 2

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

set session trolol.userr = 'Lol';

Моя настройка тестового триггера была немного проще, но идея такая же, как и вариант 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();

Я нахожу это вполне приемлемым на данный момент. Никакие инструкции DDL и вставка/обновление не будут успешными, если переменная сеанса случайно не установлена ​​по какой-либо причине.

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

SET software.theuser TO DEFAULT

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

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

установка имени_процесса через JDBC

postgre documentation on application_name