Aurora PostgreSQL 互換 DB クラスターでオブジェクト特権を管理する方法を教えてください。

解決策

Aurora PostgreSQL 互換 DB クラスターでオブジェクト特権を管理するには、データベースオブジェクトに対してさまざまなアクションを実行する特定のアクセス許可をユーザーまたはロールに付与します。

データベースのアクセス許可の種類に関する詳細については、PostgreSQL ウェブサイトの「Privileges」(特権) を参照してください。

注: AWS では "permission" (アクセス許可) という用語を "privilege" (特権) として使用しています。 

ユーザーを作成し、そのユーザーにオブジェクトへのアクセス許可を付与する

次の手順を実行します。

1. psql などの PostgreSQL クライアントを使用して Aurora PostgreSQL 互換クラスターに接続します。
2. 新しいユーザーを作成するには、次のコマンドを実行します。

   CREATE USER username WITH PASSWORD 'password';

   注: username および password を、それぞれ実際のユーザー名とパスワードに置き換えてください。
3. オブジェクトに対してアクションを実行するアクセス許可をユーザーまたはロールに付与するには、次のコマンドを実行します。

   GRANT privilege_type ON object_name TO user_or_role;

   注: privilege_type を、ユーザーに実行させたいデータベースアクションに置き換えてください。object_name を、ユーザーに操作させたいデータベースオブジェクトに置き換えてください。user_or_role を、オブジェクトへのアクセス許可を割り当てたいユーザーまたはロールに置き換えてください。

例えば、次のコマンドを実行して、user_or_role に複数のオブジェクトへのアクセス許可を付与できます。

GRANT SELECT, INSERT, UPDATE ON object_name TO user_or_role;

詳細については、PostgreSQL ウェブサイトの「GRANT」(付与) を参照してください。

ユーザーとロールのアクセス許可を取り消す

ユーザーまたはロールからアクセス許可を削除するには、次のコマンドを実行します。

REVOKE privilege_type ON object_name FROM user_or_role;

注: privilege_type を、削除したいデータベースアクションに置き換えてください。object_name をデータベースオブジェクトに置き換えてください。user_or_role を、アクセス許可を削除したいユーザーまたはロールに置き換えてください。

詳細については、PostgreSQL ウェブサイトの「REVOKE」(取り消し) を参照してください。

必要最小限のアクセス許可をユーザーに付与する

ユーザーまたはロールには必要なアクセス許可のみを付与するのがベストプラクティスです。

テーブルにあるアクセス許可を確認するには、次のコマンドを実行します。

\z table_name

注: table_name は、確認したいテーブルの名前に置き換えてください。

または、次のコマンドを実行します。

SELECT * FROM information_schema.table_privileges WHERE table_name = 'your_table';

ユーザーグループのロールを作成および割り当てる

個別のユーザーではなく、ユーザーのグループにロールを作成し、ロールにアクセス許可を割り当てるのがベストプラクティスです。詳細については、PostgreSQL ウェブサイトの「Database roles for server administration」(サーバー管理のためのデータベースロール) および「Database roles」(データベースロール) を参照してください。

ロールを作成し、そのロールにデータベース内のすべてのテーブルに対するアクセス許可を付与するには、次のコマンドを実行します。

CREATE ROLE analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO analyst_role;

注: schema_name をスキーマの名前に、analyst_role をロールの名前に置き換えてください。

ユーザーとロールにスキーマレベルのアクセス許可を付与する

ユーザーまたはロールにスキーマレベルのアクセス許可を付与するには、次のコマンドを実行します。

GRANT USAGE ON SCHEMA schema_name TO user_or_role;

注: schema_name をスキーマの名前に、user_or_role をユーザーまたはロールの名前に置き換えます。

将来のオブジェクトに対するデフォルトのアクセス許可を設定する

新しいテーブルに対するデフォルトのアクセス許可を readonly_role に設定するには、次のコマンドを実行します。

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO readonly_role;

注: schema_name をスキーマの名前に置き換えてください。

ユーザー間でオブジェクトの所有権を移行する

セキュリティ上の問題を防ぐため、オブジェクトの所有権は必ず管理してください。

所有権を新しい所有者に移行するには、次のコマンドを実行します。

ALTER TABLE table_name OWNER TO new_owner;

注: table_name をテーブルの名前に、new_owner を新しい所有者の名前に置き換えてください。

アクティビティストリームでアクセス許可の変更をモニタリングする

データベースアクティビティをモニタリングするには、DB クラスターのアクティビティストリームを起動し、databaseActivityEventList JSON 配列を使用します。詳細については、「databaseActivityEvents JSON オブジェクト」を参照してください。

アクセス許可の定期的なレビューをスケジュールする

アクセス許可の定期的なレビューをスケジュールし、現在の要件に基づいて権限を更新します。

例えば、次のクエリを実行すると、テーブルとビューに付与したすべてのアクセス許可を表示できます。

-- To view table and view privileges
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM
    information_schema.role_table_grants
ORDER BY
    grantee, table_schema, table_name, privilege_type;
-- List schema privileges
SELECT nspname, rolname, nspacl FROM pg_namespace n JOIN pg_roles r ON r.oid = n.nspowner;
-- Show all privileges for a specific role
SELECT table_catalog, table_schema, table_name, privilege_typeFROM information_schema.table_privilegesWHERE grantee = 'role_name';
-- Show role-specific schema privileges
SELECT n.nspname as schema_name,
       r.rolname as role_name,
       has_schema_privilege(r.oid, n.oid, 'CREATE') as has_create,
       has_schema_privilege(r.oid, n.oid, 'USAGE') as has_usageFROM pg_namespace nCROSS JOIN pg_roles rWHERE n.nspname NOT LIKE 'pg_%'
AND n.nspname != 'information_schema';