これはなに

ちょっとした空き時間にSQLを叩きたいので、色々と環境を整えるやつです。

調べて進める

流れ

• 以下の感じでやり方を調べて、やってみる

  • MySQLかPostgreSQLを入れられる安いサーバーを探す

    • VPSで安いのがあるならそれがいいなあ
    • 500円未満ならうれしいなあ
  • とりあえず契約してRDBMS入れて、公開する

    • DBが外から叩けるようになってんの気持ち悪いけど、まあまあまあ
    • 踏み台にされないようにとかは気をつける
    • 一旦PCから接続できるかは試しておく
  • iPhoneに入れられるDBクライアントでいいやつを探す

    • 接続を試して、いちばん使いやすいやつを選ぶ

サーバー探し

候補になってくるのは以下のサービスたち。レンタルサーバーはおそらくRDBMSの各種設定をいじれないと思うため候補から外れ、お値段を考えたときVPSになるかなと考えています。なお月額はもっともスペックの低いプランで出しています。

（参考:https://knowledge.sakura.ad.jp/7938/）

まあ500円未満はserverManくらいで、どこもどっこいどっこいって感じっぽい。コンパネに馴染みのあるさくらを使うことにする。

サーバーのセッティング

とりあえず契約したのでセキュリティ周りの設定をやる。

• 実施した項目

  • 普段使いするsudoユーザーの追加
  • Root、パスワード接続の禁止
  • sshポートの変更
  • FireWall設定（コンソール上）

参考：https://nishinatoshiharu.com/initial-vps-security-settings/

RDBMSのセットアップ

ということでPostgreSQLを入れる。

参考：https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart-ja

初学者としてへえ〜〜と思わされたのはident認証まわり。Linuxユーザと同名のpostgresqlユーザを作る（なんならデータベースも作る）というのが、それなんかの依存性になってややこしくないか？　と思った。

PostgreSQLのアクセス制御設定をやる

PostgreSQLのセットアップとリモートホスト内での接続ができたんだけど、外部からのアクセスには色々と設定が必要みたいなのでそこを設定する。Web上に設定方法の記事はたくさんあるけど（こことか）、一旦まとめると、

• やりたいこと

  • PostgreSQLの初期設定を変更し、適切な状態にする

    • 初期設定：外部からのアクセスはすべて拒否、ローカルからはパスワード不要でアクセス可能
    • 適切な状態：どこから来てもパスワードを求めてアクセスさせる
• 進め方

  • ポスグレのロールにパスワードを設定する
  • 外部/ローカルアクセスに関する設定を変える

ポスグレのロールにパスワードを設定する

psqlでデータベースに入ったあと、以下のコマンドでパスワードを設定する。

ALTER ROLE ロール名 PASSWORD 'ここにパスワードを記載';

以下のSQL文でパスワードの設定状況を見ることも可能。

SELECT * FROM pg_shadow;

usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+--------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |        |          |
 readonly |    16384 | f           | f        | f       | f            | パス設定されてるとなんか入る |          |
(3 rows)

外部/ローカルアクセスに関する設定を変える

ポスグレの関連書籍をパラパラみていたら、どうやら外部からのアクセスはデフォルトだと弾くようになっているみたいなので、そこを変える。設定ファイルは以下のフォルダに入っているらしい。

/var/lib/postgresql/<version>/main/
# winやmacの場合は、/var/lib/postgresql/<version>/data/

で、該当フォルダを見にいったんだけど、設定ファイル、ない。

そこで以下のコマンドで探してみたところ、全く別の場所にあった。

find / -name postgresql.conf 2> /dev/null
> /usr/lib/tmpfiles.d/postgresql.conf
> /etc/postgresql/12/main/postgresql.conf

こはいかに。etcって設定周りのファイルが集まるやつだから、設定ファイルだけこちらにうつるようになったのかな…？　標準に準拠してるしおかしな話ではなさそうだけど。

まあ何はともあれ探せたので、編集していく。

まずはpostgresql.confのlisten_addressesの範囲をすべてに広げる

#listen_addresses = 'localhost'
listen_addresses = '*'

次に、pg_hba.confのMETHODをパスワード認証にするのと、アクセスを受け付けるリストを修正する。

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
# ここにいい感じに追加。すべて受け入れるなら「host all all md5」みたいな感じ

おわり！　ここまでやって、つながるかどうかを確認する。

リモート接続する

ここは簡単。秘密鍵を使ったoverSSHで接続するだけ。一応画像を貼っておく。(画像が問答無用ででっけえでっけえになっちゃうのどうにかしたい)

iPhone向けDBクライアントを探す

手元のPCからリモート接続できたので、てきとうなiPhone向けDBクライアントアプリを探す。

PostgreSQL用となるとあまり種類がないんだけど、その中でもTablePlusのモバイル版が無料でそこそこ使いやすかった。スマホでの見やすさって限界あるけどね。

感想

できて良かった。適当なテーブルを作って遊びたい。

あーあとクラスタ周りを理解していないので何か読む