PostgreSQL 17がやってくる(3) ログインイベントトリガ

Last updated at 2024-04-21Posted at 2024-04-21

はじめに

にゃーん

今回は、PostgreSQL 17develに入っている「ログインイベントトリガ」を簡単に調べてみた。

調査バージョン

今回の調査では、2024-04-19時点のPostgreSQL 17devel(commit 82023d47de9e262730b1f9b4ea77fae201a89d0a)を使用した。

ログインイベントトリガとは？

ログインイベントトリガは、その名前のとおりログインイベント（認証されたユーザがシステムにログインするときに発生するイベント）をトリガとして処理を行う仕組みである。
これはPostgreSQLのイベントトリガの新しい種類として、PostgreSQL 17から追加される。

イベントトリガ

一般的なトリガはDML（INSERT, UPDATE, DELETE, TRUNCATE）の実行前/実行後に行い処理を定義するが、イベントトリガは、それ以外のイベントの発生時に実行される。
イベントトリガはPostgreSQL 12から導入された機構であり、PostgreSQL 16では以下のイベントに対応している。

イベント名	イベントの内容
ddl_command_start	ddl_command_startイベントは、CREATE、ALTER、DROP、SECURITY LABEL、COMMENT、GRANT、またはREVOKEコマンドの実行直前に発生する。
ddl_command_end	ddl_command_startイベントに対応するコマンドの実行直後に発生する。このトリガはアクションが実行された後（しかしトランザクションがコミットする前）に起動される。
sql_drop	sql_dropイベントは、データベースオブジェクトを削除するSQLコマンド(DROP ...)操作のddl_command_endイベントトリガの直前に発生する。このトリガはオブジェクトがシステムカタログから削除された後に実行される。
table_rewrite	ALTER TABLEや ALTER TYPE等の定義を変更するコマンドによりシステムテーブルが書き換えられる直前に発生する。 CLUSTERや VACUUMのようなテーブル内容を書き換えるコマンドでは発火しない。

PostgreSQL 16のイベントトリガの詳細はPostgreSQL文書のOverview of Event Trigger Behavior参照。

ログインイベントトリガのPostgreSQL文書での説明

PostgreSQL 17で追加されたログインイベントトリガの内容をPostgreSQL文書から読み解くとこんな感じらしい。

ログイン・イベントは、認証されたユーザがシステムにログインするときに発生します。
- ログイン試行の時点ではなく、認証に成功したときに発生すると読める。
接続文字列または構成ファイルでevent_triggersを falseに設定すると（ログインイベントトリガを含む）イベントトリガが無効になる。シングルユーザモードで起動することでイベントトリガを無効にすることもできる。
- 個人的には接続文字列指定でイベントトリガを無効化できるのはどうなんだろ？という気もする。postgresql.confの指定やシングルユーザモードでの再起動等の救済策はあるので。
ログインイベントはスタンバイサーバでも発生する。
- スタンバイサーバでログインイベントトリガが発火し、そのトリガプロシージャ内で、スタンバイサーバ上のデータを更新しようとするエラーになり、ログインできないという問題がおきる。
接続元のクライアント（例えば、psql）からログイン後に接続をキャンセルしても、実行中のログイントリガはキャンセルされない。

PostgreSQL文書上は、接続文字列にevent_triggers=offが指定可能と記載しているが、実際に接続文字列内に指定するとinvalid connection optionと言われてしまう。まだ、接続文字列対応できていない版で検証したからか？後日確認予定。

$ ~/pgsql/postgres/bin/psql 'host=127.0.0.1 port=17001 user=postgres dbname=db_a event_triggers=off'
psql: error: invalid connection option "event_triggers"
$

ログインイベントトリガ発生時に取得できる情報

ログインイベントトリガ用の関数も、他のイベントトリガ用関数と同様に、入力パラメータはない。なので、トリガ用関数内で、他のSQL関数等で情報を収集する必要があるようだ。
PostgreSQL文書 A Database Login Event Trigger Exampleの説明では、pg_is_in_recovery()を使って、データベースがリカバリ中かどうかをチェッする例が載っている。これはストリーミングレプリケーションを構成する場合には、たぶん必ずやっておかないといけなさそうだ。

ログインイベントトリガ関数内でSQL関数経由で取得すると良さげな情報は

あたりかなあ。

実機検証

ということで、ここから実際にログインイベントトリガの例をいくつか試してみる。
（今回は面倒なのでトリガ関数はPL/pgSQLで実装するけど、ガチでやるならC言語でのSQL関数も考えるほうがいいのかもしれない。）

基本的な流れ

まずSQL関数をCREATE FUNCTIONで作成する。以下は、PL/pgSQLでイベントトリガ用SQL関数を組むときの作法。
- 引数：なし。
- 返却型：event_trigger
- 実行部からの返却。RETURNのみで良さそう。
- エラー発生時（あるいはログインさせたくない）場合にはRAISE EXCEPTIONを発生させる。
SQL関数作成後に、CREATE EVENT TRIGGERを実行する。
- 種別にはON loginを指定。
- EXECUTE FUNCTIONに先に作成したSQL関数

