Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

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