PostgreSQL でのロールとロール属性の管理

はじめに

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

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

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

ロールとは？

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

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

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

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

ロールは、PostgreSQL 内のオブジェクト所有権の概念にも不可欠です。たとえば、各データベースとテーブルには、所有者として構成されたロールが正確に 1 つあります。スーパーユーザーを除いて、所有者ロールは、実際のオブジェクトを変更または削除できる唯一のロールです。

要約すると、ロールはほとんどの実用的なデータベース操作の中核にあります。その柔軟性により、ユーザー識別子とユーザークラスの両方として機能できます。データベースクラスタ内のすべてのアクションは、ロールの権限に対してチェックされ、データベースクラスタへの各接続の成功は、認証先のロールによって決定されます。ロール管理は非常に多くのコア操作において重要であるため、ロール管理をしっかりと把握することが重要です。

ロール属性

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

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

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

ロール属性の詳細については、PostgreSQL のドキュメントのロール属性とCREATE ROLE コマンドをご覧ください。

スーパーユーザーロールとは？

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

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

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

既存のロール属性の確認

ロール属性が何であるか、およびそれらが許可する権限のタイプについて概略を把握できたので、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 コマンドの詳細については、こちらをご覧ください。

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

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

createuser <options> <rolename>

たとえば、パスワードの入力を求めるプロンプトを表示しながら、スーパーユーザー権限を持つ admin という名前のロールを作成するには、次のように入力できます

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';

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

CREATE ROLE "user2" WITH SUPERUSER;

既存のロールの変更

既存のロールの属性を変更するには、代わりにALTER ROLE コマンドを使用できます。ロールの作成と同様に、現在のロールにも スーパーユーザー または 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;

ロールから スーパーユーザー ステータスを取り消すには (スーパーユーザー ロールを使用してのみこのコマンドを実行できます)、次のように入力します

ALTER ROLE "user2" WITH NOSUPERUSER;

ロールのパスワードを変更するには、次のように入力できます (すべてのロールは、CREATEROLE または スーパーユーザー 権限に関係なく、自分のロールでこのコマンドを実行できるはずです)

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 または スーパーユーザー 権限が必要です。

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

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

まず、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 のロールシステム、ロール属性、権限付与、および認証は、管理者 が効果的に権限とデータベースアクセスを管理できる柔軟なシステムを構築します。このガイドでは、ロールとは正確には何か、そしてロールが広範囲のユースケースをどのように包含するかについて説明しました。また、ロールの作成、変更、削除の方法、およびグローバルな機能を決定するロール属性の管理方法についても説明しました。データベースを安全に保ち、正当なユーザーがアクセスできるようにするために、これらのアイデンティティを管理する方法を理解することが必要です。

よくある質問 (FAQ)

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>;