PostgreSQLにおけるユーザー、ロール、権限管理のための必須コマンド

PostgreSQLのユーザー、ロール、権限は、誰が接続できるか、何を読み取れるか、何を変更できるかを決定します。これらの権限が推測で増えていくと、データベースの監査が難しくなり、過剰に露出しやすくなります。

PostgreSQLは、ログインユーザーとグループの両方に「ロール」という1つの概念を使用します。LOGIN属性を持つロールは接続できます。LOGINを持たないロールは、共有権限を持つグループとしてよく使用されます。

ロールの作成と変更

パスワード付きのアプリケーションログインロールを作成する:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

共有読み取り専用アクセス用のグループロールを作成する:

CREATE ROLE reporting_readonly NOLOGIN;

一般的なロール属性は次のとおりです:

属性	許可される内容
LOGIN	データベースサーバーに接続する。
CREATEDB	データベースを作成する。
CREATEROLE	多くのロールを作成、変更、削除する（スーパーユーザーロールに関する重要な制限あり）。
REPLICATION	レプリケーション接続を使用する。
SUPERUSER	通常の権限チェックをバイパスする。厳密に管理された管理者ロールにのみ使用する。
INHERIT	メンバーであるロールの権限を使用する。これがデフォルトです。

ALTER ROLEを使用してパスワードを変更したり、ログインを無効にしたりする:

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

ロールを削除する前に、そのロールが所有するものや付与された権限を処理する:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNEDは所有オブジェクトを転送します。DROP OWNEDはロールに付与された権限を削除し、現在のデータベース内でそのロールがまだ所有しているオブジェクトを削除する可能性があるため、実行する前に注意深く確認してください。

グループロールを使用してアクセスを整理する

グループロールに権限を付与し、その後、ユーザーやアプリケーションロールをそれらのグループに追加します。

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTIONを使用すると、メンバーは他のユーザーに対してそのロールのメンバーシップを付与および取り消すことができます。これ自体はメンバーをスーパーユーザーにしたり、データベースに対する無制限の権限を与えたりするものではありません。

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

このパターンにより、アクセス監査が容易になります。50人のユーザーに対してテーブル権限を確認する代わりに、グループロールを確認できます。

オブジェクト権限の付与

GRANTを使用して、特定のオブジェクトに対する特定のアクションを許可します。

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

一般的なテーブル権限は、SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGERです。

スキーマ権限はテーブル権限とは別です。ロールは通常、スキーマに対するUSAGEと、その中のテーブルに対する権限の両方が必要です:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

シーケンスを使用する挿入の場合は、シーケンスアクセスも付与します:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

関数の場合は、明示的に実行権限を付与します:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

権限の取り消し

REVOKEは直接付与された権限を削除します。

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

取り消し後もユーザーがアクセスできる場合は、グループメンバーシップを確認してください。ユーザーは別のロールを通じて同じ権限を継承している可能性があります。

将来のテーブルに対する権限の設定

GRANT ON ALL TABLESは既存のテーブルにのみ影響します。後で作成されるテーブルについては、それらのテーブルを作成するロールに対してALTER DEFAULT PRIVILEGESを使用します。

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

これはPostgreSQLの権限設定で最もよく見られる欠落手順の1つです。ユーザーは今日のテーブルは読み取れますが、デフォルト権限が設定されていないため、明日のテーブルでは拒否されます。

現在のアクセス権の監査

psqlでは、以下のコマンドが迅速な確認に役立ちます:

\du
\du+ app_user
\dp app.orders
\dn+

カタログビューは繰り返し可能なレポートに適しています:

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

テーブル権限の場合:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

効果的なセキュリティ習慣

アプリケーションには、人間のDBAアカウントではなく専用のロールを割り当てます。アプリケーションに必要な最小限の権限セットを付与します。日常業務ではSUPERUSERや広範なALL PRIVILEGESの付与を避けます。

共有アクセスにはグループロールを使用し、メンバーシップを定期的に確認します。チームを異動する場合、1つのグループメンバーシップを削除する方が、テーブルレベルの権限を探し回るよりも安全です。

実践的な次のステップは、1つの本番スキーマを選び、そのロール、メンバーシップ、テーブル権限、デフォルト権限をリストアップすることです。そこでギャップを修正してから、同じパターンを他のデータベースに拡張してください。