Secure postgres JDBC connection

[Hamza Rezgui]

I am trying to connect Jira  to postgres database using SSL with "sslmode=verify-full"

The  error I am getting is

"org.postgresql.util.PSQLException: Could not open SSL root certificate file /home/jira/.postgresql/root.crt."

My client certicats (P12 format) is in $JAVA_HOME/jre/lib/security/

I need to change the path where it is looking for the certificate. Can I change the path, and how can I do that because i dont have /home/jira/.postgresql/root.crt. in my server ?

[Charlie Misonne · Accepted solution]

Hi Hamza, welcome to the Atlassian Community!

According to Posgrre's official documentation: Configuring the Client

The location of the client certificate, the PKCS-8 client key and root certificate can be overridden with the sslcert, sslkey, and sslrootcert settings respectively. These default to /defaultdir/postgresql.crt, /defaultdir/postgresql.pk8, and /defaultdir/root.crt respectively where defaultdir is ${user.home}/.postgresql/ in *nix systems and %appdata%/postgresql/ on windows

So you can use the sslrootcert parameter. You should append it to your jdbc URL just like you did for the sslmode parameter.

Something like:

jdbc:postgresql://1.2.3.4:5432/jiradb?ssl=true&sslmode=verify-full&sslrootcert=/your/path/root.crt

Same for the other parameters if you need them.

Hope this helps! Let me know if you can get it to work. 

[Hamza Rezgui]

Hi Charlie,

Thank you for the response.

I changed the jdbc string in dbconfig.xml to

<url>jdbc:postgresql://mydbserver:5432/jiradb?sslmode=verify-full&sslkey=/opt/atlassian/jira/atlassian-jira-software-8.13.3/jre/lib/security/mycertif.p12</url>

Now, i have no errors in logs, but Jira is not connected to the database :/

[Charlie Misonne]

Hi Hamza,

So what do you get when starting Jira in your browser?

[Hamza Rezgui]

I have Jira setup page

[Charlie Misonne]

Is your database empty or does it contain Jira data already?
If it's empty the easiest would be to remove the dbconfig.xml file and configure the database settings via the setup wizard keeping in mind the extra ssl parameters.

If you're manually configuring the dbconfig.xml file there's a chance to make mistakes and Jira will not load the XML correctly.
Atlassian has a configuration tool that can help you generate the file correctly.

Make sure the user running Jira on your system has read/write access to the dbconfig.xml as well.

[Hamza Rezgui]

no the database is not empty, if i run with no sslmode, i can connect to my old ready database.

[Charlie Misonne]

Well that's odd.

So if you change

<url>jdbc:postgresql://mydbserver:5432/jiradb?sslmode=verify-full&sslkey=/opt/atlassian/jira/atlassian-jira-software-8.13.3/jre/lib/security/mycertif.p12</url>

to

<url>jdbc:postgresql://mydbserver:5432/jiradb</url>

in the same dbconfig.xml (and configure Postgres to accept non ssl connections) it works?

I just want to make sure you're not using a copy of the dbconfig.xml and forgot to set file permissions by mistake. Because Jira will nog log an error when the file permissions are incorrect as stated in JRASERVER-66551

If you're certain of the file permissions I suggest you contact Atlassian Support. I don't have a full dbconfig example with ssl to share with you myself.

[Hamza Rezgui]

yes, whene i change the jdbc string in the same dbconfig file without ssl its work, Jira can connect the the database and and i ca, find my project and my data.

No problem with th dbconfig.xml file permission :/

[dujas]

hi Hamza,

I think I am in the same boat with you, may I know if there is any further update regarding the SSL connection to Postgresql?

Thanks.

Jason Du

[Prashantgouda KARAKANAGOUDR]

Has anyone able to setup this successfully for postgres? 

Is there any Atlassian documentation for the same? like one available for MySql.

https://confluence.atlassian.com/jirakb/configure-jira-server-to-connect-to-mysql-via-ssl-280691010.html

[dujas]

Hi Prashantgouda,

Please check link as below:
https://community.atlassian.com/t5/Jira-questions/SSL-connection-between-jira-service-management/qaq-p/1944433

Hope this will help.

Thanks.

Jason

[Christopher St_ John]

I had the same problem and it took me a while to figure it out. In my case the solution was to use "&amp;" between the url settings in dbconfig.xml. The ssl key must be in pk8 format (unless you are using a keystore). The username and password were specified in separate fields. There are some web pages that configure <connection-properties> in dbconfig.xml, but I could not get it to work. Ensure the jira user can read all of the certs specified. Here is a generic example url.

<url>jdbc:postgresql://<db_hostname>:<db_port>/<db_name>?ssl=true&amp;sslmode=verify-full&amp;sslrootcert=/my/cachain.cer&amp;sslkey=/my/privatekey.pk8&amp;sslcert=/my/public.crt</url>

Here is a more precise example.

<url>jdbc:postgresql://pgdb01.local.net:5432/jira?ssl=true&amp;sslmode=verify-full&amp;sslrootcert=/etc/pki/java/cachain.cer&amp;sslkey=/etc/pki/java/privatekey.pk8&amp;sslcert=/etc/pki/java/public.crt</url>

[Jacob Francois]

Did any one figure this out ? i tried https://community.atlassian.com/t5/Jira-questions/SSL-connection-between-jira-service-management/qaq-p/1944433 but it didn't work. When i use ";" i am able to connect to the db but i run into the same problem like above. Jira is asking me to set up a new DB. 

When i use "&" i get errors in my logs saying jira can't connect to the DB. 

[Christopher St_ John]

Try "&amp;" without the quotes. See my answer on this page. I found the solution in the MySQL page.

https://confluence.atlassian.com/jirakb/configure-jira-server-to-connect-to-mysql-via-ssl-280691010.html