PostgreSQLでロールとロール属性を管理する

はじめに

PostgreSQLは、データベースクラスター内の認証、認可、およびオブジェクト所有権を実装するために様々なメカニズムを使用します。これらの中心となるのは、ロールの概念です。

PostgreSQLのロールは、ユーザーとグループのアイデアを単一の柔軟なエンティティに組み合わせたものです。それらは、ユーザーがデータベースシステム内で採用するペルソナであり、認証システムが接続を許可または拒否するエンティティであり、あらゆる範囲の権限管理ルールの対象となります。

このガイドでは、ロールとは何か、そしてPostgreSQLデータベースクラスター内でそれらを管理する方法について説明します。より具体的には、ロール属性に関連するロール管理について説明します。ロールがより大きな全体像にどのように適合するかについてのより広範な概要については、認証と認可の入門ガイドをご覧ください。特定のデータベースオブジェクトに対するロール権限を変更する方法については、ロール付与に関するガイドをご覧ください。

ロールとは？

PostgreSQLにおいて、ロールとは、特定の機能、権限、「所有」エンティティのセットをグループ化したものです。「ユーザー」と「グループ」を別々の概念として持つ代わりに、PostgreSQLはこれらの両方のアイデアをロールで表現します。ロールは現実世界の個人に対応することもできますし、他のロールがメンバーになれる特定のアクセスを持つグループとして機能することもできます。

ロールは、認証および認可ポリシーが誰に適用されるかを決定するPostgreSQL内のアンカーポイントです。普遍的に適用されないポリシーはすべて、誰を制限し、誰を許可するかを定義するために、アイデンティティの概念を必要とします。PostgreSQLでは、このアイデンティティはロールによって表現されます。

PostgreSQLの認証システムにはいくつかの異なるコンポーネントがあり、それぞれがロールに結び付けられています。データベースクラスターへの最初の接続で使用するためには、ロールはまずLOGIN属性が設定されている必要があります。認証ルール自体は、pg_hba.confというホストベースの構成ファイルで定義されます。各ルールは、個々のロールにスコープ設定できる認証方法を定義します。パスワード認証用に構成されたロールには、システムが提供されたユーザーパスワードを検証できるように、パスワード属性が設定されている必要があります。

認可の観点から見ると、ロールはデータベースクラスターレベルで定義されており、PostgreSQLでは、これはデータベース間で共有されることを意味します。ロールがデータベースにまたがるため、認可システムは各ロールが各データベースエンティティに対して持つアクセスレベルを制御します。ロールは人のグループを表すことができるため、アクセスの設定には非常に柔軟性があります。

ロールは、PostgreSQLにおけるオブジェクト所有権の概念にとっても不可欠です。たとえば、各データベースとテーブルには、所有者として設定されたロールがちょうど1つあります。superuserを除き、所有者ロールは実際のオブジェクトを変更または削除できる唯一のロールです。

要するに、ロールはほとんどの実用的なデータベース操作の中核をなします。その柔軟性により、ユーザー識別子としてもユーザークラスとしても機能します。データベースクラスター内のすべての操作は、ロールの権限と照合され、データベースクラスターへの各接続の成功は、認証されるロールによって決定されます。多くのコア操作においてその重要性があるため、ロール管理をしっかり理解することが重要です。

ロール属性

ロール属性は、ロール自体に付与されるフラグで、データベースクラスターレベルでのコア権限の一部を決定します。これらは、ロールが最初に作成されるときに設定することも、適切な属性（この場合はSUPERUSERまたはCREATEROLE）を持つロールによっていつでも変更することもできます。

ロールに適用できる属性には以下が含まれます。

• LOGIN: ユーザーがこのロールを使用してデータベースクラスターに最初に接続できるようにします。CREATE USERコマンドは自動的にこの属性を追加しますが、CREATE ROLEコマンドは追加しません。
• SUPERUSER: ロールがログイン権限を除くすべての権限チェックをバイパスできるようにします。SUPERUSER属性を持つロールは、他のSUPERUSERロールのみが作成できます。
• CREATEDB: ロールが新しいデータベースを作成できるようにします。
• CREATEROLE: ロールが他のロールを作成、変更、削除できるようにします。この属性は、ロールがロールメンバーシップを割り当てたり変更したりすることも許可します。例外として、CREATEROLE属性を持つロールは、SUPERUSER属性も持っていない限り、SUPERUSERロールを変更することはできません。
• REPLICATION: ロールがストリーミングレプリケーションを開始できるようにします。この属性を持つロールは、LOGIN属性も持っている必要があります。
• PASSWORD: passwordまたはmd5認証メカニズムで使用されるパスワードをロールに割り当てます。この属性は、属性キーワードの直後に引用符で囲まれたパスワードを引数として取ります。
• INHERIT: ロールがメンバーであるロールの権限を継承するかどうかを決定します。INHERITがない場合、メンバーはSET ROLEを使用して他のロールに変更することで、排他的な権限にアクセスする必要があります。この属性は、新しいロールにデフォルトで設定されます。

