0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

PR: Wealthy Design 株式会社
AWS・クラウド・AI開発に取り組むエンジニアを募集しています
About Promotions
@s_horikoshi(堀越 誠一朗)
in

RDS for PostgreSQL権限設定 app_owner・app_user・app_readonlyを作る

0
Last updated at Posted at 2026-09-03

RDS for PostgreSQL権限設定 app_owner・app_user・app_readonlyを作るのアイキャッチ

はじめに

RDS for PostgreSQLで、マスターユーザーをアプリケーションから使わないためには、Roleを作るだけでなく、所有権、スキーマ、既存テーブル、将来作るテーブルまで一貫して設定する必要があります。

この記事では、次のRoleを実際に作り、アプリケーション用データベースとスキーマへ最小権限を設定します。

  • app_owner: オブジェクト所有者
  • app_migrator: マイグレーション実行者
  • app_rw / app_user: アプリケーションの読み書き
  • app_ro / app_readonly: 調査やBIの参照専用

本記事は3本構成の2本目です。

  1. RDS for PostgreSQL初期設計 マスターユーザーを避けRoleを用途別に分ける
  2. 本記事: Role・データベース・スキーマの構築
  3. RDS for PostgreSQL権限テスト GRANT漏れと運用設定を確認する（今後公開予定）

先に結論

  • テーブル所有者はログイン不可のapp_ownerにする
  • app_migratorは必要なときだけSET ROLE app_ownerする
  • アプリの読み書き権限はapp_rwへまとめ、app_userへ付与する
  • 参照権限はapp_roへまとめ、app_readonlyへ付与する
  • PUBLICの不要な権限を取り消す
  • 既存オブジェクトと将来作るオブジェクトの両方へGRANTする
  • マイグレーション時のcurrent_userをapp_ownerにそろえる

前提

  • RDS for PostgreSQL 15以降を想定する
  • RDSマスターユーザーでpostgresデータベースへ接続済み
  • 前編のAWS側チェックとTLS接続確認が完了している
  • サンプルではapp_dbデータベースとappスキーマを作る
  • サンプルのRoleやデータベースはまだ存在しない

このSQLは初回構築用です。既存環境で再実行すると、Roleやデータベースの重複エラー、既存権限への影響が発生します。既存環境へ適用する場合は、現在の所有者とGRANTを取得し、差分SQLとしてレビューしてください。

以下のSQLは、2026年9月3日時点でPostgreSQL 15と16以降、およびAWSの公式資料と照合した構成例です。接続可能なRDS環境がないため、この記事の確認作業ではSQLを実行していません。Role名や既存権限を環境に合わせて調整し、検証用データベースで確認してから適用してください。

完成する構成

使用する名前

種別 名前
アプリ用データベース app_db
アプリ用スキーマ app
所有者Role app_owner
マイグレーションRole app_migrator
読み書き権限Role app_rw
アプリ接続Role app_user
参照権限Role app_ro
参照専用Role app_readonly

実際の環境では、システム名や環境名を含む命名規則に置き換えてください。

Step 1: Roleを作成する

RDSマスターユーザーでpostgresデータベースへ接続し、Roleを作成します。

-- オブジェクト所有者です。直接ログインさせません。
CREATE ROLE app_owner
    NOLOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

-- アプリの読み書き権限をまとめるグループRoleです。
CREATE ROLE app_rw
    NOLOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

-- 参照権限をまとめるグループRoleです。
CREATE ROLE app_ro
    NOLOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

-- DDLを実行するログインRoleです。
-- NOINHERITにより、接続しただけではapp_ownerの権限を使いません。
CREATE ROLE app_migrator
    LOGIN
    NOINHERIT
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

-- アプリケーションが通常利用するログインRoleです。
CREATE ROLE app_user
    LOGIN
    INHERIT
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

-- 調査やBIで使う参照専用のログインRoleです。
CREATE ROLE app_readonly
    LOGIN
    INHERIT
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION
    NOBYPASSRLS;

ログインRoleはパスワード未設定のため、この時点ではパスワード認証に成功しません。平文パスワードをSQLへ書かず、後の手順で対話設定します。

