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権限テスト GRANT漏れと運用設定を確認する

0
Posted at

RDS for PostgreSQL権限テスト GRANT漏れと運用設定を確認するのアイキャッチ

はじめに

PostgreSQLの権限設定は、GRANTがエラーなく終わっただけでは完成ではありません。

アプリ接続Roleで読み書きできること、DDLは拒否されること、参照専用Roleでは更新できないこと、後から作ったテーブルにも権限が付くことを実際に確認する必要があります。

この記事では、RDS for PostgreSQLの初期構築後に行う権限テスト、確認SQL、タイムアウト、関数権限、DBパラメータグループ、よくある失敗を整理します。

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

  1. RDS for PostgreSQL初期設計 マスターユーザーを避けRoleを用途別に分ける
  2. RDS for PostgreSQL権限設定 app_owner・app_user・app_readonlyを作る
  3. 本記事: 権限テストと運用設定

先に結論

  • 成功すべきSQLと失敗すべきSQLをセットで試す
  • app_userはデータを読み書きできるがDDLはできない状態にする
  • app_readonlyは参照できるが更新できない状態にする
  • テーブルだけでなくスキーマ、シーケンス、デフォルト権限も確認する
  • session_userとcurrent_userを確認し、所有者RoleでDDLを実行できているかを見る
  • タイムアウトはアプリやバッチの実行特性に合わせる
  • DBパラメータグループの変更は、反映方法と再起動要否を確認する

前提

  • RDS for PostgreSQL 15以降を想定する
  • app_dbデータベースとappスキーマを作成済み
  • app.usersテーブルを作成済み
  • app_user、app_readonly、app_migratorのパスワードを安全な方法で設定済み
  • TLS証明書を検証してRDSへ接続できる

この記事のSQLとpsqlコマンドは、PostgreSQLとAWSの公式資料に基づいて静的に確認した構成例です。接続可能なRDSでは未実行のため、対象バージョンと構成に合わせ、検証用データベースで結果を確認してから本番へ適用してください。

本番データを使った権限テストは避け、検証用データまたは専用テーブルで行います。失敗を確認するSQLも含むため、対象データベースと接続Roleを実行前に確認してください。

テストする権限境界

Role SELECT INSERT / UPDATE / DELETE CREATE / ALTER
app_migrator + SET ROLE app_owner 成功 成功 成功
app_user 成功 成功 失敗
app_readonly 成功 失敗 失敗

app_userで読み書きを確認する

app_userでapp_dbへ接続します。

# エンドポイントと証明書パスを実環境へ置き換えます。
psql "host=<RDSエンドポイント> \
port=5432 \
dbname=app_db \
user=app_user \
sslmode=verify-full \
sslrootcert=/path/to/global-bundle.pem"

成功する操作

読み書きできることを確認します。

psqlで実行
-- 再実行時に重複しにくいテスト用メールアドレスを作ります。
SELECT
    'permission-test-' || gen_random_uuid() || '@example.invalid' AS test_email
\gset

BEGIN;

-- 権限テスト用の1行を追加します。
INSERT INTO app.users (
    email,
    display_name
)
VALUES (
    :'test_email',
    '権限テスト'
);

-- 追加した行を参照します。
SELECT
    user_id,
    email,
    display_name,
    created_at
FROM app.users
WHERE email = :'test_email';

-- 更新権限を確認します。
UPDATE app.users
SET display_name = '権限テスト更新済み'
WHERE email = :'test_email';

-- 削除権限を確認します。
DELETE FROM app.users
WHERE email = :'test_email';

-- 権限テストの変更を残しません。
ROLLBACK;

gen_random_uuid()はPostgreSQL 13以降でコア関数として利用できます。ROLLBACKしてもIdentityやSequenceで払い出した値は戻らないため、欠番が問題になる環境では専用テーブルを使います。

失敗するのが正常な操作

app_userにはスキーマのCREATEやテーブルの所有権を付けていません。次は失敗するのが正常です。

各ブロックを個別に実行
BEGIN;

-- app_userにはCREATE ON SCHEMAを付けていないため失敗します。
CREATE TABLE app.should_fail (
    id bigint
);

