How to connect with PostgreSQL database over SSL?

Question

I have created my own certificate and configured postgresql.conf file:

...
#authentication_timeout = 1min          # 1s-600s
ssl = true                              # (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                        # (change requires restart)
#ssl_prefer_server_ciphers = on         # (change requires restart)
#ssl_ecdh_curve = 'prime256v1'          # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/company/database/certificate'           # (change requires restart)
ssl_key_file = '/etc/ssl/certs/company/database/key'            # (change requires restart)
ssl_ca_file = '/usr/share/ca-certificates/company/ca/certificate'                        # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)
#password_encryption = on
#db_user_namespace = off
#row_security = on
...

Then, I allow my server to connect with my database, pg_hba.conf:

...
hostssl    postgres             postgres             XXX.XXX.XXX.XXX/0            md5
...

So, I can connect to it via psql command line:

psql (9.5.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

But, when I try to open a connection with the database via my java application, even when I provide the truststore with my database certificate included, I keep getting no connection with it:

mvn package -Djavax.net.ssl.trustStore=/opt/app/truststore -Djavax.net.ssl.trustStorePassword=changeit

Exception:

2016-05-23 16:28:32,900 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@75fa1be3 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.>
2016-05-23 16:28:32,900 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2be057bf -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: >
java.lang.NullPointerException
    at org.postgresql.Driver.parseURL(Driver.java:532)
    at org.postgresql.Driver.acceptsURL(Driver.java:431)
    at java.sql.DriverManager.getDriver(DriverManager.java:299)
    at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:285)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:161)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:161)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:147)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:202)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2016-05-23 16:28:32,901 WARN [com.mchange.v2.resourcepool.BasicResourcePool] - <Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@75fa1be3 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.>

Via psql everything seems to be working fine, not with my application. Any suggestion ?

EDIT:

My props.properties file:

uatDb.user=postgres
uatDb.password=password
uatDb.driverClass=org.postgresql.Driver
uatDb.jdbcUrl=jdbc:postgresql://<server_name>:1234/uat?ssl=true
uatDb.port=5443
uatDb.name=uat
uatDb.host=<server_name>

Post your Java code. Are you setting the connection properties for SSL? See https://jdbc.postgresql.org/documentation/head/connect.html — Andrew Henle, May 23 '16 at 14:56
@AndrewHenle yes, I have set the properties for this connection on a file. I have edited my question. I'm calling my database via `maven` to build my application which access my database in order to do some tests. — Valter Silva, May 23 '16 at 15:02
Assuming your connected works without SSL enabled, can you set `-Djavax.net.debug=all` in MAVEN_OPTS to get Java connection debugging data? See http://stackoverflow.com/questions/2007192/maven-jetty-plugin-how-to-control-vm-arguments (I'm no Maven guru...) — Andrew Henle, May 23 '16 at 15:23

score 6 · Answer 1 · answered Oct 03 '19 at 23:01

url=jdbc:postgresql://<host_url_or_ip>:<port>/<db_name>?currentSchema=<schema_name>&sslmode=verify-ca&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory

Note: if schema_name is public, it is not required. But port even if is default i.e. 5432, you have to provide it.

For sslmode values ref: https://jdbc.postgresql.org/documentation/head/ssl-client.html set sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory to enable validation.

For non-validating ssl connection, you can use sslfactory=org.postgresql.ssl.NonValidatingFactory

But remember, once you enable SSL validation, it may require a root CA certificate.

You have various options as follows (may not be exhaustive. but these worked for me.)

You can place it in its default place i.e ~/Postgres/root.crt OR
Set PGSSLROOTCERT env variable to its path OR
import into a truststore and pass it path as: -Djavax.net.ssl.trustStore=[trust_store_path] -Djavax.net.ssl.trustStorePassword=[trust_store_password]. If you are using default truststore i.e. JRE's cacerts these two env variables are not required.

Ref:

https://jdbc.postgresql.org/documentation/head/ssl-client.html

https://www.postgresql.org/docs/9.0/libpq-ssl.html

score 3 · Answer 2 · edited Oct 28 '18 at 17:35

3

Need a keystore too I believe. Since ssl=true should require a private key for your self-signed cert. I also would add "nonValidating SSLFactory" setting

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

Also your port setting for server_name:1234 does not match 5432.

"parseUrl" is a null error, maybe there's a variable somewhere in the JDBC url that is incorrectly set.

edited Oct 28 '18 at 17:35

lospejos

1,976
3
19
35

answered Apr 30 '17 at 18:51

Dexter

6,170
18
74
101

3

The "org.postgresql.ssl.NonValidatingFactory" factory bypasses all SSL certificate validation, making the SSL connection pointless. – Graham Leggett Dec 19 '17 at 17:58
1

Not sure whether `&` is correct or necessary. For me, it works with just `&`. – hgoebl Jun 26 '18 at 17:00

score 3 · Answer 3 · answered Apr 15 '20 at 22:01

