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

user_1148249 0
Viewed 2527

When trying to run Elixir (Phoenix) Web Application using PostgreSQL Database hosted 3rd 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):

azure-psql-ssl-enabled

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: azure-psql-ssl-disable

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?

phoenix-framework elixir azure erlang postgresql
5 Answers

To add to the above answers, here is my ssl_opts block when using self-signed certificates and auth-options set to clientcert=verify_full in your pg_hba.conf. My connection is using TLSv1.3.

  ssl_opts: [
     verify: :verify_peer,
     versions: [:"tlsv1.3"],
     ciphers: :ssl.cipher_suites(:all, {3,4}),
     cacertfile: Path.expand("priv/certs/ca-cert.pem"),
     certfile: Path.expand("priv/certs/client-cert.pem"),
     keyfile: Path.expand("priv/certs/client-key.pem")
    ],
user_100877070
Related