-- CREATEが予想外に成功してもテーブルを残しません。
ROLLBACK;

BEGIN;

-- app_userはusersテーブル所有者ではないため失敗します。
ALTER TABLE app.users
ADD COLUMN permission_test_should_fail boolean;

-- ALTERが予想外に成功しても列を残しません。
ROLLBACK;

想定されるエラーは、permission denied for schema appやmust be owner of table usersです。エラー文はPostgreSQLのバージョンや実行内容で異なります。権限エラー後のトランザクションは中断状態になるため、各ブロックを個別に実行し、最後にROLLBACKします。

ALTER TABLE app.usersが成功した場合は、app_userが所有者になっているか、所有者Roleを継承している可能性があります。この例では直後のROLLBACKで列追加を取り消しますが、テストを続けず、Role所属とテーブル所有者を確認してください。

app_readonlyで参照専用を確認する

app_readonlyで接続します。

# 参照専用Roleで同じデータベースへ接続します。
psql "host=<RDSエンドポイント> \
port=5432 \
dbname=app_db \
user=app_readonly \
sslmode=verify-full \
sslrootcert=/path/to/global-bundle.pem"

SELECTは成功することを確認します。

-- 参照専用Roleでデータを取得します。
SELECT
    user_id,
    email,
    display_name
FROM app.users
ORDER BY user_id
LIMIT 10;

更新系SQLは失敗するのが正常です。

各ブロックを個別に実行
BEGIN;

-- app_readonlyにはINSERT権限がないため失敗します。
INSERT INTO app.users (
    email,
    display_name
)
VALUES (
    'readonly-test-' || gen_random_uuid() || '@example.invalid',
    '参照専用テスト'
);

ROLLBACK;

BEGIN;

-- app_readonlyにはUPDATE権限がないため失敗します。
UPDATE app.users
SET display_name = '更新できてはいけない'
WHERE false;

ROLLBACK;

BEGIN;

-- app_readonlyにはDELETE権限がないため失敗します。
DELETE FROM app.users
WHERE false;

ROLLBACK;

権限エラー後はトランザクションが中断状態になるため、各ブロックを個別に実行してROLLBACKします。WHERE falseでもPostgreSQLはテーブル権限を検査します。予想外に権限が付いていても既存行は更新・削除されません。

TLS接続を確認する

sslmode=verify-fullで接続した各Roleから、現在のセッションがTLSを使用していることを確認します。

SELECT
    ssl,
    version,
    cipher,
    bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

1行が返り、sslがtrueであることを確認します。pg_stat_sslで分かるのはサーバーとの接続がTLSかどうかです。ホスト名と認証局まで検証したことは、クライアントの接続設定がsslmode=verify-fullかつ適切なsslrootcertであることと併せて確認します。

Roleの属性を確認する

psqlでは次を実行します。

-- RoleのLOGIN、INHERIT、CREATEDBなどを表示します。
\du+

SQLで確認する場合は、管理用Roleで次を実行します。

-- アプリ関連Roleの属性を一覧にします。
SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolinherit,
    rolreplication,
    rolbypassrls
FROM pg_roles
WHERE rolname IN (
    'app_owner',
    'app_migrator',
    'app_rw',
    'app_user',
    'app_ro',
    'app_readonly'
)
ORDER BY rolname;

期待する主な状態は次のとおりです。

Role rolcanlogin rolinherit
app_owner false 任意
app_migrator true false
app_rw false 任意
app_user true true
app_ro false 任意
app_readonly true true

Roleの所属関係を確認する

次のSQLは、PostgreSQLのバージョンに依存しにくい基本的な所属関係を表示します。

-- ログインRoleに付与されたグループRoleを確認します。
SELECT
    member_role.rolname AS member_role,
    granted_role.rolname AS granted_role
FROM pg_auth_members AS membership
JOIN pg_roles AS granted_role
    ON granted_role.oid = membership.roleid
JOIN pg_roles AS member_role
    ON member_role.oid = membership.member
WHERE member_role.rolname IN (
    'app_migrator',
    'app_user',
    'app_readonly'
)
ORDER BY member_role.rolname, granted_role.rolname;

