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

目次

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

前提

• 以下は、VirtualBoxにインストールしたCentOS7の環境で確認したもの。
  • 具体的には、この手順で作成した環境下で行った。

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

• コマンドの取得

  • 公式のdownloadページに、インストール先情報を入力すると、インストールコマンドが取得できる。

    • 設定した項目は以下
      • Select Version: 9.6
      • Select platform: CentOS 7
      • Select architecture: x86_64

  • 取得したコマンド

    # 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を編集する。
  • ファイルの場所や説明は公式を参照。
  • 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するホストの設定

  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)
  

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

  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が使用できる前提

    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で接続できるようになる。

  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>