クラウドデータプロセッサがデータベース（またはその一部）にアクセスできるようにするには？

サポート インストール
1

Stitch Data（https://www.stitchdata.com/）や Skyvia（https://skyvia.com/）などの ETL サービスを使って、Discourse データベースを含むさまざまなデータソースを統合しようと考えていました。しかし、Skyvia の担当者から、これは不可能だと伝えられました。

Skyvia は SSH を通じて PostgreSQL に接続できますが、SSH サーバーがコンテナ内ではなくその手前にある場合、Docker コンテナ内の PostgreSQL には接続できません。

PostgreSQL への接続に関する Skyvia の要件は以下の通りです。

明らかな回避策はありますか？

2

Discourse コンテナで SSH を有効化し（標準以外のポートを使用）、そこに接続できるようにすることができます。Discourse_docker の samples ディレクトリに例があるかもしれません。

「いいね！」 3
3

ありがとう、ジェイ。結局、docker-ssh を公開鍵認証で使うことにしました。:+1:

「いいね！」 2
4

どうやら重要な概念を見落としているようです。カスタムポートを使って SSH で接続し、その後 su postgres -c 'psql discourse' を実行することは問題なくできるからです。この 2 段階のアプローチではすべて正常に動作しますが、pgAdmin などで直接接続するには、少し異なる設定が必要なのではないかと考えています。

カスタムポートを公開するために使用しているコマンドは以下の通りです：

