備忘録的プログラミングリファレンス

PostgreSQL ユーザー(ロール)管理

 PostgreSQL においてのユーザーの管理はロール管理と呼ばれます。 PostgreSQL にはユーザー管理はなく、ユーザーの登録とデータベースへの接続認証はロールを使います。
 ロール管理とは、PostgreSQL との接続とデータ操作における認証を管理するもので、ユーザーとグループの意味を含めています。
 ロールではグループがなく、ロールにロールを包括します。複数のロールを管理するロールという形でグループのような管理を行います。その点がユーザー管理との違いです。

 PostgreSQLでは接続とデータ操作における認証はロールで管理します。ユーザーに似ていますが、OS上のユーザーとは別として扱います。

 OS上のユーザーとロールとの照合方法は、「pg_hba.conf」ファイルで設定されています。Ubuntu では「/etc/postgresql/10/main/pg_hba.conf」にあります。

 PostgreSQL は、現在データベースごとにロールを設定できません。ロールはデータベース全体で共用されます。

postgres について

 多くの場合PostgreSQLの初期管理者ロールは「postgres」に設定されています。 「postgres」ロールはスーパーユーザーに設定されています。初期はこの「postgres」でPostgreSQLの設定を行っていくことになりますが、公のサーバーとして運用するなら初期設定の「postgres」ユーザーは変更した方がよいでしょう。
 この「postgres」ユーザーを変更することができますが、代わりのスーパーユーザーを作成しておく必要があります。

ユーザー(ロール)の確認・一覧

 システム上のユーザーからPostgreSQLに接続する時に、自動でシステム上のユーザー名でデータベースに接続できます。 そのためにロールはユーザーの読み替えと思われますが扱いは別物です。
 PostgreSQLは「pg_hba.conf」ファイルの設定によって、データベースに接続するユーザーとロールの照合を行って一致したもののみをデータベースに接続します。

 また、ロールはユーザーとグループのように明確に分かれていません。ユーザー、グループ、ロールは別物と憶えておいた方がよいでしょう。
 ロールでは継承という方法を使って、複数のロールがあるロールを継承するという方法でグループのような形をとります。

ロールの一覧

 ロールの一覧は、psql の \du コマンドを実行します。PostgreSQL でのユーザー一覧とはこのロール一覧のことを指します。

$ psql
...

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

 SQL コマンドでも確認できます。SQL コマンドではシステムカタログのテーブル pg_roles を参照します。

=# SELECT * FROM pg_roles WHERE rolname NOT LIKE 'pg_%';
...
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------+----------+------------+---------------+-------------+-------------+------
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |  10
(1 row)

 項目内容は以下です。

項目名タイプ概要
rolnamenameロール名
rolsuperboolスーパーユーザ
rolinheritboolロール権限の継承
rolcreateroleboolロール追加作成の権限
rolcreatedbboolデータベース作成の権限
rolcanloginboolログインの可/不可
rolreplicationboolレプリケーション用のロール
rolconnlimitint4同時接続の最大数(-1は無制限)
rolpasswordtextパスワード(********で表示)
rolvaliduntiltimestamptzパスワードの有効期限(NULL...有効期限なし)
rolbypassrlsboolセキュリティポリシーを無視
rolconfigtext[]実行設定のデフォルト値
oidoidロールID

 「pg_user」、「pg_shadow」でも登録されたロールを参照できます。

=# SELECT * FROM pg_user;

 「pg_shadow」は、システム上のルートユーザーからのみ参照できます。

=# SELECT * FROM pg_shadow;

ロール(ユーザー)の追加

 PostgreSQLでのロールの追加は、SQLコマンドのロールの追加で行う方法とcreateuserコマンドを使う方法があります。 どちらのコマンドもスーパーユーザー、例えば「postgres」、またはロール作成権限をもったロールでログインして実行します(-Uオプションを使う方法もあります)。

 外部からの接続、例えばシステムにログインしているユーザー、webサーバーからwww-dataやsuExecなどの実行ユーザーでPostgreSQLに接続するとそのユーザー名でログインを試みます。 これらの場合は、システムのユーザー名やwebサーバーから接続する実行ユーザー名でロールを作成します。

SQLコマンドによるロール追加
$ psql
...
=# CREATE ROLE new_name;
createuserコマンドによるロール追加
$ createuser -U postgres new_role

 この方法ではスーパーユーザーの権限、データベースを作成する権限、ロールを追加する権限などのオプションは設定しておらず、作成されたロールは認証によるログインもできないために外部からデータベースへの接続ができません。
 もしオプションを設定するにはwithで設定します。

