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

by soudai sone

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

PostgreSQLのrootアカウントを    そのまま使っていませんか？    What is it?

Slide 3

Slide 3 text

資料は公開されています    https://speakerdeck.com/soudai/pgcon2022-tutorial  What is it?

Slide 4

Slide 4 text

スーパーユーザを使っていませんか？      What is it?

Slide 5

Slide 5 text

スーパーユーザを使っていませんか？  ↓  権限管理してますか？  What is it?

Slide 6

Slide 6 text

Row Level Security（RLS）    がよくわからない  What is it?

Slide 7

Slide 7 text

そんな人達向けの話です      What is it?

Slide 8

Slide 8 text

1. 自己紹介  2. PostgreSQLのROLEを使った  アカウントの作成と権限の基本  3. RLSの概要と実際の活用方法  4. セキュリティにおけるGRANTの注意点  5. まとめ  あじぇんだ

Slide 9

Slide 9 text

Slide 10

Slide 10 text

自己紹介  曽根壮大（38歳）  Have Fun Tech LLC 代表社員  株式会社リンケージ CTO    そ　ね　　たけとも  ● 日本PostgreSQLユーザ会勉強会分科会担当  ● 3人の子供がいます（長女、次女、長男）  ● 技術的にはWeb/LL言語/RDBMSが好きです  ● コミュニティが好き

Slide 11

Slide 11 text

Slide 12

Slide 12 text

PostgreSQLのアカウントの    基本を学ぶ  アカウント作成と権限の基本

Slide 13

Slide 13 text

基本的なロールの概念から注意点まで広くまとまった素晴らしい資料。   今日は基本的なところしか説明しないので詳しく知りたい人は是非読みましょう。   なお、加藤さんは今日スピーカーとして参加しているので知りたいことがあれば、 @ShinyaKato_ さんに質問しましょう。   カンファレンスのメリットです！！   あとはNTTデータさんに感謝しながら資料を読みましょう。  https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata

Slide 14

Slide 14 text

PostgreSQLのアカウントは    全てロールに通ずる  アカウント作成と権限の基本

Slide 15

Slide 15 text

“ロールの概念には、「ユーザ」という概念と「グループ」という概念が含まれます。 PostgreSQLバージョン8.1より前までは、ユーザとグループは異なる種類の実体として扱われていました。しかし、現在ではロールしか存在しません。すべてのロールは、ユーザとして、  グループとして、またはその両方として動作することができます。”    公式ドキュメントから引用 : https://www.postgresql.jp/document/14/html/user-manag.html  アカウント作成と権限の基本

Slide 16

Slide 16 text

  グループ  アカウント作成と権限の基本ログイン  ユーザA  どちらもロール  ログイン  ユーザB 

Slide 17

Slide 17 text

注意点    メジャーバージョンアップで  仕様が変わりがち  アカウント作成と権限の基本

Slide 18

Slide 18 text

● 9.6 デフォルトロールが追加  ● 10 デフォルトロールから  定義済みロールに変更  ● 14 定義済みロールが更に追加  ● 15 publicスキーマの権限が変更  アカウント作成と権限の基本

Slide 19

Slide 19 text

● 9.6 デフォルトロールが追加  ● 10 デフォルトロールから  定義済みロールに変更  ● 14 定義済みロールが更に追加  ● 15 publicスキーマの権限が変更  アカウント作成と権限の基本互換性を壊すような変更は少ないが、   新機能の追加や細かい調整は多く入っている  

Slide 20

Slide 20 text

今日はPostgreSQL 14を    前提として話をします    アカウント作成と権限の基本

Slide 21

Slide 21 text

CREATE ROLEでロールの作成  DROP ROLEでロールの削除  ALTER ROLEでロールの変更  アカウント作成と権限の基本

Slide 22

Slide 22 text

Slide 23

Slide 23 text

-- 現在の設定を確認します 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

Slide 24

Slide 24 text

-- 現在の設定を確認します SELECT * FROM pg_roles; ● ロールの一覧は専用のVIEWが用意されている  ● psqlのメタコマンドでも参照可能  ● 詳細は公式ドキュメントを読みましょう  公式ドキュメントを読もう https://www.postgresql.jp/document/14/html/view-pg-roles.html

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

権限、グループの追加には    GRANTを使う  アカウント作成と権限の基本

Slide 27

Slide 27 text

権限、グループの削除には    REVOKEを使う  アカウント作成と権限の基本

Slide 28

Slide 28 text

次のRLSで出てくるので    ここでは割愛します  アカウント作成と権限の基本

Slide 29

Slide 29 text

再掲  詳しく知りたい人は公式ドキュメントと合わせてこちらの資料を読みましょう。   https://www.slideshare.net/nttdata-tech/postgresql-roles-osc2022-online-osaka-nttdata

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Row Level Security（RLS）とは      RLSの概要と実際の活用方法

Slide 32

Slide 32 text

Row Level Security（RLS）とは  ↓  ロール単位で行のアクセスを制御する  RLSの概要と実際の活用方法

Slide 33

Slide 33 text

● テーブル単位はロール  ● 列単位はVIEW  ● 行単位はRLS  RLSの概要と実際の活用方法表示の制限 

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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;

Slide 36

Slide 36 text

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だけの結果を返す

Slide 37

Slide 37 text

● 今日の説明はこちらの記事を使います  ● 記事はすでに公開されています  ● RLSの基本的な実装方法を記載しています  詳細の使い方はこちら https://soudai.hatenablog.com/entry/2022/11/11/110825

Slide 38

Slide 38 text

Slide 39

Slide 39 text

ロール管理はセキュリティに直結      セキュリティにおけるGRANTの注意点

Slide 40

Slide 40 text

アプリケーションから    スーパーユーザを使うのはやめましょう  セキュリティにおけるGRANTの注意点

Slide 41

Slide 41 text

アプリケーションから    スーパーユーザを使うのはやめましょう  セキュリティにおけるGRANTの注意点とにかくまじで本当にこれ  

Slide 42

Slide 42 text

アプリケーションが利用するアカウントに    CREATE ROLEをつけるのはやめましょう  セキュリティにおけるGRANTの注意点

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

RLSのポリシー漏れに注意      セキュリティにおけるGRANTの注意点

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

publicスキーマは注意しましょう    すべてのユーザのCurrentなので権限が強い  セキュリティにおけるGRANTの注意点

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Slide 49

Slide 49 text

公式ドキュメントを読みましょう      まとめ

Slide 50

Slide 50 text

実際に動かしてみて    自分たちの要件を整理しましょう  まとめ

Slide 51

Slide 51 text

アカウントは後から整理するのは大変    最初から正しく運用しましょう  まとめ

Slide 52

Slide 52 text

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