想定結果です。

 member_role  | granted_role
--------------+--------------
 app_migrator | app_owner
 app_readonly | app_ro
 app_user     | app_rw

PostgreSQL 16以降では、pg_auth_membersのadmin_option、inherit_option、set_optionも確認できます。psql 16以降の\drg、または次のSQLで詳細を確認します。

PostgreSQL 16以降
SELECT
    member_role.rolname AS member_role,
    granted_role.rolname AS granted_role,
    membership.admin_option,
    membership.inherit_option,
    membership.set_option
FROM pg_auth_members AS membership
JOIN pg_roles AS granted_role
    ON granted_role.oid = membership.roleid
JOIN pg_roles AS member_role
    ON member_role.oid = membership.member
WHERE member_role.rolname IN (
    'app_migrator',
    'app_user',
    'app_readonly'
)
ORDER BY member_role.rolname, granted_role.rolname;

連載2本目の構成では、app_migratorからapp_ownerへの所属はinherit_option = false、set_option = true、app_userからapp_rwとapp_readonlyからapp_roへの所属はinherit_option = true、set_option = trueになることを確認します。管理権限を再委譲させない構成では、いずれもadmin_option = falseです。

session_userとcurrent_userを確認する

app_migratorで接続し、SET ROLE app_ownerした場合は、ログインしたRoleと現在の権限Roleが異なります。

-- ログイン元と現在の権限チェック主体を確認します。
SELECT
    session_user,
    current_user,
    current_database(),
    current_schema();
項目 意味
session_user 実際にログインしたRole
current_user 現在、権限チェックに使われるRole

マイグレーション中は、次のようになることを確認します。

session_user = app_migrator
current_user = app_owner

テーブルとスキーマの権限を確認する

psqlでは次が便利です。

-- appスキーマ内のテーブル権限を表示します。
\dp app.*

-- スキーマの所有者と権限を表示します。
\dn+

-- デフォルト権限を表示します。
\ddp

SQLでテーブル権限を確認します。

-- appスキーマの明示的なテーブル権限を一覧にします。
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY table_name, grantee, privilege_type;

information_schema.role_table_grantsに表示されるのは、付与者または付与先が現在有効なRoleである権限だけです。管理用Roleの接続状態によっては、存在する権限が一覧に出ないことがあります。対象Roleの継承を含む実効権限は、次のSQLでも確認します。

WITH target_roles(role_name) AS (
    VALUES
        ('app_user'::name),
        ('app_readonly'::name)
)
SELECT
    role_name,
    has_schema_privilege(role_name, 'app', 'USAGE') AS schema_usage,
    has_schema_privilege(role_name, 'app', 'CREATE') AS schema_create,
    has_table_privilege(role_name, 'app.users', 'SELECT') AS can_select,
    has_table_privilege(role_name, 'app.users', 'INSERT') AS can_insert,
    has_table_privilege(role_name, 'app.users', 'UPDATE') AS can_update,
    has_table_privilege(role_name, 'app.users', 'DELETE') AS can_delete
FROM target_roles
ORDER BY role_name;

期待値は次のとおりです。

Role schema_usage schema_create SELECT INSERT UPDATE DELETE
app_user true false true true true true
app_readonly true false true false false false

後から作ったテーブルにも権限が付くか

ALTER DEFAULT PRIVILEGESの確認では、app_migratorからapp_ownerへ切り替え、検証用テーブルを作ります。

BEGIN;

-- デフォルト権限を設定したapp_ownerとして作成します。
SET LOCAL ROLE app_owner;

-- 後から作ったテーブルへの自動GRANTを確認します。
CREATE TABLE app.default_privilege_test (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    note text NOT NULL
);

COMMIT;

app_userでINSERTとSELECT、app_readonlyでSELECTが成功すれば、テーブルのデフォルト権限が機能しています。

確認後はapp_migratorからapp_ownerへ切り替えて削除します。

BEGIN;

-- 所有者Roleで検証用テーブルだけを削除します。
SET LOCAL ROLE app_owner;
DROP TABLE app.default_privilege_test;

COMMIT;

よくあるGRANT漏れ

シーケンス権限がない

