PostgreSQL はデフォルトではローカルホストからしか接続できません。PostgreSQL の動作するホストとは別のリモートホストから接続するには設定が必要です。ただ接続するだけでよければ設定は簡単ですが、ここでは、なるべく安全に接続するのに必要な設定について説明します。
説明は基本的に PostgreSQL 11 を CentOS 7 にセットアップした状態を前提にしますが、バージョンで異なる部分はなるべく補足します。PostgreSQL のインストールについては こちらの記事 を参照してください。また、設定に伴う操作はとくに断わりのない限り postgres ユーザで行うものとします。
パスワード暗号化アルゴリズムの設定
ユーザのパスワードはデフォルトでは MD5 で暗号化して格納されます。PostgreSQL 10 以降ではより安全な SCRAM-SHA-256 という暗号化アルゴリズムを使えます。SCRAM-SHA-256 に未対応の古いクライアントからの接続が不要であれば、変更したほうがいいでしょう。ここでは、暗号化アルゴリズムを SCRAM-SHA-256 に設定します。
パスワード暗号化アルゴリズムの設定は postgresql.conf ファイル内の password_encryption パラメータで行います。
postgresql.conf ファイルは PostgreSQL の設定ファイルで、データベースクラスタ内にあります。postgresql.conf ファイルは 1 行に 1 つの設定を パラメータ名 = 値 という書式で書きます。# から行末まではコメントを表します。ほとんどのパラメータはコメントになっていて、コメントになった最初の状態は一部のパラメータを除いてデフォルト値を表します。設定を変更する際にはパラメータ名の前の # を外すのを忘れないようにしてください。また、コメントに (change requires restart) と書いてあるパラメータは設定の反映にデータベースサーバの再起動が必要です。書いてないパラメータは設定ファイルの再読み込みで反映できます。
postgresql.conf ファイルをエディタで編集し、password_encryption パラメータの設定を scram-sha-256 に変更します。
password_encryption = scram-sha-256 # md5 or scram-sha-256
pg_ctl コマンドの reload モードで設定ファイルの再読み込みを行い、password_encryption パラメータの設定を反映します。現在の設定は postgres コマンドの -C オプションで確認できます。
$ pg_ctl reload
サーバにシグナルを送信しました
$ postgres -C password_encryption
scram-sha-256
接続用ユーザの作成
リモートホストからの接続はデフォルトで存在する postgres ユーザでもできますが、強い権限をもつスーパーユーザができるのはあまりよくありません。ここでは、スーパーユーザとは別に接続用ユーザを作成します。
psql コマンドでデータベースに接続し、CREATE ROLE 文でログイン権限のみをもつ test という名前のユーザを作成します。ユーザの一覧は \du コマンドで確認できます。
$ psql
psql (11.4)
"help" でヘルプを表示します。
postgres=# CREATE ROLE test LOGIN;
CREATE ROLE
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+----------------------------------------------------------------------------+--------------
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
test | | {}
psql コマンドは \q コマンドで終了できますが、このあとの設定もデータベースに接続した状態で行うので、終了しないでください。
パスワードの設定
ユーザのパスワードはデフォルトでは設定されておらず、パスワード認証が行えません。ここでは、作成した接続用ユーザと設定してなければスーパーユーザのパスワードを設定します。
まず、\password コマンドで test ユーザのパスワードを設定します。
postgres=# \password test
新しいパスワードを入力してください: (パスワードを入力)
もう一度入力してください: (パスワードを再入力)
次に、postgres ユーザのパスワードを設定します。ユーザ名を指定しなければ自分のパスワードが設定されます。
postgres=# \password
新しいパスワードを入力してください: (パスワードを入力)
もう一度入力してください: (パスワードを再入力)
パスワードの設定は CREATE ROLE や ALTER ROLE 文でもできますが、パスワードが画面に表示されてしまい、設定によってはログにも出力されてしまうので、\password コマンドを使ったほうがいいでしょう。
接続用データベースの作成
リモートホストからの接続先はデフォルトで存在する template1 や postgres データベースにできなくもないですが、template1 データベースは新たにデータベースを作成するときのデフォルトのテンプレートで、postgres データベースもアプリケーションのデフォルトの接続先を意図したものなので、いずれも通常のデータベースとして使うのにはよくありません。ここでは、ユーザと同じく接続用データベースを作成します。
CREATE DATABASE 文で test ユーザを所有者にしてユーザ名と同じ test という名前のデータベースを作成します。データベースの一覧は \l コマンドで確認できます。
postgres=# CREATE DATABASE test OWNER test;
CREATE DATABASE
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
test | test | UTF8 | C | C |
(4 行)
public スキーマの設定
スキーマとはデータベース内の名前空間のことで、デフォルトで public という名前のスキーマが存在します。
public スキーマはデフォルトですべてのユーザがデータベースオブジェクトを作成でき、search_path パラメータで指定されるスキーマの検索パスにも含まれています。そのため、やろうと思えば public スキーマにデフォルトで存在する関数名と同じ名前の関数を作成し、ほかのユーザに実行させたりできてしまいます。それを防ぐ方法はいくつかありますが、ここでは、public スキーマから不要な権限を取り除き、スキーマの検索パスで public スキーマより優先されるユーザ名と同じ名前のスキーマを作成します。
\c コマンドで test データベースに接続しなおし、REVOKE ROLE 文で public スキーマからすべてのユーザのデータベースオブジェクト作成権限を取り除き、CREATE SCHMEA 文で test ユーザを所有者としてユーザ名と同じ test という名前のスキーマを作成します。権限の情報を含む詳細なスキーマの一覧は \dn+ で確認できます。
postgres=# \c test
データベース "test" にユーザ "postgres" として接続しました。
test=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
test=# CREATE SCHEMA AUTHORIZATION test;
CREATE SCHEMA
test=# \dn+
スキーマ一覧
名前 | 所有者 | アクセス権限 | 説明
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =U/postgres |
test | test | |
(2 行)
test=# \q
Unix ドメインソケット作成先の設定
Unix ドメインソケットとは同じホスト上で動作するプロセス間の通信方法のことで、基本的に Linux などの Unix 系 OS のみで使えます。TCP/IP を使った接続をホスト接続と呼ぶのに対し、Unix ドメインソケットを使った接続をローカル接続と呼びます。
Unix ドメインソケットの作成先はデフォルトでは、パッケージでインストールした場合には /var/run/postgresql と /tmp ディレクトリ、ソースコードからインストールした場合には /tmp ディレクトリです。/tmp ディレクトリはすべてのユーザがファイルを作成できるので、やろうと思えばデータベースサーバの停止中に別のサーバを起動し、なりすますことができてしまいます。それを防ぐには Unix ドメインソケットの作成先を postgres ユーザのみが書き込み権限をもつディレクトリ、ここでは /var/run/postgresql ディレクトリに設定します。Windows では Unix ドメインソケットを使えないのでディレクトリの設定は必要ありません。
ソースコードからインストールした場合には、/var/run/postgresql ディレクトリが存在しないので、root ユーザに切り替わり、/var/run/postgresql ディレクトリを作成し、所有者を postgres ユーザに変更し、所有者以外の権限を取り除きます。
$ su -
パスワード: (パスワードを入力)
最終ログイン: 2019/07/26 (金) 11:25:37 JST日時 pts/0
# mkdir /var/run/postgresql
# chown postgres:postgres /var/run/postgresql
# chmod go-rwx /var/run/postgresql
# exit
ログアウト
psql などのクライアントプログラムはデフォルトでローカル接続を行いますが、ソースコードからインストールした場合には、/tmp ディレクトリ内の Unix ドメインソケットに接続しようとします。そのため、Unix ドメインソケットの作成先を変更した場合には、そのパスを -h または --host オプションで明示的に指定する必要があります。その都度、オプションを指定するのが面倒であれば、~/.pgsql_profile ファイルで環境変数 PGHOST を設定するといいでしょう。
環境変数の設定は、通常、設定ファイル ~/.bash_profile で行いますが、postgres ユーザの ~/.bash_profile ファイルはパッケージのインストール時に上書きされてしまいます。そのため、~/.bash_profile ファイルから ~/.pgsql_profile ファイルを読み込むようになっており、環境変数の設定は ~/.pgsql_profile ファイルで行います。
export PGHOST=/var/run/postgresql
~/.pgsql_profile ファイルを編集し、すぐに設定を反映したければ、ファイルの再読み込みを行ってください。
$ . ~/.bash_profile
最後に、Unix ドメインソケットの作成先を unix_socket_directories パラメータで /var/run/postgresql のみに変更します。
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
# (change requires restart)
unix_socket_directories パラメータはコメントに (change requires restart) と書いてあるので、設定の反映にデータベースサーバの再起動が必要です。ここでは、あとでまとめて行うので省略します。
SSL による通信暗号化の設定
クライアントとデータベースサーバ間の通信はデフォルトでは平文で行われるので、やろうと思えばネットワーク上をやり取りされるパケットをキャプチャし、中身のデータを盗み見ることができてしまいます。それを防ぐには SSL で通信を暗号化するか、SSH などで暗号化したトンネルを介して通信します。ここでは、SSL による通信暗号化の設定を行います。
SSL (Secure Socket Layer) とはネットワーク上の通信を暗号化するプロトコルのことです。SSL を使うにはビルド時に有効にしておく必要があります。パッケージでインストールした場合には有効になっています。有効になっているかは pg_config コマンドでビルド時のオプションに --with-openssl が含まれているかを見れば分かります。
$ pg_config --configure | grep -o -- --with-openssl
--with-openssl
SSL による通信暗号化の設定を行うには、まず、データベースサーバの証明書と秘密鍵を作成します。
サーバの証明書と秘密鍵の作成には openssl コマンドを使います。ここでは、データベースクラスタ内にサーバの証明書 server.crt と秘密鍵 server.key を作成します。サーバ証明書は自己署名、有効期間は 3650 日 (約 10 年)、ホスト名は dbhost.example.com に指定しています。dbhost.example.com はデータベースサーバのホスト名に合わせて変更してください。サーバの証明書と秘密鍵の作成方法について詳しくは OpenSSL のドキュメントを参照してください。
$ openssl req -new -x509 -days 3650 -nodes -text -out $PGDATA/server.crt -keyout $PGDATA/server.key -subj /CN=dbhost.example.com
Generating a 2048 bit RSA private key
..................+++
......................+++
writing new private key to '/var/lib/pgsql/11/data/server.key'
-----
サーバ秘密鍵は所有者以外がアクセスできると使えないので、所有者以外の権限を取り除きます。
$ chmod go-rwx $PGDATA/server.key
ルート証明書は自己署名なのでサーバ証明書のコピーで済ませます。
$ cp $PGDATA/server.crt $PGDATA/root.crt
次に、SSL モードを有効にします。
SSL モードを有効にするには、postgresql.conf ファイル内の ssl パラメータを on に変更します。
ssl = on
ssl パラメータの設定を反映するには、設定ファイルの再読み込みが必要です。ここでは、あとでまとめて行うので省略します。
接続監視アドレスの設定
クライアントからの接続はデフォルトではローカルホスト宛てしか監視していないので、リモートホストからデータベースサーバのアドレス宛てには接続できません。ローカルホスト宛て以外の接続を監視するには、監視対象に宛て先のアドレスを設定します。
接続監視アドレスの設定は listen_addresses パラメータで行います。パラメータには接続先のホスト名または IP アドレスをカンマ区切りで指定します。デフォルトは localhost で、Unix ドメインソケットとローカルホスト宛ての接続を監視します。たまに勘違いしている人をみかけますが、接続元のアドレスを指定するパラメータではありません。どのクライアントからの接続を許可するかは後述の クライアント認証 で設定します。
複数のネットワークインタフェースを備えたホストでは、各インタフェースに割り当てられたアドレスを指定する代わりに、すべてのインタフェースを表す * を指定することもできますが、接続を受けつけるアドレスが限られるのであれば、必要なアドレスのみを指定したほうがいいでしょう。
ここでは、ホスト dbhost.example.com 宛てにも接続できるように、postgresql.conf ファイル内の listen_addresses パラメータに dbhost.example.com を追加します。dbhost.example.com はデータベースサーバのホスト名に合わせて変更してください。
listen_addresses = 'localhost,dbhost.example.com' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
listen_addresses パラメータの設定を反映するには、データベースサーバの再起動が必要です。ここでは、あとでまとめて行うので省略します。
クライアント認証の設定
クライアントからの接続はデフォルトではローカルホストからしか許可されていないので、リモートホストから接続できません。ローカルホスト以外からの接続を許可するには、クライアント認証の設定を行います。
クライアント認証の設定はデータベースクラスタ内の pg_hba.conf ファイルで行います。ファイル名に含まれる hba は host-based authentication の略で、ホストに基づく認証を表します。pg_hba.conf ファイルは 1 行につき 1 つレコードを書きます。postgresql.conf ファイルと同じく # から行末まではコメントを表します。ファイルの最初はコメントが続き、最後の数行がレコードです。
レコードは 1 つ以上の空白またはタブで区切られた複数のフィールドからなります。フィールドには接続の条件とその条件に一致した場合に適用する認証方式を指定します。認証はファイル内で最初に一致したレコードのみで行われます。一致するレコードがなければ接続は拒否されます。デフォルトではローカル接続に対してデータベースクラスタの作成時に指定した認証方式で認証を行う設定になっています。
ここでは、ローカル接続の認証方式を scram-sha-256 認証に変更し、ホストが直接接続しているサブネット内から test ユーザによる test データベースへの SSL によるホスト接続に対して同じく scram-sha-256 認証を行うレコードを追加します。
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
# Allow SSL connections from any address in any subnet that the server
# is directly connected to.
hostssl test test samenet scram-sha-256
1 つ目の TYPE フィールドには接続方式を指定します。local はローカル接続、host は SSL かどうかを問わないホスト接続、hostssl は SSL によるホスト接続を表します。
2 つ目の DATABASE フィールドには接続先のデータベース名をカンマ区切りで指定します。all はすべてのデータベース、replication はデータベース名でなく、レプリケーションの接続を表します。レプリケーションとはデータを別のノードに複製する機能のことです。
3 つ目の USER フィールドには接続を行うユーザ名をカンマ区切りで指定します。all はデータベースと同じくすべてのユーザを表します。
4 つ目の ADDRESS フィールドにはホスト接続の場合に接続元のアドレスをホスト名または IP アドレス範囲で指定します。samenet はホストが直接接続しているサブネット内のすべてのアドレスを表します。より安全を求めるなら接続を許可するクライアントのアドレスのみを指定したほうがいいでしょう。
5 つ目の METHOD フィールドには条件に一致した接続に適用する認証方式を指定します。scram-sha-256 は SCRAM-SHA-256 暗号化によるパスワード認証を表します。scram-sha-256 認証はバージョン 10 で追加された認証方式で、以前からある md5 認証よりも安全ですが、古いバージョンのクライアントでは対応していません。
クライアント認証の設定を反映するには、設定ファイルの再読み込みが必要です。ここでは、あとでまとめて行うので省略します。
ファイアウォールの設定
PostgreSQL のポート番号はデフォルトでは 5432 ですが、5432 ポートへのアクセスはデフォルトではファイアウォールで許可されていないので、リモートホストから接続できません。5432 ポートへのアクセスを許可するには、ファイアウォールの設定を行います。
まず、root ユーザに切り替わります。
$ su -
パスワード: (パスワードを入力)
最終ログイン: 2019/07/26 (金) 11:25:41 JST日時 pts/0
次に、5432 ポートへのアクセスを許可します。
# firewall-cmd --permanent --add-port=5432/tcp
success
# firewall-cmd --reload
success
# lokkit -p 5432:tcp
root ユーザからは exit コマンドで戻れますが、このあとの設定も root ユーザで行うので、戻らないでください。
データベースサーバの再起動
パラメータやクライアント認証の設定を反映するため、データベースサーバを再起動します。
# systemctl restart postgresql-11.service
# service postgresql-11 restart
postgresql-11 サービスを停止中: [ OK ]
postgresql-11 サービスを開始中: [ OK ]
これで、データベースサーバ側の設定は完了です。
クライアントの設定と接続確認
ここからはデータベースサーバへの接続を行うクライアント上での操作になります。
データベースサーバへの接続時にサーバが本物かどうかはデフォルトでは検査しません。そのため、やろうと思えばデータベースサーバの停止中に同じアドレスでサーバを起動し、なりすますことができてしまいます。それを防ぐにはサーバ証明書を検査するようにします。
サーバ証明書を検査するには、まず、ルート証明書をインストールします。ルート証明書は SSL による通信暗号化の設定 で作成した root.crt ファイルです。
$ mkdir ~/.postgresql
$ scp root@dbhost.example.com:/var/lib/pgsql/11/data/root.crt ~/.postgresql
root@dbhost.example.com's password: (パスワードを入力)
root.crt 100% 977 1.0KB/s 00:00
次に、サーバ証明書を検査するように指定し、データベースサーバに接続します。
データベースサーバへの接続時にどのように SSL 接続を行うかは sslmode 接続パラメータで指定します。デフォルトは prefer で、最初に SSL 接続を行い、失敗したら非 SSL 接続を行います。SSL 接続のみを行い、サーバ証明書を完全に検査するには verify-full を指定します。psql コマンドで接続パラメータを指定するには、接続先のデータベース名の代わりに 接続パラメータ名=値 を空白で区切った文字列を指定します。
$ psql -h dbhost.example.com -U test "dbname=test sslmode=verify-full"
ユーザ test のパスワード: (パスワードを入力)
psql (11.4)
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。
test=>
「SSL 接続」と出力されれば、SSL 接続が行えています。
その都度、接続文字列を指定するのが面倒であれば、~/.pgsql_profile ファイルで環境変数 PGSSLMODE を設定するといいでしょう。
export PGSSLMODE=verify-full
PostgreSQL にリモートホストから安全に接続するのに最低限必要な設定はこれで完了です。