nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Secure postgres JDBC connection

Hamza Rezgui_jj773n

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 ?

Accepted answers

Charlie Misonne

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&<span>sslmode=verify-full</span>&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.

All comments

Charlie Misonne

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&<span>sslmode=verify-full</span>&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_jj773n

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_jj773n

I have Jira setup page

Jacob Francois_hmf90i

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

I had the same problem and it took me a while to figure it out. In my case the solution was to use "&" 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>

Christopher St_ John

Try "&" 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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

server

jira

jira-server

atl-gruland

ssl

postgres