ユーザー（ロール）管理

スーパーユーザー postgres について

　多くの場合 PostgreSQL の初期管理者ロールはpostgresに設定されています。postgresロールはスーパーユーザーとも呼ばれます。

　このpostgresユーザーは PostgreSQL のインストール時に OS 上のユーザーとしても作成されています。そのため、以下のように端末からsuコマンドでログインができます。

$ sudo su postgres
..

　初期の状態では postgres ユーザーにパスワードは設定されていませんが、sudo でログインすることができます。PostgreSQL の初期設定は、この postgres ユーザーで行います。

　公開サーバーとして運用する場合、postgres ユーザーにパスワードを設定しない、あるいは外部からのログインを制限するなどのセキュリティ対策が不可欠です。

　postgres ユーザーは PostgreSQL の起動ユーザーとして使用されている可能性があるため、安易に削除すると PostgreSQL が起動できなくなることがあります。 削除を検討する際は、慎重な検討が必要です。

　初期のデータベースクラスタは、本格的な運用には適さない場合があります。必要に応じて、新しいデータベース・クラスタ（インスタンス）を新規に作成することも可能です。

　初期の認証設定ファイル（pg_hba.conf）では、スーパーユーザーに対してPeer認証が設定されており、ログインした上でPostgreSQLへの接続が許可されます。 Peer認証の詳細については、関連ドキュメントのログイン認証とロールセクションをご参照ください。

ロールについて

　OS 上のユーザーで PostgreSQL データベースに接続できます。これは、PostgreSQL のロールと同じ名称のユーザーが OS 上にあり、pg_hba.conf ファイルで Peer 接続による認証が設定されているためです。

　例えば、以下のように OS の端末で user_a ユーザーに切り替え後に psql でログインしたとします。

-- ユーザー user_a で psql にログイン --
user_a@user-PC:/home/user_a$ psql
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1))
Type "help" for help.

user_a=#

　すんなりとログインできる場合は、ユーザー名 user_a と同じ名称がロールでも管理されており、Peer 接続で認証が許可されているためです。

　もしもOS上のユーザーで psql にログインできない場合は、PostgreSQL に該当するロールが登録されていないためです。ロールを追加するには以下のロール（ユーザー）の追加を参照してください。

　ロールはユーザーの読み替えと思われますが扱いは別物です。PostgreSQL では、登録されたロールで接続・データ操作を行います。
　PostgreSQL の「pg_hba.conf」は、データベースに接続するロールの照合設定用のファイルです。Peer 設定では、OSにログイン中のユーザーとロールとを照合します。

　「pg_hba.conf」ファイルは、Ubuntu では「/etc/postgresql/10/main/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)

　項目内容は以下です。

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

=# SELECT * FROM pg_user;

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

=# SELECT * FROM pg_shadow;

ロール（ユーザー）の追加

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

$ sudo su postgres
..

$ psql
...
=# CREATE ROLE new_name;

　もしも、OS上のユーザーと同じ名称のロールを追加する場合は以下のようにします。以下の例では、ユーザー user_a のためのロールを追加しています。LOGIN オプションを追加しないと Peer 接続ができません。

$ sudo su postgres
..

=# CREATE ROLE user_a WITH LOGIN;

　これで、user_a から psql にログインすることができます。

　オプションを設定することもできます。オプションは、以下のように WITH で設定します。

オプションを付加

=# CREATE ROLE new_name WITH SUPERUSER LOGIN PASSWORD 'password';

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

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

　INHERIT | NOINHERIT は、親のロールからデータ操作権限を継承するか／しないかの設定です。ロールより親がない場合は NOINHERIT でよいでしょう。
　親がある場合は、INHERIT にしておかないとデータ操作権限は継承されません。

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

　PostgreSQL よりも外部からの接続の場合にはロールやその認証の設定が必要です。
　例えばシステムにログインしているユーザー、web サーバーから www-data や suExec などの実行ユーザーで PostgreSQL に接続するとそのユーザー名でログインを試みます。
　これらの場合は、システムのユーザー名や web サーバーから接続する実行ユーザー名のロールがなければなりません。ほとんどの場合はインストール時にデフォルトで用意されています。 もしもロールが用意されていなければユーザー名と同じ名称のロールを作成しておきます。

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

$ createuser -U postgres new_role

ロール権限の変更

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

=# ALTER ROLE new_name WITH LOGIN;

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

=# ALTER ROLE new_name WITH CREATEDB;

ALTER ROLE new_name WITH PASSWORD 'password';

ロールの削除

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

=# DROP ROLE new_name;

$ dropuser -U postgres new_name

ロールのグループ管理

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は一人のユーザーとして扱います。

$ psql
...
=# CREATE ROLE new_group;
=# CREATE ROLE user_name WITH INHERIT;

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

　user_nameをnew_groupに追加します。

=# GRANT new_group TO user_name;

グループ用ロールの削除

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

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