アノテーション、Postgre 大好き村上です。2024 年もテクサポと学ぶ PostgreSQL シリーズをよろしくお願いいたします。
シリーズ第 2 回目 テクサポと学ぶ PostgreSQL #2 ユーザー(1) では、デフォルトユーザー postgres に対して PostgreSQL ログイン時のパスワードを設定しました。シリーズ第 3 回目は、デフォルトユーザー以外のユーザーを作成してログイン属性を変更してみます。
本シリーズでしていくこと
PostgreSQL に対しての基本的な知識を確認しながら、時には EC2 上にインストールした PostgreSQL 15 において DDL や DML 等のコマンドを実行して動作確認をしていきます。
また、業務で RDS for PostgreSQL や Aurora PostgreSQL に対してのお問い合わせに対応しているので、設定や運用面でお役に立つような情報も合わせてご紹介していきます。
ユーザーを作成してみる
まずは、psql ツールを利用してデフォルトユーザー postgres でログインします。
以下のシンプルな SQL 文で、オプションを指定せずに testuser を作成してみます。
postgres=# CREATE USER testuser;
CREATE ROLEtestuser を作成することができました。
なお、PostgreSQL においてはユーザーとロールに違いは無く、グループもロールに含まれる概念となります。以後、本記事ではユーザーという表現は極力用いずにロールと呼ぶようにします。
pg_authid
ロールは「ユーザ」と「グループ」の概念を包括しています。
CREATE USER と CREATE ROLE の違いは？
先ほど作成したロール testuser は、CREATE USER 文を利用して作成しましたが、実は CREATE ROLE 文でもロールの作成は可能です。CREATE USER 文でのロール作成と、CREATE ROLE 文でのロール作成で何が違うのかというお問い合わせをいただいたことがあります。
CREATE USER 文でロールを作成するとデフォルトでログイン属性を持つロールが作成され、CREATE ROLE 文でロールを作成するとデフォルトでログイン属性を持たないロールが作成されます。ちなみにログイン属性を持たないロール（ pg_monitor 等）は、定義済みロール として PostgreSQL 内部で利用されています。
CREATE USER
CREATE USERはCREATE ROLEの別名になりました。 唯一の違いは、CREATE USERという名前でコマンドが呼び出されると、デフォルトでLOGINになり、CREATE ROLEという名前でコマンドが呼び出されると、デフォルトでNOLOGINとなる点です。
では、実際に CREATE ROLE 文でロールを作成して、それぞれのロールのログイン属性を比較してみます。
postgres=# CREATE ROLE testuser01;
CREATE ROLEpg_authid カタログで、各ロールの属性を確認してみます。
pg_authidカタログはデータベース認証識別子（ロール）の情報を保持します。
postgres=# select * from pg_authid;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |
rolpassword | rolvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+------
---------------------------------------------------------------------------------------------------------------------------------+---------------
6171 | pg_database_owner | f | t | f | f | f | f | f | -1 |
|
6181 | pg_read_all_data | f | t | f | f | f | f | f | -1 |
|
6182 | pg_write_all_data | f | t | f | f | f | f | f | -1 |
|
3373 | pg_monitor | f | t | f | f | f | f | f | -1 |
|
3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 |
|
3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 |
|
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 |
|
4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 |
|
4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 |
|
4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 |
|
4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 |
|
4544 | pg_checkpoint | f | t | f | f | f | f | f | -1 |
|
10 | postgres | t | t | t | t | t | t | t | -1 | SCRAM
-SHA-256$4096:vey+IiGCJq5d/6pfSrk90w==$EJ28jOgz2v4icq9SNvhmoLzmRmcRgzLXrEpge3QkXgc=:evzbyWB/VUJfIfFO0o+MWYekcM6HPhgIuX8qcvoGA9Q= |
16388 | testuser | f | t | f | f | t | f | f | -1 |
|
16389 | testuser01 | f | t | f | f | f | f | f | -1 |
|
(15 rows)
rolcanlogin の設定値を見てみると、CREATE USER 文で作成したロール testuser については t（trure）となっているのに対して、CREATE ROLE 文で作成した testuser01 は f（false）となっていることが確認できました。
pg_authid
rolcanlogin bool
ロールはログインすることができます。つまりロールはセッションを始める認証の識別子となることができます。
現在、local からの接続はパスワード認証を利用するように設定しているので（テクサポと学ぶ PostgreSQL #2 ユーザー(1)で設定済み）2 つのロールにログイン時のパスワードを設定してから、ログイン時の動作を確認してみます。以下の SQL を実行してパスワードを設定します。
postgres=# alter role testuser with password 'testuser';
ALTER ROLEpostgres=# alter role testuser01 with password 'testuser01';
ALTER ROLE2 つのロール（testuser、testuser01）にパスワードが設定されているか、pg_authid カタログで確認しておきます。
postgres=# select * from pg_authid;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |
rolpassword | rolvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+------
---------------------------------------------------------------------------------------------------------------------------------+---------------
6171 | pg_database_owner | f | t | f | f | f | f | f | -1 |
|
6181 | pg_read_all_data | f | t | f | f | f | f | f | -1 |
|
6182 | pg_write_all_data | f | t | f | f | f | f | f | -1 |
|
3373 | pg_monitor | f | t | f | f | f | f | f | -1 |
|
3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 |
|
3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 |
|
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 |
|
4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 |
|
4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 |
|
4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 |
|
4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 |
|
4544 | pg_checkpoint | f | t | f | f | f | f | f | -1 |
|
10 | postgres | t | t | t | t | t | t | t | -1 | SCRAM
-SHA-256$4096:vey+IiGCJq5d/6pfSrk90w==$EJ28jOgz2v4icq9SNvhmoLzmRmcRgzLXrEpge3QkXgc=:evzbyWB/VUJfIfFO0o+MWYekcM6HPhgIuX8qcvoGA9Q= |
16388 | testuser | f | t | f | f | t | f | f | -1 | SCRAM
-SHA-256$4096:e8qi1h2BrJfwxdlMBmeC4g==$MXwFFokYvlQHXj2kBUkGAx73HnqkKwLpxux5IzRsOng=:768I6kn+oZ5DNWgFc432NVYLc3wLO7Jqit37bAgwkzM= |
16389 | testuser01 | f | t | f | f | f | f | f | -1 | SCRAM
-SHA-256$4096:5yiaB1D4bMUBXco3utU3tA==$UNa0qwFjxZ3AnTUJ5IeTOp1S73fFVTWNZesBQFCgdyQ=:UhZR70lQhbd9+UH81j5zFzbtRtOrcqd8oFPE3eoLFPw= |
(15 rows)2 つのロール（testuser、testuser01）にパスワードが設定されていることを確認できました。一度、PostgreSQL からログアウトした後にそれぞれのロールを指定して、psql で接続ができるか動作確認をしてみます。
まずは、ロール testuser を指定して、データベース postgres へ接続ができるか確認してみます。testuser は、CREATE USER 文で作成したロールなのでログイン属性を持ちます。
[postgres@ip-172-31-0-165 ~]$ psql -U testuser -d postgres
Password for user testuser:
psql (15.4)
Type "help" for help.
postgres=>
postgres=> select current_user;
current_user
--------------
testuser
(1 row)testuser でログインしていることが確認できました。
次は、ロール testuser01 を指定して、データベース postgres へ接続ができるか確認してみます。testuser01 は、オプションの指定をしていない CREATE ROLE 文で作成したロールなので、ログイン属性を持たないロールです。
[postgres@ip-172-31-0-165 ~]$ psql -U testuser01 -d postgres
Password for user testuser01:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "testuser01" is not permitted to log in当然の結果ですが、ロール testuser01 はログイン属性を持たない為、ログインに失敗しました。デフォルトユーザー postgres で PostgreSQL へログインし直してから、ALTER ROLE 文でロール testuser01 のログイン属性を変更してみます。
[postgres@ip-172-31-0-165 ~]$ psql
Password for user postgres:
psql (15.4)
Type "help" for help.
postgres=# alter role testuser01 login;
ALTER ROLEALTER ROLE 文で、ロール testuser01 のログイン属性を変更できたので、再度、ロール testuser01 とデータベース postgres を psql のオプションで指定して接続してみます。
[postgres@ip-172-31-0-165 ~]$ psql -U testuser01 -d postgres
Password for user testuser01:
psql (15.4)
Type "help" for help.
postgres=> select current_user;
current_user
--------------
testuser01
(1 row)ログイン属性を持つロールになったことで、ロール testuser01 でもログインすることができるようになりました！
まとめ
本記事では、CREATE USER 文と CREATE ROLE 文で作成されるロールの属性の違いに注目してみました。
本シリーズを通して、PostgreSQL の基本的な設定や機能を一緒に学んでいければ嬉しいです。
この記事がどなたかのお役に立てば幸いです。
2
