最小権限のロール階層を構築(GRANT + ALTER DEFAULT PRIVILEGES)

コマンド

-- Group roles (NOLOGIN): object owner, read-only, read-write
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Restrict PUBLIC's default privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Existing objects
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- Objects app_owner will create IN THE FUTURE
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Login users get privileges only through group membership
CREATE ROLE web_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readwrite;
CREATE ROLE report_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readonly;

-- Configured default privileges (psql: \ddp)
SELECT pg_get_userbyid(d.defaclrole) AS owner_role,
       n.nspname AS schema_name,
       d.defaclobjtype AS object_type,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

概要

所有者、読み取り専用、読み取り/書き込みのグループロールの階層を構築する。既存および将来の表/シーケンスに適切な権限を付与し、アプリケーションユーザーにはグループメンバーシップを通じてのみ権限を与える。

使用する場面

新しいアプリケーションスキーマの設定時、スーパーユーザーや表の所有者として接続しているアプリケーションの修正、監査前の権限の簡素化に。

注意

ALTER DEFAULT PRIVILEGESは、FOR ROLEで指定したロールが後で作成するオブジェクトにのみ適用される。マイグレーションツールが別のユーザーで表を作成する場合、権限は適用されないため、SET ROLE app_ownerでDDLを実行する。GRANT ... ON ALL TABLESはその時点で存在するオブジェクトのみを対象とする。PG15以降で作成したデータベースでは、publicスキーマのCREATEはすでにPUBLICに付与されていない。古いバージョンからアップグレードしたデータベースではREVOKEが必要。REVOKE ALL ON DATABASE ... FROM PUBLICはCONNECTとTEMPも削除するため、それらを必要とする他のロールには個別に付与する。パスワードをコマンドラインに書く代わりにpsqlの\passwordを使用する(ログや履歴に残らない)。