PostgreSQLでクラサバDB環境構築

作成する環境

以下のような環境を構築します。

• サーバ
  CentOS 7.2
  PostgreSQL 9.2.15

• クライアント
  Windows7
  psqledit_4

サーバ設定

PostgreSQLのインストール～サービスの起動

※リポジトリによっては最新のバージョンではないですが、今回はよいものとします
※最新バージョンを使用する場合は公式サイトからダウンロードします
※https://www.postgresql.jp/download

yumでパッケージをインストール

[root@sample ~]# yum -y install postgresql postgresql-contrib postgresql-devel

DBのセットアップ

[root@sample ~]# /usr/bin/postgresql-setup initdb

サービスの起動

[root@sample ~]# service postgresql start
[root@sample ~]# service postgresql status
[root@sample ~]# systemctl enable postgresql

ユーザ/データベースの作成

以下のようにユーザとデータベースを作成します

USER	PASSWORD	DB_NAME
sampleuser	samplepassword	sampledb

Linuxのユーザ作成

[root@sample ~]# adduser sampleuser
[root@sample ~]# passwd sampleuser
ユーザー sampleuser のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました

PostgreSQLのユーザ作成

[root@sample ~]# su postgres
bash-4.2$ psql
psql (9.2.15)
"help" でヘルプを表示します.

postgres=# CREATE USER "sampleuser" CREATEDB PASSWORD 'samplepassword' LOGIN;
CREATE ROLE
postgres=# \q
bash-4.2$ exit

sampleuserをownerとしたデータベースを作成

[root@sample ~]# su sampleuser
[sampleuser@sample ~]$ createdb sampledb;
[sampleuser@sample ~]$

ローカルでの接続確認

[sampleuser@sample ~]$ psql -U sampleuser sampledb
sampledb=> 

以上でローカル環境でのPostgreSQLのセットアップが完了します
次は、リモートで接続を許可するための設定をします

認証/アクセス制御

必要なのは、次の3つのレイヤ観点からの設定です

1. OS
2. PostgreSQLサービス
3. DBへのユーザ認証

1-1. OS
リモートからPostgreSQLサーバにアクセスするためには、TCP:5432番ポートの通信を許可する必要があります
iptablesに穴をあけます

5432番ポートの開放

[root@sample ~]# firewall-cmd --permanent --add-port=5432/tcp
[root@sample ~]# firewall-cmd --reload
[root@sample ~]# iptables -L -n | grep 5432

2-1. PostgreSQLサービス
PostgreSQLサービスにアクセス設定は[ postgresql.conf ]に記載されています
listen_addresses でアクセスを許可するIPアドレスを指定できます
listen_addresses = '*'とすることですべてのネットワークからのアクセスが可能になります
※今回の場合は192.168.1.0/24 からの通信

[postgresql.conf]の修正

[root@sample ~]# vim /var/lib/pgsql/data/postgresql.conf

\#listen_addresses = 'localhost'
listen_addresses = '*'

3-1. DBへのユーザ認証
DBへのリモートホストからの認証は[ pg_hba.conf ]に記載されています
細かいパラメタの説明については今回は省きます
https://www.postgresql.jp/document/9.5/html/auth-pg-hba-conf.html を参照ください

[pg_hba.conf]の修正

[root@sample ~]# vim /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sampledb        sampleuser      192.168.1.0/24          md5

修正内容の反映(PostgerSQLサービスの再起動)

[root@sample ~]# service postgresql restart
Redirecting to /bin/systemctl restart  postgresql.service
[root@sample ~]# service postgresql status

以上で、リモートからPostgreSQLに接続する準備が整いました

クライアント設定

今度は、クライアント環境からリモートで接続できるか確認していきます
PostgreSQLへ接続するためのクライアントツールとしては、

• pgAdmin
• PSqlEdit

等が考えられます。クライアントツールとしては機能がシンプルなPSqlEditが使いやすいと思います。(あくまで主観です)

http://www.hi-ho.ne.jp/a_ogawa/psqledit/psqledit_4010.zip からダウンロードできます
適当に C:\Program Files\psqledit_4 と解凍し、psqledit.exeを実行します

USER	PASSWORD	DB_NAME	HOST	PORT NO
sampleuser	samplepassword	sampledb	192.168.1.10	5432

なお、psqledit.exe実行時に以下のように表示された場合、
http://www.hi-ho.ne.jp/a_ogawa/libpq/index.htm からlibpq.dllをダウンロードしてpsqledit.exeと同じ階層に配備します

以下のようになれば、クライアントからのサーバ接続は完了です