PostgreSQL (Ubuntuホスト) に外部からアクセス
Ubuntu に PostgreSQL を入れてみました にメモしたとおり Ubuntu Desktop 15.10 に PostgreSQL サーバを立ち上げたわけですが、そこに他のマシンからアクセスしました。
他のマシンは、同じネットワークにある、以下の２台です。
・Linux マシン linuxBean (ASUS Eee PC 1002HA)
・Android 5.0.2 (ZenFone 2 Laser)
なお、このサーバは外部に公開してはいないので、あくまで個人宅の中だけの話です。
寄り道ですが、こちら↓はサーバを長期運用するのは大変というお話で、共感できました。
今は自宅内でコソコソとお勉強してるだけですが、もし、社外アクセスも視野に入れるならクラウドとか使いたいですね。
本題に戻って、以下は ３部構成です。
ファイヤーウォールなどでポートを閉じている場合は開放する必要があります。
もし、この結果が「非アクティブ」なら、ポートは開放されているのでOKなはずです。
そうではなく「アクティブ」だったら、PostgreSQL で使うポートを開けてあげます。
PostgreSQL のデフォルトのままなら 5432 番を開けます。
おまけですが、ファイヤーウォールの ON／OFF は以下のコマンドです。
さらに、おまけで、ルールを誤って設定したなどで、削除したい場合の手順です。
まず、ルールの番号を確認します。
例えば、[ 3] のルールを削除するならこれ↓です。
ファイヤーウォールでは散々悩みました
しかし、結局、自分のマシンはもともと disable 状態で何もしなくてよかったみたい
1) postgresql.conf の修正
ここで、postgres に su しておきます。そうすると、以降で書き込み権で悩まずに済みます。
/etc/postgresql/9.4/main/postgresql.conf に下記の設定を追加(変更)します。
2) pg_hba.conf の修正
/etc/postgresql/9.4/main/pg_hba.conf に下記を追加します。
3) PostgreSQL 再起動
postgres から exit します。もし、psotgres が sudoers だったらそのままでもOKですが。
PostgreSQL を再起動します。
4) ポートを確認します。
下記のように表示されればOK
もし、下記のようだと、外部接続は NG
違いは４番目の「内部アドレス」が 0 0.0.0.0 なのか 127.0.0.1 なのかです。
5) 今回、一番悩んだポイント
最初は上記 NG ケースに悩まされました。
その原因は、上記 1), 2) の修正対象を誤っていたことです。
Ubuntu に PostgreSQL を入れてみました のとおり、データディレクトリをデフォルトの
/etc/postgresql/
から
/home1/postgresql/
に移動しました。
これにともない、/home1/postgresql/ にも postgresql.conf と pg_hba.conf がコピーされていたのですが、これらを編集していました。
これは全く意味がありません
PostgreSQL の設定はデフォルトのまま /etc/postgresql/9.4/main/ だったのです。
で、この設定ファイルの場所も移動しようかと思ったのですが、諦めました。とりあえず、憶測ですが /etc/init.d/postgresql あたりから手を付ける必要がありそうです。このファイルをちらっと読んでみてメゲました
最後に、紛らわしいので /home1/postgresql/ にある postgresql.conf と pg_hba.conf は消しました
6) 外部からのポートアクセスの調べ方
外部からポートの状態を調べるコマンドはこれ↓が使えました。
PostgreSQL サーバのポートにアクセス成功の場合のログ
Apache サーバのポートにアクセス成功の場合のログ
ポートのアクセスに失敗する場合のログ
今回はポート接続問題にあたって、ルータのポート開放に関する設定もいじりましたが関係ありませんでした。全てが同じルータにぶら下がっている構成（ハブ、ブリッジあり）だとルータの設定は関係ないのかもしれません。
Synaptic パッケージマネージャー
で、
postgresql-client
でヒットするパッケージをインストールしておきます。
サーバへの接続
データベースの確認
ロール（ユーザと表現されることもあるような気がするのだが？）の確認
データベース、ユーザの切替
これで PostgreSQL ホスト上にて、つまり、ローカルで "psql testdb" でアクセスしている状態と同じようにテーブルにアクセスできました。
このアプリで、下記↓の設定で、お試しで作成した testdb にアクセスできました。
注意点は、設定が正しくても "TEST CONENCTION" でエラーになることがありました。そんな擬似エラーになる条件まではよく分かりません
今回は、ポートの開放でかなり悩みました。おかげで、外部からアクセスされるサーバを運用する大変さを少し理解できたかもしれません
他のマシンは、同じネットワークにある、以下の２台です。
・Linux マシン linuxBean (ASUS Eee PC 1002HA)
・Android 5.0.2 (ZenFone 2 Laser)
なお、このサーバは外部に公開してはいないので、あくまで個人宅の中だけの話です。
寄り道ですが、こちら↓はサーバを長期運用するのは大変というお話で、共感できました。
今は自宅内でコソコソとお勉強してるだけですが、もし、社外アクセスも視野に入れるならクラウドとか使いたいですね。
本題に戻って、以下は ３部構成です。
- PostgreSQL サーバ (Ubuntu) の設定
- クライアント(linuxBean) からのアクセス
- クライアント(Andorid) からのアクセス
PostgreSQL サーバ (Ubuntu) の設定
0) ネットワークの設定ファイヤーウォールなどでポートを閉じている場合は開放する必要があります。
$ sudo ufw status
もし、この結果が「非アクティブ」なら、ポートは開放されているのでOKなはずです。
状態: 非アクティブ
そうではなく「アクティブ」だったら、PostgreSQL で使うポートを開けてあげます。
状態: アクティブ
PostgreSQL のデフォルトのままなら 5432 番を開けます。
$ sudo ufw allow 5432/tcp
ルールを追加しました
ルールを追加しました (v6)
おまけですが、ファイヤーウォールの ON／OFF は以下のコマンドです。
$ sudo ufw enable
$ sudo ufw disable
さらに、おまけで、ルールを誤って設定したなどで、削除したい場合の手順です。
まず、ルールの番号を確認します。
$ sudo ufw status numbered
To Action From
-- ------ ----
[ 1] 5432/tcp ALLOW IN Anywhere
[ 2] 80/tcp ALLOW IN Anywhere
[ 3] 81/tcp DENY IN Anywhere
： 以下、省略
例えば、[ 3] のルールを削除するならこれ↓です。
$ sudo ufw delete 3
ファイヤーウォールでは散々悩みました
しかし、結局、自分のマシンはもともと disable 状態で何もしなくてよかったみたい
1) postgresql.conf の修正
ここで、postgres に su しておきます。そうすると、以降で書き込み権で悩まずに済みます。
$ su postgres
/etc/postgresql/9.4/main/postgresql.conf に下記の設定を追加(変更)します。
listen_addresses = '*'デフォルトでは、これらの設定項目はコメントアウトされていました。
port = 5432
2) pg_hba.conf の修正
/etc/postgresql/9.4/main/pg_hba.conf に下記を追加します。
host all all 0.0.0.0/0 trust
3) PostgreSQL 再起動
postgres から exit します。もし、psotgres が sudoers だったらそのままでもOKですが。
PostgreSQL を再起動します。
$ sudo service postgresql stop
$ sudo service postgresql start
4) ポートを確認します。
$ sudo netstat -ntlp
下記のように表示されればOK
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 5690/postgres
もし、下記のようだと、外部接続は NG
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 875/postgres
違いは４番目の「内部アドレス」が 0 0.0.0.0 なのか 127.0.0.1 なのかです。
5) 今回、一番悩んだポイント
最初は上記 NG ケースに悩まされました。
その原因は、上記 1), 2) の修正対象を誤っていたことです。
Ubuntu に PostgreSQL を入れてみました のとおり、データディレクトリをデフォルトの
/etc/postgresql/
から
/home1/postgresql/
に移動しました。
これにともない、/home1/postgresql/ にも postgresql.conf と pg_hba.conf がコピーされていたのですが、これらを編集していました。
これは全く意味がありません
PostgreSQL の設定はデフォルトのまま /etc/postgresql/9.4/main/ だったのです。
で、この設定ファイルの場所も移動しようかと思ったのですが、諦めました。とりあえず、憶測ですが /etc/init.d/postgresql あたりから手を付ける必要がありそうです。このファイルをちらっと読んでみてメゲました
最後に、紛らわしいので /home1/postgresql/ にある postgresql.conf と pg_hba.conf は消しました
6) 外部からのポートアクセスの調べ方
外部からポートの状態を調べるコマンドはこれ↓が使えました。
$ nc -v -z -w 3 <サーバのアドレス> <ポート番号>
PostgreSQL サーバのポートにアクセス成功の場合のログ
Connection to 192.168.11.3 5432 port [tcp/postgresql] succeeded!
Apache サーバのポートにアクセス成功の場合のログ
Connection to 192.168.11.3 80 port [tcp/http] succeeded!
ポートのアクセスに失敗する場合のログ
nc: connect to 192.168.11.19 port 80 (tcp) failed: Connection refused
今回はポート接続問題にあたって、ルータのポート開放に関する設定もいじりましたが関係ありませんでした。全てが同じルータにぶら下がっている構成（ハブ、ブリッジあり）だとルータの設定は関係ないのかもしれません。
クライアント(Linux) からのアクセス
まず、必要なら、Synaptic パッケージマネージャー
で、
postgresql-client
でヒットするパッケージをインストールしておきます。
サーバへの接続
$ psql -h <PostgreSQL ホストのアドレス> -p 5432 -U postgres
データベースの確認
postgres=# \l
ロール（ユーザと表現されることもあるような気がするのだが？）の確認
postgres=# \du
データベース、ユーザの切替
postgres=# \c testdb <ロール名>
これで PostgreSQL ホスト上にて、つまり、ローカルで "psql testdb" でアクセスしている状態と同じようにテーブルにアクセスできました。
クライアント(Andorid) からのアクセス
Google Play で検索するとクライアントなのかどうかよくわからないアプリが大量にヒットします。その中で、名称も分かりやすいし、説明にも PostgresSQL への接続を謳っているアプリ "SQL-Client" を使ってみました。このアプリで、下記↓の設定で、お試しで作成した testdb にアクセスできました。
今回は、ポートの開放でかなり悩みました。おかげで、外部からアクセスされるサーバを運用する大変さを少し理解できたかもしれません
