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

ログイン認証とロールの設定

 ここでは、PostgreSQLデータベースに接続しデータを操作する際のログイン認証や操作許可について説明します。 特にPostgreSQLサーバー以外から接続する場合には理解しておいた方がよいでしょう。

 外部からPostgreSQLデータベースに接続し、データを操作するには、先ずPostgreSQLにログインできなればなりません。 さらに、ロール自体の設定がチェックされます。 そして、そのロールでデータベース、テーブル、ビューなどが操作の許可がチェックされます。
 概ね、PostgreSQLデータベースのデータ操作には以下の手順を踏みます。

データ操作クエリー
	↓
PostgreSQLへのログインロールのオプションをチェック
	↓ 
データベース、テーブル、ビューなどの操作許可
	↓
データ操作実行

PostgreSQLへのログイン

 PostgreSQLのデフォルトではpostgresロールのみが登録されています。 そのままではpostgres以外のシステムユーザーでデータベースに接続を試みてもログインすることができません。
 外部からPostgreSQLにアクセスするような場合に、peer認証設定(pg_hba.conf)をもとにログインできるロールとシステムのユーザーの一致がチェックされます。

 もしもPostgreSQLにログインできないなどの問題が発生したら、/var/log/postgresql/以下のログファイルをみてみましょう。

$ sudo less /var/log/postgresql/postgresql-10-main.log

 以下のようなエラーがあったらpeer認証エラーが起きています。

...
LOG:  provided user name (rolename) and authenticated user name (www-data) do not match
FATAL:  Peer authentication failed for user "rolename"
...

 PostgreSQLにアクセスするにはシステムユーザーと同名のロールを登録するか、pg_ident.confファイルにシステムユーザー名とロール名の読み替え定義をする必要があります。

スーパーユーザーのロールの登録
# CREATE ROLE user_name WITH SUPERUSER;

詳しくは「PostgreSQLユーザー(ロール)管理」を参照して下さい。

 もしくは、pg_hba.confファイルの設定をtrust認証にすれば、ログインチェックまでなら、システム上のユーザーと一致している必要はありません。

...
local   all             all                                     trust
...

pg_ident.confファイル

 システムユーザーとログインユーザー(ロールに設定されたユーザー)との読み替えはpg_ident.confファイルに定義します。

$ sudo nano /etc/postgresql/10/main/pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
rolename          www-data                  rolename

 設定を変更したら内容を確認してPstgreSQLを再起動します。

$ sudo systemctl restart postgresql

ロールのオプションをチェック

 次の段階ではロールのオプションのチェックがあります。 オプションにはログインの許可/不許可がありますが、ロールにログインの許可がされていればPostgreSQLにログインすることができます。

ロール自体の設定を確認するには、psqlインターフェイス内で\duコマンドを使います。

=# \du

 ロール自体の設定は、ロールの管理、データベースの作成権限、PostgreSQLへのアクセスに関する設定です。 外部からPostgreSQLにアクセスしデータを参照するためには、まずはPostgreSQLにアクセスできなければなりません。 ロールの設定でログイン許可を与える必要があります。
 詳しくは、「PostgreSQLユーザー(ロール)管理 ユーザー権限の変更」を参照して下さい。

 ロール自体の設定はALTERコマンドを使用します。

ロール自体への設定 ログイン可能に変更
=# ALTER ROLE new_name WITH LOGIN;

 詳しくは、「PostgreSQLユーザー(ロール)管理」を参照して下さい。

 さらにPostgreSQLのテーブル上のデータを閲覧、挿入、削除などの操作を行うにはテーブルごとにロールとその操作権限のチェックがあります。 スーパーユーザーまたはデータベースの作成者は操作権限を与えられていますが、それ以外はデータベース、テーブルごとに操作権限を与えなければなりません。

 セキュリティーを考慮すると、閲覧のみのロールグループ、データの挿入、削除が行えるロールグループを設けた方がよいかもしれません。

データベース、テーブル、ビューなどの操作許可

 PostgreSQLにアクセスできてもデータの閲覧などの操作を許可されていないとデータ操作はできません。 データベース、テーブル、ビューごとへの設定は、それらにロールとその操作権限の許可/不許可を設定します。詳しくは以下を参照して下さい。

テーブルtable_nameにrole_nameによる全ての操作(SELECT 、 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE 、REFERENCES、 TRIGGER)を許可
=# GRANT ALL PRIVILEGES ON table_name To role_name;

 詳しくは、「データベース、テーブルの操作権限とロール」を参照して下さい。