I've installed Postgres 10.1 on a Windows server, and it's running as a Windows service. It's installed into %APPDATA% for the Local System user, which is the user account the Window service is running with:
C:\Windows\System32\config\systemprofile\AppData\Roaming\My App Database
My server-side SSL certs are in this same directory, and my postgresql.conf is configured to find them:
ssl_cert_file = 'db.crt'
ssl_key_file = 'db.key'
ssl_ca_file = 'ca.crt'
My pg_hba.conf file seems to be configured correctly:
hostssl my-database my-username 0.0.0.0/0 cert clientcert=1 map=my-username
I'm trying to connect with the psql command line tool:
psql "postgresql://my-username@localhost/my-database?sslmode=verify-full&sslkey=C:\MyDir\MyClientCert.key&sslcert=C:\MyDir\MyClientCert.crt"
but I get this error:
psql: root certificate file "C:\Users\Administrator\AppData\Roaming/postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification.
I can't find any reference to root.crt in my config, and I've no idea why it's looking in %APPDATA%\postgresql, rather than the configured PGDATA directory, %APPDATA%\My App Database.
Connecting with sslmode=verify-full implies that you want the client to verify the server's certificate which requires specifying a "root certificate" using "sslrootcert" connection parameter or "PGSSLROOTCERT" environment variable. I would hazard to guess that it is supplying %APPDATA%\postgres\root.crt as the default.
The error message supplies your solution, but I suspect you misunderstood -- this is a client error not a server error, the server configuration is immaterial.
You can change sslmode to "require" or provide a root cert.