Zenn
Open3

PostgreSQLの構造を理解する

KenshiroKenshiro

データベースクラスタとは

  • データベースクラスタとはポスグレ独自の用語で、1つのサーバーインスタンスで管理されているデータベースの集合体のこと
  • initdbコマンドでデータベースクラスタを作成し、createdbコマンドを実行することで、データベースを追加できる。initdbコマンド直後は、template0, tempate1の2つのテンプレートデータベースとpostgresというデータベースが存在する
  • 1つのデータベース内に複数のデータベースを作ることは、ポスグレ以外のRDBMSでもできるが、内部のdbの独立具合はRDBMSの種類で変わる
  • まとめると、1つのデータバースクラスタ内に複数のデータベースがり、その中に複数のテーブルがある
  • クラスタの使われ方の違い
    • ポスグレ: クラスタ = 1つのデータディレクトに属する全てのデータベース
    • MySQLなど: クラスタ = 複数のサーバーが強調動作するシステム
    • 比較
RDBMS クラスタの意味 1つのサーバーで複数クラスタ作成可？ 1つのクラスタに複数DBを作成可？
PostgreSQL 1つのデータディレクトリに属するDB群 ✅ 可能（initdb で複数作成可） ✅ 可能（デフォルトで複数DBを管理）
MySQL レプリケーションやシャーディングのグループ ❌ 不可（1つのインスタンス = 1クラスタ） ✅ 可能（CREATE DATABASE で複数DB作成）
Oracle データベース全体の管理単位（RAC構成） ✅ 可能（複数インスタンス構成が可能） ✅ 可能
SQL Server 可用性やスケーラビリティのための構成 ❌ 不可（1つのSQL Server = 1クラスタ） ✅ 可能（複数DBを管理）

  • 上記の1つのサーバーとは、ポスグレやMySQLなどかインストールされ実行される物理サーバーや仮想サーバー、クラウドインスタンスのこと。

  • 1つのサーバーとデータベースの関係

    物理 or 仮想サーバー (AWS, GCP, 自宅PC)
  ├── PostgreSQL (RDBMSのインスタンス)
       ├── クラスタ1 (データベースの集合)
       │     ├── データベースA
       │     ├── データベースB
       │     ├── データベースC
       │
       ├── クラスタ2 (別のデータベースの集合)
             ├── データベースX
             ├── データベースY

  • 参考
    https://oss-db.jp/dojo/dojo_18

KenshiroKenshiro

権限設定

  • ポスグレのユーザーはクラスタ単位で管理される。つまり、ポスグレインスタンス内にクラスタAとクラスタBがある場合は、クラスタAのユーザーはクラスタBに存在しない
  • 権限の確認順番 (SELECTのデータ取得版)
    1. PostgreSQLへのLOGIN権限があるか
    2. データベースへのCONNECT権限があるか
    3. スキーマのUSAGE権限があるか
    4. SELECT権限があるか
  • PostgreSQLへのLOGIN権限付与
    CREATE ROLE user1 WITH LIGIN PASSWORD 'hoge';
  • データベースのCONNECT権限付与
    GRANT CONNECT ON DATABASE testdb to user1;
  • スキーマへのUSAGE権限付与
    GRANT USAGE ON SCHEMA "systemA" TO user1;
    • ポスグレは小文字大文字の区別ができないのダブルクオテーションをつける。なしだとsysytemaと判断される
  • テーブルへのアクセス権限
    • テーブル内の全ての操作
      GRANT ALL PRIVIKEGES IN ALL TABLES IN SCHEMA "sysytemA" TO user1;
    • SELECT文のみ
      GRANT SELECT ON TABLE "systemA".users to user1;
  • クラスタ内のユーザー取得
    SELECT * FROM pg_user;
  • クラスタ内の権限の確認コマンド (rolname = ユーザー名(ロール名))
    SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin FROM pg_roles;
  • PostgreSQL では「ユーザー（User）」と「ロール（Role）」は同じ概念なので、同じロール名は1つしか存在しない
  • 権限付与(GRANT)を実行できるユーザーは、スーパーユーザーか、スキーマやテーブルの所有者
KenshiroKenshiro

新規作成したスキーマで権限付与エラーはこれを設定

https://supabase.com/docs/guides/api/using-custom-schemas?queryGroups=language&language=curl

  • SQL解説
    GRANT USAGE ON SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA myschema TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA myschema TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;
    • 1行目: USAGE権限付与
    • 2行目: ALL PRIVILEGES付与 (ALL PRIVILEGES = SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERANCES, TRINNGER)
    • 3行目: myschema内の全てのルーチン(関数、プロシージャ)に対して全権限を付与
    • 4行目: スキーマ内の全てのシーケンスにアクセスできるようにする
    • 5行目: 今後、postgresユーザーが新規テーブルを作成した時に、そのテーブルにanon等にALL権限を付与する
    • 6行目: 今後、postgres ユーザーが myschema に新しいルーチンを作成した場合に、anon等にALL権限を付与する
    • 7行目: 今後、postgres ユーザーが myschema に新しいシーケンスを作成した場合に、anon等にALL権限を付与する