ようへいの日々精進XP

よかろうもん

【俺の一行チップス】PostgreSQL 9.x で localhost 以外からデータベース接続する

やりたいこと

PostgreSQL 9.x において, localhost 以外からデータベース接続を設定する機会があったので, あらためて手順を整理しておく.

諸注意

動作確認環境は以下の通り.

# PostgreSQL サーバー側 (Ubuntu 上にパッケージで PostgreSQL をインストール)
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"

$ /usr/lib/postgresql/9.6/bin/postgres --version
postgres (PostgreSQL) 9.6.16

# psql 側
$ docker-compose exec postgresql_client psql --version
psql (PostgreSQL) 9.6.16

やったこと

環境の構築とか

環境は docker-compose で用意する前提.

version: '3'

services:
  postgresql_server:
    image: ubuntu:latest
    container_name: postgresql-server
    ports:
      - "5432:5432"
    networks:
      my_net:
        ipv4_address: 10.0.101.10
    command: tail -f /dev/null

  postgresql_client:
    image: postgres:9
    container_name: postgresql-client
    networks:
      my_net:
        ipv4_address: 10.0.101.11

networks:
  my_net:
    driver: bridge
    ipam:
     driver: default
     config:
       - subnet: 10.0.101.0/24

docker-compose up -d で起動した後, postgrsql-serverdocker exec でアクセスして以下のコマンドを流した. とりあえず, 流したコマンドをダイジェストで.

# Install dependency package
sudo apt-get update && apt-get install gnupg gnupg2 wget sudo net-tools -y
# Add repository
echo 'deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main' >> /etc/apt/sources.list.d/pgdg.list
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
apt-get install postgresql-9.6 -y
service postgresql status
service postgresql start

PostgreSQL が起動したら, pgadmin というユーザーを作成する図.

# sudo su - postgres
$ createuser --pwprompt --interactive pgadmin
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

インストール直後の状態

以下のように localhost からか, UNIX ドメインソケットからのみ接続を受け付ける設定になっている.

# sudo su - postgres
$ netstat -an| grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     134429   /var/run/postgresql/.s.PGSQL.5432

listen_addresses の変更

デフォルトでは, listen_addresses というパラメータは以下のようにコメントアウトされている.

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

このパラメータについて, コメントに書かれているように値を * に変更する.

listen_addresses = '*'

listen_addresses について, ドキュメントより引用.

クライアントアプリケーションからの接続をサーバが監視する TCP/IP アドレスを指定します。 この値は、ホスト名をコンマで区切ったリスト、そして/もしくは、数値によるIPアドレスです。 *という特別なエントリは利用可能な全てのIPインタフェースに対応します。

pg_hba.conf ファイル

pg_hba.conf の hba とは Host Based Authentication の略でホストベース認証の設定ファイル. 今回は以下のように追記した.

$ diff -u pg_hba.conf.bk pg_hba.conf
--- pg_hba.conf.bk      2020-01-26 09:06:43.328190906 +0900
+++ pg_hba.conf 2020-01-26 09:50:43.414152960 +0900
@@ -90,6 +90,7 @@
 local   all             all                                     peer
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            md5
+host    all             all             10.0.101.0/24            md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5
 # Allow replication connections from localhost, by a user with the
  • hostTCP/IP を使用した接続に対応
  • all は全てのデータベースに適用
  • 次の all は全てのユーザーに適用
  • 10.0.101.0/24 はクライアントのアドレス範囲
  • md5 はクライアントに対して認証時に二重 MD5 ハッシュ化パスワードを要求

pg_hba.conf の書式については, ドキュメントより引用.

pg_hba.confファイルの一般的な書式は、1行につき1つのレコードというレコードの集合です。 空行はコメント用の#文字以降の文字と同じく無視されます。 レコードは行をまたいで続けることはできません。 レコードはスペースもしくはタブ、もしくはその両方で区切られた、複数のフィールドで構成されています。 フィールドには、フィールド値が二重引用符付きの場合空白文字を含むことができます。 データベース、ユーザもしくはアドレスフィールド内のキーワード(例:allまたはreplication)の一つを引用するとその特別な意味が失われ、その名称のデータベース、ユーザもしくはホストと一致するようになります。

プロセスの再起動

$ sudo service postgresql restart
 * Restarting PostgreSQL 9.6 database server

接続確認

Docker が起動している macOS 側から以下のように接続してみる.

$ docker-compose exec postgresql_client psql -U pgadmin -h 10.0.101.10 -d postgres
Password for user pgadmin:
psql (9.6.16)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

やったー, 接続出来た!

参考

www.postgresql.jp

www.postgresql.jp

(完)