Step 2: Roleの所属関係を設定する

Roleを作っただけでは権限は連動しません。権限RoleをログインRoleへ付与します。

-- マイグレーション時だけapp_ownerへ切り替えられるようにします。
GRANT app_owner TO app_migrator;

-- app_userへ読み書き権限グループを付与します。
GRANT app_rw TO app_user;

-- app_readonlyへ参照権限グループを付与します。
GRANT app_ro TO app_readonly;

app_migratorはNOINHERITです。app_ownerへ所属していても、接続しただけでは所有者権限を自動利用しません。DDLを実行するときにSET ROLE app_ownerします。

PostgreSQL 16以降では、RoleメンバーシップごとにADMIN、INHERIT、SETのオプションがあります。app_migratorはNOINHERITで作成してからGRANTしているため、16以降では新しいメンバーシップのINHERITが無効、SETが有効になります。実際の所属状態は3本目の確認SQLで検証します。

管理ユーザーからapp_ownerへ切り替えられるようにする

RDSマスターユーザーがapp_owner所有のデータベースを作成し、初期設定を行うには、app_ownerへSET ROLEできる必要があります。最初にバージョンを確認します。

SHOW server_version_num;

PostgreSQL 15では、次のように所属と管理権限を付与します。

-- PostgreSQL 15用です。
GRANT app_owner
TO CURRENT_USER
WITH ADMIN OPTION;

PostgreSQL 16以降では、CREATEROLEを持つ非スーパーユーザーがRoleを作成したとき、作成したRoleがSET FALSEで自動付与されます。既存の所属へSET TRUEを明示します。

-- PostgreSQL 16以降用です。
GRANT app_owner
TO CURRENT_USER
WITH ADMIN TRUE, SET TRUE, INHERIT FALSE;

15用と16以降用のSQLは、利用中のメジャーバージョンに合う方だけを実行してください。16以降のSET TRUEを省くと、後続のSET ROLE app_ownerやCREATE DATABASE ... OWNER = app_ownerが失敗する場合があります。

Step 3: ログインRoleのパスワードを設定する

psqlでは、平文をSQLファイルへ残さず対話入力できます。

-- マイグレーション用Roleのパスワードを設定します。
\password app_migrator

-- アプリ接続Roleのパスワードを設定します。
\password app_user

-- 参照専用Roleのパスワードを設定します。
\password app_readonly

アプリで使う資格情報は、Secrets Managerなどの秘密情報管理サービスへ保存します。SQLファイル、.envのひな型、チケット、チャット、シェル履歴へ実パスワードを残しません。

RDSとSecrets Managerの直接統合でRDSが生成・管理するのは、RDSマスターユーザーのパスワードです。自分で作成したapp_userなどは別のシークレットとして保存し、ローテーション方法を別途設計します。

Step 4: app_dbを作成する

所有者をapp_ownerにして、アプリ用データベースを作成します。

-- CREATE DATABASEはトランザクション外で実行します。
CREATE DATABASE app_db
    WITH
    OWNER = app_owner
    ENCODING = 'UTF8'
    TEMPLATE = template0;

CREATE DATABASEはトランザクションブロック内で実行できません。次のようにBEGINとCOMMITでは囲みません。

-- 実行不可の例です。
BEGIN;
CREATE DATABASE app_db;
COMMIT;

データベースへの接続を限定する

PUBLICはインターネット公開を意味する言葉ではありません。PostgreSQLで、現在および将来作られるすべてのRoleを表す特別なグループです。

-- データベース所有者へ切り替えます。
SET ROLE app_owner;

-- 全Roleに付いているapp_dbの権限を取り消します。
REVOKE ALL PRIVILEGES
ON DATABASE app_db
FROM PUBLIC;

-- 必要なRoleだけapp_dbへ接続できるようにします。
GRANT CONNECT
ON DATABASE app_db
TO app_owner, app_migrator, app_rw, app_ro;

RESET ROLE;

app_userはapp_rw、app_readonlyはapp_roの権限を継承するため、間接的にCONNECTを利用できます。NOINHERITのapp_migratorには直接付与しています。

