PostgreSQLでユーザー（ロール）を作成しデータベース単位で権限を絞る手順｜CREATE ROLEとGRANTの実務

この記事のポイント

・CREATE ROLE 名前 LOGIN PASSWORD 'パスワード' でアプリ専用ユーザーを作成できる
・権限はDB接続→スキーマ使用→テーブル操作の3段階で付与する必要がある
・GRANT ON ALL TABLESで既存テーブルを一括付与、DEFAULT PRIVILEGESで新規テーブルに自動付与
・permission denied for tableはスキーマのUSAGE権限が抜けているケースが多い

-- postgres ユーザーで psql を起動
$ sudo -u postgres psql

-- ロールを作成（LOGIN と PASSWORD を指定する）
CREATE ROLE appuser LOGIN PASSWORD 'your_secure_password';

postgres=# CREATE ROLE appuser LOGIN PASSWORD 'your_secure_password';
CREATE ROLE

-- myapp データベースへの接続を許可する
GRANT CONNECT ON DATABASE myapp TO appuser;

-- myapp データベースに切り替えてから実行する
\c myapp

-- public スキーマの使用権限を付与する
GRANT USAGE ON SCHEMA public TO appuser;

-- 読み書き権限を付与する（SELECT・INSERT・UPDATE・DELETE）
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

-- シーケンスの更新権限も付与する（SERIAL / BIGSERIAL 列がある場合に必要）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

-- postgres ユーザーが今後作成するテーブルにも自動でアクセス権を付与する
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO appuser;

-- 接続先データベース: myapp で実行
CREATE ROLE readonly_user LOGIN PASSWORD 'your_secure_password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
\c myapp
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

CREATE ROLE appuser LOGIN PASSWORD 'your_secure_password';
GRANT CONNECT ON DATABASE myapp TO appuser;
\c myapp
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO appuser;

CREATE ROLE db_admin LOGIN PASSWORD 'your_secure_password' CREATEDB;
GRANT CONNECT ON DATABASE myapp TO db_admin;
\c myapp
-- スキーマ内のオブジェクト作成・変更を許可する
GRANT ALL PRIVILEGES ON SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL PRIVILEGES ON TABLES TO db_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL PRIVILEGES ON SEQUENCES TO db_admin;

-- 全ロール一覧を表示する
\du

-- テーブルの権限一覧を表示する
\dp テーブル名

-- またはすべてのテーブルの権限を表示する
\dp

-- ロールのログイン属性などを確認する
SELECT rolname, rolcanlogin, rolcreatedb, rolsuper
  FROM pg_roles
 WHERE rolname = 'appuser';

myapp=# \du
                                   List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 appuser    | 
 db_admin   | Create DB
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS
 readonly_user | 

-- テーブル権限を剥奪する
REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM appuser;

-- スキーマ使用権限を剥奪する
REVOKE USAGE ON SCHEMA public FROM appuser;

-- データベース接続権限を剥奪する
REVOKE CONNECT ON DATABASE myapp FROM appuser;

-- ロール自体を削除する（権限をすべて剥奪してから実行する）
DROP ROLE appuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM appuser;

ERROR:  permission denied for schema public

GRANT USAGE ON SCHEMA public TO appuser;

-- 新規テーブルに対してもう一度 GRANT を実行するか、
-- ALTER DEFAULT PRIVILEGES を設定して自動付与にする
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;