システムカタログが見え過ぎちゃって困る PostgresSQL Unconference #31 (2022-02-22, Cat’s Day)

自己紹介 ● ぬこ＠横浜 (@nuko_yokohama) ● にゃーん ● 趣味でポスグレをやってる者だ ● 猫の日なのでなんか話します

今日のお題 カタログ情報が見えてしまって困る件

設定 postgres データベース zoo データベース cat スキーマ common スキーマ persons テーブル (uid, mnumber) persons テーブル (uid, name) encrypt 関数 decrypt 関数 public スキーマ 見守る postgres( 神 ) umeko （ zoo DB owner ） tora （参照のみ、 mnumber 復号参照不可 関数実行不可） mike （ cat スキーマの フルアクセス） poti （ common スキーマの フルアクセス , cat スキーマアクセス不可）

暗号化キーの問題 ● PostgreSQL の列暗号化を行うために contrib/pgcrypto を使う ことにした。 – 暗号化 / 復号を行うために pgcrypto の関数を使う。 – 関数には暗号化対象にするデータと、暗号化キーを指定する。 – 復号時にも同じ暗号化キーを指定する必要がある。 まあそうだね

暗号化キーの問題 ● ベタにクエリに書いちゃうと、 SQL ログに暗号化 / 復号キーが出力され てしまう・・・。 – log_statement = ‘all’ 設定や、 pgaudit 導入など $ psql -U mike zoo psql (14.2) Type "help" for help. zoo=> SELECT cm.uid, cm.name, pgp_sym_decrypt(ca.mnumber, 'pass') FROM common.persons cm JOIN cat.persons ca ON (cm.uid = ca.uid); uid | name | pgp_sym_decrypt -----+------------+----------------- 1 | クマ | 314159265358 2 | ロジャー | 223456722345 3 | アレックス | 334334334334 (3 rows) あらあら

関数でラップすればいいのか？ ● ユーザ定義関数でラップしちゃおう。 – この関数は　 は利用可能、　 は使用不可能な権限設定にする CREATE FUNCTION cat.decrypt(enc bytea) RETURNS text AS 'SELECT pgp_sym_decrypt(enc, ''pass'')' LANGUAGE sql' CREATE FUNCTION $ psql -U mike zoo -a -f select_and_df.sql SELECT cm.uid, cm.name, cat.decrypt(ca.mnumber) FROM common.persons cm JOIN cat.persons ca ON (cm.uid = ca.uid); uid | name | decrypt -----+------------+-------------- 1 | クマ | 314159265358 2 | ロジャー | 223456722345 3 | アレックス | 334334334334 (3 rows) クエリには ‘pass’ は出ない

関数でラップすればいいのか？ ● ユーザ定義関数は　 は使えるけど　 は使えない。ヨシ！ CREATE FUNCTION cat.decrypt(enc bytea) RETURNS text AS 'SELECT pgp_sym_decrypt(enc, ''pass'')' LANGUAGE sql' CREATE FUNCTION psql -U tora zoo -a -f select_and_df.sql SELECT cm.uid, cm.name, cat.decrypt(ca.mnumber) FROM common.persons cm JOIN cat.persons ca ON (cm.uid = ca.uid); 2022-02-20 12:15:48.755 JST [25095] ERROR: permission denied for function decrypt) ヨシ！

だがしかし ● 　 は decrypt 関数を実行することはできない。 ● しかし dercrypt 関数の定義は参照できるので暗号化キー情報が 見えちゃう・・・。 ● ユーザ定義関数を使わず、直接 pgp_sym_decrypt 発行すると 　 でも mnumber が見えてしまう！ にゃーん

だがしかし ● 　 は関数定義を覗ける。キーを入手すれば復号もできる。 $ psql -x -U tora zoo -c "\df+ cat.decrypt" List of functions -[ RECORD 1 ]-------+------------------------------------ Schema | cat Name | decrypt （略） Language | sql Source code | SELECT pgp_sym_decrypt(enc, 'pass') Description | $ psql -U tora zoo -c "SELECT uid, pgp_sym_decrypt(mnumber, 'pass') FROM cat.persons" uid | pgp_sym_decrypt -----+----------------- 1 | 314159265358 2 | 223456722345 3 | 334334334334 (3 rows)) ヨシ！ （よくない）

対策はいろいろありそうだが・・・ ● 正攻法。　 が mnumber にアクセスできないように、列レベルの権限 制御を行う。 – 　には暗号化キーがバレるが、列自体にアクセスできなければ問題 はなし。 ● 邪道。 \df+ で定義を見せなくする。→できるのか？ ● 邪道。 pgp_sym_decrypt() をピンポイントに実行不可能にする。 どうする？

システムカタログ参照を制限する

やりかた ● pg_catalog.pg_proc システムカタログを参照不可にする。 ● pg_catalog スキーマ内のシステムカタログは、どんなユーザでもデフォ ルトで参照できてしまうから、良い感じに権限を REVOKE すれば良い。 ● pg_catalog.pg_proc へのアクセス権限の初期状態。 $ psql -U postgres zoo -c "\dp pg_catalog.pg_proc" Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies ------------+---------+-------+---------------------------+-------------------+---------- pg_catalog | pg_proc | table | postgres=arwdDxt/postgres+| | | | | =r/postgres | | (1 row)

