ロールと権限
GRANTとREVOKEでアクセス権を管理します。
「ロールと権限」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。
ロールと権限とは
SQLでは、ロールは割り当て可能な権限の名前付きグループです。権限は、ユーザーまたはロールがテーブル、ビュー、関数などのデータベースオブジェクトに対して実行できる操作を制御します。
各ユーザーに個別に権限を付与するのではなく、必要な権限を持つロールを作成し、そのロールを複数のユーザーに一度に割り当てます。これにより、大規模な環境でもアクセス制御をはるかに簡単に管理できます。
ロールの作成
PostgreSQLで新しいロールを定義するには、CREATE ROLEを使用します。設定によって、ロールは単一のユーザーまたはユーザーグループを表すことができます。
ロールはデフォルトでは権限なしで作成されるため、オブジェクトへのアクセス権を明示的に付与する必要があります。
CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;テーブル権限の付与
GRANT文を使うと、ロールまたはユーザーにデータベースオブジェクト上で特定の操作を実行する権限を与えられます。テーブルレベルでよく使われる権限には、SELECT、INSERT、UPDATE、DELETEがあります。
権限を1つだけ付与することも、カンマで区切って複数の権限を一度に付与することもできます。
-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;すべての権限の付与
ロールにテーブルへの完全なアクセス権が必要な場合は、各権限を個別に列挙する代わりにGRANT ALL PRIVILEGESを使用できます。これにより、指定したオブジェクトに適用可能なすべての権限が付与されます。
ALL PRIVILEGESの使用には注意してください。オブジェクトを完全に管理する必要が実際にあるロールにのみ付与してください。
-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;
-- Or the shorthand form
GRANT ALL ON orders TO db_admin;権限の取り消し
REVOKE文は、ロールまたはユーザーから以前に付与した権限を削除します。要件が変わった場合や、ロールに特定の権限が不要になった場合に、アクセスを制限するために使用します。
REVOKEを実行すると、対象のロールは指定されたオブジェクトに対する該当の権限を直ちに失います。
-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;
-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;ユーザーへのロールの割り当て
PostgreSQLでは、ユーザーもロールの一種です。LOGIN属性を持つロールはデータベースに接続できます。グループロールをログインロールに割り当てるには、GRANT role TO userを使用します。
割り当てると、ユーザーはそのロールに属するすべての権限を継承します。そのため、多数のユーザーの権限を一度に簡単に管理できます。
-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';
-- Assign the readonly role to alice
GRANT readonly_user TO alice;
-- Alice can now SELECT on tables granted to readonly_userユーザーからのロールの取り消し
ユーザーからロールの割り当てを削除するには、REVOKE role FROM userを使用します。これにより、ユーザーはそのロールから継承していた権限を失います。
従業員の担当業務が変わった場合や組織を離れた場合に便利です。ロール自体の権限定義を変更せずに、そのユーザーのロールだけを取り消せます。
-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;
-- Alice no longer has SELECT on tables via that roleスキーマレベルの権限
ロールがスキーマ内のオブジェクトにアクセスするには、まずそのスキーマに対するUSAGE権限が必要です。これがないと、特定のテーブルに対するSELECT権限を持つロールでも、スキーマパスを解決できないためデータを読み取れません。
オブジェクトレベルの権限と併せて、必ずスキーマに対するUSAGEを付与してください。
-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;
-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;将来のオブジェクトに対するデフォルト権限
スキーマ内に新しいテーブルを作成しても、既存のロールが自動的にアクセス権を得ることはありません。ALTER DEFAULT PRIVILEGESを使用すると、特定のロールが今後作成するオブジェクトに別のロールが自動的にアクセスできるように設定できます。
これにより、新しく作成したテーブルが、誰かが手動でGRANTを実行するまでアプリケーションロールから見えなくなるという、よくある問題を防げます。
-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;WITH GRANT OPTION
デフォルトでは、権限を受け取ったロールは、その権限を他のロールに再付与できません。WITH GRANT OPTIONを追加すると、受け取ったロールもその権限を他のロールに付与できるようになります。
これは慎重に使用してください。ロールがアクセス制御における信頼された委任ポイントになるため、使いすぎると監査が複雑になる可能性があります。
-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;
-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;付与された権限の確認
PostgreSQLは、権限に関する情報をシステムカタログビューに保存しています。information_schema.role_table_grantsをクエリすると、どのロールにどのテーブルへのどの権限が付与されているかを確認できます。
psqlのバックスラッシュコマンド\dp tablenameでも、テーブルのアクセス権限一覧を簡潔な形式で表示できます。
-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;簡単な確認
SQLにおけるロールと権限について、理解度を確認しましょう。
まとめ：ロールと権限
このレッスンでは、SQLがロールと権限を通じてアクセス制御を管理する方法を学びました。
- CREATE ROLEは、新しいロールまたはユーザーアカウントを定義します
- GRANTは、オブジェクトに対する権限（SELECT、INSERT、UPDATE、DELETE、ALL）をロールに付与します
- REVOKEは、それらの権限を削除します
- GRANT role TO userは、グループロールをログインユーザーに割り当て、その権限を継承できるようにします
- ロールがスキーマ内のオブジェクトにアクセスするには、スキーマに対するUSAGEが必要です
- ALTER DEFAULT PRIVILEGESは、今後作成されるオブジェクトに自動的にアクセスできるようにします
- WITH GRANT OPTIONを使うと、受け取ったロールがその権限をさらに委任できます
権限を最小限に抑え、ロールベースで管理することは、安全なデータベース設計の基本原則です。必要な権限だけを付与し、不要になったら速やかに取り消してください。
よくある質問
「ロールと権限」レッスンは無料ですか？
はい。「ロールと権限」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。
「ロールと権限」で何を学びますか？
GRANTとREVOKEでアクセス権を管理します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。
「ロールと権限」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Academyレッスンでコードを書いて実行できますか？
はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。