この記事では、さくらのVPS上のDebianにPostgreSQLをインストールして、Macから外部接続する方法を紹介します。

1. 環境

  • Debian GNU/Linux 11 bullseye (さくらのVPS)
  • macOS Monterey 12.4
  • PostgreSQL 14.3

2. 手順

以下の流れで進めていきます。

  1. PostgreSQLのインストール
  2. 接続確認
  3. ユーザの作成
  4. 外部接続の設定
  5. 外部接続の確認

2-1. PostgreSQLのインストール

2-1-1. 必要なパッケージのインストール

まずは、PostgreSQLのインストールに必要な以下のパッケージをインストールします。

以下のコマンドを実行してパッケージをインストール。

$ sudo apt install curl gnupg2 ca-certificates lsb-release
  • パッケージの情報を表示：apt show <パッケージ名>
  • インストール済みパッケージの確認：apt list --installed <パッケージ名>

2-1-2. 署名鍵のインポート

次に、aptがパッケージの信頼性を確認できるようにするために、PostgreSQLが公式に公開している署名鍵をインポートします。

以下のコマンドを実行して署名鍵をインポート。

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null

2-1-3. リポジトリの追加

aptの設定ファイルにPostgreSQLの公式リポジトリを追加します。

以下のコマンドを実行して /etc/apt/sources.list.d 配下に設定ファイルを作成します。

$ echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

作成された /etc/apt/sources.list.d/pgdg.list の内容は以下のようになります。

deb http://apt.postgresql.org/pub/repos/apt bullseye-pgdg main

2-1-4. PostgreSQLのインストール

以下のコマンドを実行してPostgreSQLをインストールします。

$ sudo apt update
$ sudo apt install postgresql

上記の一連の流れについて、以下でより詳細に解説しています。

【Debian】Nginx公式リポジトリにある最新バージョンのNginxをインストールする | あまブログ

2-2. 接続確認

次に、データベースへの接続確認を行います。

PostgreSQLをインストールするとpostgresユーザが自動で作成されます。

以下では、postgresユーザでデータベースへの接続確認を行います。

まずは、postgresユーザが作成されていることを確認。

$ cat /etc/passwd | grep postgres

次に、postgresユーザにパスワードを設定します。

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

postgresユーザに切り替え。(先程設定したパスワードを入力。)

$ su - postgres

postgresユーザに切り替わっていることを確認し、以下のコマンドを実行してデータベースに接続します。

postgres@ ~$ psql

データベースに接続できました。

postgres=# SELECT current_database();
 current_database
------------------
 postgres
(1 row)

上記ではPostgreSQLインストール時に自動生成されるpostgresデータベースに接続しています。

PostgreSQLの基本的な操作方法は以下を参照してください。

【macOS】PostgreSQLの基本操作 | あまブログ

2-3. ユーザの作成

次に、外部接続で使用する、DB操作用のユーザを作成します。

以下のコマンドを実行して、ユーザを作成します。 ユーザ名には、OS管理ユーザ(現在ログインしているユーザ)の名前を入力します。

postgres=# CREATE USER <ユーザ名> SUPERUSER PASSWORD '<パスワード>';
CREATE ROLE

2-4. 外部接続の設定

次に、外部接続の設定を行います。

2-4-1. postgresql.confの編集

以下のコマンドを実行して、/etc/postgresql/14/main/postgresql.conf を編集します。

$ sudo vi /etc/postgresql/14/main/postgresql.conf

編集前(60行目あたり)

#listen_addresses = 'localhost'		# what IP address(es) to listen on;

編集後(60行目あたり)

#listen_addresses = 'localhost'		# what IP address(es) to listen on;
listen_addresses = '*'

2-4-2. pg_hba.confの編集

以下のコマンドを実行して、/etc/postgresql/14/main/pg_hba.conf を編集します。(hba：host-based authentication)

$ sudo vi /etc/postgresql/14/main/pg_hba.conf

編集前(96行目あたり)

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

編集後(96行目あたり)

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             <グローバルIPアドレス>/32            scram-sha-256

<グローバルIPアドレス>にはMacのグローバルIPアドレスをIPv4形式で入力します。

グローバルIPアドレスの確認

$ curl https://ifconfig.me
XXX.XXX.XXX.XXX

postgresql.conf と pg_hba.conf の編集後、PostgreSQLを再起動して設定の変更を反映させます。

$ sudo systemctl restart postgresql

2-5. 外部接続の確認

最後に、外部接続の確認を行います。

Macで以下のコマンドを実行します。

$ psql -U <ユーザ名> -d <データベース名> -h <ホスト名>

<ユーザ名>に先程作成したDB操作用のユーザ名、<データベース名>にpostgres、<ホスト名>にさくらのVPSのホスト名(またはIPアドレス)を入力します。

以上で終了です。

【参考】

【メモ】