シーケンスを利用するテーブルで、次のエラーが発生することがあります。

permission denied for sequence users_user_id_seq

既存シーケンスと将来作るシーケンスの両方を確認します。

SET ROLE app_owner;

-- 既存シーケンスへ利用権限を付与します。
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA app
TO app_rw;

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

RESET ROLE;

後から作ったテーブルだけ参照できない

GRANT ON ALL TABLESは既存テーブルだけに作用します。ALTER DEFAULT PRIVILEGESがないか、テーブルを作ったRoleがapp_ownerではない可能性があります。

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

app_migratorが所有者になっている場合は、マイグレーションのSET LOCAL ROLE app_ownerを確認します。

Roleごとのタイムアウトを設定する

タイムアウトは、無限に待つクエリや、トランザクションを開いたまま放置する接続を減らすために使えます。ただし、適切な値はアプリ、バッチ、集計処理で異なります。

次の設定は、対象Roleを変更できる管理用Roleで実行します。RDSのマスターユーザーでも、PostgreSQLのバージョンやRoleの作成・委譲状態によって変更権限が異なるため、実行主体を確認してください。

-- 通常APIのSQLが30秒を超えたら停止する例です。
ALTER ROLE app_user
IN DATABASE app_db
SET statement_timeout = '30s';

-- トランザクション内で60秒操作がない接続を停止する例です。
ALTER ROLE app_user
IN DATABASE app_db
SET idle_in_transaction_session_timeout = '60s';

-- ロック取得を5秒以上待たない例です。
ALTER ROLE app_user
IN DATABASE app_db
SET lock_timeout = '5s';

-- 参照専用クエリを60秒で停止する例です。
ALTER ROLE app_readonly
IN DATABASE app_db
SET statement_timeout = '60s';

-- 参照専用Roleの既定トランザクションを読み取り専用にします。
ALTER ROLE app_readonly
IN DATABASE app_db
SET default_transaction_read_only = 'on';

default_transaction_read_onlyは補助的な防御です。参照専用Roleへ更新権限を付けないことが基本です。

Role・データベース単位の既定値は、対象Roleが次にログインしたときに反映されます。現在のセッションや、別RoleでログインしてSET ROLEしただけのセッションには適用されません。app_userとapp_readonlyで新しく接続し、次を確認します。

SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;
SHOW lock_timeout;
SHOW default_transaction_read_only;

この例では、app_userのstatement_timeoutは30s、idle_in_transaction_session_timeoutは60s、lock_timeoutは5sです。app_readonlyのstatement_timeoutは60s、default_transaction_read_onlyはonになります。Roleごとに設定していない項目は、データベースやシステムの既定値を継承します。

バッチ処理や大規模集計にstatement_timeout = '30s'をそのまま使うと、正常な処理まで停止する可能性があります。実行時間の計測、SLO、再実行方法を確認して値を決めます。

関数を使う場合のEXECUTE権限

PostgreSQLの関数は、既定でPUBLICへEXECUTEが付く場合があります。独自関数を使う場合は、公開範囲を明示します。

SET ROLE app_owner;

-- 今後作る関数を全Roleが実行できないようにします。
ALTER DEFAULT PRIVILEGES
REVOKE EXECUTE
ON FUNCTIONS
FROM PUBLIC;

-- appスキーマで今後作る関数をapp_rwだけ実行可能にします。
ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT EXECUTE
ON FUNCTIONS
TO app_rw;

-- appスキーマに既存の関数がある場合、PUBLICの権限を取り消します。
REVOKE EXECUTE
ON ALL FUNCTIONS IN SCHEMA app
FROM PUBLIC;

-- 必要な既存関数をapp_rwが実行できるようにします。
GRANT EXECUTE
ON ALL FUNCTIONS IN SCHEMA app
TO app_rw;

RESET ROLE;

参照専用Roleへ関数実行権限を付ける場合は、その関数がデータ更新や権限昇格を行わないことを確認します。特にSECURITY DEFINER関数は、所有者権限で動作するため個別レビューが必要です。

RDSパラメータグループを確認する

次の設定は、SQLだけで完結させず、RDSのDBパラメータグループで管理します。

