如何通过SSL从Phoenix Web App连接到PostgreSQL? [英] How to connect to PostgreSQL from Phoenix Web App via SSL?

问题描述

当尝试使用PostgreSQL数据库托管的3 ^rd 一方"数据库即服务"(

洞察力:在Azure DB上启用了强制SSL" ...

通过调查，我们发现该错误是原因，因为Azure PostgreSQL服务已将Enforce SSL Connection设置为Enabled( by default ):

我们认为将强制SSL"设置为Enabled对于安全性是好，但我们无法使其与Phoenix一起使用...

(临时)解决方案:禁用强制执行SSL"

因此，我们暂时禁用了( ) SSL:

但是，我们会更愿意使用"永久"解决此问题.

首选解决方案:连接到PostgreSQL时使用SSL

如果任何人都可以澄清(或将我们指向) 如何 从Phoenix/Ecto通过SSL连接到PostgreSQL
我们将非常感谢！ :-)

应用程序(Phoenix)服务器是否需要配置SSL证书才能从应用程序服务器连接到数据库服务器...?
例如: http://www.phoenixframework.org/docs/configuration-for-ssl 吗?

Microsoft具有以下帮助指南: https://docs.microsoft.com/zh-我们/azure/postgresql/concepts-ssl-connection-security 似乎暗示我们需要在App Server上使用OpenSSL ...任何人都可以确认吗?

解决方案

背景

我在将Phoenix/Ecto/Postgrex连接到PostgreSQL服务器的Azure数据库时遇到了同样的问题.即使在Repo配置中设置了ssl: true之后，即使在同一台计算机上成功使用psql "postgresql://...?sslmode=require" -U ...进行连接，我仍然无法使用Postgrex连接到数据库. ssl: true返回的错误是:

