PostgreSQL でログイン、ログアウトの履歴を取得する。

データベースの運用では、ユーザーの利用状況を調べるために監査を行います。

PostgreSQL では、ユーザーのログイン・ログアウト・クエリの実行などの操作を、ログファイルに記録する機能があります。

今回は、ログイン・ログアウトの履歴をログに記録して、ログイン状況を確認します。

PostgreSQLのログ設定
ログイン履歴の取得設定
ログアウト時のログ内容
ログイン状況の集計
おわりに

PostgreSQLのログ設定

今回の検証では、AlmaLinux release 8.10 にインストールした PostgresSQL 16 を使用しています。

検証用に pgdb01 データベースを作成し、ユーザー「sato」、「tanaka」、「suzuki」を作成しています。

PostgresSQLのログを取得するには、下記のコマンドでログ関連のパラメータを確認します。

psql -c "show all;" | grep "^ log"

以下、ログ関連の主要なパラメータです。ログイン、ログアウトをログに記録する log_connection と log_disconnection はデフォルトで off になっています。

パラメータ名	値	説明
logging_collector	on	このパラメータはログ収集機構を有効にします
log_destination	stderr	ログの出力先を指定します
log_directory	log	ログファイルを格納するディレクトリを指定します
log_connections	off	ログインを記録します
log_disconnections	off	ログアウトを記録します
log_duration	off	すべての完了した文について、その経過時間をログするようにします
log_error_verbosity	default	サーバログに書き込まれる詳細の量を制御します
log_file_mode	0600	ログファイルのパーミッションを設定します(Windowsでは無効)
log_filename	postgresql-%a.log	ログファイル名を指定します
log_hostname	off	接続元のホスト名を記録します
log_line_prefix	%m [%p]	ログの先頭に表示する書式を指定します
log_min_duration_sample	-1	指定した時間以上で実行完了した文の実行時間のサンプルを許可します
log_lock_waits	off	ロック獲得待ちの発生を記録します
log_min_duration_statement	-1	SQL文の実行に指定した時間かかった場合にログに記録します
log_min_error_statement	error	エラー条件の原因となったどのSQL文をサーバログに記録するかを制御します
log_min_messages	warning	どのメッセージレベルをサーバログに書き込むかを管理します
log_parameter_max_length	-1	エラーではない時にバインドパラメータ値が、文とともにこの指定バイト数に短縮されて記録されます
log_parameter_max_length_on_error	0	エラー時のバインドパラメータ値が、エラーメッセージ中にこの指定バイト数に短縮されて記録されます
log_rotation_age	1d	このパラメータは個々のログファイルの最大寿命を決定します
log_rotation_size	0	個々のログファイルの最大容量を決定します
log_statement	none	どのSQL文をログに記録するかを制御します
log_statement_sample_rate	1	log_min_duration_sampleを越え、記録対象となる文の割合を決定します
log_temp_files	-1	一時ファイルのファイル名とサイズのログ出力を制御します

ログイン履歴の取得設定

ログイン履歴を取得するために log_connections パラメータを有効にします。

${PGDATA}/postgresql.conf に log_connections パラメータを追記して、設定ファイルを読み込みます。

sed -i -e '/^log_connections/d' postgresql.conf  && echo "log_connections = on">>postgresql.conf
grep -e log_connections postgresql.conf
pg_ctl reload

「log_connections = on」と表示されることを確認してください。

ログインが発生すると以下のようなログが出力されます。

2025-02-25 11:01:48.270 JST [28696] LOG:  connection received: host=[local]
2025-02-25 11:01:54.169 JST [28701] LOG:  connection received: host=[local]
2025-02-25 11:01:54.189 JST [28701] LOG:  connection authenticated: identity="sato" method=scram-sha-256 (/var/lib/pgsql/16/data/pg_hba.conf:114)
2025-02-25 11:01:54.189 JST [28701] LOG:  connection authorized: user=sato database=pgdb01 application_name=psql

ローカルホストからの接続を受け付け、ユーザー「sato」が「scram-sha-256」方式で認証を試み、「pgdb01 」データベースに「psql」でログインしたことがわかります。

このセッションのプロセスIDは「28701」になります。

ログアウト時のログ内容

${PGDATA}/postgresql.conf に以下パラメータを追記して設定ファイルを読み込みます。

sed -i -e '/^log_disconnections/d' postgresql.conf && echo "log_disconnections = on">>postgresql.conf
grep -e log_disconnections postgresql.conf
pg_ctl reload

「log_disconnections = on」と表示されることを確認してください。

ログアウトが発生すると以下のようなログが出力されます。

2025-02-25 11:08:06.453 JST [28701] LOG:  disconnection: session time: 0:06:12.284 user=sato database=pgdb01 host=[local]

プロセスID「28701」で接続していた、ユーザー「sato」がログアウトしたことがわかります。

ログイン状況の集計

ログイン状況をするにはログを解析すればいいのですが、デフォルトのままでは集計しにくいのでログを CSV 形式で出力するようにします。

さらに、CSVで出力されたログをDBに取り込みます。

ログ出力形式を CSV形式に変更

パラメータ log_destination を stderr から csvlog に変更します。

sed -i -e '/~log_destination/d' postgresql.conf && echo "log_destination = csvlog" >> postgresql.conf
grep -e log_destination postgresql.conf
pg_ctl reload

出力されるログファイル名は～.log から～.csv に変更され、ログイン時のログは以下のようにCSV形式になります。

2025-02-25 11:23:18.293 JST,,,29420,"[local]",67bd2996.72ec,1,"",2025-02-25 11:23:18 JST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized",,0
2025-02-25 11:23:19.769 JST,,,29421,"[local]",67bd2997.72ed,1,"",2025-02-25 11:23:19 JST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized",,0
2025-02-25 11:23:19.791 JST,"sato","pgdb01",29421,"[local]",67bd2997.72ed,2,"authentication",2025-02-25 11:23:19 JST,4/5756,0,LOG,00000,"connection authenticated: identity=""sato"" method=scram-sha-256 (/var/lib/pgsql/16/data/pg_hba.conf:114)",,,,,,,,,"","client backend",,0
2025-02-25 11:23:19.791 JST,"sato","pgdb01",29421,"[local]",67bd2997.72ed,3,"authentication",2025-02-25 11:23:19 JST,4/5756,0,LOG,00000,"connection authorized: user=sato database=pgdb01 application_name=psql",,,,,,,,,"","client backend",,0

CSVログのデータベースへの取り込み

CSVログのカラムに合わせてテーブルを作成します。

CREATE TABLE login_history
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text,
  leader_pid integer,
  query_id bigint,
  PRIMARY KEY (session_id, session_line_num)
);

ログからログインの記録である「connection authorized」を抜き出して、作成した login_historyテーブルに挿入します。

grep "connection authorized" postgresql-Tue.csv |psql pgdb01 -c "copy login_history from stdin with csv"

エラーが出なければログの取り込み完了です。

ログイン回数の集計

ユーザーごとのログイン回数を確認します。

pgdb01=# select user_name,count(*) from login_history group by user_name;
 user_name | count
-----------+-------
 suzuki    |     2
 tanaka    |     7
 sato      |     1
 postgres  |    19