PostgreSQLでクラサバDB環境構築

# 作成する環境
以下のような環境を構築します。
![output.png](https://qiita-image-store.s3.amazonaws.com/0/89391/0103c74a-89fe-0b3c-2a6e-bbbf0fa91d5b.png)


- サーバ 
CentOS 7.2
PostgreSQL 9.2.15

- クライアント 
Windows7
psqledit_4

## サーバ設定
### PostgreSQLのインストール～サービスの起動

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

```txt:yumでパッケージをインストール
[root@sample ~]# yum -y install postgresql postgresql-contrib postgresql-devel
```

```txt:DBのセットアップ
[root@sample ~]# /usr/bin/postgresql-setup initdb
```


```txt:サービスの起動
[root@sample ~]# service postgresql start
[root@sample ~]# service postgresql status
[root@sample ~]# systemctl enable postgresql
```


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

| USER | PASSWORD | DB_NAME |
|:------------:|:------------:|:------------:|
|     sampleuser     |     samplepassword     |     sampledb     |

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

```txt: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
```

```txt:sampleuserをownerとしたデータベースを作成
[root@sample ~]# su sampleuser
[sampleuser@sample ~]$ createdb sampledb;
[sampleuser@sample ~]$
```

```txt:ローカルでの接続確認
[sampleuser@sample ~]$ psql -U sampleuser sampledb
sampledb=> 
```

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

### 認証/アクセス制御
必要なのは、次の3つのレイヤ観点からの設定です

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

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

```txt: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 からの通信

```txt:[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 を参照ください

```txt:[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
```

```txt:修正内容の反映(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_connetion.png](https://qiita-image-store.s3.amazonaws.com/0/89391/ffcdd078-6e99-29c4-5777-5c7735f19cab.png)

なお、psqledit.exe実行時に以下のように表示された場合、
http://www.hi-ho.ne.jp/a_ogawa/libpq/index.htm からlibpq.dllをダウンロードしてpsqledit.exeと同じ階層に配備します
![libpg_dll.png](https://qiita-image-store.s3.amazonaws.com/0/89391/60cfede9-7591-5603-d034-70d5de81811e.png)


以下のようになれば、クライアントからのサーバ接続は完了です
![connention_success.png](https://qiita-image-store.s3.amazonaws.com/0/89391/5b7d1f59-66c0-1e78-f808-bcbc08efb19d.png)