DebianにPostgreSQLをインストールして外部から接続する

はじめに

DebianにPostgreSQLをインストールして、外部接続の設定を行うまでの手順を記録。

環境

• Debian GNU/Linux 10（さくらVPS）
• macOS Big Sur 11.4
• PostgreSQL 11.12

手順

DebianにPostgreSQLをインストール

1. aptでインストール

$ sudo apt update
$ sudo apt install postgresql

2. インストールされていることを確認

$ psql --version
psql (PostgreSQL) 11.12 (Debian 11.12-0+deb10u1)

バージョン11.12がインストールされた。

3. postgresユーザーのパスワードを設定

PostgreSQLをインストールすると、ユーザーpostgresが自動的に作成されるが、このユーザーにはパスワードが設定されていないので、パスワードを設定する。

$ sudo passwd postgres
New password:
Retype new password:
passwd: password updated successfully

パスワードを2回入力してsuccessfullyと表示されればOK

4. DB操作用のユーザーを追加

postgresユーザーでDB操作はできるが、普段使っているユーザーでもDB操作ができるようする。

ユーザーをpostgresに切り替える。

$ su - postgres
Password:
postgres@ ~$ # postgresユーザーに切り替わっている

以下のコマンドでユーザーを追加する。ここでは仮にsample1というユーザー名にする

postgres@ $ createuser --pwprompt --interactive sample1

✍createuser
新しいPostgreSQLのユーザーアカウント定義するコマンド。

✍--pwprompt
createuserコマンドのオプション。このコマンドが指定されると、createuserは新しいユーザーのパスワードのプロンプトを表示する。もし新規ユーザーのパスワード認証をする予定がなければこれは必要なし。

新規ユーザーのパスワードを設定する。

Enter password for new role:
Enter it again:

パスワードの設定後、スーパーユーザーにするか聞かれるので、yesと回答。

Shall the new role be a superuser? (y/n) y

これで、PostgreSQLのインストールおよびユーザーの作成が完了。

外部接続の設定

続いて、外部接続を許可するための設定を行っていく。

1. postgresql.confを開く

postgres@ $ vim /etc/postgresql/11/main/postgresql.conf

✍postgresql.confは、postgreSQLの基本的な設定ファイル

59行目あたりにある

# listen_addresses='localhost'

のコメントアウトを外し、

listen_addresses='*'

に書き換える。

2. pg_hba.confを開く

postgres@ $ vim /etc/postgresql/11/main/pg_hba.conf

✍pg_hba.confはPostgreSQLに接続するクライアントの認証に関する設定ファイル。クライアントのアドレスとロール名を指定してどのデータベースに対して接続を許可するのかを設定するために使用する。
※参考：PostgreSQL | pg_hba.confファイルの設定方法

90行目あたりにある

# IPv4 local connections:
host    all             all             IPアドレス            md5

の下に、

host    all             all             自分のMacIPアドレス/0            md5

を追記する。

なお、MacのIPアドレスは下記のサイトなどで確認できる。
アクセス情報【使用中のIPアドレス確認】

また、IPアドレスの末尾にある/0という表記はCIDR表記といってサブネットマスクを表している。Macから接続する場合は、おそらく/0で問題ないと思う。
※参考：IPアドレスのあとにあるスラッシュと数の意味 - ひこたろうのシンプルライフ

3. 最初のユーザーに戻って、PostgreSQLを再起動する

postgres $ exit
$ sudo /etc/init.d/postgres restart

これで外部接続の許可設定は完了。

MacにPostgreSQLをインストールする

外部接続を行う前に、Macはデフォルトでpsqlが使えるようになっていないので、MacにもPostgreSQLをインストールする必要がある。

1. homebrewでインストール

# まずはhomebrewをアップデート
$ brew update
# PostgresSQLをインストール
$ brew install postgresql

2. 自動起動の設定をする

$ brew services start postgres

これによって、PostgreSQLが起動し、MacOSの再起動〜ログインした際にもPostgreSQLが自動で起動する設定になる。

3. 外部接続をする

先ほどDebian側で作成したユーザーsample1で、MacからDebian上のPostgreSQLに外部接続ができるか確認する。

$ psql -U sample1 -d postgres -h <ホスト名>

※ホスト名は、Debianをインストールしているサーバーのホスト名。さくらVPSの場合は管理画面から確認できる。

パスワードが求められるので入力してEnter。

Password for user sample1:

プロンプトがpostgres=#に変われば外部接続は成功。

psql (13.4, server 11.12 (Debian 11.12-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

参考URL

• Debian 10にPostgreSQLをインストールして外部接続を許可する - Qiita
• debianにpostgresqlをインストール - メモ日記
• createuser
• [macOS High Sierra][Homebrew] PostgreSQL のインストールからDB作成まで - Qiita
• 環境構築：Debian 10(buster) にPostgreSQL 11をインストールし、新規DBを作成する方法