How can i upload SSL client certificates to connect to GCP storage?

[iampeterbanjo]

I've created a new Phoenix umberlla project and deployed it to Gigalixir at https://unhealthy-frugal-alligatorgar.gigalixirapp.com/. I have created a PostgreSql database on Google cloud storage with the recommend network mask and updated the DATABASE_URL environment variable but I'm unable to connect.

I get the error

2018-12-14T22:10:21.414930+00:00 unhealthy-frugal-alligatorgar[unhealthy-frugal-alligatorgar-544687c55c-2jscq]: web.1  | 22:10:21.414 [error] Postgrex.Protocol (#PID<0.285.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification): connection requires a valid client certificate

I believe that the solution is to upload my client certificates like this article suggests https://www.amberbit.com/blog/2017/11/13/connecting-ecto-to-postgresql-with-ssl/ but I don't want to check them into version control.

How can I get these certificates to the server? Or perhaps there's another way to connect to GCP storage.

[jesseshieh]

Sorry for the terse reply. I'm in my phone. I think you probably want to use key instead of key_file. See http://erlang.org/doc/man/ssl.html

And gigalixir config:set possibly with the -- argument separator depending on what's your cert looks like.

[iampeterbanjo]

thanks for the quick response. i have a client cert, key and server-ca that I got from GCP storage. you're suggesting I set them as strings and import them as env variables in my config.exs? Sorry I'm not that experienced with Elixir/Erlang

[jesseshieh]

yep =)

[iampeterbanjo]

Thanks I'll try that :)

[iampeterbanjo]

Sorry that doesn't work because the config is expecting a file path

ssl connect: Invalid key file ${CLIENT_KEY_PEM}: no such file or directory - {:options, {:keyfile, '${CLIENT_KEY_PEM}', {:error, :enoent}}}

[jesseshieh]

Are you using key or keyfile?

[iampeterbanjo]

I'm running a Phoenix app

# prod.exs
# Configure your database
config :blog, Blog.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "",
  ssl: true,
  pool_size: 1,
  ssl_opts: [
    cacertfile: "${SERVER_CA_PEM}",
    keyfile: "${CLIENT_KEY_PEM}",
    certfile: "${CLIENT_CERT_PEM}"
  ]

[jesseshieh]

It's kinda confusing, but the ssl_opts there are essentially just passed into the erlang ssl module described here: http://erlang.org/doc/man/ssl.html

The documentation there says you can use key instead of keyfile. With keyfile, you give it a path(), with key you give it something like {'RSAPrivateKey', "${CLIENT_KEY_PEM}"}. Then do the same for cacartfile and certfile.

Here is the relevant snippet from the link above.

| {key, {'RSAPrivateKey'| 'DSAPrivateKey' | 'ECPrivateKey' | 'PrivateKeyInfo', public_key:der_encoded()} | #{algorithm := rsa | dss | ecdsa, engine := crypto:engine_ref(), key_id := crypto:key_id(), password => crypto:password()}
| {keyfile, path()}

[jesseshieh]

In case you're curious, the Ecto postgres adapter says

:ssl_opts - A list of ssl options, see Erlang’s ssl docs

https://hexdocs.pm/ecto_sql/Ecto.Adapters.Postgres.html#module-connection-options

[iampeterbanjo]

Right, I think I understand what you meant about the ssl_option params. Thanks for pointing me in the right direction. I found a solution but it looks complicated https://elixirforum.com/t/using-client-certificates-from-a-string-with-httposion/8631/3

My naive attempt isn't working

  ssl_opts: [
    key: :public_key.pem_entry_decode(:RSAPrivateKey, System.get_env("CLIENT_KEY_PEM") || ""),
    cert: :public_key.pem_entry_decode(:RSAPublicKey, System.get_env("CLIENT_CERT_PEM") || ""),
  ]

-----> Fetching app dependencies with mix
remote: ** (FunctionClauseError) no function clause matching in :public_key.pem_entry_decode/2    
remote:     
remote:     The following arguments were given to :public_key.pem_entry_decode/2:

Maybe adding the SSL certs is the wrong approach. Does Gigalixir have an IP address whitelist that I can use to secure the database?

[jesseshieh]

Gigalixir doesn't really have an ip address range since the pods and nodes change relatively frequently.

[hourliert]

For anyone like me who arrived on this issue to understand how to setup SSL for ecto using env variables (this works for anything using the erlang ssl module), here what I did:

defmodule ErlangSSL do
  @moduledoc """
  Convenient module to decode cert and key files using erlang ssl module.
  """
  def get_der(value) do
    {type, entry} =
      value
      |> decode_pem_bin()
      |> decode_pem_entry()
      |> split_type_and_entry()

    {type, encode_der(type, entry)}
  end

  defp decode_pem_bin(pem_bin) do
    pem_bin |> :public_key.pem_decode() |> hd()
  end

  defp decode_pem_entry(pem_entry) do
    :public_key.pem_entry_decode(pem_entry)
  end

  defp encode_der(ans1_type, ans1_entity) do
    :public_key.der_encode(ans1_type, ans1_entity)
  end

  defp split_type_and_entry(ans1_entry) do
    ans1_type = elem(ans1_entry, 0)
    {ans1_type, ans1_entry}
  end
end

# Repo config
config :my_app, MyApp.Repo,
  ssl: true,
  ssl_opts: [
    cacerts: [ErlangSSL.get_der(System.get_env("DB_SERVER_CA")) |> elem(1)],
    cert: ErlangSSL.get_der(System.get_env("DB_CLIENT_CERT")) |> elem(1),
    key: ErlangSSL.get_der(System.get_env("DB_CLIENT_KEY"))
  ]

I hope that's useful.

[acco]

This is one of the only places on the internet with this intel, so leaving this for posterity:

For your cert strings, be sure to have newlines around the delimiters. This will not parse in :public_key.pem_decode():

# does not parse
-----BEGIN CERTIFICATE-----KNkXpoxpHhpyp8AU5KpOX [...] AU5KpOX-----END CERTIFICATE-----

But adding a newline does the trick:

# parses
-----BEGIN CERTIFICATE-----
KNkXpoxpHhpyp8AU5KpOX [...] AU5KpOX
-----END CERTIFICATE-----

[fabriziosestito]

@acco not all heroes wear capes! Thanks for this.