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

PostgreSQLのロール・権限のここがわかりづらい

 PostgreSQLのロール・権限のここがわかりづらい

まぐろ

July 03, 2023
Tweet

Other Decks in Programming

Transcript

  1. PostgreSQLのロール・権限
    のここがわかりづらい!
    まぐろ

    View Slide

  2. はじめに
    • 虎の巻でわいわい言っていた皆さんお疲れさまでした、16でも
    ロール周りは複雑になりそうです
    • そもそも、PostgreSQLのロールや権限周りの設定・機能って
    わかりにくいですよね? わたしも全然わかりません
    • というわけで、とにかく個人的に「ここがわかりづらい!」と
    いう点を列挙しました
    • 残念ながら「こうすればわかりやすくなるよ!」という話はあ
    りません

    View Slide

  3. 自己紹介
    • まぐろ(Twitter:@tameguro)
    • 都内某SI勤務のSE
    • PostgreSQLの設計・導入・保守などをやっていたこともあります
    • PostgreSQL歴はだいたい10年くらい
    • PostgreSQLの技術同人誌をいくつか書いてます
    • その縁で電子書籍として商業誌を書かせてもらえました
    • Kindleなどで「目黒聖」で検索するとたぶんなんか出てきます

    View Slide

  4. ロールの属性

    View Slide

  5. まずは基本のおさらいから…
    • PostgreSQLではユーザもグループもいわゆるロールもひっく
    るめて、すべて「ロール」として扱われます
    • 端的に言うと、ログイン属性を持つロールをユーザ、ログイン
    属性を持たないロールをグループとして扱います
    • CREATE ROLEでロールを作成しますが、次のSQLも有効です
    • CREATE USER:ログイン属性をデフォルトで付加してロールを作成
    • CREATE GROUP:単にCREATE ROLEの別名

    View Slide

  6. ロールの属性とは?
    • スーパーユーザ、データベース作成、ロールの作成、ログイン、
    レプリケーションなど、データベース全体にまたがる役割です
    • Oracleでいう「システム権限」に近い……かもしれない……
    • 「属性」とは言いますが「権限」と言っても差し支えなく、日
    常会話では「権限」と言っても全く問題ありません
    • 実質権限ではありますが、GRANTやREVOKEではなくALTERで
    変更します
    • つまりPostgreSQLには権限の設定の方法が2つある

    View Slide

  7. グループについて

    View Slide

  8. ロールとグループについて
    • CREATE GROUPはCREATE ROLEの別名となっており、
    PostgreSQLにおいて実質的にグループはなくなっていると
    思っていいでしょう
    • 下位互換のためpg_groupシステムビューは残っていますが、結局は
    pg_rolesシステムビューのrolcanloginがfalseのロールを表示している
    のみ
    • グループは存在せず、ユーザとロール(いくつかの権限を集め
    た権限の集合)しかないと考えたほうがいいと思います

    View Slide

  9. グループと考えてしまうと…
    • グループだとこのGRANT文が理解しにくくなります
    • 普通はグループの中にユーザなのに、GRANT文ではユーザの中にグルー
    プを入れているように見えてしまい、どっちがグループかわかりにくい
    • ロール(権限の集合)と考えればGRANT文は妥当に見えます

    View Slide

  10. グループなんてものは存在しない
    • グループという概念は忘れたほうがよさそうです
    • いつまでも下位互換と言わず整理して廃止したら?
    こういうところに残ってる
    (英語ではMember of)

    View Slide

  11. オブジェクト権限
    も曲者

    View Slide

  12. オブジェクト権限は複雑すぎる
    • saitamaユーザとtokyoユーザがいて、それぞれsaitamaスキー
    マとtokyoスキーマの所有者であり、他の権限は何もついてい
    ない状態とします
    • Oracleはユーザとスキーマが一緒ですが、それを擬似的に再現
    していると思ってください

    View Slide

  13. オブジェクト権限は複雑すぎる
    • tokyoスキーマにはtestというテーブルがありますが…
    • saitamaユーザが検索すると、tokyoスキーマへのアクセス権限
    がないと言われてしまいます

    View Slide

  14. オブジェクト権限は複雑すぎる
    • それでは、saitamaユーザにtokyoスキーマの全権限を与えてみます
    • もう一度saitamaユーザで検索すると…
    • tokyoスキーマの全権限を与えたはずなのに、tokyoスキーマに存在
    するテーブルが参照できません

    View Slide

  15. オブジェクト権限について
    • PostgreSQLでは、オブジェクトごとにどんな権限が設定できるかが
    決まっており、スキーマはUSAGEとCREATE権限のみを与えること
    ができます
    • USAGEはオブジェクトによって意味が異なるが、スキーマではスキーマ内の
    オブジェクト名を参照できる権限
    • つまり、GRANT ALL ON SCHEMAを実行しても、「そのスキーマ
    にあるオブジェクト名を参照する権限」と、「そのスキーマにオブ
    ジェクトを作成する権限」が与えられるだけで、そのスキーマとそ
    の中のオブジェクトに対してなんでもできちゃう権限が与えられる
    わけではない
    • もちろんテーブルを参照するにはスキーマの権限がないとダメなの
    で、誰にどのオブジェクトに対しどういう権限を与えるかをミスる
    と予想外の動きをする

    View Slide

  16. View Slide

  17. テーブルの全権限を与えるには
    • ちょっと工夫が必要です
    • テーブルが参照できるようになりました!

    View Slide

  18. 新規テーブルの権限
    • しかし新しくテーブルを作ると…
    • 新しいテーブルは検索できません

    View Slide

  19. デフォルト権限

    View Slide

  20. デフォルト権限というものがあります!
    • GRANTやREVOKEは「すでに存在しているオブジェクトに対し
    て」権限を付与したり剥奪したりする構文
    • まだ存在していないオブジェクトに対しては、デフォルト権限
    というものが存在します!
    • その名の通り、「新しく作成されたオブジェクトに対して」デ
    フォルトの権限を割り当てるというもの
    • 動的にテーブルが増えたり開発中でテーブル設計が変わる可能
    性が高いときに使ったりします

    View Slide

  21. デフォルト権限というものがあります!
    • ALTER DEFAULT PRIVILEGES構文(詳しくはマニュアルを参
    照)
    • 「今後tokyoスキーマに作成されるテーブルに対し、saitama
    ロールに自動的にSELECT権限を付与する」という意味
    • めんどくさいしいくらなんでもわかりにくすぎる
    ALTER DEFAULT PRIVILEGES IN SCHEMA tokyo
    GRANT SELECT ON TABLES TO saitama;

    View Slide

  22. 今ついてる権限は?

    View Slide

  23. 権限を確認するには
    • オブジェクト権限はpg_catalog.pg_classのrelaclという列に保存さ
    れています
    • アルファベット1文字が権限を表します
    • が、例えば「a=append(INSERT)」などマニュアルを見ないとわか
    らない表し方になっており、パッと見どういう権限なのか全然わか
    りません
    権限を付与されたロール=付与された権限/権限を付与したロール

    View Slide

  24. View Slide

  25. 権限を確認するには
    • オブジェクトについている権限しかわからない
    • でも一般的に権限って、「誰が」「どういう」権限を持ってい
    るか?or持っていないか?で確認したくないですか?
    • ユーザがどういう権限を持っているかを一発で出すコマンドは
    ない(はず)
    • オブジェクトからユーザの持ってる権限を探すって結構きつい
    し、漏れも出てきそう

    View Slide

  26. デフォルト権限を確認するには
    • デフォルト権限はpg_default_aclカタログに記録されています
    • ¥ddpメタコマンドで確認できます

    View Slide

  27. まとめ
    • 属性と権限の設定方法統一してほしい…
    • 現在サポート中のバージョンではすでにグループという概念が
    ないのだから、すべて廃止すればいいのでは?
    • オブジェクト権限を真面目に設定するの難しすぎる
    • デフォルト権限がわかりにくい
    • ユーザが付与されている権限を確認
    できる方法がほしい
    • 権限分掌はわかるんですけどより複雑で
    直感的じゃない方に進んでないですかね?

    View Slide