サンプル：ログイン監査

非常に簡単な例として、ログインしたときの

セッションユーザ名
接続データベース名
イベントトリガ実行時刻

を監査用テーブルに挿入するログイン監査っぽいものをやってみる。

事前準備

postgres以外の一般データベースユーザ(user_a,user_b)を作成する。
イベントトリガを設定するデータベース(db_a)を作成する。

ログイン監査用のスキーマ/テーブルを作成する。

以下のようなスキーマとテーブルを作成しuser_a,user_bに対する適切な権限を設定する。

CREATE SCHEMA audit;

CREATE TABLE IF NOT EXISTS audit.login_event (
  id bigserial primary key,
  user_name text,
  db_name text,
  login_timestamp timestamp
);

--
-- user_a, user_bへのスキーマへのアクセス権限
--
GRANT USAGE ON SCHEMA audit TO user_a, user_b;

--
-- user_a, user_bへのlogin_eventへのアクセス権限
--
GRANT INSERT ON TABLE audit.login_event TO user_a, user_b;

一般ユーザuser_aやuser_bでログインした場合、ログインイベント関数内でaudit.login_eventテーブルへの挿入をするためINSERT権限は必要だが、一般ユーザによる監査用テーブルのでaudit.login_eventの内容が参照できるのは好ましくない、という想定でこのテーブルにはINSERT権限のみ与えている。

イベントトリガ関数の定義

こんなイベントトリガ関数を作成する。内容はセッションユーザ名、データベース名、現在時刻を取得して、audit.login_eventテーブルにINSERTしているだけ。

--
-- ログインイベントトリガ関数
-- login_auditテーブルへセッション情報をINSERTする
--
CREATE OR REPLACE FUNCTION login_event_func ()
  RETURNS event_trigger SECURITY DEFINER
  LANGUAGE plpgsql AS
$$
BEGIN
--  RAISE NOTICE 'session_user=%,current_database=%', session_user, current_database() ;
INSERT INTO audit.login_event (user_name, db_name, login_timestamp) VALUES
  ( session_user, current_database(), current_timestamp);
END;
$$
;

今回は簡易化のために実装していませんが、実運用時には、スタンバイで動作するときのチェック（例：pg_is_in_recovery()でリカバリモードなのかチェックしてtrueだった場合（テーブルへの更新ができないので）トリガ関数を即座に終了する、といった処理を組み込んでおかないと、スタンバイ側へのログインができなくなる、といったツラいことになるので注意が必要です。

イベントトリガの定義

ログインイベントトリガを定義する。
このときに、さっき作成したログインイベントトリガ関数を指定する。
作成後にALTER EVENT TRIGGERコマンドでイベントトリガを有効にしておく。

-- ログインイベントトリガ定義
CREATE EVENT TRIGGER login_event_trig
  ON login
  EXECUTE FUNCTION login_event_func();
ALTER EVENT TRIGGER login_event_trig ENABLE ALWAYS;

実行例

まず、postgresユーザでログインしてSELECT文を実行する。

$ ~/pgsql/postgres/bin/psql -p 17001 -U postgres db_a -c "SELECT now()"
Null display is "(null)".
              now
-------------------------------
 2024-04-21 16:20:11.004477+09
(1 row)

次に、user_aユーザでログインしてSELECT文を実行する。

$ ~/pgsql/postgres/bin/psql -p 17001 -U user_a db_a -c "SELECT now()"
Null display is "(null)".
              now
-------------------------------
 2024-04-21 16:20:23.813146+09
(1 row)

その後、user_aユーザでログインしてaudit.login_eventテーブルを参照しようとしても権限エラーになる（想定どおりの挙動）。

$ ~/pgsql/postgres/bin/psql -p 17001 -U user_a db_a -c "TABLE audit.login_event"
Null display is "(null)".
2024-04-21 16:20:44.429 JST [6496] ERROR:  permission denied for table login_event
2024-04-21 16:20:44.429 JST [6496] STATEMENT:  TABLE audit.login_event
ERROR:  permission denied for table login_event

postgresユーザはaudit.login_eventテーブルを参照できる。

$ ~/pgsql/postgres/bin/psql -p 17001 -U postgres db_a -c "TABLE audit.login_event"
Null display is "(null)".
 id | user_name | db_name |      login_timestamp
----+-----------+---------+----------------------------
  1 | postgres  | db_a    | 2024-04-21 16:20:10.999999
  2 | user_a    | db_a    | 2024-04-21 16:20:23.809272
  3 | user_a    | db_a    | 2024-04-21 16:20:44.425497
  4 | postgres  | db_a    | 2024-04-21 16:20:58.054033
(4 rows)
$

ということで、ログインイベント機能を使って、ログイン監査ログをテーブルに格納できた。

おわりに

PostgreSQL 17の新機能の中で気になっていたログインイベントトリガについて調べてみたが、これはうまく使うと監査や想定していないログインを抑止できる機能かもしれない。正式版にもぜひとも残ってほしい機能だと思えた。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up