PostgreSQL ユーザー（ロール）管理

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

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

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

$ su postgres
password:
..

　パスワードは Ubuntu のデフォルトインストールではpostgresです。

　初期はこのpostgresで PostgreSQL の設定を行っていくことになりますが、公のサーバーとして運用するなら初期設定のpostgresは変更した方がよいでしょう。 その場合は、代わりのスーパーユーザーを作成しておくことを忘れないでください。
　インストール時にスーパーユーザー名を指定することもできます。インストール時から計画的にデータベースを構築することをお勧めします。

ロールについて

　OS 上のユーザーで PostgreSQL データベースに接続できます。これは、OS 上のユーザーと同じ名称のロールが PostgreSQL にあり、Peer 接続で認証が行われているためです。

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

-- ユーザーを postgres に切り替え --
$ su postgres
パスワード:

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

postgres=#

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

　ロールはユーザーの読み替えと思われますが扱いは別物です。
　PostgreSQL は「pg_hba.conf」ファイルで、データベースに接続するユーザーとロールの照合を行っています。

　「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オプションを使う方法もあります）。

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

$ psql
...
=# CREATE ROLE new_name;

$ createuser -U postgres new_role

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

オプションを付加

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

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

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

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

ロール権限の変更

　作成したロールの権限を変更するには 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

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

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

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

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

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

=# DROP ROLE superuser;

=# ALTER ROLE old_name RENAME TO 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;