一時テーブルが必要になった場合だけ、要件を確認してTEMPORARYを追加します。

-- 一時テーブルが本当に必要な場合だけ付与します。
SET ROLE app_owner;

GRANT TEMPORARY
ON DATABASE app_db
TO app_rw;

RESET ROLE;

Step 5: app_dbへ接続する

ここからは接続先をapp_dbへ切り替えます。

-- psqlで接続中のデータベースを変更します。
\connect app_db

接続先を間違えたままスキーマを作らないよう、最初に確認します。

-- app_dbへ接続できていることを確認します。
SELECT
    current_user,
    current_database();

Step 6: publicスキーマを制限する

PostgreSQL 15以降で新規作成したデータベースは、publicスキーマへ全RoleがCREATEできない既定になっています。ただし、古いバージョンからアップグレードした環境や復元した環境では権限が残る場合があります。

状態に依存しないよう、明示的に取り消します。

-- 一般Roleがpublicスキーマへオブジェクトを作れないようにします。
SET ROLE app_owner;

REVOKE CREATE
ON SCHEMA public
FROM PUBLIC;

RESET ROLE;

書き込み可能なスキーマを無条件でsearch_pathへ含めると、同名の関数やオブジェクトによる予期しない動作につながります。アプリ用スキーマのCREATEは所有者に限定します。

Step 7: appスキーマを作成する

app_ownerへ切り替え、所有者としてスキーマを作成します。

-- 所有者Roleへ切り替えます。
SET ROLE app_owner;

-- app_owner所有のアプリ用スキーマを作成します。
CREATE SCHEMA app;

-- 全Role共通の権限を取り消します。
REVOKE ALL PRIVILEGES
ON SCHEMA app
FROM PUBLIC;

-- 読み書きRoleと参照Roleは、スキーマ内を参照できます。
GRANT USAGE
ON SCHEMA app
TO app_rw, app_ro;

-- 管理ユーザーの権限へ戻します。
RESET ROLE;

USAGEはスキーマ内のオブジェクトを参照するための権限です。CREATEは付けていないため、app_userやapp_readonlyは勝手にテーブルを作れません。

Step 8: 既存オブジェクトへ権限を付与する

既存のテーブルとシーケンスへ権限を付与します。初期構築直後で対象が0件でも、復元済みデータベースへ適用する場合に必要です。

-- デフォルト権限の主体と所有者をapp_ownerへそろえます。
SET ROLE app_owner;

-- app_rwへ既存テーブルの読み書きを許可します。
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_rw;

-- SERIALや明示的なnextvalで使う既存シーケンスを許可します。
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA app
TO app_rw;

-- app_roへ既存テーブルの参照だけを許可します。
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO app_ro;

RESET ROLE;

テーブルのINSERT権限とシーケンス権限は別です。シーケンスを使う設計では、テーブルだけGRANTするとpermission denied for sequenceになる場合があります。

Step 9: 将来作るオブジェクトのデフォルト権限を設定する

GRANT ON ALL TABLESは、実行時点で存在するテーブルだけが対象です。マイグレーションで後から作るテーブルには、ALTER DEFAULT PRIVILEGESを設定します。

-- 今後オブジェクトを作るRoleへ切り替えます。
SET ROLE app_owner;

-- app_ownerが今後作るテーブルへ、app_rwの読み書きを自動付与します。
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES
TO app_rw;

-- app_ownerが今後作るシーケンスへ、app_rwの利用権限を自動付与します。
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT USAGE, SELECT
ON SEQUENCES
TO app_rw;

-- app_ownerが今後作るテーブルへ、app_roの参照権限を自動付与します。
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT
ON TABLES
TO app_ro;

RESET ROLE;

デフォルト権限は、「実際にオブジェクトを作成した現在のRole」を基準に適用されます。app_migratorのままテーブルを作ると、app_ownerに設定したデフォルト権限は適用されません。

この例で設定するデフォルト権限は、テーブルとシーケンスが対象です。PostgreSQLは新しい関数やプロシージャのEXECUTEをPUBLICへ既定で付与します。マイグレーションで関数やプロシージャを作る場合は、app_ownerのデフォルト権限と、実行を許可するRoleを別途設計してください。

