How to connect to postgrex with SSL certificate?

Fl4m3Ph03n1x

Background

We are making a proof of concept to use a postgres DB with Elixir. To achieve this we are using Postgrex.

Issue

The problem here is we need to connect to the DB using a certificate instead of a username and password. Unfortunately the documentation only has 1 example where one connects via username and password.

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "postgres")

Research

I have read the documentation and I found that I can use the ssl and ssl_opts parameters when connecting to the DB: Postgrex — Postgrex v0.22.2

However, the documentation tells me to loo for the ssl docs, but no link is given.

It is the first time I am dealing with this kind of information in Elixir, I have no idea on how to proceed from here on.

Questions

How do I connect to my postgres DB using a certificate and Postgrex?
What are these ssl_opts documents the documentation refers to? How do they translate to Elixir code?

5 comments

#ssl #postgrex

0 5419 5

2019-10-23 07:22:11 UTC

Showing Posts 1 to 5

Show Best Posts
Show All Posts (oldest first)
Show All Posts (newest first)

hauleth

It tells you to look into ssl module of Erlang standard library. To be exact you should look at tls_option/0. In the end it is keyword list with values specified in these type specs.

Post #1

Fl4m3Ph03n1x

Would it be something like this?

{:ok, pid} = Postgrex.start_link(
  hostname: "localhost", 
  database: "postgres",
  ssl: true, 
  ssl_opts: [certfile: "/home/certs/my_cert.pem"], 
)

Post #2

hauleth

As documentation states that :certfile need to be string() which is Erlang’s string, in Elixir known as charlist you need to do this like that:

{:ok, pid} = Postgrex.start_link(
  hostname: "localhost", 
  database: "postgres",
  ssl: true, 
  ssl_opts: [certfile: '/home/certs/my_cert.pem'] 
)

Post #3

voltone

It depends what you’re trying to achieve by using TLS. If you just want to authenticate with a client certificate while obscuring data from passive observers, then just a :certfile option may work, assuming this one file contains the client certificate, any intermediate certificates needed, and the private key. If the private key is stored in a separate file you’d have to pass a :keyfile option as well.

If you want to strongly authenticate the server, to prevent active (MitM) attacks, you’re going to have to pass in a few more options, starting with verify: :verify_peer, the server’s hostname (using the :server_name_indication option is easiest) and the trusted CA certificates (using the :cacertfile option, which may interfere with selection of the client certificate’s intermediate CA certs).

Post #4

Fl4m3Ph03n1x

This is really valuable information. I felt quite lost reading through all of those examples, it’s always good to have some extra directions.

We’re not completely sure of our implementation, but if we have additional questions we’ll be sure to post them in the forum.

As a followup from this discussion I made a PR to improve the docs:

https://github.com/elixir-ecto/postgrex/pull/486

As a newcomer, I truly believe I needed more directions and the docs were at fault. This is my approach to improving them (the simplest approach I could think of, though I am still not convinced it’s enough).

Feel free to drop in with any suggestions on how to improve.

Post #5

— All posts loaded —