オプションを付加

オプション付きでロールの作成
=# CREATE ROLE new_name WITH SUPERUSER LOGIN PASSWORD 'password';

オプションには以下があります。

オプション名摘要
SUPERUSER | NOSUPERUSERスーパーユーザーとしてロールが作成される
CREATEDB | NOCREATEDBデータベースの作成権限を付与
CREATEROLE | NOCREATEROLEロール(ユーザー)の管理を付与
INHERIT | NOINHERIT継承
LOGIN | NOLOGINログイン認証の可/不可
REPLICATION | NOREPLICATIONストリーミングレプリケーションの可/不可
BYPASSRLS | NOBYPASSRLS行単位セキュリティ(RLS)ポリシー
CONNECTION LIMIT connlimit最大同時接続数(デフォルト -1で無制限)
[ ENCRYPTED ] PASSWORD 'password'パスワードの設定
VALID UNTIL 'timestamp'ロールのパスワードが無効になる日時を設定
IN ROLE rolename [, ...]作成するロールを既存のロールに追加する
ROLE rolename [, ...]既存のロールを作成するロールに追加する
ADMIN rolename [, ...]作成するロールに、ロールの追加、削除の権限を付与して、既存のロールを追加。
SYSID uid後方互換のため。無視される

* [, ...] は複数の設定ができます

 ロールの権限の変更は以下の「ロール権限の変更」を参照してください。

ロール権限の変更

 作成したロールの権限を変更するにはSQLのALTER roleコマンドを使います。

ログイン可能に変更
=# ALTER ROLE new_name WITH LOGIN;

 データベースの作成を可能にするには以下のようにします。

データベースの作成可
=# ALTER ROLE new_name WITH CREATEDB;

ロールのパスワード設定

 データベースの接続時の認証にパスワードを必要とする場合には以下のように設定します。

パスワードの追加、変更
ALTER ROLE new_name WITH PASSWORD 'password';

オプションには以下があります。

オプション名摘要
SUPERUSER | NOSUPERUSERスーパーユーザー
CREATEDB | NOCREATEDBデータベース作成
CREATEROLE | NOCREATEROLEロール(ユーザー)の管理
INHERIT | NOINHERIT継承
LOGIN | NOLOGINログイン認証の可/不可
REPLICATION | NOREPLICATIONストリーミングレプリケーションの可/不可
BYPASSRLS | NOBYPASSRLS行単位セキュリティ(RLS)ポリシー
CONNECTION LIMIT connlimit最大同時接続数(デフォルト -1で無制限)
[ ENCRYPTED ] PASSWORD 'password'パスワードの設定
VALID UNTIL 'timestamp'ロールのパスワードが無効になる日時を設定

ロールの削除

 ロールの削除は、SQLコマンドのロールの削除で行う方法とdropuserコマンドを使う方法があります。

SQLコマンドによるロール削除
=# DROP ROLE new_name;
dropuserコマンドによるロール削除
$ dropuser -U postgres new_name

スーパーユーザーの追加と削除

 スーパーユーザーの追加作成と削除です。

オプション付きでスーパーユーザー(ロール)の追加作成
=# CREATE ROLE new_name WITH SUPERUSER LOGIN PASSWORD 'password';

 スーパーユーザーの削除はDROP ROLEによって実行できますが、既に削除対象のスーパーユーザーで作成したデータベースがある場合は注意しましょう。

 スーパーユーザーを削除や変更は、別のスーパーユーザーで実行しなければなりません。

スーパーユーザー(ロール)の削除(*データベースがある場合は注意)
=# DROP ROLE superuser;

 ロールの名称を変更する方法がありますがパスワードがクリアされます。

スーパーユーザー(ロール)の名称変更
=# ALTER ROLE old_name RENAME TO new_name ;