Step 10: timezoneとsearch_pathを設定する

日時をUTCで保存し、表示時に利用者のタイムゾーンへ変換する設計なら、データベースの既定値をUTCにします。

-- データベースの既定タイムゾーンをUTCにします。
SET ROLE app_owner;

ALTER DATABASE app_db
SET timezone = 'UTC';

RESET ROLE;

ログインRoleごとに、システムカタログとアプリ用スキーマを検索対象にします。

-- pg_catalogを先にし、次にアプリ用スキーマを検索します。
ALTER ROLE app_migrator
IN DATABASE app_db
SET search_path = pg_catalog, app;

ALTER ROLE app_user
IN DATABASE app_db
SET search_path = pg_catalog, app;

ALTER ROLE app_readonly
IN DATABASE app_db
SET search_path = pg_catalog, app;

search_pathを設定しても、マイグレーションや重要なSQLではapp.usersのようにスキーマ名を明示すると、対象をレビューしやすくなります。

Step 11: マイグレーションRoleでテーブルを作る

app_migratorでapp_dbへ接続し、トランザクション内だけapp_ownerへ切り替えます。

BEGIN;

-- このトランザクション内だけ、所有者RoleとしてDDLを実行します。
SET LOCAL ROLE app_owner;

-- 参照するスキーマの順番をトランザクション内で固定します。
SET LOCAL search_path = pg_catalog, app;

-- スキーマ名を明示してテーブルを作成します。
CREATE TABLE app.users (
    user_id bigint
        GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    email text
        NOT NULL
        UNIQUE,
    display_name text
        NOT NULL,
    created_at timestamptz
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz
        NOT NULL
        DEFAULT CURRENT_TIMESTAMP
);

COMMIT;

SET LOCAL ROLEとSET LOCALはトランザクション終了時に戻ります。マイグレーション処理の外へ所有者Roleの状態を残しにくい点が利点です。

Step 12: 所有者を確認する

作成したテーブルがapp_owner所有になっていることを確認します。

-- appスキーマ内のテーブル所有者を確認します。
SELECT
    schemaname,
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname = 'app'
ORDER BY tablename;

想定結果は次のとおりです。

 schemaname | tablename | tableowner
------------+-----------+------------
 app        | users     | app_owner

app_migratorが所有者になっていた場合は、マイグレーション中のSET ROLEが抜けています。後から所有者を直すだけでなく、マイグレーション手順とデフォルト権限の適用主体を見直します。

構築後チェックリスト

  • app_owner、app_rw、app_roはNOLOGINになっている
  • app_userはapp_rwへ所属している
  • app_readonlyはapp_roへ所属している
  • app_migratorは通常時に所有者権限を自動継承しない
  • 管理ユーザーがSET ROLE app_ownerできる
  • app_dbの所有者がapp_ownerになっている
  • appスキーマのCREATEをアプリRoleへ付けていない
  • 既存テーブルと将来作るテーブルの両方へ権限を設定した
  • usersテーブルの所有者がapp_ownerになっている
  • 実パスワードをSQLやGitへ残していない

参考・確認先

この記事は2026年9月3日に、次の公式資料を再確認して作成しました。

関連記事

まとめ

RDS for PostgreSQLの権限設定では、Role作成よりも、誰を所有者にし、誰が将来のオブジェクトを作るかが重要です。

app_ownerへ所有権とデフォルト権限を集め、app_migratorは必要なトランザクションだけRoleを切り替えます。アプリと参照利用者には、ログインしない権限Roleを通して必要な操作だけを許可します。

次の記事では、app_userとapp_readonlyで接続し、成功すべきSQLと失敗すべきSQLの両方を実行して権限境界を確認します。

おわりに

同じような構成を検討するときは、公式ドキュメントを確認しつつ、まず小さな検証用データベースで権限テストを行ってから本番へ適用するのが安全です。

Wealthy Designでは、Webシステム開発、クラウド活用、データベース設計と運用改善に取り組んでいます。会社の取り組みは、会社サイトにまとめています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?