The problem of seeing the system catalog

Transcript

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

   View Slide

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

   View Slide

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

   View Slide

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 スキーマアクセス不可）
   

   View Slide

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

   View Slide

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)
   あらあら
   

   View Slide

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’ は出ない
   

   View Slide

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)
   ヨシ！
   

   View Slide

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

   View Slide

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))
    ヨシ！
    （よくない）
    

    View Slide

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

    View Slide

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

    View Slide

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)
    

    View Slide

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

    View Slide

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)
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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 に
    何も表示されないのね・・・
    

    View Slide

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
    見やすさのために
    途中で改行してます
    

    View Slide

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 が
    見えるようになる。
    にゃーん。
    

    View Slide

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
    特定の関数 / ユーザのみ
    実行不可能にできる
    

    View Slide

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

    View Slide