ロール属性の詳細については、PostgreSQLのロール属性とCREATE ROLEコマンドに関するドキュメントを参照してください。

superuserロールとは？

上記で簡単に触れたように、superuserと呼ばれる特別な権限は、データベースクラスターへの無制限の管理アクセスを許可します。これはLinuxおよびUnixライクなオペレーティングシステムにおけるrootアカウントに似ていますが、データベースレベルでのものです。

各データベースクラスターには、常に少なくとも1つのsuperuser権限を持つロールが存在しなければなりません。最初のsuperuserアカウントは、インストールプロセス中に作成されます。最初のsuperuserアカウントの名前はインストールプロセスによって異なる場合がありますが、ほとんどの場合、このアカウントはpostgresと呼ばれます。

superuser権限を持つアカウントを使用して日常業務を行うことは推奨されません。これは、破壊的なアクションの可能性があり、また広範囲なアクセスを持つアカウントが侵害される可能性を最小限に抑えるためでもあります。代わりに、ほとんどの場合、ユーザーは作業している特定の機能やデータオブジェクト専用のアカウントを使用し、より強力なアクセスが必要な場合にのみsuperuserアカウントを使用すべきです。

既存のロール属性の確認

ロール属性とは何か、そしてどのような種類の権限を許可するのかについて大まかな理解が得られたので、PostgreSQL全体でロールに適用されている属性を見つける方法を学ぶ必要があります。このセクションでは、ロール全般に設定されている属性と、特に現在のロールに設定されている属性を見つけるのに役立ついくつかのコマンドを示します。

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

-- turn on vertical display
\x on
SELECT * FROM pg_roles WHERE rolname !~ '^pg_';
-- turn off vertical display
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | 
rolbypassrls   | t
rolconfig      | 
oid            | 10

SELECT rolname FROM pg_roles WHERE rolsuper;

 rolname
----------
 postgres
(1 row)

SELECT usename,usesuper FROM pg_user;

 usename  | usesuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

SELECT rolname,rolsuper FROM pg_roles WHERE rolname !~ '^pg_';

 rolname  | rolsuper
----------+----------
 postgres | t
 user1    | f
(2 rows)

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

-- First, turn on vertical output
\x on
SELECT * FROM pg_roles WHERE rolename = CURRENT_ROLE;
-- Change back to normal output
\x off

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | t
rolconfig      |
oid            | 10

SHOW is_superuser;

 is_superuser
--------------
 on
(1 row)

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

 Users who can manage roles
----------------------------
 postgres
(1 rows)

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

 Can I manage roles?
---------------------
 Yes
(1 row)

ロールの作成

ロール管理権限があることを確認したら、PostgreSQL内でロールの作成、変更、削除を開始できます。

ロール属性を設定する1つのオプションは、ロールを作成する際にそれらを宣言することです。これにより、ロールの初期条件を設定できますが、後でアクセスレベルを変更したい場合は、いつでも変更できます。基本的な構文に慣れるために、使用するCREATE ROLEコマンドに関する詳細情報を参照してください。

ロールを作成する方法の一つはコマンドラインからです。PostgreSQLには、データベースクラスター内にLOGIN権限を持つロールを作成するcreateuserコマンドが含まれています。

一般的な構文は次のとおりです。

createuser <options> <rolename>

例えば、adminという名前のロールをsuperuser権限で作成し、パスワードを尋ねるには、次のように入力します。

createuser --superuser admin

その後、pg_hba.confファイルに記載されている認証方法に従って、adminアカウントを使用してログインできるようになります。

SQLを使用してロールを作成する場合、一般的な構文は次のようになります。

CREATE ROLE <role>;

属性は、ロール名の後にWITHを使用して指定することで定義できます。

CREATE ROLE <role> WITH <options>;

例えば、パスワードsecretpasswordでログインできるuser1という名前のロールを作成するには、次のように入力します。

