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

ログイン認証とロール

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

 注意する点としては、ver 8.1 より後では、ユーザーによる PostgreSQL データベースへの認証はできません。データベースへの接続認証と操作権限はロールで管理します。

 登録したロールで PostgreSQL データベースに接続しデータを操作するには、先ず PostgreSQL にログインできなればなりません。
 ログイン後は概ね以下の手順を踏みデータの操作ができます。

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

 ログインの後に、ロール自体の設定がチェックされます。 そして、そのロールでデータベース、テーブル、ビューなどが操作の許可がチェックされます。

 ロールとは PostgreSQL においてユーザーやグループを意味するもので、認証やデータベースへの接続を管理する際に使用されるものです。詳しくはPostgreSQL ユーザー(ロール)管理を参照してください。
 データベース、テーブル、ビューなどのロール設定の確認と設定についてはデータベース、テーブルの操作権限とロールを参照してください。

- ad -

PostgreSQL へのログイン

 PostgreSQL へのユーザーの認証設定ファイルには pg_hba.conf や pg_ident.conf があります。これらのファイルはデータベース・クラスタごとにあり、設定されたユーザーがログインへ進むことができます。
 pg_hba.conf ファイルは、peer(ローカル端末)やネットワークからのユーザーの認証方法の設定ファイルになります。
 pg_ident.conf ファイルはユーザーとロールの関連性を設定するユーザー名マッピングファイルと呼ばれます。ログインを試みるユーザー名とロール名の読み替えの設定ができます。

 これらのファイルは/etc/postgresql/*/以下にあります。* はバージョン番号です。

接続認証設定 pg_hba.conf

 PostgreSQL をインストールしたままの状態では postgres ロール( または postgresql )が登録されており、pg_hba.conf に peer による接続設定がされています。

pg_hba.conf における postgres の設定
# Database administrative login by Unix domain socket
local   all             postgres                            peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

 上記の例では、ローカル端末から postgres による PostgreSQL へのアクセスを許可しています。このことによって、ローカル内の端末から PostgreSQL にアクセスすることができます。

 しかし、postgres 以外のロール名でデータベースに接続を試みてもログインすることができません。

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

PostgreSQL へのアクセスログ
$ sudo less /var/log/postgresql/postgresql-10-main.log

 以下のようなエラーがあったら認証エラーが起きています。1つの解決方法は、pg_hba.conf に認証設定をしておきます。

peer 接続エラー
rolename@pc $ psql -p 5435 -d postgres

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5435" failed:
FATAL:  Peer authentication failed for user "rolename"

 pg_hba.conf に以下のように追記してみましょう。

pg_hba.conf における postgres の設定
# Database administrative login by Unix domain socket
local   all             postgres                            peer
local   all             rolename                            peer

 上記の例では、rolename でログインした OS のローカルの端末からの PostgreSQL への接続を許可しています。

 pg_hba.conf では、 peer 以外にもmd5passwordなどパスワードによる認証方法を指定することもできます。
 PostgreSQL への接続ユーザー名とログインを試みるロール名に違いがある場合はパワードによる認証方法の方がよいかもしれません。

 以下のようなエラーが出る場合にはパスワードによる認証またはユーザーの読み替え設定が必要です。

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

 上記のエラーは、rolename ユーザーでPostgreSQL に接続を試みていますが実際に接続をしようとしているのは www-data ユーザーというエラーです。
 pg_hba.conf ファイルに以下のような設定で解消できるかもしれません。

pg_hba.conf におけるロールの接続設定
# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     password

 上記の例ではロールには登録されているが OS にはユーザーと登録されていない場合を想定しています。OS のユーザーにはないため peer 認証はコメントアウトしています。

 pg_hba.conf では、指定されたユーザーであれば trust の指定によって何でも許可することもできます。 trust にすれば、システム上のユーザーと一致している必要もパスワードも必要はありません。ロール名で PostgreSQL にアクセスできるかを確認する場合には便利な方法です。

trust 指定
...
local   all             all                                    trust
...

 認証方法について詳しくはPostgreSQL 9.4.5文書 第19章クライアント認証を参照してください。

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

PostgreSQL の再起動
$ sudo systemctl restart postgresql

pg_ident.conf ファイル

 PostgreSQL にアクセスにおいて、pg_ident.conf ファイルにシステムユーザー名とロール名の読み替え設定をする場合があります。

 例えば、以下のようなrolename ユーザーでPostgreSQL に接続を試みていますが実際に接続をしようとしているのは www-data ユーザーというエラーが起きた場合に、pg_ident.conf ファイルにユーザーの読み替え設定をすることでも解消できる場合があります。

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

 システムユーザーとログインユーザー(ロールに設定されたユーザー)との読み替えを 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を再起動します。

PostgreSQL の再起動
$ sudo systemctl restart postgresql

 読み替えではなく、pg_hba.conf に認証が必要なユーザーを登録することで解消できる場合もあります。

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

 次の段階ではロールのオプションのチェックがあります。 オプションにはログインの許可/不許可がありますが、ロールにログインの許可がされていれば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;

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