One IT Thing

IT業界で飯を食う為の学習系雑記

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の有効化と共通鍵暗号の動作確認

PostgreSQLで「pgcrypto」拡張機能を有効にすると、PostgreSQLに保存するデータを暗号化出来るようになります。 INSERTする際、共通鍵暗号はpgp_sym_encrypt関数 …

JavaとPerlでTCPソケット通信

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

hidden.inとSoftEtherで無料のビデオ会議環境を構築する

自粛が続いて会えない人も多くなり、人との交流が恋しくなりますね。 仕事ではミーティングをオンライン化するニーズが増え、以下メジャーなパブリックサービスを使っている人も増えていると思います。 Skype …

GitLab Omunibus版のPostgreSQLに普通のpsqlコマンドで接続

GitLab Omunibus版でインストールされるビルトインPostgreSQLは、 TCP:5432ではなくホスト内でしか接続出来ないUnix Domain Socketで待機ローカルホストからO …

ERROR: Several keys given – pgcrypto does not handle keyring、またはERROR: Corrupt ascii-armor

gpgキーストアに同じUIDで複数の鍵ペアを登録してしまうと、ファイルにexportした際に1ファイルに複数の鍵情報が入ってしまい、1ファイル1鍵を期待しているpgcryptoに怒られます。 目次1 …

 

shingo.nakanishi
 

東京在勤、職歴20年越え中年ITエンジニアです。まだ開発現場で頑張っています。

19歳(1996年)から書き始めた個人日記が5,000日を超え、残りの人生は発信をして行きたいと思い、令和元日からこのサイトを開始しました。勉強と試行錯誤をしながら、自分が経験したIT関連情報を投稿しています。

私と同じく、今後IT業界で生計を立てて行きたいと考えている方や、技術共有したいけどフリーランスで孤独、といった方と一緒に成長、知識共有して行けたら楽しいな、と思っています。