How to configure database connection to PostgreSQL with SSL/TLS?

I’ve set up a test VM with PostgreSQL 12 server on Windows 10 and PostgreSQL is configured for SSL/TLS connections (with self-signed certificates), which are working.
I have tested the SSL connection from my PC with EMS Manager for PostgreSQL software nad is OK.
(you define in the software where the root.crt, server.crt, server.key files are)

Now, I have a hard time figuring out, what I must do on the Ignition server to create a secure database connection to this test PostgreSQL server with SSL…?
Where do I put root.crt, server.crt, server.key files on Ignition server (installed on Windows 2016 server)?
Are there any extra connection properties necessary?

Can anyone help, please?

No need to install any certs on the Ignition side. Add this to your URL:

?ssl=true&sslmode=require&sslfactory=org.postgresql.ssl.NonValidatingFactory

I suppose those can be split out as extra connection properties, but I don’t recall getting it to work that way. Maybe because they are applied after connection?

Thank you… :+1:

But this is what I get:
Screenshot_15

That’s a totally unrelated error: your gateway is failing to resolve ignserv4 to an IP address. Replace that with the IP address of the postgresql server if you can’t get your local DNS to behave.

Yes, you’re right (like always)… my VM network adapter was on NAT…
Now the DNS name is OK, but I get this now:

java.sql.SQLException: Cannot create PoolableConnectionFactory (FATAL: connection requires a valid client certificate)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:656)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:534)
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:299)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:252)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1096)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:582)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:613)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:161)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225)
	at org.postgresql.Driver.makeConnection(Driver.java:465)
	at org.postgresql.Driver.connect(Driver.java:264)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:357)
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:103)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:652)
	... 12 more

8.1.15 (b2022030114)
Azul Systems, Inc. 11.0.14.1

Screenshot_16

And in my pg_hba.conf I have this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv4 remote connections for authenticated users
hostssl   all     postgres             0.0.0.0/0            md5 clientcert=1
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I gues clientcert=1 is causing error?

That means you set up postgresql to only accept connections from clients that have a specifically signed certificate. I don’t think that is supported.

To have a normal encrypted connection, only the server needs a cert.

{ Get rid of the clientcert=1 }

Consider also not using the postgres user for production connections. Make a separate user that owns just the specific database.

Yep, when I remove clientcert=1 then the connection is established and it’s TLS1.3.

And yes, postgres user is only for testing in my test VM…

Thank you Phil. :+1:

The certs would get dropped into Ignition's supplemental certificate folder (no need for the private key).

I think this quoted option may be removing certificate trust from the equation.

Hmm. I'm pretty sure I needed the NonValidatingFactory recently (for a self-signed cert in postgresql)-- 8.1.15? I'll test next time.

Yup. For a self-signed.

You would need it if you didn’t also add that self-signed certificate to Ignition’s supplemental certificates.

Then I’d have to make a new, unique self-signed cert for each DB. Okay, that’s a good practice, equivalent to pinning website certs. Some might call it overkill.

Well, I don’t see why you couldn’t use the same self-signed cert for all the DB instances, but yes it would be good practice.

By turning off certificate validation an attacker or MITM could swap in another DB instance, and you’d happily connect without ever knowing. It’s probably not a vector most people are concerned with but you should at least know what you’re opting out of.

Ah, well if these are proper TLS certificates then they include the hostname, so nevermind that :slight_smile: