How to connect to PostgreSQL from Phoenix Web App via SSL?

Question

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?

Answer 1

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.