Upgrade to Pro — share decks privately, control downloads, hide ads and more …

The problem of seeing the system catalog

nuko_yokohama
February 22, 2022

The problem of seeing the system catalog

nuko_yokohama

February 22, 2022
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

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

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

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

  4. 設定 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 スキーマアクセス不可)
  5. 暗号化キーの問題 • PostgreSQL の列暗号化を行うために contrib/pgcrypto を使う ことにした。 – 暗号化 /

    復号を行うために pgcrypto の関数を使う。 – 関数には暗号化対象にするデータと、暗号化キーを指定する。 – 復号時にも同じ暗号化キーを指定する必要がある。 まあそうだね
  6. 暗号化キーの問題 • ベタにクエリに書いちゃうと、 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) あらあら
  7. 関数でラップすればいいのか? • ユーザ定義関数でラップしちゃおう。 – この関数は  は利用可能、  は使用不可能な権限設定にする 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’ は出ない
  8. 関数でラップすればいいのか? • ユーザ定義関数は  は使えるけど  は使えない。ヨシ! 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) ヨシ!
  9. だがしかし •   は decrypt 関数を実行することはできない。 • しかし dercrypt 関数の定義は参照できるので暗号化キー情報が

    見えちゃう・・・。 • ユーザ定義関数を使わず、直接 pgp_sym_decrypt 発行すると   でも mnumber が見えてしまう! にゃーん
  10. だがしかし •   は関数定義を覗ける。キーを入手すれば復号もできる。 $ 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)) ヨシ! (よくない)
  11. 対策はいろいろありそうだが・・・ • 正攻法。  が mnumber にアクセスできないように、列レベルの権限 制御を行う。 –  には暗号化キーがバレるが、列自体にアクセスできなければ問題 はなし。

    • 邪道。 \df+ で定義を見せなくする。→できるのか? • 邪道。 pgp_sym_decrypt() をピンポイントに実行不可能にする。 どうする?
  12. システムカタログ参照を制限する

  13. やりかた • 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)
  14. やりかた • 前スライドの黄色枠内の意味はこんな感じ。 – postgres=arwdDxt/postgres • postgres ユーザは、 a( 挿入

    ),r( 参照 ),w( 更新 ),d( 削除 ),D( 空化 ), x( リファレン ス ),t( トリガ ) の権限を持つ。 – =r/postgres • PUBLIC なユーザ( = の左が空白の場合)は、 r( 参照 ) の権限を持つ。 • この下の権限を変えれば良い。 – PUBLIC なユーザの権限剥奪 – 上記を実施すると mike も \df+ に失敗するので、 mike の参照権限を改めて与える。 – 注! tora ユーザの参照権限を REVOKE しようとしてもダメ!
  15. やりかた • 実行例 + 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)
  16. やりかた •  は関数定義を参照できる。 $ 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
  17. おまけ: pg_sym_decrypt の実行権限を 制限する

  18. やりかた • 同じように、 contrib/pgcrypto で提供されてい る、 pgp_sym_decrypt 関数のみを特定ユーザから実行不可能に することもできる。 •

    PUBLIC なユーザから実行権を剥奪する。 • mike ユーザには改めて実行権を与える。 • pgp_sym_decrypt は同名で複数の関数が存在するので、全ての 関数に対して同様の操作を行う。
  19. 実行例 • 変更前の状態 $ 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 に 何も表示されないのね・・・
  20. 実行例 • 変更例 $ 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 見やすさのために 途中で改行してます
  21. 実行例 • 変更後の状態 $ 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 が 見えるようになる。 にゃーん。
  22. •  は関数を実行可能。 •  は権限不足で関数は実行できない。 実行例 • 変更後に 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 特定の関数 / ユーザのみ 実行不可能にできる
  23. おわり PostgreSQL の権限制御 なんもわからん・・・