title: Unable to connect to PostgreSQL with SSL on Django
body: <p>My Django application is hosted on one server, and my PostgreSQL database on another. I want the communication between the two servers to be forced to go through SSL.</p>
  
  <p>My database configuration in Django's <code>settings.py</code>:</p>
  
  <pre><code>'default': {
          'ENGINE': 'django.db.backends.postgresql_psycopg2',
          'NAME': os.environ.get("DB_NAME"),
          'USER': os.environ.get("DB_USER"),
          'PASSWORD': os.environ.get("DB_PASS"),
          'HOST': os.environ.get("DB_HOST"),
          'PORT': '5432',
          'OPTIONS': {
                   'sslmode': 'verify-full',
                   'sslrootcert': '/home/{user}/.postgresql/default_root.crt',
                   'sslcert': '/home/{user}/.postgresql/default.crt',
                   'sslkey': '/home/{user}/.postgresql/default.key',
                  },
      }
  </code></pre>
  
  <p>My configurations in the <code>pg_hba.conf</code> file:</p>
  
  <pre><code>hostssl {DB_NAME}   {DB_USER}   {DJANGO_SERVER_IP}        md5 clientcert=1
  </code></pre>
  
  <p>Running <code>python manage.py dbshell --database default</code> throws the following errors:</p>
  
  <pre><code>psql: FATAL:  connection requires a valid client certificate
  FATAL:  no pg_hba.conf entry for host "{DB_HOST}", user "{DB_USER}", database "{DB_NAME}", SSL off
  </code></pre>
  
  <p>The full error traceback:</p>
  
  <pre><code>Traceback (most recent call last):
    File "manage.py", line 20, in &lt;module&gt;
      main()
    File "manage.py", line 16, in main
      execute_from_command_line(sys.argv)
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
      utility.execute()
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute
      self.fetch_command(subcommand).run_from_argv(self.argv)
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/core/management/base.py", line 323, in run_from_argv
      self.execute(*args, **cmd_options)
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/core/management/base.py", line 364, in execute
      output = self.handle(*args, **options)
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/core/management/commands/dbshell.py", line 22, in handle
      connection.client.runshell()
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/db/backends/postgresql/client.py", line 71, in runshell
      DatabaseClient.runshell_db(self.connection.get_connection_params())
    File "/home/{user}/.virtualenvs/{project_name}/lib/python3.6/site-packages/django/db/backends/postgresql/client.py", line 61, in runshell_db
      subprocess.check_call(args)
    File "/usr/lib/python3.6/subprocess.py", line 311, in check_call
      raise CalledProcessError(retcode, cmd)
  subprocess.CalledProcessError: Command '['psql', '-U', '{DB_USER}', '-h', '{DB_HOST}', '-p', '5432', '{DB_NAME}']' returned non-zero exit status 2.
  </code></pre>
  
  <p>However, when I run <code>psycopg2.connect("host={DB_HOST} dbname={DB_NAME} user={DB_USER} password={DB_PASS} sslmode=verify-full sslcert=/home/{user}/.postgresql/default.crt sslkey=/home/{user}/.postgresql/default.key sslrootcert=/home/{user}/.postgresql/default_root.crt")</code> in the Django python shell, connection can be established.</p>
  
  <p>Also, <code>openssl verify -CAfile default_root.crt default.crt</code> shows <code>default.crt: OK</code>.</p>
  
  <p>Any help will be greatly appreciated. Thanks in advance!</p>