Secure postgres JDBC connection

Hamza Rezgui May 28, 2021

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 ?

3 answers

1 accepted

0 votes
Answer accepted
Charlie Misonne
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 28, 2021

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 sslcertsslkey, 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 May 30, 2021

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 30, 2021

Hi Hamza,

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

Like Hamza Rezgui likes this
Hamza Rezgui May 31, 2021

I have Jira setup page

image.pngimage.png

Charlie Misonne
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 31, 2021

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.

Like Hamza Rezgui likes this
Hamza Rezgui May 31, 2021

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

Charlie Misonne
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 31, 2021

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.

Like Hamza Rezgui likes this
Hamza Rezgui May 31, 2021

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 February 14, 2022

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 March 7, 2022

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 March 8, 2022

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

Like # people like this
0 votes
Christopher St_ John August 30, 2023

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>
0 votes
Jacob Francois March 22, 2023

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 August 30, 2023

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

Like Charlie Misonne likes this

Suggest an answer

Log in or Sign up to answer