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=#
Discussion