CentOSへのPostgreSQLのインストールと初期設定

# CentOSへのPostgreSQLのインストールと初期設定

## 目次
1. 前提
1. インストール及び初期設定
1. peer認証の解除
1. psqlでの接続
1. 他マシンからの接続(SSHポートフォワーディング利用)
1. 他マシンからの接続(ファイアウォールに穴を開ける)

## 前提
* 以下は、VirtualBoxにインストールしたCentOS7の環境で確認したもの。
   * 具体的には、[この手順](https://qiita.com/9ryuuuuu/items/c604d8d086f62aaf2362)で作成した環境下で行った。

## インストール及び初期設定

* コマンドの取得
   * [公式のdownloadページ](https://www.postgresql.org/download/linux/redhat/)に、インストール先情報を入力すると、インストールコマンドが取得できる。
      * 設定した項目は以下
         * Select Version: 9.6
         * Select platform: CentOS 7
         * Select architecture: x86_64

   * 取得したコマンド

      ```Bash
      # Install the repository RPM:
      yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

      # Install the client packages:
      yum install postgresql96

      # Optionally install the server packages:
      yum install postgresql96-server

      # Optionally initialize the database and enable automatic start:
      /usr/pgsql-9.6/bin/postgresql96-setup initdb
      systemctl enable postgresql-9.6
      systemctl start postgresql-9.6
      ```

* コマンドの実行
   * 上記コマンドを順に実行する(途中、なにか聞かれたらYと答える)

* postgresql-9.6 サービスが起動していることを確認する。

   ```
   # systemctl status postgresql-9.6
   Active: active (running)
   ```

## peer認証の解除

* 概要
   * 最初はpeer認証になっている。
   * CentOS上のユーザー名、パスワードの組み合わせが、PostgreSQL上のユーザー、パスワードの組み合わせでないとPostgreSQLに接続できない。
   * 使いづらいので、普通にパスワード認証すれば接続できるようにする。

* 設定ファイルの編集

   * pg_hba.confを編集する。
   * ファイルの場所や説明は[公式](https://www.postgresql.jp/document/9.0/html/runtime-config-file-locations.html)を参照。
   * local接続のpeer認証をmd5に変更する。

   ```
   vi /var/lib/pgsql/9.6/data/pg_hba.conf

   # "local" is for Unix domain socket connections only
   local   all             all                                     peer
   ↓
   # "local" is for Unix domain socket connections only
   local   all             all                                     md5
   ```

* postgresユーザーのパスワード設定
   * 変更を反映する前に、postgresユーザーにパスワードを設定する。

   * postgresユーザーでpsqlに接続。

      ```
      # su - postgres
      $ psql -U postgres
      ```

   * psqlに入ったら、パスワード変更

      ```
      postgres=# \password
         Enter new password:
         Enter it again:
      postgres=# \q

      $ exit
      ```

* サービスを再起動して、pg_hba.confの設定を反映させる。

   ```
   # systemctl restart postgresql-9.6
   ```

* パスワード認証で接続できるようになる

   ```
   # psql -U postgres
   Password for user postgres:
   ```

## 他マシンからの接続(SSHポートフォワーディング利用)

* listenするホストの設定

   ```Bash
   vi /var/lib/pgsql/9.6/data/postgresql.conf

   # listen_addresses = 'localhost'     # what IP address(es) to listen on;
   # port = 5432                        # (change requires restart)
   ↓
   listen_addresses = '*'     # what IP address(es) to listen on;
   port = 5432                        # (change requires restart)
   ```

* 接続を許可するホストの設定

   ```Bash
   vi /var/lib/pgsql/9.6/data/postgresql.conf

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            ident
   ↓
   # IPv4 local connections:
   host    all             all             0.0.0.0/0               md5
   ```

* sshポートフォワーディングを利用して別のマシンから接続

   * ポートフォワーディング
      * OpenSSHが使用できる前提

      ```PowerShell
      ssh -N -L 15432:192.168.56.99:5432 root@192.168.56.99
      ```

   * ポートフォワーディングしたマシンから接続

      ```
      psql -U postgres -h localhost -p 15432
      ```

## 他マシンからの接続(ファイアウォールに穴を開ける)

* publicゾーンに永久的に登録しているサービスの確認

   ```
   # firewall-cmd --zone=public --list-services --permanent
   ssh dhcpv6-client
   ```

* ファイアウォールに登録できるサービスの確認

   ```
   # firewall-cmd --get-services

   pop3 pop3s postgresql privoxy
   ```

* PostgreSQLを追加

   ```
   # firewall-cmd --add-service=postgresql --zone=public --permanent
   success
   ```

* サービスが登録されたことを確認

   ```
   # firewall-cmd --zone=public --list-services --permanent
   ssh dhcpv6-client postgresql
   ```

* ファイアウォールを再起動

   ```
   # systemctl restart firewalld
   ```

* 他マシンからpsqlで接続できるようになる。

   ```PowerShell
   psql -U postgres -h 192.168.56.99 -p 5432
   ```

* ポートを変更したいときは/usr/lib/firewalld/services/postgresql.xmlを編集して、別の場所に置く。

   ```
   cp /usr/lib/firewalld/services/postgresql.xml /etc/firewalld/services/postgresql.xml
   vi /etc/firewalld/services/postgresql.xml

   <?xml version="1.0" encoding="utf-8"?>
   <service>
   <short>PostgreSQL</short>
   <description>PostgreSQL Database Server</description>
   <port protocol="tcp" port="5432"/>
   </service>
   ↓
   <?xml version="1.0" encoding="utf-8"?>
   <service>
   <short>PostgreSQL</short>
   <description>PostgreSQL Database Server</description>
   <port protocol="tcp" port="<変更後のポート番号>"/>
   </service>
   ```