network PostgreSQL

SSHトンネルを使ってリモートデータベースをBI

投稿日:2019年8月25日

クラウド上のDBを分析する為、ローカルPCにSSHトンネル(ポートフォワード)環境を作ります。

リモートDBポートにクラウド外からセキュアに接続できれば、クラウドにBIツールを入れる手間が省けます。

ssh -NL ローカルPCポート:接続先IP:接続先ポート 踏み台サーバユーザ@踏み台サーバIP

ローカルPCと踏み台サーバでsshコマンドが使える環境であれば、この1コマンドでリモートPCポートをローカルPCポートに持ってくることが出来、後はDBツールで接続するなりBIするなり自由自在です。

動作イメージ

DBポートをSSHトンネルでローカルPC(192.168.0.5)に持ってきて、ローカルPCにいれたMetabaseでBI。

環境

  • ローカルPC(Windows 10 + Cygwin、WSL等sshコマンドが使える環境)
  • 踏み台サーバ(CentOS 7.6)
  • DBサーバ(CentOS 7.6)

以下の記事で環境が構築されている状態です。

PostgreSQL11にdvdrentalデータベースを構築。

WindowsにMetabaseを導入。

手順

SSHトンネルを作成

192.168.0.4:5432を、192.168.0.2経由で、192.168.0.5:5432に持ってきます。

踏み台サーバ(192.168.0.2)にSSHログイン後、-LでローカルPC:5432とDBサーバ:5432をポートフォワード。踏み台サーバが両PCを繋ぐ架け橋になります。

-Nはssh中にリモートコマンドを叩いてしまわない為の保険です。

C:\> ssh -NL 5432:192.168.0.4:5432 naka@192.168.0.2
Could not create directory '/home/naka/.ssh'.
The authenticity of host '192.168.0.2 (192.168.0.2)' can't be established.
ECDSA key fingerprint is SHA256:LUmE4Zs3L78GwDvkWy25D69/r3h4Iym4x+QCf6IuN6U.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Failed to add the host to the list of known hosts (/home/naka/.ssh/known_hosts).
naka@192.168.0.2's password:

この時点でlocalhost:5432がPostgreSQLのポートになっているので、A5 Mk2やJailerでDBに接続することが出来ます。この時点で既に便利ですね ^^

Metabaseで接続

localhost:5432にMetabaseを繋いでBI環境を作ります。

まずはMetabaseを起動。metabase.jarがあるディレクトリでjarを実行します。

C:\tools> java -jar metabase.jar

localhost:3000で起動するのでブラウザからアクセス。新たに分析対象のデータベースを追加します。

メニューから「管理者」をクリックして管理者画面へ移動。

管理者画面で「データベースを追加する」をクリック。

データソースを設定。
sshコマンドで作成したlocalhost:5432を指定します。

設定を保存するとlocalhost:5432経由でDBサーバ:5432に接続され、dvdrentalデータベースの分析を開始できます。

リモートPCに入っているPostgreSQLを、まるでローカルPCに入っているかのような設定でBI出来るようになりました。

リモートPCのPostgreSQLポートを開ける必要が無いのでセキュリティ的にもプラス効果です。

まとめ

Metabaseに限って言えば実はsshトンネルを独自に作ってDB接続する機能は標準搭載されています。

ただMetabaseが起動していないとリモートDBにSSHトンネル出来ないのは不便ですね。

sshコマンドでトンネルを作る知識は汎用かつ実戦的で重宝します。

-network, PostgreSQL
-,

執筆者:

関連記事

pgcryptoで公開鍵暗号の動作確認

共通鍵暗号で暗号化されたデータはパスワードが漏洩すると復号される危険が高まるのに対し、公開鍵暗号で暗号化されたデータは秘密鍵とパスワードの二つが漏洩しないと復号できません。 APサーバとDBサーバ通信 …

インターネット速度テストサービス10個を順位づけしてみる

「インターネット疎通の速度が帯域通りになっているか確かめたい」 仕事でもプライベートでもそんなニーズがあります。 数多あるインターネット速度計測サービスを試してみて「日本国内で使う場合、最も正確な速度 …

JavaとPerlでTCPソケット通信

目次1 はじめに2 対象読者3 実装3.1 TCPサーバ(Perl)3.2 TCPクライアント(Java)3.3 実行4 まとめ はじめに 前回の続きです。One IT ThingJavaとPerlで …

ER図自動生成ツール「SchemaSpy」でdvdrentalを可視化

SchemaSpyを使うとDBからデータを吸い上げてテーブルの相関をビジュアライズしてくれます。 本来ER図からDDLを生成出来るようにしておくべきで、出来れば使わない方がいいチート系のツールですがC …

Linux、Mac、Windows間のネットワーク速度をiperf3で調べる

なんかネットワークが遅いから調べたい。あのLANケーブルひょっとして100Mbps用じゃない?1Gbpsじゃないかも。試験的にネットワーク帯域を絞ったから通信速度を確認したい。 こういった時、通信する …

 

shingo.nakanishi
 

東京在勤、1977年生まれ、IT職歴2n年、生涯現役技術者を目指しています。健康第一。