PostgreSQL ユーザー(ロール)管理
PostgreSQL においてのユーザーの管理はロール
で行います。
PostgreSQL にはユーザー管理ではなく、ユーザーの登録とデータベースへの接続認証はロール
で管理します。
ロールは、データ操作における認証と PostgreSQL との接続を管理するもので、ユーザーとグループの意味を含めています。
ロールではグループがなく、ロールにロールを包括します。複数のロールを管理するロールという形でグループのような管理を行います。その点がユーザーとの違いです。
ページ内 Index
PostgreSQLでは接続とデータ操作における認証はロールで管理します。ユーザーに似ていますが、OS上のユーザーとは別として扱います。
PostgreSQL は現在データベースごとにはロールを設定できません。ロールは PostgreSQL のシステム全体で共用されていることに注意してください。
- ad -
スーパーユーザー 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」にあります。
ロールはユーザーとグループのように明確に分かれていません。ユーザー、グループ、ロールは別物と憶えておいた方がよいでしょう。
ロールでは継承という方法を使って、あるロールを複数のロールで継承するという方法でグループのような形をとります。
- ad -
ユーザー(ロール)の確認・一覧
ロールの一覧は、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)
項目内容は以下です。
項目名 | タイプ | 概要 |
---|---|---|
rolname | name | ロール名 |
rolsuper | bool | スーパーユーザ |
rolinherit | bool | ロール権限の継承 |
rolcreaterole | bool | ロール追加作成の権限 |
rolcreatedb | bool | データベース作成の権限 |
rolcanlogin | bool | ログインの可/不可 |
rolreplication | bool | レプリケーション用のロール |
rolconnlimit | int4 | 同時接続の最大数(-1は無制限) |
rolpassword | text | パスワード(********で表示) |
rolvaliduntil | timestamptz | パスワードの有効期限(NULL...有効期限なし) |
rolbypassrls | bool | セキュリティポリシーを無視 |
rolconfig | text[] | 実行設定のデフォルト値 |
oid | oid | ロールID |
「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';
オプションには以下があります。
オプション名 | 摘要 |
---|---|
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 コマンドを使う方法があります。
=# DROP ROLE new_name;
$ 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は一人のユーザーとして扱います。
$ 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;