[error] Postgrex.Protocol (#PID<0.1853.0>) failed to connect: **(DBConnection.ConnectionError) ssl connect: closed

** (DBConnection.ConnectionError) connection not available because of disconnection
    (db_connection) lib/db_connection.ex:926: DBConnection.checkout/2
    ...

在仔细研究了源代码之后，我发现失败的呼叫实际上是来自

通过Wireshark进行监听，我能够看到，与psql成功连接时，可以看到以TLSV1.2作为协议的数据包，但是当postgrex与ssl: true连接时，我看到的是SSL作为无法连接之前的协议.

查看 Ecto.Adapters.Postgres选项文档，您将看到一个ssl_opts配置选项，最终将其传递给:ssl.connect/3，您可以在其中配置versions来覆盖用于连接的TLS版本.

解决方案

通过将以下内容添加到我的Repo配置中，我能够连接到数据库:

ssl_opts: [
  versions: [:"tlsv1.2"]
]

我的完整配置最终看起来像这样:

config :myapp, Myapp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "myapp@dev-db",
  password: "...",
  database: "myapp_dev",
  port: 5432,
  hostname: "dev-db.postgres.database.azure.com",
  pool_size: 10,
  ssl: true,
  ssl_opts: [
    versions: [:"tlsv1.2"]
  ]

我不太确定为什么需要明确设置TLS版本，也许对此领域有更多专业知识的人可以对此有所了解.

When trying to run Elixir (Phoenix) Web Application using PostgreSQL Database hosted 3^rd party "Database-as-a-Service" (Azure Database for PostgreSQL).

We attempt to start the app with mix phoenix.server we see the following error:

[info] Running Pxblog.Endpoint with Cowboy using http://localhost:4000
[error] GenServer #PID<0.247.0> terminating
** (FunctionClauseError) no function clause matching in Postgrex.Messages.decode_fields/1
    (postgrex) lib/postgrex/messages.ex:339: Postgrex.Messages.decode_fields("")
    (postgrex) lib/postgrex/messages.ex:344: Postgrex.Messages.decode_fields/1
    (postgrex) lib/postgrex/messages.ex:344: Postgrex.Messages.decode_fields/1
    (postgrex) lib/postgrex/messages.ex:131: Postgrex.Messages.parse/3
    (postgrex) lib/postgrex/protocol.ex:1842: Postgrex.Protocol.msg_decode/1
    (postgrex) lib/postgrex/protocol.ex:1816: Postgrex.Protocol.msg_recv/3
    (postgrex) lib/postgrex/protocol.ex:560: Postgrex.Protocol.auth_recv/3
    (postgrex) lib/postgrex/protocol.ex:475: Postgrex.Protocol.handshake/2
    (db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

Insight: "Enforce SSL" was Enabled on the Azure DB ...

Through investigation we realised that the error is cause because the Azure PostgreSQL Service had Enforce SSL Connection set to Enabled (by default):

We think having "Enforce SSL" to Enabled is good for Security, but we aren't able to get it working with Phoenix ...

(Temporary) Solution: Disable "Enforce SSL"

So, we have (temporarily) disabled SSL for now:

But we would much prefer a "permanent" solution to this issue.

Preferred Solution: Use SSL when Connecting to PostgreSQL

If anyone can clarify (or point us to) how to connect to PostgreSQL over SSL from Phoenix/Ecto
we would be super grateful! :-)

Does the Application (Phoenix) Server need to have an SSL Certificated configured in order to connect from the App server to the DB Server...?
e.g: http://www.phoenixframework.org/docs/configuration-for-ssl ?

Microsoft has the following help guide: https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security It seems to suggest we need OpenSSL on the App Server ... can anyone confirm?

解决方案

Background

I was experiencing the same problem connecting Phoenix/Ecto/Postgrex to Azure Database for PostgreSQL server. Even after setting ssl: true in my Repo configuration, I was still not able to connect to the database with Postgrex even though connecting using psql "postgresql://...?sslmode=require" -U ... on the same machine succeeded. The error returned with ssl: true was:

[error] Postgrex.Protocol (#PID<0.1853.0>) failed to connect: **(DBConnection.ConnectionError) ssl connect: closed

** (DBConnection.ConnectionError) connection not available because of disconnection
    (db_connection) lib/db_connection.ex:926: DBConnection.checkout/2
    ...

After digging through the source code, I discovered that the failing call was actually the ssl.connect/3 call from the Erlang ssl module:

# deps/postgrex/lib/postgrex/protocol.ex:535

defp ssl_connect(%{sock: {:gen_tcp, sock}, timeout: timeout} = s, status) do
  case :ssl.connect(sock, status.opts[:ssl_opts] || [], timeout) do
    {:ok, ssl_sock} ->
      startup(%{s | sock: {:ssl, ssl_sock}}, status)
    {:error, reason} ->
      disconnect(s, :ssl, "connect", reason)
  end
end

Doing some snooping with Wireshark, I was able to see that when connecting successfully with psql, I could see packets with TLSV1.2 as the protocol, but when postgrex was connecting with ssl: true I was seeing packets with SSL as the protocol before failing to connect.

Looking at the Ecto.Adapters.Postgres options docs, you'll see there's an ssl_opts configuration option which ends up getting passed to :ssl.connect/3 in which you can set versions to override the TLS version(s) used to connect.

Solution

I was able to connect to the database by adding the following to my Repo configuration:

ssl_opts: [
  versions: [:"tlsv1.2"]
]

My full configuration ended up looking like this:

config :myapp, Myapp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "myapp@dev-db",
  password: "...",
  database: "myapp_dev",
  port: 5432,
  hostname: "dev-db.postgres.database.azure.com",
  pool_size: 10,
  ssl: true,
  ssl_opts: [
    versions: [:"tlsv1.2"]
  ]

I'm not really sure why the TLS version needs to be set explicitly, perhaps someone with more expertise in this area can shed some light on this.

这篇关于如何通过SSL从Phoenix Web App连接到PostgreSQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！