DebianにPostgreSQLをインストールして外部から接続する方法
この記事では、さくらのVPS上のDebianにPostgreSQLをインストールして、Macから外部接続する方法を紹介します。
環境
- Debian GNU/Linux 11 bullseye (さくらのVPS)
- macOS Monterey 12.4
- PostgreSQL 14.3
手順
以下の流れで進めていきます。
- PostgreSQLのインストール
- 接続確認
- ユーザの作成
- 外部接続の設定
- 外部接続の確認
1. PostgreSQLのインストール
必要なパッケージのインストール
まずは、PostgreSQLのインストールに必要な以下のパッケージをインストールします。
以下のコマンドを実行してパッケージをインストール。
$ sudo apt install curl gnupg2 ca-certificates lsb-release
- パッケージの情報を表示:
apt show <パッケージ名>
- インストール済みパッケージの確認:
apt list --installed <パッケージ名>
署名鍵のインポート
次に、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
リポジトリの追加
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
PostgreSQLのインストール
以下のコマンドを実行してPostgreSQLをインストールします。
$ sudo apt update $ sudo apt install postgresql
上記の一連の流れについて、以下でより詳細に解説しています。
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の基本的な操作方法は以下を参照してください。
3. ユーザの作成
次に、外部接続で使用する、DB操作用のユーザを作成します。
以下のコマンドを実行して、ユーザを作成します。 ユーザ名には、OS管理ユーザ(現在ログインしているユーザ)の名前を入力します。
postgres=# CREATE USER <ユーザ名> SUPERUSER PASSWORD '<パスワード>'; CREATE ROLE
4. 外部接続の設定
次に、外部接続の設定を行います。
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 = '*'
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
5. 外部接続の確認
最後に、外部接続の確認を行います。
Macで以下のコマンドを実行します。
$ psql -U <ユーザ名> -d <データベース名> -h <ホスト名>
<ユーザ名>に先程作成したDB操作用のユーザ名、<データベース名>にpostgres、<ホスト名>にさくらのVPSのホスト名(またはIPアドレス)を入力します。
以上で終了です。
【参考】
手順の参考
インストール
postgresql.confの設定
pg_hba.confの設定
peer認証
IPアドレスについて
【メモ】
Linuxユーザ確認
- 一覧確認:
cat /etc/passwd
- 現在のユーザ確認:
whoami
- ユーザ切り替え:
su - <ユーザ名>
- 一覧確認:
SSH接続時のグローバルIPアドレスの確認方法
プライベートIPアドレスの確認方法
- 方法1:Macでネットワークインターフェイスに割り当てられたIPアドレスを調べる方法 - Qiita
- 方法2:「システム環境設定」>「ネットワーク」>「詳細」>「TCP/IP」>「IPv4アドレス」