Postgres ssl connection

[Joel]

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

<url>jdbc:postgresql://XXX.XXX.XXX.XXX:5432/servicedesk?ssl=true</url>

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

<url>jdbc:postgresql://XXX.XXX.XXX.XXX:5432/servicedesk?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory</url>

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.

[Daniel Eads]

Hi Joel,

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

1. Have you restarted Jira since installing the SSL certs you're using for Postgres into Jira's keystore?
2. 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.
3. 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

[Joel]

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.

[Earl McCutcheon]

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