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

**URL:** <https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253>\
**Category:** Ignition\
**Tags:** database\
**Created:** [June 1, 2022, 12:18pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253 "2022-06-01T12:18:00Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [June 1, 2022, 12:18pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/1 "2022-06-01T12:18:00Z")

</div>

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?

 ![SSL_connection](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/9/29439c69f90366c6ad7015427edcecce16988eae.png)

Can anyone help, please?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 12:26pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/2 "2022-06-01T12:26:41Z")

</div>

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

```auto
?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?

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [June 1, 2022, 12:34pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/3 "2022-06-01T12:34:43Z")

</div>

Thank you… 👍

But this is what I get:  
 ![Screenshot_15](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/7/47de8b054bf62d8dc73aa27c04e47ac499a4d901.png)

 ![Screenshot_14](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/1/9159419f8bb9a0b903a00ab0036ab4628fe811a5.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 12:37pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/4 "2022-06-01T12:37:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [June 1, 2022, 12:44pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/5 "2022-06-01T12:44:05Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/c/7c9c77d8755cb852f94ebdf832b591231fb582a2.png)

And in my **pg\_hba.conf** I have this:

```auto
# 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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 12:47pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/6 "2022-06-01T12:47:15Z")

</div>

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 }

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 12:48pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/7 "2022-06-01T12:48:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [June 1, 2022, 12:53pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/8 "2022-06-01T12:53:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [June 1, 2022, 12:53pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/9 "2022-06-01T12:53:22Z")

</div>

> [@pturmel](#):
>
> `sslfactory=org.postgresql.ssl.NonValidatingFactory`

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 1:25pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/10 "2022-06-01T13:25:54Z")

</div>

> [@Kevin.Herron](#):
>
> The certs would get dropped into Ignition’s supplemental certificate folder

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.

> [@Kevin.Herron](#):
>
> removing certificate trust

Yup. For a self-signed.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [June 1, 2022, 1:27pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/11 "2022-06-01T13:27:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 1, 2022, 1:30pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/12 "2022-06-01T13:30:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [June 1, 2022, 1:32pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/13 "2022-06-01T13:32:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [June 1, 2022, 1:35pm UTC](https://forum.inductiveautomation.com/t/how-to-configure-database-connection-to-postgresql-with-ssl-tls/60253/14 "2022-06-01T13:35:35Z")

</div>

> [@Kevin.Herron](#):
>
> Well, I don’t see why you couldn’t use the same self-signed cert for all the DB instances

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