postgreSQL: テーブルの作成とユーザー設定

経緯

1. pgAdminにスーパーユーザーでログイン
2. pgAdminでテーブル作成
3. laravelから「pgAdminで作成したテーブル」にアクセス → 「権限が無い」って怒られた

対応

権限のみ付与するか、所有者にするか…

• 各テーブルで、対象となるユーザーに権限を付与
• 各テーブルで、所有者を対象となるユーザーに変更

各テーブルで、対象となるユーザーに権限を付与

対象テーブルに対象クエリに対する権限を付与

select文のみ権限付与

GRANT SELECT ON TABLE 対象テーブル TO ユーザー名;

「ユーザー名」は識別子として扱われるそうだ…

▼文字列リテラルという扱いで「'」で囲むとエラー

GRANT SELECT ON TABLE 対象テーブル TO 'recatnap';

▼識別子という扱いなのでそのまま使うのが正解

GRANT SELECT ON TABLE 対象テーブル TO recatnap;

select文以外も権限付与

▼対象すべてをマルっとセット

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 対象テーブル TO ユーザー名;

権限の確認

SELECT 
    grantee, 
    privilege_type 
FROM 
    information_schema.role_table_grants 
WHERE 
    table_name = '対象テーブル' 
    AND table_schema = '対象スキーマ' # 特に設定していないと「public」
    AND grantee = 'ユーザー名';

各テーブルで、所有者を対象となるユーザーに変更

• ひとつずつ変更
• マルっと作り直す

データベース、スキーマへの権限付与

-- データベースへの接続権限を付与
GRANT CONNECT ON DATABASE データベース名 TO ユーザー名;

-- スキーマへの使用権限を付与。特に指定をしていないなら「public」
GRANT USAGE ON SCHEMA public TO ユーザー名;

ひとつずつ変更

ALTER TABLE スキマー.対象テーブル OWNER TO ユーザー名;

▼スキマーは何もしてなければ「public」になる

ALTER TABLE public.対象テーブル OWNER TO ユーザー名;

マルっと作り直す: テーブルを作り直すことが可能な場合のみ…

対象ユーザーでログインして作成すればOK

（pgAdminなど）スーパーユーザーでログインしている場合

-- 一時的に対象ユーザーに切り替える
SET ROLE ユーザー名;

-- テーブルを作成する
CREATE TABLE public.〇〇〇 (
    〇〇〇 SERIAL PRIMARY KEY,
    〇〇〇 TEXT NOT NULL
);

-- スーパーユーザーに戻る
RESET ROLE;