PostgreSQL - SSL connection without providing the root-ca certificate? I set the following parameters to enable SSL on my PostgreSQL server:


ssl = True
ssl_ca_file = 'path-to-root-ca' 
ssl_cert_file = 'path-to-server-crt'
ssl_key_file = 'path-to-server-key'


For my tests, I use the PGSSLROOTCERT variable which contains the path to the root ca certificate.

I realized that it is still possible for a client to connect to the database without providing the root CA certificate and that the prompt states that an SSL connection is used:

psql "postgres://user:XXXXXX@hostname:port/postgres"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q


If the PGSSLROOTCERT variable is set to a wrong value:

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql: SSL error: certificate verify failed


The pg_hba.conf file:

local  all  postgres    trust
hostssl  all  all  0.0.0.0/0  md5
In the absence of PGSSLROOTCERT or sslrootcert, the library libpq (which psql uses) looks in a compiled-in default location for the file.  And apparently, it finds it at that location.

For Linux, that location is ~/.postgresql/root.crt.  Although it is possible that "Debian 10.3-1.pgdg90+1" patched it to look someplace else instead.