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)

　項目内容は以下です。

　「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サーバーから接続する実行ユーザー名でロールを作成します。

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