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

ユーザー(ロール)管理

 PostgreSQL においてのユーザーの管理は一括してロールで行います。ver 8.1 より後では、ユーザーによる PostgreSQL データベースへの認証はできません。
 PostgreSQL にはユーザー管理ではなく、ユーザーの登録とデータベースへの接続認証はロールで管理します。

 ロールは、データ操作における認証と PostgreSQL との接続を管理するもので、ユーザーとグループの意味を含めています。

 ロールではグループがなく、ロールにロールを包括します。複数のロールを管理するロールという形でグループのような管理を行います。その点がユーザーとの違いです。

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

 PostgreSQL はデータベースごとにはロールを設定できません。ロールはデータベース・クラスタ(インスタンス)ごとに共用されていることに注意してください。

- ad -

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

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

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

su でユーザー切り替え
$ su postgres
password:
..

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

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

 スーパーユーザーを追加する注意点として、OSのユーザー名とロール名が一致していたとしてもローカルで PostgreSQL にアクセスできない場合があります。それは、Peer 認証の設定がされていないためです。
 Peer 認証の設定については「ログイン認証とロール」を参照してください。

ロールについて

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

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

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」にあります。

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

- ad -

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

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

$ psql
...

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

 データベース・クラスタごとにロールが管理されているため、上記の場合はデフォルトインストールにおいてのデータベース・クラスタでのロールの一覧になります。

 内容は以下になります。

属性値概要
Superuserスーパーユーザーであることを示す。すべてのデータベースとそのデータにアクセスできる
Create roleロールの作成許可
Create DBデータベースの作成許可
Replicationデータベースのバックアップの許可
Bypass RLS行単位のデータの参照、編集のための RLS( Row Level Security )機能を許可

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

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後方互換のため。無視される

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

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

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

ロール権限の変更

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

- ad -

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

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

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

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

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

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

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

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

- ad -

ロールのグループ管理

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;