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

PostgreSQLの権限管理 ~ アカウントの操作とRow Level Securityの活用 ~ / pgcon2022-tutorial

soudai sone
October 19, 2022

PostgreSQLの権限管理 ~ アカウントの操作とRow Level Securityの活用 ~ / pgcon2022-tutorial

# PostgreSQLのロール管理とその注意点(Open Source Conference 2022 Online/Osaka 発表資料)
https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata
# マルチテナントにおけるRow Level Securityの具体的な実装と注意点
https://soudai.hatenablog.com/entry/2022/11/11/110825

soudai sone

October 19, 2022
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. PostgreSQLの権限管理
    ~ アカウントの操作とRow Level Securityの活用 ~
    PostgreSQLカンファレンス 2022 チュートリアル T3

    View Slide

  2. PostgreSQLのrootアカウントを


    そのまま使っていませんか?


    What is it?

    View Slide

  3. 資料は公開されています


    https://speakerdeck.com/soudai/pgcon2022-tutorial

    What is it?

    View Slide

  4. スーパーユーザを使っていませんか?



    What is it?

    View Slide

  5. スーパーユーザを使っていませんか?

    ↓

    権限管理してますか?

    What is it?

    View Slide

  6. Row Level Security(RLS)


    がよくわからない

    What is it?

    View Slide

  7. そんな人達向けの話です



    What is it?

    View Slide

  8. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  9. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  10. 自己紹介

    曽根 壮大(38歳)

    Have Fun Tech LLC 代表社員

    株式会社リンケージ CTO


    そ  ね   たけ とも

    ● 日本PostgreSQLユーザ会 勉強会分科会 担当

    ● 3人の子供がいます(長女、次女、長男)

    ● 技術的にはWeb/LL言語/RDBMSが好きです

    ● コミュニティが好き

    View Slide

  11. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  12. PostgreSQLのアカウントの


    基本を学ぶ

    アカウント作成と権限の基本

    View Slide

  13. 基本的なロールの概念から注意点まで広くまと
    まった素晴らしい資料。 

    今日は基本的なところしか説明しないので詳しく
    知りたい人は是非読みましょう。 

    なお、加藤さんは今日スピーカーとして参加して
    いるので知りたいことがあれば、 @ShinyaKato_
    さんに質問しましょう。 

    カンファレンスのメリットです!! 

    あとはNTTデータさんに感謝しながら資料を読
    みましょう。

    https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata

    View Slide

  14. PostgreSQLのアカウントは


    全てロールに通ずる

    アカウント作成と権限の基本

    View Slide

  15. “ロールの概念には、「ユーザ」という概念と「グループ」という概念が含まれま
    す。 PostgreSQLバージョン8.1より前までは、ユーザとグループは異なる種類
    の実体として扱われていました。 しかし、現在ではロールしか存在しません。
    すべてのロールは、ユーザとして、

    グループとして、またはその両方として動作することができます。”


    公式ドキュメントから引用 : https://www.postgresql.jp/document/14/html/user-manag.html

    アカウント作成と権限の基本

    View Slide


  16. グループ

    アカウント作成と権限の基本
    ログイン

    ユーザA

    どちらもロール

    ログイン

    ユーザB


    View Slide

  17. 注意点


    メジャーバージョンアップで

    仕様が変わりがち

    アカウント作成と権限の基本

    View Slide

  18. ● 9.6 デフォルトロールが追加

    ● 10 デフォルトロールから

    定義済みロールに変更

    ● 14 定義済みロールが更に追加

    ● 15 publicスキーマの権限が変更

    アカウント作成と権限の基本

    View Slide

  19. ● 9.6 デフォルトロールが追加

    ● 10 デフォルトロールから

    定義済みロールに変更

    ● 14 定義済みロールが更に追加

    ● 15 publicスキーマの権限が変更

    アカウント作成と権限の基本
    互換性を壊すような変更は少ないが、 

    新機能の追加や細かい調整は多く入っている 


    View Slide

  20. 今日はPostgreSQL 14を


    前提として話をします


    アカウント作成と権限の基本

    View Slide

  21. CREATE ROLEでロールの作成

    DROP ROLEでロールの削除

    ALTER ROLEでロールの変更

    アカウント作成と権限の基本

    View Slide

  22. -- ログイン可能なロールを作成します。
    -- ただし、パスワードはありません。
    CREATE ROLE jonathan LOGIN;
    -- パスワード付きのロールを作成します。
    -- CREATE USERはLOGINを意味する点を除き、
    -- CREATE ROLEと同一です。
    CREATE USER davide WITH PASSWORD 'jw8s0F4';
    -- 2004年まで有効なパスワードを持つロールを作成します。
    -- 2005年に1秒でも入った時点でパスワードは無効になります。
    CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4'
    VALID UNTIL '2005-01-01' ;
    -- データベースを作成でき、かつ、
    -- ロールを管理できるロールを作成します。
    CREATE ROLE admin WITH CREATEDB CREATEROLE ;
    CREATE ROLE name [ [ WITH ] option [ ... ] ]


    ここでoptionは以下の通りです。


    SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid


    公式ドキュメントを読もう
    https://www.postgresql.jp/document/14/html/sql-createrole.html

    View Slide

  23. -- 現在の設定を確認します
    SELECT SESSION_USER, CURRENT_USER;
    session_user | current_user
    --------------+--------------
    peter | peter
    -- 一時的にロールをセットします
    SET ROLE 'paul';
    SELECT SESSION_USER, CURRENT_USER;
    session_user | current_user
    --------------+--------------
    peter | paul
    ● ロールは一時的に設定することも可能

    ● SET ROLE {name} で設定

    ● RESET ROLEで元のロールに戻る

    ● セッションユーザがメンバ資格を持つロールのみ使用できる 

    ● セッションユーザとは、セッションを開始したユーザ

    ● スーパーユーザは任意のロールを使用可

    ● NONEおよびRESET形式は、現在のユーザ識別子を現在のセッ
    ションユーザ識別子に戻します。 

    この形式はすべてのユーザが実行することができます。

    公式ドキュメントを読もう
    https://www.postgresql.jp/document/14/html/sql-set-role.html

    View Slide

  24. -- 現在の設定を確認します
    SELECT * FROM pg_roles;
    ● ロールの一覧は専用のVIEWが用意されている

    ● psqlのメタコマンドでも参照可能

    ● 詳細は公式ドキュメントを読みましょう

    公式ドキュメントを読もう
    https://www.postgresql.jp/document/14/html/view-pg-roles.html

    View Slide

  25. アカウント作成と権限の基本
    https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata/18

    View Slide

  26. 権限、グループの追加には


    GRANTを使う

    アカウント作成と権限の基本

    View Slide

  27. 権限、グループの削除には


    REVOKEを使う

    アカウント作成と権限の基本

    View Slide

  28. 次のRLSで出てくるので


    ここでは割愛します

    アカウント作成と権限の基本

    View Slide

  29. 再掲

    詳しく知りたい人は公式ドキュメントと合わせて
    こちらの資料を読みましょう。 

    https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata

    View Slide

  30. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  31. Row Level Security(RLS)とは



    RLSの概要と実際の活用方法

    View Slide

  32. Row Level Security(RLS)とは

    ↓

    ロール単位で行のアクセスを制御する

    RLSの概要と実際の活用方法

    View Slide

  33. ● テーブル単位はロール

    ● 列単位はVIEW

    ● 行単位はRLS

    RLSの概要と実際の活用方法
    表示の制限


    View Slide

  34. RLSの概要と実際の活用方法
    テーブルA
    テーブルB
    ロールでアクセス制限

    View Slide

  35. RLSの概要と実際の活用方法
    ID NAME ROLE email
    1 test admin [email protected]
    2 hoge user [email protected]
    3 fuga user [email protected]
    4 foo admin [email protected]
    5 bar user [email protected]
    Viewで列を制限
    ID NAME ROLE
    1 test admin
    2 hoge user
    3 fuga user
    4 foo admin
    5 bar user
    SELECT id,name,role
    FROM hoge;

    View Slide

  36. RLSの概要と実際の活用方法
    ID NAME ROLE email
    1 test admin [email protected]
    2 hoge user [email protected]
    3 fuga user [email protected]
    4 foo admin [email protected]
    5 bar user [email protected]
    RLSで行を制限
    SELECT * FROM hoge;
    例えばrole=userだけの結果を返す

    View Slide

  37. ● 今日の説明はこちらの記事を使います

    ● 記事はすでに公開されています

    ● RLSの基本的な実装方法を記載しています

    詳細の使い方はこちら
    https://soudai.hatenablog.com/entry/2022/11/11/110825

    View Slide

  38. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  39. ロール管理はセキュリティに直結



    セキュリティにおけるGRANTの注意点

    View Slide

  40. アプリケーションから


    スーパーユーザを使うのはやめましょう

    セキュリティにおけるGRANTの注意点

    View Slide

  41. アプリケーションから


    スーパーユーザを使うのはやめましょう

    セキュリティにおけるGRANTの注意点
    とにかくまじで本当にこれ 


    View Slide

  42. アプリケーションが利用するアカウントに


    CREATE ROLEをつけるのはやめましょう

    セキュリティにおけるGRANTの注意点

    View Slide

  43. アプリケーションが利用するアカウントに


    CREATE ROLEをつけるのはやめましょう

    セキュリティにおけるGRANTの注意点
    自分に権限がなくても、 

    強い権限のアカウントを作れてしまう 


    View Slide

  44. RLSのポリシー漏れに注意



    セキュリティにおけるGRANTの注意点

    View Slide

  45. RLSのポリシー漏れに注意



    セキュリティにおけるGRANTの注意点
    ユーザ、テーブルの設定漏れ 

    コマンド(SELECT以外とか)の設定漏れ 


    View Slide

  46. publicスキーマは注意しましょう


    すべてのユーザのCurrentなので権限が強い

    セキュリティにおけるGRANTの注意点

    View Slide

  47. publicスキーマは注意しましょう


    すべてのユーザのCurrentなので権限が強い

    セキュリティにおけるGRANTの注意点
    15で変更が入っているので、 

    逆にpublicを使っている人は注意!! 


    View Slide

  48. 1. 自己紹介

    2. PostgreSQLのROLEを使った

    アカウントの作成と権限の基本

    3. RLSの概要と実際の活用方法

    4. セキュリティにおけるGRANTの注意点

    5. まとめ

    あじぇんだ

    View Slide

  49. 公式ドキュメントを読みましょう



    まとめ

    View Slide

  50. 実際に動かしてみて


    自分たちの要件を整理しましょう

    まとめ

    View Slide

  51. アカウントは後から整理するのは大変


    最初から正しく運用しましょう

    まとめ

    View Slide

  52. ご清聴ありがとうございました



    まとめ

    View Slide