LoginSignup
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nuko_yokohama(nuko yokohama)

plshで簡易データベース監査

Last updated at Posted at 2016-10-27

時間がない人向けの要約

  • plshを使うと簡易なデータベース監査が出来るかもよ。
  • plshを使うとサーバログと監査ログの分離がしやすいかもよ。
  • トリガベースなのでSELECTは監査できないよ。あと、ユーザ情報が取得できないのは残念だよ。

はじめに

前回の記事でplshをとりあえず使って、SQL関数からシェルを実行するという例を試してみたが、トリガとイベントトリガについては試してなかった。
今回は、plshからトリガとイベントトリガを使って、非常に簡単な「なんちゃってデータベース監査」を実装してみる。

トリガ

トリガは大体、どのDBMSでも実装されており、使ったことのある人も多いと思うので詳細説明は割愛。
大雑把に言えば、表に対する更新操作(INSERT/UPDATE/DELETE/TRUNCATE)を実行した契機で、ユーザ定義の動作を行うというもの。
plshでもトリガ関数を記述することでトリガを扱える。

plshのトリガ関数例

例えば、こんな感じのトリガ関数を plsh 内で記述する。

$ cat trigger_sample_01.sql 
DROP TABLE IF EXISTS test_a ;
CREATE TABLE IF NOT EXISTS test_a (id int primary key, data text);

CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger AS $$
# !/bin/sh
echo "execute trigger" >> /tmp/trigger.txt
$$ LANGUAGE plsh;

CREATE TRIGGER test_a_trigger
AFTER INSERT OR UPDATE OR DELETE ON test_a
    FOR STATEMENT EXECUTE PROCEDURE trigger_func();

INSERT INTO test_a VALUES (1, 'aaa');

CREATE OR REPLACE FUNCTION 文でトリガ用の関数をシェルで定義する。
この例だと、単にexecute triggerというテキストを/tmp/trigger.txtにリダイレクトしているだけ。
このトリガ関数をCREATE TRIGGER文で、test_aテーブルに設定する。
そして、test_aテーブルへのINSERT文契機で動作させる。

動作例

実行前には、/tmp/trigger.txtファイルはない。

[nuko@localhost ~]$ cat /tmp/trigger.txt
cat: /tmp/trigger.txt: そのようなファイルやディレクトリはありません
[nuko@localhost ~]$

さっきのスクリプト(trigger_sample_01.sql)を動かしてみる。

[nuko@localhost plsh]$ psql -e -U postgres plsh -f trigger_sample_01.sql 
DROP TABLE IF EXISTS test_a ;
DROP TABLE
CREATE TABLE IF NOT EXISTS test_a (id int primary key, data text);
CREATE TABLE
CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger AS $$
# !/bin/sh
echo "execute trigger" >> /tmp/trigger.txt
$$ LANGUAGE plsh;
CREATE FUNCTION
CREATE TRIGGER test_a_trigger
AFTER INSERT OR UPDATE OR DELETE ON test_a
    FOR STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
INSERT INTO test_a VALUES (1, 'aaa');
INSERT 0 1

すると、/tmp/trigger.txtというファイルが作成されて、中にexecute triggerというテキストが書き込まれているのが確認できる。

[nuko@localhost ~]$ cat /tmp/trigger.txt
execute trigger
[nuko@localhost ~]$

これで、plshで定義したスクリプトがトリガ経由で呼び出されるのは確認できた。

トリガを使って簡易データベース監査をやってみる

今度は、もう少し実用的なトリガ関数を作ってみることにする。
plshではトリガ関数を作成するときに、以下の環境変数を使うことができる(plshのドキュメントより抜粋)。

  • PLSH_TG_NAME: trigger name
  • PLSH_TG_WHEN: BEFORE, INSTEAD OF, or AFTER
  • PLSH_TG_LEVEL: ROW or STATEMENT
  • PLSH_TG_OP: DELETE, INSERT, UPDATE, or TRUNCATE
  • PLSH_TG_TABLE_NAME: name of the table the trigger is acting on
  • PLSH_TG_TABLE_SCHEMA: schema name of the table the trigger is acting on

これらの環境変数はトリガ関数実行前に、plshが設定してくれるっぽい。
今回は簡易データベース監査を行うために、以下の環境変数を使う。

  • PLSH_TG_OP
  • PLSH_TG_TABLE_NAME

これを使うと、「どのテーブルに」「どんな種別の操作」が行われたかという情報を取得できる。
あとは、その情報をファイルにリダイレクトするだけ。

定義例

