psqlでロールとデータベースを管理する方法｜postgresユーザーへの切り替えと権限付与の基本

この記事のポイント

・psql接続は「su - postgres → psql」の2ステップが基本
・CREATE ROLE 名前 WITH LOGIN PASSWORD でアプリ用ロールを作る
・権限付与はCONNECT→USAGE→テーブル権限の3段階で行う
・PostgreSQL 15以降はpublicスキーマのデフォルトCREATE権限が変わった

# OSユーザーをpostgresに切り替える
su - postgres

# sudoが使える環境はこちらでも可
sudo -i -u postgres

# postgresOSユーザーからの場合（引数なしで接続可）
psql

# ユーザー名・DB名を明示して接続する
psql -U postgres -d postgres

# ホスト・ポートを指定してTCP接続する
psql -U postgres -h 127.0.0.1 -p 5432 -d postgres

-- ログイン可能なロール（パスワード付き）
CREATE ROLE webapp WITH LOGIN PASSWORD 'StrongPass123!';

-- ログイン不可のグループロール
CREATE ROLE readonly_group;

-- 既存ロールをグループロールのメンバーに追加
GRANT readonly_group TO webapp;

postgres=# \du
                                   List of roles
 Role name      |                         Attributes                         | Member of
----------------+------------------------------------------------------------+-----------
 postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly_group | Cannot login                                                | {}
 webapp         |                                                             | {readonly_group}

SELECT rolname, rolcanlogin, rolsuper FROM pg_roles ORDER BY rolname;

-- ロールを削除する（オブジェクトを所有している場合はエラー）
DROP ROLE webapp;

-- 存在チェック付き（スクリプト向け）
DROP ROLE IF EXISTS webapp;

-- 基本的な作成（オーナーはpostgres）
CREATE DATABASE myappdb;

-- オーナーを指定して作成する
CREATE DATABASE myappdb OWNER webapp;

-- 文字コードとロケールを指定して作成する（日本語環境向け）
CREATE DATABASE myappdb
  OWNER webapp
  ENCODING 'UTF8'
  LC_COLLATE 'ja_JP.UTF-8'
  LC_CTYPE 'ja_JP.UTF-8'
  TEMPLATE template0;

ALTER DATABASE myappdb OWNER TO webapp;

-- Step 1: データベースへの接続権限
GRANT CONNECT ON DATABASE myappdb TO webapp;

-- Step 2: スキーマの使用権限（\c myappdb でDBに切り替えてから実行）
\c myappdb
GRANT USAGE ON SCHEMA public TO webapp;

-- Step 3: 既存テーブルへの権限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;

-- 今後追加されるテーブルへのデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;

-- シーケンス（SERIAL/BIGSERIALの自動採番）への権限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO webapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO webapp;

-- PostgreSQL 15以降: publicスキーマのCREATE権限を明示的に付与
GRANT CREATE ON SCHEMA public TO webapp;

-- テーブルへの書き込み権限を剥奪
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM webapp;

-- データベース接続権限を剥奪
REVOKE CONNECT ON DATABASE myappdb FROM webapp;

\c myappdb
GRANT USAGE ON SCHEMA public TO webapp;

-- 対話的にパスワードを変更（コマンド履歴に平文が残らない）
\password webapp

-- SQLで直接変更する場合
ALTER ROLE webapp WITH PASSWORD 'NewStrongPass456!';