title: Postgres SSL connection error: Received fatal alert: handshake_failure
body: <p>Postgresql version 10 on Ubuntu Bionic</p>
  
  <p>Simple script to generate server certificate</p>
  
  <pre><code>#!/bin/bash
  
  if [ -z $1 ] || [ -z $2 ];
      then
          echo "Usage: ./self-signed-server-certificates &lt;curve&gt; &lt;signing-user&gt;"
      exit 1
  fi
  
  CURVE=$1
  SIGNING_USER=$2
  
  openssl ecparam -name $CURVE -out private.pem -genkey
  openssl req -new -sha256 -key private.pem -out server.csr
  openssl x509 -req -sha256 -days 365 -in server.csr -signkey private.pem -out certificate.crt
  chmod 400 private.pem
  sudo chown postgres:postgres private.pem cp certificate.crt root.crt
  </code></pre>
  
  <p>Another simple script to generate client certificate</p>
  
  <pre><code>#!/bin/bash
  
  if [ -z $1 ] || [ -z $2 ] || [ -z $3 ] || [ -z $4 ];
      then
          echo "Usage: ./self-signed-server-certificates &lt;curve&gt; &lt;signing-user&gt; &lt;root certificate path&gt; &lt;server key path&gt;"
      exit 1
  fi
  
  CURVE=$1
  SIGNING_USER=$2
  ROOT_CERTIFICATE=$3
  SERVER_KEY=$4
  
  openssl ecparam -name $CURVE -out private.pem -genkey
  openssl req -new -sha256 -key private.pem -out server.csr
  openssl x509 -req -sha256 -in server.csr \
  -CA $ROOT_CERTIFICATE -CAkey $SERVER_KEY -out public.crt -CAcreateserial
  chmod 400 private.pem
  sudo chown postgres:postgres private.pem
  </code></pre>
  
  <p>On DigitalOcean where I setup postgres, here is how I generate the server certificate</p>
  
  <pre><code>// assume we are in newly created droplet and created another super user(not root) called thor
  
  sudo apt-get -y -qq update &gt;&gt; /dev/null &amp;&amp; sudo apt-get -y -qq install postgresql postgresql-contrib &gt;&gt; /dev/null
  
  cd ~ &amp;&amp; mkdir experiment &amp;&amp; touch experiment/gen_server_cert.sh
  
  // paste the above generate server certificate code to it, chmod +x for it then run with
  
  ./gen_server_cert.sh brainpoolP512t1 thor
  
  // interactive shell will come up to ask about info and blab blah, just enter blank line for all except the password challenge field, assume I entered "123!@#QWEqwe"
  </code></pre>
  
  <p>It successfully output 4 files, those are: <code>certificate.crt</code> <code>private.pem</code> <code>root.crt</code> and <code>server.crs</code>, then I copy them to <code>/etc/ssl/</code></p>
  
  <pre><code>sudo cp private.pem /etc/ssl/private/postgresql_private.pem &amp;&amp; sudo cp root.crt /etc/ssl/certs/postgresql_server.crt
  </code></pre>
  
  <p>Before I setup actually setup the SSL, I want to verify that I could successfully connect remotely from my local machine using <code>dbeaver</code>.</p>
  
  <p>I start by creating a role for my postgres, create a testdb, change some config to allow connection from outside</p>
  
  <pre><code>sudo -u postgres -i
  createuser -P -s -e thor // another interactive shell came up, enter password then done
  // ctrl + d to get out from postgres
  createdb ssl_test_db
  
  sudo vim /etc/postgresql/10/main/pg_hba.conf
  
  // inside pg_hba.conf add this line
  ...
  host ssl_test_db thor 0.0.0.0/0 md5
  ...
  
  sudo vim /etc/postgresql/10/main/postgresql.conf
  
  // inside postgresql.conf uncomment the listen line and change it to listen to *
  
  listen_addresses '*'
  
  sudo service postgresql restart
  
  // from dbeaver at my client machine, the setting for the connect are
  Host: host_ip
  Port: 5432
  Database: ssl_test_db
  User: thor
  password: myverysecretpassword
  
  // hit the button and yes it successfully connected
  </code></pre>
  
  <p>Then I went to change those config again to use ssl</p>
  
  <pre><code>// inside pg_hba.conf comment the line that just added and add this one
  hostssl ssl_test_db thor 0.0.0.0/0 md5 clientcert=1
  
  // inside postgresql.conf uncomment some line and change some line
  ssl = on
  ssl_ciphers = 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!3DES:!MD5:!PSK' # allowed SSL ciphers
  ssl_prefer_server_ciphers = on
  ssl_ecdh_curve = 'brainpoolP512t1'
  ssl_cert_file = '/etc/ssl/certs/postgresql_server.crt'
  ssl_key_file = '/etc/ssl/private/postgresql_private.pem'
  ssl_ca_file = '/etc/ssl/certs/postgresql_server.crt'
  
  // out to terminal
  sudo service postgresql restart
  
  // check for status
  pg_lsclusters
  
  // output
  Ver Cluster Port Status Owner    Data directory              Log file
  10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
  </code></pre>
  
  <p>Then I open another terminal from local machine, sftp to it and get those necessary files to generate client certificate</p>
  
  <pre><code>sftp&gt; cd experiment
  sftp&gt; get root.crt
  sftp&gt; get private.pem
  </code></pre>
  
  <p>Then at my local machine, create an empty folder, copy paste the generate client certificate script, chmod +x it then run it to generate those key and certificate</p>
  
  <pre><code>// assume at ~
  // the files that get from server will also be at ~
  mkdir experiment &amp;&amp; cd experiment &amp;&amp; vim gen_client_cert.sh // copy paste in
  chmod +x ./gen_client_cert.sh
  
  ./gen_client_cert.sh brainpoolP512t1 thor ../root.crt ../private.pem
  </code></pre>
  
  <p>It will successfully generated 3 files, those are: <code>private.pem</code> <code>public.crt</code> and <code>server.scr</code></p>
  
  <p>Back to <code>dbeaver</code>, leave the above basic connection setting the same, go into SSL option and those setting are:</p>
  
  <pre><code>Use SSL - enabled
  Root certificate: ~/root.crt
  SSL certificate: ~/experiment/public.crt
  SSL certificate key: ~/experiment/private.pem
  </code></pre>
  
  <p>When I hit the test connection button, the error occur and the message are:</p>
  
  <pre><code>SSL error: Received fatal alert: handshake_failure
  </code></pre>
  
  <p>The log from the server are:</p>
  
  <pre><code>LOG:  could not accept SSL connection: no shared cipher
  </code></pre>
  
  <p>Verification of the generated public certificate:</p>
  
  <pre><code>// back to terminal at local machine and current directory at ~
  openssl verify -CAfile root.crt -purpose sslclient experiment/public.rt
  
  // output
  experiment/public.crt: OK
  </code></pre>