CREATE ROLE "user1" WITH LOGIN PASSWORD 'secretpassword';

代わりに、superuser権限を持つロール（このコマンドを正常に実行するには、あなたもsuperuserである必要があります）で、ログインができないロール（ユーザーはこのロールに変更するためにSET ROLEを使用する必要があります）を作成するには、次のように入力できます。

CREATE ROLE "user2" WITH SUPERUSER;

既存のロールの変更

既存のロールの属性を変更するには、代わりにALTER ROLEコマンドを使用できます。ロールの作成と同様に、現在のロールもsuperuserまたはCREATEROLEのいずれかの権限を持っている必要があります。これらの権限を持たないユーザーは、自身のパスワードを変更するためにのみALTER ROLEコマンドを使用できます。

ロールを変更することで、作成後にロールに割り当てられた属性を変更できます。ロール作成セクションで述べたのと同じ属性をALTER ROLE構文で使用できます。1つの違いは、各属性タイプにNOプレフィックスを追加することで否定できることです。たとえば、ロールがデータベースクラスターにログインできるようにするには、LOGIN属性を付与します。その機能を削除するには、NOLOGINを指定してロールを変更します。

ALTER ROLEコマンドは、明示的に指定された属性のみを変更します。言い換えれば、ALTER ROLEコマンドは属性への変更を指定するのであって、新しい属性の完全なセットを指定するわけではありません。

user2ロールがデータベースクラスターにログインできるようにするには、次のように入力します。

ALTER ROLE "user2" WITH LOGIN;

これはログインを有効にしますが、許可される認証方法は依然としてpg_hba.confファイルによって制御されることに注意してください。

もしuser2にログイン、ロール作成、データベース作成を許可したい場合は、それら3つの属性をスペースで区切って指定できます。

ALTER ROLE "user2" WITH LOGIN CREATEROLE CREATEDB;

ロールからsuperuserステータスを取り消すには（このコマンドは別のsuperuserロールを使用してのみ実行できます）、次のように入力します。

ALTER ROLE "user2" WITH NOSUPERUSER;

ロールのパスワードを変更するには、以下を入力します（すべてのロールは、CREATEROLEやsuperuser権限に関係なく、自身のロールに対してこのコマンドを実行できるはずです）。

ALTER ROLE <role> WITH PASSWORD '<password>';

上記のコマンドは機能しますが、可能であれば、パスワードの変更にはpsqlのメタコマンドを使用することをお勧めします。psqlコマンドは自動的にパスワードを要求し、サーバーに送信する前に暗号化します。これにより、ログに機密データが漏れるのを防ぐことができます。

psqlでロールのパスワードを変更するには、次のように入力します。

-- To change your own password
\password
-- To change the password for another role
\password <role>

ALTER ROLEコマンドを使用してロール名を変更することもできます。

ALTER ROLE <role> RENAME TO <newrole>

現在のセッションロールは名前変更できないことに注意してください。

ロールの削除

既存のロールの削除は、以前のコマンドと同様のパターンに従います。繰り返しになりますが、これらのコマンドを実行するにはCREATEROLEまたはsuperuserの権限を持っている必要があります。

複雑な要因の一つとして、データベース内のオブジェクトによって参照されているロールは削除できないという点があります。これは、そのロールが所有するオブジェクトをすべて削除または所有権を移転する必要があることを意味します。その後、そのロールがデータベースオブジェクトに対して持つ追加の権限もすべて取り消す必要があります。

権限を適切に再割り当ておよび削除する方法の詳細な説明は、Database Administrators Stack ExchangeサイトのErwin Brandstetter氏によって提供されています（How to appropriately reassign and drop privileges）。以下の手順も同じプロセスを使用します。

まず、REASSIGNED OWNEDコマンドを使用して、ロールが所有するすべてのオブジェクトを再割り当てできます。たとえば、user2ロールを削除する準備をしている場合、次のように入力してそのオブジェクトをpostgresロールに割り当てることができます。

REASSIGN OWNED BY "user2" TO "postgres";

オブジェクトがpostgresによって所有されるようになったので、DROP OWNEDコマンドを使用して、付与されていた他のすべての権限を取り消すことができます。このコマンドは、私たちが所有するオブジェクトも削除しますが、先ほどpostgresロールにそれらを転送したばかりなので、user2ロールはもはや所有するオブジェクトを持っていません。このため、このコマンドはロールの追加権限のみを取り消します。

