PostgreSQL: PostgreSQLのユーザー、ロール、権限管理

-- グループロール（ログイン不可）
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team NOINHERIT;
CREATE ROLE audit_team NOINHERIT;

-- ログインユーザー
-- ⚠️ 本番環境ではパスワードをハードコードせず、環境変数やシークレット管理を使用してください
CREATE USER alice_dev PASSWORD 'SecureP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'SecureP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'SecureP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'SecureP@ss4' IN ROLE ops_team INHERIT;

CREATE TABLE

-- opsチームリーダーにCREATEDB権限を追加
ALTER ROLE diana_ops CREATEDB;

-- パスワード有効期限を設定
ALTER ROLE charlie_analyst VALID UNTIL '2025-12-31';

-- ロール名を変更
ALTER ROLE dev_team RENAME TO engineering_team;

-- 一時的にログインを無効化
ALTER ROLE bob_dev NOLOGIN;

CREATE TABLE

-- Devチーム: ビジネステーブルに対する完全CRUD
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO dev_team;

-- Analyticsチーム: 読み取り専用
GRANT SELECT ON products TO analytics_team;
GRANT SELECT ON orders TO analytics_team;
GRANT SELECT ON customers TO analytics_team;

-- Auditチーム: 監査ログのみ
GRANT SELECT ON price_audit_log TO audit_team;
GRANT SELECT ON ddl_audit_log TO audit_team;

INSERT 0 1

-- Devチームが開発スキーマでオブジェクト作成可能
GRANT CREATE, USAGE ON SCHEMA dev TO dev_team;

-- Analyticsチームがpublicスキーマを使用可能（作成不可）
GRANT USAGE ON SCHEMA public TO analytics_team;

-- Analyticsが自身のスキーマでマテリアライズドビュー作成を許可
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;

-- アプリサービスがデータベースに接続
GRANT CONNECT ON DATABASE shop_db TO app_service;

CREATE TABLE

-- DevチームからproductsのDELETEを取り消し
REVOKE DELETE ON products FROM dev_team;

-- テーブルから全権限を取り消し
REVOKE ALL PRIVILEGES ON orders FROM public;

-- スキーマ作成権限を取り消し
REVOKE CREATE ON SCHEMA dev FROM dev_team;

-- カスケード: 権限と依存する付与を取り消し
REVOKE SELECT ON products FROM analytics_team CASCADE;

DELETE 2

-- publicスキーマの将来の全テーブルをanalytics_teamが読み取り可能に
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;

-- app_serviceが作成する将来の全テーブルをdev_teamが書き込み可能に
ALTER DEFAULT PRIVILEGES FOR ROLE app_service IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_team;

-- publicスキーマの将来の全シーケンス
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- 将来の全関数
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO analytics_team;

INSERT 0 1

SELECT
  pg_get_userbyid(defaclrole) AS grantor,
  pg_get_userbyid(defaclnamespace) AS namespace_owner,
  n.nspname AS schema,
  defaclobjtype AS object_type,
  defaclacl AS acl
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- ordersテーブルでRLSを有効化
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- アナリストは完了注文のみ表示可能
CREATE POLICY pol_analytics_completed
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Devチームは全行を表示可能
CREATE POLICY pol_dev_all
  ON orders FOR ALL
  TO dev_team
  USING (true)
  WITH CHECK (true);

CREATE TABLE

-- customersテーブルでRLSを有効化
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- 各カスタマーサービスユーザーは担当リージョンのみ表示
CREATE POLICY pol_region_access
  ON customers FOR SELECT
  USING (region = current_setting('app.region', true));

-- テーブル所有者にもRLSを強制
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE TABLE

-- 営業チームは自身のリージョンの注文のみ挿入可能
CREATE POLICY pol_sales_insert
  ON orders FOR INSERT
  TO dev_team
  WITH CHECK (region = current_setting('app.region', true));

-- 営業チームは自身のリージョンの注文のみ更新可能
CREATE POLICY pol_sales_update
  ON orders FOR UPDATE
  TO dev_team
  USING (region = current_setting('app.region', true))
  WITH CHECK (region = current_setting('app.region', true));

INSERT 0 1

-- NOINHERITで強力なロールを作成
CREATE ROLE admin_role NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Dianaはadmin_roleに所属するが管理権限を自動では使用不可
CREATE USER diana PASSWORD 'SecureP@ss5' IN ROLE admin_role NOINHERIT;

-- Dianaは管理権限を使用するために明示的に切り替える必要がある
SET ROLE admin_role;
-- これでDianaは管理操作を実行可能
RESET ROLE;
-- 通常の権限に戻る

CREATE TABLE

-- 全ロールとその属性を一覧表示
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

-- ロールメンバーシップを一覧表示
SELECT
  r1.rolname AS member,
  r2.rolname AS role
FROM pg_auth_members m
JOIN pg_roles r1 ON r1.oid = m.member
JOIN pg_roles r2 ON r2.oid = m.roleid
ORDER BY r2.rolname, r1.rolname;

-- ロールのテーブル権限を確認
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_team'
ORDER BY table_name, privilege_type;

CREATE TABLE

-- Step 1: グループロールを作成
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team INHERIT;
CREATE ROLE audit_team NOINHERIT;
CREATE ROLE app_service LOGIN PASSWORD 'AppSecRet!';

-- Step 2: スキーマ権限を付与
GRANT CREATE, USAGE ON SCHEMA public TO dev_team;
GRANT USAGE ON SCHEMA public TO analytics_team;
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
GRANT USAGE ON SCHEMA public TO ops_team;
GRANT USAGE ON SCHEMA audit TO audit_team;

-- Step 3: テーブル権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders, order_items, customers TO app_service;

-- Step 4: 将来のテーブルに対するデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit
  GRANT SELECT ON TABLES TO audit_team;

-- Step 5: シーケンスと関数の権限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dev_team;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- Step 6: RLS - アナリストは完了注文のみ表示
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_analytics_orders
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Step 7: RLS - 監査チームはauditスキーマのみ表示
ALTER TABLE price_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_audit_read
  ON price_audit_log FOR SELECT
  TO audit_team
  USING (true);

-- Step 8: ログインユーザーを作成してロールを割り当て
CREATE USER alice_dev PASSWORD 'DevP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'DevP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'AnlP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'OpsP@ss4' IN ROLE ops_team CREATEROLE;
CREATE USER eve_audit PASSWORD 'AudP@ss5' IN ROLE audit_team;