Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,165
Community Members
 
Community Events
165
Community Groups

Secure postgres JDBC connection

Edited

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 ?

1 answer

1 accepted

0 votes
Answer accepted
Charlie Misonne Community Leader 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. 

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

Hi Hamza,

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

Like Hamza Rezgui likes this

I have Jira setup page

image.pngimage.png

Charlie Misonne Community Leader 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

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

Charlie Misonne Community Leader 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

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 :/

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

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

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you