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

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 aa@hoge.com
    2 hoge user bb@hoge.com
    3 fuga user cc@hoge.com
    4 foo admin dd@hoge.com
    5 bar user ee@hoge.com
    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 aa@hoge.com
    2 hoge user bb@hoge.com
    3 fuga user cc@hoge.com
    4 foo admin dd@hoge.com
    5 bar user ee@hoge.com
    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