3

If you don't need to verify database client, just comment out ssl_ca_file in postgresql.conf.

ssl_ca_file - trusted certificate authorities, checks that client certificate is signed by a trusted certificate authority.

Your database URL should look like this:

url=jdbc:postgresql://host:5432/db_name?ssl=true&sslmode=require

Refer to PostgreSQL JDBC docs for more details on sslmode.

Otherwise, in case you need to verify the database client, you should add sslcert=... and sslkey=... parameters to your database URL.

answered Apr 15 '20 at 22:01

Oleksandr Shmyrko

1,720
17
22

Hi ! How to use sslkey= ? Will I also need sslcert= or with key itself it will work? – Ashutosh Tiwari Feb 08 '22 at 09:40
@AshutoshTiwari, it will be something like this `jdbc:postgresql://host:5432/db_name?sslmode=verify-full&sslrootcert=CA.crt&sslcert=db-client.crt&sslkey=db-client.pk8` – Oleksandr Shmyrko Feb 08 '22 at 16:27

score 2 · Answer 4 · answered Sep 21 '22 at 20:26

For those that use Springboot & SSL

Convert the key from pem to pkcs8

openssl pkcs8 -topk8 -inform PEM -outform DER \
   -in client-key.pem \
   -out client-key.pkcs8 -nocrypt

Add the certs to db url in applications.properties

spring.datasource.url=jdbc:postgresql://<DB_IP>/mydbName?ssl=true&sslmode=verify-ca\
&sslcert=BOOT-INF/classes/db/certs/client-cert.pem\
&sslkey=BOOT-INF/classes/db/certs/client-key.pkcs8\
&sslrootcert=BOOT-INF/classes/db/certs/server-ca.pem

Hey, if anyone find a way to put this certs/key in env var and use inside jdbc-url, please edit/add that info. That would be better aligned with cloudnative/k8s.

score 1 · Answer 5 · edited Dec 01 '22 at 16:53

i had issue with jfrog artifactory connection to azure postgres, this below worked for me

sudo cat <<EOF >/opt/jfrog/artifactory/var/etc/system.yaml
configVersion: 1
shared:
    node:
    database:
        type: postgresql
        driver: org.postgresql.Driver
        url: "jdbc:postgresql://test-psqlserver.postgres.database.azure.com:5432/postgres?ssl=true&sslmode=require"
        username: psqladminun@test-psqlserver
        password: password:-)
EOF

score 0 · Answer 6 · answered Sep 06 '22 at 20:19

https://stackoverflow.com/users/3612771/d-v-santhosh-kiran is on to something here guys.

Some of their information is not perfectly accurate. sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory is good - but it relies on SSLSocketFactory.getDefault() which knows how to read CA trust stores from $JAVA_HOME/lib/security/cacerts, or from javax.net.ssl.* properties as per point number 3 in that answer. Points 1 and 2 are correct if you are using the default sslfactory, org.postgresql.ssl.LibPQFactory, but are 100% not at all compatible with DefaultJavaSSLFactory. So be careful.

Also, I propose option number 4. Add root CA to JRE cacerts file, and let java do its default thing. You'll have to get out keytool for this one, but it is more flexible than java.net.ssl settings if you have more than one client in your jvm at runtime.

keytool -importcert -keystore $JAVA_HOME/lib/security/cacerts -storepass changeit -noprompt -alias "$ALIAS" -file $YOUR_CERT -trustcacerts

See https://docs.oracle.com/en/java/javase/11/tools/keytool.html and mileage may vary. What you are trying to do is import (x509/pkix) certificates into the cacerts java keystore

fwiw org.postgresql.ssl.LibPQFactory is a terrible name that makes people think that the postgres driver will do some JNI and call into C. That is not what the author intended. The class could have been SslFactoryThatDoesPathsLikeLibPQBro. That's all it does regarding libpq: looks for certs in paths like LibPQ does. Apart from that it uses regular old java JSSE to load truststore certs for TLS. Have a look at the source code: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/ssl/LibPQFactory.java#L102

SSLContext ctx = SSLContext.getInstance("TLS");
... stuff about libpq path locations ...
TrustManagerFactory tmf = TrustManagerFactory.getInstance("PKIX");
...
ks = KeyStore.getInstance("jks");
...
CertificateFactory cf = CertificateFactory.getInstance("X.509");
Object[] certs = cf.generateCertificates(fis).toArray(new Certificate[]{});
ks.load(null, null);
for (int i = 0; i < certs.length; i++) {
  ks.setCertificateEntry("cert" + i, (Certificate) certs[i]);
}
tmf.init(ks);
tm = tmf.getTrustManagers();
...
// finally we can initialize the context
...
        KeyManager km = this.km;
        ctx.init(km == null ? null : new KeyManager[]{km}, tm, null);
...
      factory = ctx.getSocketFactory();

How to connect with PostgreSQL database over SSL?

6 Answers6

Linked