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)

    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