docker run -d -p 2222:22 \
        -v /var/run/docker.sock:/var/run/docker.sock \
        -v ~/.ssh/authorized_keys:/authorized_keys \
        --name my-sshd \
        -e FILTERS={\"name\":[\"^/app$\"]} -e AUTH_MECHANISM=publicKey \
        -e AUTHORIZED_KEYS=/authorized_keys \
        jeroenpeeters/docker-ssh

これにより、後で以下のように直接実行できるようになります（launcher enter app で Docker コンテナを起動する必要はありません）：

ssh whatever@host -p 2222
su postgres -c 'psql discourse'

いくつか試してみましたが、うまくいきませんでした。ssh whatever@host -p XXXX を実行して直接データベースに接続できる方法があるはずだと感じています（おそらく pgAdmin が期待しているのはこれでしょう）。

5

接続できていませんか、それとも権限の問題ですか？

6

コマンドラインから SSH で接続し、その後 psql を使用することはできます。しかし、pgAdmin からは接続できません。

「いいね！」 1
7

pgAdmin 経由で接続するには、PostgreSQL ポートを直接公開する必要があります。

app.yml の上部付近を見ると、ポート 80 と 443 が開放されているのが確認できます。PostgreSQL のポート 5432 についても、新しい行を追加できます。

ただし、これは明らかに非常に危険な行為です。データベースがローカル接続のみを受け付けていた状態から、インターネット全体に公開されることになります。

単にたまにレポートを作成するだけであれば、Data Explorer から CSV をダウンロードし、お好みのツールに読み込むだけで十分かもしれません。また、Discourse のバックアップ（アップロードファイルを除く）をダウンロードすることも可能です。これらは標準的な PostgreSQL ダンプ形式です。これを手元にあれば、ローカルの PostgreSQL インスタンスに復元して分析を行うことができます。

「いいね！」 1
8

ありがとうございます、ラファエル。

これを実行してコンテナを再構築しましたが、まだ動作しません（XX.XX.XX.XX の代わりに実際の IP アドレスが表示されています）

image
image1002×670 39.2 KB

SSH トンネルタブでは次のようになっています：

image
image814×736 40.3 KB

表示されるエラーはこれです：

image
image1498×542 42.4 KB

これについてですが、保護の層が一つ減ることは理解していますが、それでも SSH 秘密鍵が必要ですよね？

9

app.yml に 5432 を追加すると、SSH トンネルを介さずに直接公開されてしまいます。

pgAdmin の SSH トンネルについては、使用したことがないためアドバイスできません。おそらく、ローカル接続用にポートをリスンさせることを想定しているため、インターネットに公開する必要はないと思われます。

以下をお試しください：

expose:
  - "80:80"
  - "443:443"
  - "5432"
「いいね！」 1
10

ただし、スーパーユーザーである必要があるため、Postgres のパスワードは存在しません。pg_hba.conf ファイルでは「local」接続の権限が「peer」に設定されているため、UNIX ユーザーに依存します。つまり、SSH 経由でのログインが必要だということでしょうか？

これは動作しません：psql -h XX.XX.XX.XX -p 5432 -U postgres -d discourse

11

psql にスーパーユーザーとして接続できます

./launcher enter app
su postgres
psql

レポート作成に必要な権限を持つユーザーを作成してください。

12

はい、アプリの Docker コンテナからの接続には問題ありません。私の問題は、ローカルマシン（pgAdmin を使用するため）や Stitch などのクラウドデータプロセッサから直接 PostgreSQL データベースに接続できないことです。これらはホスト IP アドレスと SSH 認証情報を必要としますが、うまくいかず（上記のエラーが発生します）。

私ができたのは、docker-ssh を使用して、ローカルコンピュータから（launcher enter app を実行せず）公開鍵を介してアプリの Docker コンテナに直接アクセスすることだけです。しかし、データベースにアクセスするにはまだ su postgres 'psql discourse' を実行する必要があります。これが pgAdmin や Stitch で問題となっている要因だと思われます。これらは直接接続を期待しているためです。

13

パスワード付きの新しい PostgreSQL を作成し、それをサービスに渡すことは試しましたか？

14

はい、彼らはかなり長い手順を持っています。

しかし、私はローカルコンピュータからpgAdminを単に使用しているだけで、同じ問題が発生しています。

15

パスワードを設定する方法については、スタンドアロンコンテナから個別の Web コンテナとデータ コンテナへ移行する方法 に手順が記載されている可能性があります。

また、Postgres ポートを 127.0.0.1 のみにバインドすることもできると思います。

expose:
  - "80:80"
  - "443:443"
  - "127.0.0.1:5432:5432"
「いいね！」 1
16

DBへの接続をポートを公開せずに試せるか確認するために、一時的に手を引くことにしました。:grin:

コンテナ内に入ると、以下のような表示になります。

# netstat -lp | grep postgres
tcp        0      0 0.0.0.0:postgresql      0.0.0.0:*               LISTEN      -
tcp6       0      0 [::]:postgresql         [::]:*                  LISTEN      -
unix  2      [ ACC ]     STREAM     LISTENING     263612292 -                    /var/run/postgresql/.s.PGSQL.5432

コンテナから退出し、リモートサーバー上（まだローカルコンピューターではありません）にいる場合、以下で接続できるはずです。

/var/discourse# psql -h localhost -d discourse -U postgres

しかし、パスワードの入力を求められます。postgresユーザーにはパスワードが設定されていないため、別のユーザーを作成してパスワードを割り当ててみました。

CREATE USER whatever_user WITH ENCRYPTED PASSWORD '<whatever password>';
GRANT CONNECT ON DATABASE discourse TO whatever_user;
GRANT USAGE ON SCHEMA public TO whatever_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO whatever_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO whatever_user;

pg_hba.conf にそのユーザー用の行を md5 で追加し、service postgresql restart で PostgreSQL を再起動しました。

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

しかし、リモートサーバーから接続しようとすると、認証エラーが発生します。

# psql -h localhost -d discourse -U whatever_user
Password for user whatever_user:
psql: FATAL:  password authentication failed for user "whatever_user"
FATAL:  password authentication failed for user "whatever_user"

何が足りないのでしょうか？まずは同じサーバーから DB に接続できるようにしたいと考えています。ステップ2として SSH トンネルを使うことも検討していますが、まずはステップ1を解決する必要があります。ご助言いただければ幸いです。

17

OK、ついに解決しました🎉

これを

これを

  • “127.0.0.1:5433:5432”
    に変更しました。ポートが既に使用されているというエラーが発生したためです。

コンテナを再構築し、ポートが実際に開いていることを確認しました。

$ sudo docker ps
CONTAINER ID        IMAGE                           COMMAND             CREATED             STATUS              PORTS                      NAMES
whatever_id        local_discourse/app             "/sbin/boot"        20 minutes ago      Up 20 minutes       127.0.0.1:5433->5432/tcp   app

これで、SSHトンネルを作成し、パスワード付きのユーザーを使ってリモートサーバーから接続できるようになりました。

# トンネルを作成（バックグラウンドで実行するには ssh -f を使用することも可能）
ssh -v -N -L 5433:localhost:5433 SERVER_IP_ADDRESS

# 他のタブで接続し、パスワードを入力
psql -h localhost -d discourse -U whatever_user -p 5433

もしこれを試していて問題に遭遇した方がいれば、教えてください。

「いいね！」 4