DROP OWNED BY "user2";

上記のDROP OWNEDショートカットを使用しない場合、ロールが権限を持つすべての個別のオブジェクトまたはオブジェクトタイプに対してREVOKE ALL PRIVILEGESを実行する必要があります。

関連するすべての権限を取り消したら、次のように入力してロールを削除できます。

DROP ROLE "user2";

psqlを使用したログイン

新しいロールを設定し、pg_hba.confファイルを使用して認証の詳細を設定したら、新しいロールを使用してデータベースクラスターにログインできます。psqlコマンドラインクライアントは、これを行う簡単な方法を提供します。

デフォルトでは、psqlは、オペレーティングシステムのユーザー名と一致するロールを使用して接続したいと仮定します。したがって、コンピューターにjohnとしてログインしている場合、psqlは、johnという名前のロールを使用してデータベースに接続しようとすると仮定します。

この動作を上書きするには、-Uまたは--username=オプションを渡します。たとえば、kerryというロールにログインしたい場合は、次のように入力します。

psql -U kerry

psqlコマンドの成功は、kerryロールの存在、接続しようとしているサーバーのアクセシビリティ、およびサーバーで定義されている認証ルールに依存します。

セッション中に別のロールに変更する

場合によっては、アクセス権を持つ別のロールの権限とアイデンティティを一時的に採用したいことがあります。たとえば、現在のロールにINHERIT属性がない場合に、自分がメンバーであるロールの権限を取得したい場合に、これが必要になります。

これがどのように機能するかを理解するには、PostgreSQLがアクティブなロールを分類するために使用する用語を知る必要があります。

• セッションロール: セッションロールとは、PostgreSQLデータベースクラスターへの初期接続時にログインしたロールのことです。これにより、初期の権限が設定され、システムへのアクセスが決定されます。このロールはLOGIN属性を持っている必要があります。
• 現在のロール: 対照的に、現在のロールとは、現在あなたが行動しているロールのことです。現在のロールに関連付けられた権限は、直接設定されたものであろうと、他のロールから継承されたものであろうと、実行を許可されるアクションとアクセスできるオブジェクトを決定します。

セッションロールと現在のロールの値を確認するには、次のように入力します。

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)

セッションロールを変更する唯一の方法は、別のロールを使用して新しい接続を開始することですが、SET ROLEコマンドを使用して現在のロールを変更できます。SET ROLEコマンドは、一時的に別のロールとして行動するために使用されます。このコマンドには、オプションで以下の修飾子も指定できます。

• SESSION: デフォルト設定。これにより、SET ROLEコマンドはデータベースセッション全体に影響します。
• LOCAL: この修飾子を使用すると、コマンドは現在のトランザクションに対してのみロールを変更します。

現在のロールをuser2ロールに変更するには（セッションの残りの間）、次のように入力します。

SET ROLE "user2";

セッションロールと現在のロールの値を確認すると、現在のロールの値が変更されていることがわかります。

SELECT SESSION_USER, CURRENT_USER;

 current_user | session_user
--------------+--------------
 user2        | postgres
(1 row)

すべての操作は、今後はuser2ロールをコンテキストとして使用します。

以前使用していたセッションロールに戻るには、次のように入力します。

SET ROLE NONE;

同じ結果を達成する別の方法は以下の通りです。

RESET ROLE;

まとめ

PostgreSQLのロール、ロール属性、グラント、および認証のシステムは、管理者が権限とデータベースアクセスを効果的に管理できる柔軟なシステムを構築します。このガイドでは、ロールが正確には何であるか、そしてそれらが幅広いユースケースをどのように包含するかを説明しました。また、ロールの作成、変更、削除、およびそれらのグローバルな機能を決定するロール属性の管理方法についても説明しました。これらのアイデンティティを管理する方法を理解することは、データベースを保護し、正当なユーザーに利用可能なアクセスを提供するために不可欠です。

よくある質問

ALTER ROLE <role> WITH PASSWORD '<password>';

- To change your own password
\password
-- To change the password for another role
\password <role>

\du :USER

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT rolname as "Users who can manage roles" FROM pg_roles WHERE rolsuper OR rolcreaterole;

SELECT 'Yes' AS "Can I manage roles?" FROM pg_roles WHERE rolname = :'USER' AND (rolsuper OR rolcreaterole);

CREATE ROLE <role>;

CREATE ROLE <role> WITH <options>;