やりかた ● 前スライドの黄色枠内の意味はこんな感じ。 – postgres=arwdDxt/postgres ● postgres ユーザは、 a( 挿入 ),r( 参照 ),w( 更新 ),d( 削除 ),D( 空化 ), x( リファレン ス ),t( トリガ ) の権限を持つ。 – =r/postgres ● PUBLIC なユーザ（ = の左が空白の場合）は、 r( 参照 ) の権限を持つ。 ● この下の権限を変えれば良い。 – PUBLIC なユーザの権限剥奪 – 上記を実施すると mike も \df+ に失敗するので、 mike の参照権限を改めて与える。 – 注！ tora ユーザの参照権限を REVOKE しようとしてもダメ！

やりかた ● 実行例 + psql -U postgres zoo -c 'REVOKE SELECT ON TABLE pg_catalog.pg_proc FROM public' REVOKE + psql -U postgres zoo -c 'GRANT SELECT ON TABLE pg_catalog.pg_proc TO mike;' GRANT + psql -U postgres zoo -c '\dp pg_catalog.pg_proc' Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies ------------+---------+-------+---------------------------+-------------------+---------- pg_catalog | pg_proc | table | postgres=arwdDxt/postgres+| | | | | mike=r/postgres | | (1 row)

やりかた ● 　は関数定義を参照できる。 $ psql -U mike zoo -x -c "\df+ cat.decrypt" List of functions -[ RECORD 1 ]-------+------------------------------------ Schema | cat Name | decrypt （略） Source code | SELECT pgp_sym_decrypt(enc, 'pass') Description | ● 　は関数定義を参照しようとすると権限エラーになる。 $ psql -U mike zoo -x -c "\df+ cat.decrypt" List of functions -[ RECORD 1 ]-------+------------------------------------ Schema | cat Name | decrypt （略） Source code | SELECT pgp_sym_decrypt(enc, 'pass') Description | $ psql -U mike zoo -x -c "\df+ cat.decrypt" List of functions -[ RECORD 1 ]-------+------------------------------------ Schema | cat Name | decrypt （略） Source code | SELECT pgp_sym_decrypt(enc, 'pass') Description | $ psql -U tora zoo -x -c "\df+ cat.decrypt" 2022-02-20 14:55:29.954 JST [27104] ERROR: permission denied for table pg_proc

おまけ： pg_sym_decrypt の実行権限を 制限する

やりかた ● 同じように、 contrib/pgcrypto で提供されてい る、 pgp_sym_decrypt 関数のみを特定ユーザから実行不可能に することもできる。 ● PUBLIC なユーザから実行権を剥奪する。 ● mike ユーザには改めて実行権を与える。 ● pgp_sym_decrypt は同名で複数の関数が存在するので、全ての 関数に対して同様の操作を行う。

実行例 ● 変更前の状態 $ psql -U postgres zoo -x -c "\df+ pgp_sym_decrypt" List of functions -[ RECORD 1 ]-------+--------------------- Schema | public Name | pgp_sym_decrypt Result data type | text Argument data types | bytea, text （略） Access privileges | （略） -[ RECORD 2 ]-------+--------------------- Schema | public Name | pgp_sym_decrypt Result data type | text Argument data types | bytea, text, text （略） Access privileges | （略） Access privileges に 何も表示されないのね・・・

実行例 ● 変更例 $ psql -U postgres zoo -x -c "REVOKE EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text) FROM public" REVOKE $ psql -U postgres zoo -x -c "REVOKE EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text, text) FROM public" REVOKE $ psql -U postgres zoo -x -c "GRANT EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text) TO mike" GRANT $ psql -U postgres zoo -x -c "GRANT EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text, text) TO mike" GRANT 見やすさのために 途中で改行してます

実行例 ● 変更後の状態 $ psql -U postgres zoo -x -c "\df+ pgp_sym_decrypt" List of functions -[ RECORD 1 ]-------+--------------------- Schema | public Name | pgp_sym_decrypt （略） Access privileges | postgres=X/postgres + | mike=X/postgres （略） -[ RECORD 2 ]-------+--------------------- Schema | public Name | pgp_sym_decrypt （略） Access privileges | postgres=X/postgres + | mike=X/postgres （略） PUBLIC から権限剥奪後に Access privileges が 見えるようになる。 にゃーん。

● 　は関数を実行可能。 ● 　は権限不足で関数は実行できない。 実行例 ● 変更後に pgp_sym_decrypt を実行 $ psql -U mike zoo -c "SELECT uid, pgp_sym_decrypt(mnumber, 'pass') FROM cat.persons" uid | pgp_sym_decrypt -----+----------------- 1 | 314159265358 2 | 223456722345 3 | 334334334334 (3 rows) $ psql -U tora zoo -c "SELECT uid, pgp_sym_decrypt(mnumber, 'pass') FROM cat.persons" 2022-02-20 16:23:52.514 JST [28649] ERROR: permission denied for function pgp_sym_decrypt 2022-02-20 16:23:52.514 JST [28649] STATEMENT: SELECT uid, pgp_sym_decrypt(mnumber, 'pass') FROM cat.persons ERROR: permission denied for function pgp_sym_decrypt 特定の関数 / ユーザのみ 実行不可能にできる

おわり PostgreSQL の権限制御 なんもわからん・・・