Postgres ssl connection

Question

I am using: Red Hat 7.5 Postgres 9.6 Atlassian Service Desk 7.10.2 I have followed postgres's guide to setting up SSL: https://www.postgresql.org/docs/9.6/static/ssl-tcp.html This works perfectly on my confluence server (Confluence uses jdbc connector to connect over ssl to postgres 9.6), but on my Service Desk server when I add: ssl=true to: /var/atlassian/application-data/jira/dbconfig.xml jdbc:postgresql://XXX.XXX.XXX.XXX:5432/servicedesk?ssl=true I recieve this error: >LOG: could not accept SSL connection: sslv3 alert certificate unknown when I go to the Service Desk website I see an error indicating: Database: JIRA couldn't connect to your database JIRA failed to establish a connection to your database. This could be because: * Your database isn't running * The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.) * There is a network issue between JIRA and your database (e.g. firewall, database doesn't allow remote access etc.) There are several other solutions you can try, review our documentation and see what works for you. Learn more If I add this: sslfactory=org.postgresql.ssl.NonValidatingFactory so jdbc:postgresql://XXX.XXX.XXX.XXX:5432/servicedesk?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory when I go to the Service Desk website (which is https and the SSL on the HTTPS works just fine) I am presented with the initial setup menu (https://XXX.XXX.XXX.XXX:8443/secure/SetupMode!default.jspa) My pg_hba.conf file is set to allow: hostssl all all 0.0.0.0/0 md5 I have tried hostssl db db_user 0.0.0.0/0 md5 hostssl db db_user 0.0.0.0/0 cert hostssl db db_user 0.0.0.0/0 cert clientcert=1 All of my certs (Intermediate, root, and server) are loaded into the java keystore and placed in the correct location for postgres My database (and db user) matches the CN of my cert for the Service Desk server. If anyone has any suggestions I would appreciate it.

Joel_ei27jt · Answer

Thank you for your response.

1. I have restarted jira (many times)

2.  I have been using the atlassian java keytool (/opt/atlassian/jira/jre/bin/keytool) and have verified my certs are loaded.  I am using https on the webserver portion and that works just fine (on both confluence and service desk)

3. I am not sure that you can test this with openssl as it does not work with my working database either

https://www.postgresql.org/message-id/17849.1436537026%40sss.pgh.pa.us

EXAMPLE Log data from confluence database:

5bda5.647 2018-07-23 07:36:05 EDT confluence.example.com (51588) >LOG:  connection authorized: user=user database=confluence SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

There is (as far as I can tell) nothing wrong with my postgres setup as it works just fine on another database that was built identically.

Daniel Eads · Answer

Hi Joel,

Sorry you're having trouble! Let's gather some more information to see if we can figure out what's up.

* Have you restarted Jira since installing the SSL certs you're using for Postgres into Jira's keystore?
* After restarting Jira, were you able to verify that the certificates are being picked up? As an FYI, if you're using the .bin installer to set up Jira (the recommended method), it uses a bundled JRE and therefore you need to make sure you're using the keytool in Jira's install directory, not the system JRE/keytool.
* Are you able to establish a connection from the system Jira is on to Postgres?openssl s_client -connect <yourpostgres-ip>:5432/servicedesk -prexit

I'll take a look and see what ciphers should be supported in the meantime - curious that it seems to work for Confluence with the same postgres setup!

Thanks,
Daniel

Earl McCutcheon · Answer

Hello Joel,

Check out the following Guide for some troubleshooting steps to take on this error, and narrow it down a bit more:

* Startup check: JIRA database connections

Regards,
Earl