PostgreSQL JDBC via SSL

[dujas]

Guys,

I am using PostgreSQL 13.5 as the backend database for JSM 8.21.0.

To enable mTLS connection between JSM and database, I have turned on SSL on database side, it is working fine. And generated the necessary files on JSM side, I could verify it via:

psql "host=<FQDN> port=5432 user=jiradbuser dbname=jiradb sslmode=verify-full sslcert=/path/to/jiradb.crt sslrootcert=/path/to/root.crt sslkey=/path/to/jiradb.key"

Meanwhile, in pg_hba.conf, I have added "cert clientcert=1" as well.

However, when I turned that into JDBC string it is always complaining:

no pg_hba.conf entry for host <ip address of JSM>, user "jiradbuser", database "jiradb", SSL off

May I know what the correct format of the JDBC should be?

[dujas]

I have figured out the issue:

1. The delimiter should be "&" instead of ";", afterwards the complaining changed to "cannot read the private key"

2. The default private key is PKCS#1, I converted it to PKCS#8 via command below: 

3. Delete the plain text password in dbconfig.xml

Now the service is up and running under mTLS mode.

[dujas]

openssl pkcs8 -topk8 -inform PEM -outform DER -in <source key file> -out <target key file> -nocrypt

[Nic Brough -Adaptavist-]

This is not an error in your Jira settings, the pg_hba file is access control on the database.  It is saying you need to add access for Jira to that file on the database side.

[dujas]

Hi Nic,

Thanks for your prompt reply.

I am a little confused regarding the access control part, pg_hba.conf by default is 0600, do you mean I have to add extra permission on it?

Jason

[Nic Brough -Adaptavist-]

No, you need to add access for Jira to the file.

[dujas]

hi Nic,

Sorry I did not get you here, could you please guide me a little bit on how to do this?

Thanks.

Jason

[Nic Brough -Adaptavist-]

The pg_hba.conf file does access control.  When your connection from Jira is trying to read/write the database, the postgres server is refusing access because the pg_hba file does not have a rule that lets your user into iit.

You need to add a rule to the pg_hba.conf file to allow your user into the database.

See https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

[dujas]

Thanks Nick.

I have checked the configuration in pg_hba.conf, which is:

hostssl jiradb all 0.0.0.0/0 cert clientcert=1

This indicates postgres is accepting all users via cert authentication.

[Nic Brough -Adaptavist-]

The error message says there's no access for "SSL off", and that line you've got is for SSL connections.

This suggests that you have not configured Jira to connect over SSL.

[dujas]

Thanks Nick for your reply, I have figured out the issue, will update it later.