開発用PostgreSQLをインストールした後の外部接続、ユーザ作成、認証周りの設定

ローカル開発用にPostgreSQLを入れた後、開発や分析をし易くする為に外部ホストからパスワード付き接続出来るようにしていきます。

また、postgresスーパーユーザは権限が強すぎて開発アプリからの接続ユーザとして使うのは危険です。最低限の権限の一般ユーザも一緒に作っていきます。

1 環境
2 １．PostgreSQLサーバを外部公開
3 ２．一般作業ユーザを作成
4 ３．外部からのdevelユーザ接続をmd5認証に変更
5 接続確認
- 5.1 ローカルホストから接続
- 5.2 リモートホストから接続
6 まとめ

環境

CentOS 7.6
PostgreSQL 11

PostgreSQLは以下の記事で入れてある状態です。未インストールの場合はご参照ください。

One IT Thing

開発用PostgreSQLをCentOSにインストールしてSQLを流すまで

https://one-it-thing.com/4944

開発や勉強に使う為のPostgreSQLをCentOSにインストールします。インストールから基本操作を体験して、0からデータベースを構築する経験を積んでおきます。環境CentOS 7.6PostgreSQL 11.4インストール手順を公式サイトで確認公式サイトから「Download」→「CentOS/Fedora/Scientific/Oracle」リンクをクリックしてOSを選択すると条件別インストール手順にたどり着けるのでこの手順に従います。https://www.postgresql.org/download/linux/redhat/Postgresql公式yumリポジトリを追加公式手順に従ってyumリポジトリ定義ファイルをインス...

１．PostgreSQLサーバを外部公開

インストール直後はインストールしたホストからしか接続出来ないので、外部からTCP:5432ポートで接続出来るようにしておきます。
（#プロンプトはrootユーザで作業します）

# vi /var/lib/pgsql/11/data/postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;

firewall-cmdで5432ポートを開け、

# firewall-cmd --add-port=5432/tcp --zone=public --permanent
success
# firewall-cmd --reload
success

サーバを再起動して外部へ公開。

# systemctl restart postgresql-11

A5 Mk2、jailer、Metabaseのようなツールで外部から接続出来るようにしておくことで開発作業を円滑に進められます。

サーバは外部公開出来ましたが、まだ肝心の外部から接続出来るユーザが居ません。以下で作っていきます。

２．一般作業ユーザを作成

最初から居るpostgresスーパーユーザは権力が強すぎるので、開発プログラムやコンソール、外部ホストから接続する為の作業用ユーザを一人作ります。（名前は任意で。ここでは仮にdevelユーザとします）

一般作業ユーザの認証方法は以下の2通りにします。

PostgreSQLが入ったローカルホストからはOSユーザなら接続出来るpeer認証
外部ホストからはパスワードが必要なmd5認証

まずはローカルホストからpeer認証で接続出来るようにdevelというOSユーザをrootユーザで作成。ローカル開発ユーザなのでパスワードも適当にユーザ名と同じにしておきます。

（怒られますが作れます。複雑なパスワードは本番環境で設定すればOKです）

# adduser devel
# passwd devel
ユーザー devel のパスワードを変更。
新しいパスワード: [develと入力]
よくないパスワード: このパスワードは 8 未満の文字列です。
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。

sshやコンソールからdevelユーザ、develパスワードでCentOSにログイン出来るようになりました。

続いてPostgreSQL内にもdevelユーザを作ります。パスワードは気持ちセキュリティ強めでdevel#123にしてみます。

-bash-4.2$ /usr/pgsql-11/bin/psql

postgres=# CREATE ROLE devel LOGIN PASSWORD 'devel#123';
CREATE ROLE

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO devel;
GRANT

psqlコマンドに-dでデータベースを指定しなかった場合、ユーザ名と同じデータベースに接続されるので、develデータベースを作っておきます。

postgres=# CREATE DATABASE devel OWNER devel;
CREATE DATABASE

他にも一般作業ユーザに管理させたいデータベースがあれば作成しておきます。

postgres=# CREATE DATABASE dvdrental OWNER devel;
CREATE DATABASE

３．外部からのdevelユーザ接続をmd5認証に変更

インストール直後はOSユーザしかPostgreSQLにログイン出来ないpeer認証のみ。外部からpsqlコマンドやJDBCでログイン出来るようにパスワード認証にしておきます。

/var/lib/pgsql/11/data/pg_hba.confを編集してdevelユーザの外部ホスト認証方法をmd5認証に変更。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all            all                                    peer
# ↓↓↓↓↓　追加　↓↓↓↓↓
host    all            devel         192.168.0.0/24           md5

192.168.0セグメントの外部ホストからdevelユーザならパスワード有りで接続可能、ローカルホストならpeer認証でデフォルト通りパスワード無し接続が可能な設定です。

設定をPostgreSQLに読み込ませて反映します。
restartではなくreloadで再起動せずに設定ファイルを再読み込みできます。
（restartでも問題無いです）

# systemctl reload postgresql-11

外部から接続する為の設定完了です。

接続確認

ローカルホストから接続

PostgreSQLが入ったローカルホストからならpostgres、devel両ユーザはパスワード無しで接続可能。

[root@spock ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。

postgres=#

[root@spock ~]# su - devel
[devel@spock ~]$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。

devel=>

リモートホストから接続

Windows10からDBクライアント神ツール「A5 Mk2」で接続してみます。

a5m2.mmatsubara.com

A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール .. 松原正和

https://a5m2.mmatsubara.com

サーバ―名：PostgreSQLが入っているホストIP
データベース名：devel
ユーザーID：devel
パスワード：devel#123

接続が出来れば外部ホストで動くプログラムから接続が出来る開発用PostgreSQL環境の完成です。

まとめ

本番環境のPostgreSQLにはAPサーバのような特定ホストIPからしか繋げず、接続ユーザのパスワードも複雑なものを設定しますが、開発環境が

development（開発用）
staging（本番想定テスト用）
production（本番）

と分かれているなら、development環境はこれくらいの緩め設定にしておき、各開発者PCからの接続や、BIツールでの分析を色々やれるようにした方が開発が円滑に進みます。