こんな感じで定義する。
トリガ関数内で、「どのテーブルに」「どんな種別の操作」を行ったのかテキストを生成して、それを/tmp/audit.txtにリダイレクトする。
そして、そのトリガ関数を使ったトリガ定義をtest_aテーブルに設定する。
トリガ契機はINSERT/UPDATE/DELETE/TRUNCATE文とする。

--
-- create table/create trigger
--
DROP TABLE IF EXISTS test_a ;
CREATE TABLE IF NOT EXISTS test_a (id int primary key, data text);

CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger AS $$
# !/bin/sh
echo `date` $PLSH_TG_TABLE_NAME $PLSH_TG_OP >> /tmp/audit.txt
$$ LANGUAGE plsh;

CREATE TRIGGER test_a_trigger
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_a
    FOR STATEMENT EXECUTE PROCEDURE trigger_func();

この状態で、以下のようなDML文を発行する。

INSERT INTO test_a VALUES (1, 'aaa'),(2, 'bbb');
SELECT * FROM test_a WHERE id = 1;
UPDATE test_a SET data = 'BBB' WHERE id = 2;
DELETE FROM test_a WHERE id = 2;
TRUNCATE test_a;

実行例

最初は/tmp/audit.txtは存在していない。

[nuko@localhost ~]$ cat /tmp/audit.txt
cat: /tmp/audit.txt: そのようなファイルやディレクトリはありません

トリガ定義を行い、DML文を実行する。

[nuko@localhost plsh]$ psql -e -U postgres plsh -f trigger_sample_02.sql 
DROP TABLE IF EXISTS test_a ;
psql:trigger_sample_02.sql:4: NOTICE:  table "test_a" does not exist, skipping
DROP TABLE
CREATE TABLE IF NOT EXISTS test_a (id int primary key, data text);
CREATE TABLE
CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger AS $$
# !/bin/sh
echo `date` $PLSH_TG_TABLE_NAME $PLSH_TG_OP >> /tmp/audit.txt
$$ LANGUAGE plsh;
CREATE FUNCTION
CREATE TRIGGER test_a_trigger
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_a
    FOR STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
INSERT INTO test_a VALUES (1, 'aaa'),(2, 'bbb');
INSERT 0 2
SELECT * FROM test_a WHERE id = 1;
 id | data 
----+------
  1 | aaa
(1 row)

UPDATE test_a SET data = 'BBB' WHERE id = 2;
UPDATE 1
DELETE FROM test_a WHERE id = 2;
DELETE 1
TRUNCATE test_a;
TRUNCATE TABLE

すると、/tmp/audit.txtに以下のような監査ログが生成される。

[nuko@localhost ~]$ cat /tmp/audit.txt
Thu Oct 27 11:11:34 JST 2016 test_a INSERT
Thu Oct 27 11:11:34 JST 2016 test_a UPDATE
Thu Oct 27 11:11:34 JST 2016 test_a DELETE
Thu Oct 27 11:11:34 JST 2016 test_a TRUNCATE

さて、一応は監査ログっぽいものは出来たのだが、この監査ログには以下の情報が足りない・・・

  • 「誰がその操作を行ったか」：実行したデータベースユーザの情報がない。
  • 「どんな操作を行ったか」：コマンドの種別はでは表示されているが、操作の詳細(SQLコマンド内容)がない。
  • 検索(SELECT)操作は出力されない。トリガベースなので、SELECTのイベントは拾えない・・・。

ということで、データベース監査能力としては、非常にpoorなものだが悪いことばかりでもない。
この方法だと、データベース監査ログをサーバログと簡単に分離することができそうだ。

今回は簡単な例にするため、単純にリダイレクトしているが、これだと複数のバックエンドから同時に更新等が実行されたときに、正しくログに書き出されないだろう。
きちんと複数バックエンドからの書き込みを保全するなら、syslogに出力したり、あるいは別サーバ上のPostgreSQLに挿入するなどの検討は必要だろう。

イベントトリガによるDDL監査

plshは通常のトリガだけでなく、イベントトリガも対応している。
イベントトリガは(たぶん)PostgreSQL固有の機能で、非常に簡単に言ってしまうと、DDLトリガである。
例えば、テーブルの作成(CREATE TABLE)やテーブルの変更(ALTER TABLE)、関数の作成(CREATE FUNCTION)などのイベントを捕捉できる。

イベントトリガの作成

イベントトリガ用の関数もplshで作成できる。
イベントトリガでもから呼ばれたときにplshで以下の環境情報を設定し、それをスクリプトの中から参照できる。

  • PLSH_TG_EVENT: event name
  • PLSH_TG_TAG: command tag