rds.force_ssl
password_encryption
rds.accepted_password_auth_method
log_connections
log_disconnections
log_min_duration_statement
shared_preload_libraries
max_connections

現在のPostgreSQL設定をSQLで確認できます。

-- 運用に関係する主要パラメータを確認します。
SELECT
    name,
    setting,
    unit,
    context,
    pending_restart
FROM pg_settings
WHERE name IN (
    'password_encryption',
    'log_connections',
    'log_disconnections',
    'log_min_duration_statement',
    'max_connections',
    'shared_preload_libraries'
)
ORDER BY name;

RDSのデフォルトDBパラメータグループは直接変更できません。変更する場合は、利用中のPostgreSQLファミリーに合うカスタムDBパラメータグループを作成し、RDSへ関連付けます。

パラメータには、即時反映できる動的なものと、再起動が必要な静的なものがあります。コンソールやAWS CLIのApply type、RDSのpending-reboot状態、pg_settings.pending_restartを確認します。

SCRAMへ変更するときの注意

RDS for PostgreSQL 14以降では、password_encryptionの既定値はscram-sha-256です。ただし、この設定は新しく設定するパスワードの保存方式に作用し、既存のMD5パスワードを自動変換しません。

rds.accepted_password_auth_methodをSCRAMのみに変更する前に、次を確認します。

  • 利用中のJDBC、言語ドライバー、接続ツールがSCRAMに対応している
  • 既存RoleのパスワードをSCRAMで再設定した
  • RDS Proxyを使っている場合の影響を確認した
  • 検証環境で全接続元を試した
  • ロールバック手順を決めた

未対応のRoleが残ったままSCRAMのみにすると、そのRoleは接続できなくなります。

初心者がやりやすい失敗

失敗 起きること 確認する場所
アプリからマスターユーザーで接続する RoleやDB管理まで影響が広がる アプリの接続シークレット
app_userをテーブル所有者にする DDLを拒否できない pg_tables.tableowner
GRANT ON ALL TABLESだけ実行する 後から作ったテーブルへ権限が付かない \ddp、作成Role
シーケンス権限を忘れる 採番時にエラーになる \dp app.*
app_migratorのままDDLする 所有者とデフォルト権限がずれる session_user、current_user
参照専用RoleへグループRoleを誤付与する 更新できてしまう pg_auth_members
タイムアウトを一律に短くする 正常なバッチや集計が失敗する 実行時間、監視ログ
パラメータ変更の再起動要否を見ない 変更が反映されない、想定外再起動 RDSのParameter group status

最終チェックリスト

  • app_userでSELECT・INSERT・UPDATE・DELETEが成功した
  • app_userでCREATE・ALTERが失敗し、各テストをROLLBACKした
  • app_readonlyでSELECTが成功した
  • app_readonlyで更新系SQLが失敗した
  • 後から作ったテーブルにも権限が付いた
  • テーブル所有者がapp_ownerになっている
  • Roleの所属関係が設計どおりになっている
  • TLS接続をpg_stat_sslで確認した
  • タイムアウト値を処理特性に合わせて決めた
  • DBパラメータグループの反映方法と再起動要否を確認した
  • テストデータをROLLBACKし、検証用テーブルを片付けた

参考・確認先

この記事は2026年9月4日に、想定対象であるPostgreSQL 15と、Roleメンバーシップの仕様が変わったPostgreSQL 16の公式資料を確認して更新しました。

関連記事

まとめ

権限設定は、付与したSQLを見るだけでなく、各Roleで実際に接続して境界を確認します。成功する操作だけでなく、拒否されるべき操作が失敗することも重要なテストです。

Role、所有者、既存権限、デフォルト権限、タイムアウト、RDSパラメータグループまで確認すると、アプリ接続と管理作業を分離した状態を維持しやすくなります。

おわりに

RDS for PostgreSQLの権限は、初期構築時だけでなく、新しいテーブル、関数、接続元を追加したときにも再確認します。
Wealthy Designでは、Webシステム開発、クラウド活用、AIを使った業務改善に取り組んでいます。

会社の取り組みは、会社サイトにまとめています。

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?