はじめに
警告:例によってPostgreSQLの実運用には役に立たない情報です。
いかにして参照しかできないテーブルのようなものを実装するか
PostgreSQL上で参照しかできないテーブル(のようなもの)をどうやって実現できるのか。面白そうなのでいくつか考えてみた。
まずはPostgreSQLでどう実装するか考えてみたけど、方式によっては他のDBMSでも可能なものがあるかもしれない。(本当かどうかは知らん)
- 権限で制御する。
- ルールを書いて更新を抑止する。
- トリガを書いて更新を抑止する。
まあ、ここまでは元のテーブルをそのまま使えるやりかた。
また、誰でも思いつきそうな方法なのでここでは詳細はかかない。
以降は、元のテーブルとは別に参照しかさせないデータベースオブジェクトを経由させるやりかた。
- postgres_fdwのupdatableで制御する
- 更新不可能なビューを定義する
- 対象テーブルと同じデータ内容を返却する関数を定義する
最後は究極の手段。
- HOOK関数を差し込んでDMLを全てブロックする
他になにか面白そうな方法あるかなー?
postgres_fdwのupdatableで制御する
PostgreSQL 9.3以降、PostgreSQLから別サーバ上のPostgreSQL表にアクセスできるcontribモジュール、postgres_fdwがサポートされている。
基本的にはpostgres_fdwは更新文も対応しているので、リモートサーバ上の外部表の更新も可能なのだが、オプションで更新を抑止することもできる。
test=# CREATE SERVER f_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'test');
CREATE SERVER
test=# CREATE USER MAPPING for postgres SERVER f_server OPTIONS (user 'postgres');
CREATE USER MAPPING
test=# CREATE FOREIGN TABLE f_foo (id int, data text) SERVER f_server OPTIONS (updatable 'false', table_name 'foo');
CREATE FOREIGN TABLE
updatebleオプションはCREATE SERVERでもCREATE FOREIGN TABLEでも指定できるが、今回は特定のテーブルのみ更新不可能にしたいので、CREATER FOREIGN TABLEで指定する。
で、このように定義されたf_fooテーブルに対して更新文を実行するとこんな感じでエラーになる。
test=# SELECT * FROM f_foo;
id | data
----+------
1 | aaa
(1 row)
test=# UPDATE f_foo SET data = 'AAA';
ERROR: foreign table "f_foo" does not allow updates
test=# INSERT INTO f_foo VALUES (2, 'bbb');
ERROR: foreign table "f_foo" does not allow inserts
test=# DELETE FROM f_foo;
ERROR: foreign table "f_foo" does not allow deletes
更新不可能なビューを定義する
PostgreSQL 9.3以降、一定の条件を満たす単純なクエリで定義されたビューは更新可能になった。
なので、
CREATE VIEW foo_v AS SELECT * FROM foo;
みたいなビューの場合、foo_vに対して更新文を実行することができてしまう。
test=> CREATE TABLE foo (id int, data text);
CREATE TABLE
test=> CREATE VIEW foo_v AS SELECT * FROM foo;
CREATE VIEW
test=> INSERT INTO foo_v VALUES (1, 'aaa');
INSERT 0 1
test=> UPDATE foo_v SET data = 'AAA';
UPDATE 1
test=> DELETE FROM foo_v;
DELETE 1
test=>
逆に言えば一定の条件を満たさないクエリであれば、そのビューは更新不可能にできる。
更新不可能になるクエリの条件はPostgreSQL文書の更新可能なビューに詳しく記載されているが、一番手っ取り早そうなのは、
ビューのFROMリストには正確に1つだけの項目を持たなければならず
という条件に反したクエリを書くことかな。
なので、こんな感じのVIEWを定義すればよろし。
test=> CREATE VIEW foo_v AS SELECT foo.* FROM foo JOIN (SELECT) t ON (true);
CREATE VIEW
要はVIEWのクエリ定義としてJOINをかませば良いのだ。
せっかくPostgreSQLを使っているので、
- SELECTキーワードだけの列も行も生成しないクエリを発行し結合(PostgreSQL 9.4~)
- 結合条件には(true)を指定して常に真
とか無駄に使ってみる。
こうして作成されたビュー foo_v に更新をかけると更新可能ビューじゃないのに更新文を実行しようとした、とエラーにしてくれる。
test=> INSERT INTO foo_v VALUES (1, 'aaa');
ERROR: cannot insert into view "foo_v"
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
test=> UPDATE foo_v SET data = 'AAA';
ERROR: cannot update view "foo_v"
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
test=> DELETE FROM foo_v;
ERROR: cannot delete from view "foo_v"
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
test=>
なお、これは権限制御とは別の枠組みでエラーにしているので、仮にpostgresのような特権ユーザでもエラーになる。
[nuko@localhost test]$ psql test -U postgres
psql (9.6.0)
Type "help" for help.
test=# INSERT INTO foo_v VALUES (1, 'aaa');
ERROR: cannot insert into view "foo_v"
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
test=#
とはいえ、特権ユーザなら、このビュー定義自体を削除すればいいし、そもそも参照元テーブルへはフルアクセスなので、あまり意味はないのだがw
対象テーブルと同じデータ内容を返却する関数を定義する
PostgreSQLではCREATE FUNCTIONコマンドでユーザ定義関数を作成できるが、単に値を返す関数だけでなく、行集合を返却する関数を作成することもできる。
例えばfooの内容を返却する関数 foo_func をSQL関数として定義する。
関数作成前に、CREATE TYPEで複合型 foo_type を定義するのがポイント(というか、一手間かかる)。
例えばこんな感じで複合型と関数を定義する。
test=# CREATE TYPE foo_type AS (id integer, data text);
CREATE TYPE
test=# CREATE OR REPLACE FUNCTION fn_foo() RETURNS SETOF foo_type AS 'SELECT * FROM foo' LANGUAGE sql;
CREATE FUNCTION
そうすると、以下のようなSELECT文でfooと同じ内容を返却することができる。
test=# SELECT * FROM foo;
id | data
----+------
1 | aaa
2 | bbb
(2 rows)
test=# SELECT * FROM fn_foo();
id | data
----+------
1 | aaa
2 | bbb
(2 rows)
で、関数に対して更新文などかけることはできない。
構文エラーとなる。
test=# INSERT INTO fn_foo VALUES (3, 'ccc');
ERROR: relation "fn_foo" does not exist
LINE 1: INSERT INTO fn_foo VALUES (3, 'ccc');
^
test=# INSERT INTO fn_foo() VALUES (3, 'ccc');
ERROR: syntax error at or near ")"
LINE 1: INSERT INTO fn_foo() VALUES (3, 'ccc');
^
test=# UPDATE fn_foo() SET data = 'XXX';
ERROR: syntax error at or near "("
LINE 1: UPDATE fn_foo() SET data = 'XXX';
^
test=# DELETE FROM fn_foo();
ERROR: syntax error at or near "("
LINE 1: DELETE FROM fn_foo();
しかし、この方法は実用的にはおすすめできない。
なぜなら、WHERE句を書いたとしても、それは fn_foo() 関数が返却した結果セット全体に適用されてしまうので、foo テーブルの件数が多くなると当然ながら線形に性能が劣化するからだ。まあ、これは大道芸的なやり方ってことで。
HOOK関数を差し込んでDMLを全てブロックする
PostgreSQLにはHOOKインタフェースという素敵な機構があり、規定のインタフェースで実装した任意のC関数を差し込んで、PostgreSQLの動作を制御することができる。
なので、Executorの先頭やProcessUtilityの先頭で、実行木の内容をチェックして、更新文だと判断したら、そこで以降の処理をスキップするというHOOK関数を実装すれば、更新操作をブロックできる。
具体的な説明は長くなるので割愛するが、以前、PGCon.jp 2015のライトニングトーク、フックの鬼や、PostgreSQL Advenrt Calender 2015 のメンテナンス専用スーパーユーザで、似たようなことを発表したので、同じように実装すれば、参照専用のテーブルも実装できるんじゃないかと思う。
おわりに
ね、役に立たなかったでしょ?