例えば以下のようにイベントトリガ関数をplshで作成できる。

CREATE OR REPLACE FUNCTION event_trigger_func() RETURNS event_trigger AS  $$
# !/bin/sh
echo `date` $PLSH_TG_EVENT $PLSH_TG_TAG >> /tmp/audit.txt
$$ LANGUAGE plsh;

上記スクリプト内の$PLSH_TG_EVENTはイベントトリガの種類、$PLSH_TG_TAGは実行されたDDLの文の種類(コマンドタグ)である。

こうして作成されたイベントトリガ関数を使って、イベントトリガを定義する。

CREATE EVENT TRIGGER event_trigger ON ddl_command_start
    EXECUTE PROCEDURE event_trigger_func();

これで、以降のCREATE TABLEやCREATE FUNCTIONも監査ログに出力することができる。

実行例

さっきのトリガのサンプルの先頭にイベントトリガを追加して実行してみる。

[nuko@localhost plsh]$ psql -e -U postgres plsh -f trigger_sample_03.sql 
DROP FUNCTION IF EXISTS event_trigger_func() CASCADE;
psql:trigger_sample_03.sql:4: NOTICE:  function event_trigger_func() does not exist, skipping
DROP FUNCTION
CREATE OR REPLACE FUNCTION event_trigger_func() RETURNS event_trigger AS  $$
# !/bin/sh
echo `date` $PLSH_TG_EVENT $PLSH_TG_TAG >> /tmp/audit.txt
$$ LANGUAGE plsh;
CREATE FUNCTION
DROP EVENT TRIGGER IF EXISTS event_trigger;
psql:trigger_sample_03.sql:10: NOTICE:  event trigger "event_trigger" does not exist, skipping
DROP EVENT TRIGGER
CREATE EVENT TRIGGER event_trigger ON ddl_command_start
    EXECUTE PROCEDURE event_trigger_func();
CREATE EVENT TRIGGER
DROP TABLE IF EXISTS test_a ;
psql:trigger_sample_03.sql:17: NOTICE:  table "test_a" does not exist, skipping
DROP TABLE
CREATE TABLE IF NOT EXISTS test_a (id int primary key, data text);
CREATE TABLE
CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger AS $$
# !/bin/sh
echo `date` $PLSH_TG_TABLE_NAME $PLSH_TG_OP >> /tmp/audit.txt
$$ LANGUAGE plsh;
CREATE FUNCTION
CREATE TRIGGER test_a_trigger
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_a
    FOR STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
INSERT INTO test_a VALUES (1, 'aaa'),(2, 'bbb');
INSERT 0 2
SELECT * FROM test_a WHERE id = 1;
 id | data 
----+------
  1 | aaa
(1 row)

UPDATE test_a SET data = 'BBB' WHERE id = 2;
UPDATE 1
DELETE FROM test_a WHERE id = 2;
DELETE 1
TRUNCATE test_a;
TRUNCATE TABLE

監査ログファイルを参照してみる。

[nuko@localhost ~]$ cat /tmp/audit.txt
Thu Oct 27 11:25:57 JST 2016 ddl_command_start DROP TABLE
Thu Oct 27 11:25:57 JST 2016 ddl_command_start CREATE TABLE
Thu Oct 27 11:25:57 JST 2016 ddl_command_start CREATE FUNCTION
Thu Oct 27 11:25:57 JST 2016 ddl_command_start CREATE TRIGGER
Thu Oct 27 11:25:57 JST 2016 test_a INSERT
Thu Oct 27 11:25:57 JST 2016 test_a UPDATE
Thu Oct 27 11:25:57 JST 2016 test_a DELETE
Thu Oct 27 11:25:57 JST 2016 test_a TRUNCATE

DMLトリガによる出力の前に、イベントトリガによる出力が行われているのがわかる。

  • 1行目は test_a テーブルの削除
  • 2行目は test_a テーブルの生成
  • 3行目は trigger_func 関数の生成
  • 4行目は test_a_trigger トリガの生成

ただ、コマンドの種別は分かるけど、どんなテーブルに対する操作や、どんな関数を作成したのかといった詳細までは出力されないのが残念・・・。

ということで、イベントトリガも併用すれば、情報はかなり限定されるけど、ある程度はDDLに対する監査もできそうである。

おわりに

plshを使って無理やり監査っぽいことをやってみたけど、これをもってPostgreSQLのデータベース監査ができた！とはさすがに言いづらいなあ・・・。特にSELECTの監査ができないのと、ユーザ情報を取得できないのが残念。
ま、個人的にはplshでのトリガ/イベントトリガの使い方がわかったから良しとする。

6
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?