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?
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?
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
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
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.