PostgreSQLのロールの説明を自分なりに解釈し直してみました。

 PostgreSQLでは、データベースを利用するユーザーの権限はロールという概念で管理します。
 ロールはユーザーごと、ユーザーグループごとの設定ができます。
 ロールの持つ権限には、データベースやテーブルなどのデータベースオブジェクトの所有や作成、他のロールを追加、データベースオブジェクトへのアクセス権限、ロールのメンバとして他のロールを加えることができます。メンバとして加えられるとその権限を継承します。

 ロールには、ユーザーとグループという概念が含まれます。PostgreSQLバージョン8.1以前はユーザーとグループは分けられていました。現在では、データベースの権限を管理するロールは、ユーザー、グループ、またはその両方として扱われます。

 本章では、ロールの作成と管理の方法、権限システムについて説明します。データベースオブジェクトの様々な種類や権限の効果についての詳細な情報は第5章に記載されています。

18.1. データベースロール
 データベースロールは、オペレーティングシステムのユーザーとは別です。システムのユーザーをデータベースロールと一致させておくと便利ですが必須ではありません。
データベースロールは、PostgreSQLデータベース管理システム全体で利用しますので、データベースクラスタインストレーションで管理されます。


ロールの追加、削除は上記を参照すること。

また、psqlプログラムの\duメタコマンドも既存のロールを列挙する際に役に立ちます。


与えられたクライアント接続で接続できるデータベースロールの集合は、第20章で説明されているように、クライアント認証の設定によって決められます。?
接続したクライアントに付与される権限の内容はロールIDによって決定されるため、マルチユーザ環境を設定する際にはロールIDを注意深く設定することが重要です。

ロールのグループ管理

PostgreSQL ではグループ用のロールはないです。グループ化する方法として、ロールにロールを追加することでグループを構築します。

グループとしてのロールの確認

 pg_auth_membersカタログを確認します。

postgres=# SELECT (select pg_authid.rolname as auth_name from pg_authid where pg_authid.oid = pg_auth_members.roleid),(select pg_authid.rolname as members from pg_authid where pg_authid.oid = pg_auth_members.roleid),(select pg_authid.rolname as grantor_member from pg_authid where pg_authid.oid = pg_auth_members.roleid),admin_option FROM pg_auth_members;
...
auth_name       |       members        |    grantor_member    | admin_option
----------------------+----------------------+----------------------+--------------
pg_read_all_settings | pg_read_all_settings | pg_read_all_settings | f
pg_read_all_stats    | pg_read_all_stats    | pg_read_all_stats    | f
pg_stat_scan_tables  | pg_stat_scan_tables  | pg_stat_scan_tables  | f
(3 rows)

グループとしてのロールを作成

 グループ用ロールの作成はユーザー用のロール作成と同じです。グループとして利用するという前提でロールを作成します。 このロールにロールを追加することでグループ化します。ロールに所属するロールをメンバロールといいます。

 例えば、new_groupロールにuser_nameロールを追加するとします。まずは、new_groupロール、user_nameロールを作成します。 user_nameは一人のユーザーとして扱います。

SQLコマンドによるグループ用ロールとユーザー用ロールの作成
$ psql
...
=# CREATE ROLE new_group;
=# CREATE ROLE user_name WITH INHERIT;

 user_nameは、INHERITによって所属するnew_groupロールの権限を継承します。 このことでnew_groupの権限がそのロールに所属するロールに反映されます。

 user_nameをnew_groupに追加します。

SQLコマンド グループへのロール追加
=# GRANT new_group TO user_name;

グループ用ロールの削除

 グループ用ロールを削除するにはDROPコマンドを使用します。所属していたメンバロールの継承していた権限は無くなります。

SQLコマンド グループからロールを削除
=# REVOKE new_group FROM user_name;

データベース、テーブルの操作権限とロール

データベース、テーブル、ビューなどにロールによる閲覧、挿入、削除などの操作権限を追加する方法についてです。

 詳しくは、「データベース、テーブルの操作権限とロール」を参照してください。

 外部からPostgreSQLのデータ操作には、PostgreSQLへのログイン認証、ロールの設定、テーブルごとのロール設定がチェックされます。
 ログイン認証、ロールの設定、テーブルごとの設定については「ログイン認証とロールの設定」を参照して下さい。

ロール名のデータベース作成

 システム上のユーザー名をロール名として使うこともできます。その場合、システムのユーザーでpsqlコマンドを実行するとユーザー名のデータベースにログインします。

user_name@ubuntu-PC:~$ psql
...
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

user_name=>

 ただし、ユーザー名(ロール名)のデータベースがない場合にpsqlコマンドだけではエラーが発生します。

$ psql
↓
エラー表示

 ユーザー名(ロール名)のデータベースを作成しおく必要があります。

# CREATE DATABASE user_name OWNER user_name;