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

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

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

-- ログイン可能なロールを作成します。 -- ただし、パスワードはありません。 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

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

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

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

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

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

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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