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
2. PostgreSQLのrootアカウントを
 
 そのまま使っていませんか？
 
 What is it?
3. 資料は公開されています
 
 https://speakerdeck.com/soudai/pgcon2022-tutorial
 What is it?
4. スーパーユーザを使っていませんか？
 
 
 What is it?
5. スーパーユーザを使っていませんか？
 ↓
 権限管理してますか？
 What is it?
6. Row Level Security（RLS）
 
 がよくわからない
 What is it?
7. そんな人達向けの話です
 
 
 What is it?
8. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
9. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
10. 自己紹介
 曽根 壮大（38歳）
 Have Fun Tech LLC 代表社員
 株式会社リンケージ CTO
 
 そ　 ね　　 たけ とも
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます（長女、次女、長男）
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
11. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
12. PostgreSQLのアカウントの
 
 基本を学ぶ
 アカウント作成と権限の基本
13. 基本的なロールの概念から注意点まで広くまと まった素晴らしい資料。 
 今日は基本的なところしか説明しないので詳しく 知りたい人は是非読みましょう。 
 なお、加藤さんは今日スピーカーとして参加して いるので知りたいことがあれば、 @ShinyaKato_ さんに質問しましょう。 
 カンファレンスのメリットです！！ 
 あとはNTTデータさんに感謝しながら資料を読 みましょう。
 https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata
14. PostgreSQLのアカウントは
 
 全てロールに通ずる
 アカウント作成と権限の基本
15. “ロールの概念には、「ユーザ」という概念と「グループ」という概念が含まれま す。 PostgreSQLバージョン8.1より前までは、ユーザとグループは異なる種類 の実体として扱われていました。 しかし、現在ではロールしか存在しません。 すべてのロールは、ユーザとして、
 グループとして、またはその両方として動作することができます。”
 
 公式ドキュメントから引用 : https://www.postgresql.jp/document/14/html/user-manag.html
 アカウント作成と権限の基本
16. 
 グループ
 アカウント作成と権限の基本 ログイン
 ユーザA
 どちらもロール
 ログイン
 ユーザB

17. 注意点
 
 メジャーバージョンアップで
 仕様が変わりがち
 アカウント作成と権限の基本
18. • 9.6 デフォルトロールが追加
 • 10 デフォルトロールから
 定義済みロールに変更
 • 14 定義済みロールが更に追加
 • 15 publicスキーマの権限が変更
 アカウント作成と権限の基本
19. • 9.6 デフォルトロールが追加
 • 10 デフォルトロールから
 定義済みロールに変更
 • 14 定義済みロールが更に追加
 • 15 publicスキーマの権限が変更
 アカウント作成と権限の基本 互換性を壊すような変更は少ないが、 
 新機能の追加や細かい調整は多く入っている 

20. 今日はPostgreSQL 14を
 
 前提として話をします
 
 アカウント作成と権限の基本
21. CREATE ROLEでロールの作成
 DROP ROLEでロールの削除
 ALTER ROLEでロールの変更
 アカウント作成と権限の基本
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
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
24. -- 現在の設定を確認します SELECT * FROM pg_roles; • ロールの一覧は専用のVIEWが用意されている
 • psqlのメタコマンドでも参照可能
 • 詳細は公式ドキュメントを読みましょう
 公式ドキュメントを読もう https://www.postgresql.jp/document/14/html/view-pg-roles.html
25. アカウント作成と権限の基本 https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata/18
26. 権限、グループの追加には
 
 GRANTを使う
 アカウント作成と権限の基本
27. 権限、グループの削除には
 
 REVOKEを使う
 アカウント作成と権限の基本
28. 次のRLSで出てくるので
 
 ここでは割愛します
 アカウント作成と権限の基本
29. 再掲
 詳しく知りたい人は公式ドキュメントと合わせて こちらの資料を読みましょう。 
 https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata
30. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
31. Row Level Security（RLS）とは
 
 
 RLSの概要と実際の活用方法
32. Row Level Security（RLS）とは
 ↓
 ロール単位で行のアクセスを制御する
 RLSの概要と実際の活用方法
33. • テーブル単位はロール
 • 列単位はVIEW
 • 行単位はRLS
 RLSの概要と実際の活用方法 表示の制限

34. RLSの概要と実際の活用方法 テーブルA テーブルB ロールでアクセス制限
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;
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だけの結果を返す
37. • 今日の説明はこちらの記事を使います
 • 記事はすでに公開されています
 • RLSの基本的な実装方法を記載しています
 詳細の使い方はこちら https://soudai.hatenablog.com/entry/2022/11/11/110825
38. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
39. ロール管理はセキュリティに直結
 
 
 セキュリティにおけるGRANTの注意点
40. アプリケーションから
 
 スーパーユーザを使うのはやめましょう
 セキュリティにおけるGRANTの注意点
41. アプリケーションから
 
 スーパーユーザを使うのはやめましょう
 セキュリティにおけるGRANTの注意点 とにかくまじで本当にこれ 

42. アプリケーションが利用するアカウントに
 
 CREATE ROLEをつけるのはやめましょう
 セキュリティにおけるGRANTの注意点
43. アプリケーションが利用するアカウントに
 
 CREATE ROLEをつけるのはやめましょう
 セキュリティにおけるGRANTの注意点 自分に権限がなくても、 
 強い権限のアカウントを作れてしまう 

44. RLSのポリシー漏れに注意
 
 
 セキュリティにおけるGRANTの注意点
45. RLSのポリシー漏れに注意
 
 
 セキュリティにおけるGRANTの注意点 ユーザ、テーブルの設定漏れ 
 コマンド（SELECT以外とか）の設定漏れ 

46. publicスキーマは注意しましょう
 
 すべてのユーザのCurrentなので権限が強い
 セキュリティにおけるGRANTの注意点
47. publicスキーマは注意しましょう
 
 すべてのユーザのCurrentなので権限が強い
 セキュリティにおけるGRANTの注意点 15で変更が入っているので、 
 逆にpublicを使っている人は注意！！ 

48. 1. 自己紹介
 2. PostgreSQLのROLEを使った
 アカウントの作成と権限の基本
 3. RLSの概要と実際の活用方法
 4. セキュリティにおけるGRANTの注意点
 5. まとめ
 あじぇんだ
49. 公式ドキュメントを読みましょう
 
 
 まとめ
50. 実際に動かしてみて
 
 自分たちの要件を整理しましょう
 まとめ
51. アカウントは後から整理するのは大変
 
 最初から正しく運用しましょう
 まとめ
52. ご清聴ありがとうございました
 
 
 まとめ