Struggling to connect Jira to Postgresql

[William Nettmann]

I am pretty much at my wits end. I am trying tom configure the dbconfig.xml file using the bin/config.sh method. This is my dbhconfig.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
 <name>defaultDS</name>
 <delegator-name>default</delegator-name>
 <database-type>postgres72</database-type>
 <jdbc-datasource>
 <url>jdbc:postgresql://localhost:5432/jiradb</url>
 <driver-class>org.postgresql.Driver</driver-class>
 <username>jira</username>
 <password>xxxxxx</password>
 <pool-min-size>20</pool-min-size>
 <pool-max-size>20</pool-max-size>
 <pool-max-wait>30000</pool-max-wait>
 <validation-query>select 1</validation-query>
 <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
 <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
 <pool-max-idle>20</pool-max-idle>
 <pool-remove-abandoned>true</pool-remove-abandoned>
 <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 <pool-test-on-borrow>false</pool-test-on-borrow>
 <pool-test-while-idle>true</pool-test-while-idle>
 </jdbc-datasource>
</jira-database-config>

This is the output from config.sh:

Database Selection [P]> 
PostgreSQL Database Configuration.
Hostname (localhost)> 
Port (5432)> 
Database (jiradb)> 
Username (jira)> 
Password (*****)> 
Test Connection ([Y]/N)? > y
Attempting to connect to localhost:5432/jiradb
*** Connection failed: Could not connect to the DB: Connection attempt timed out.

--- Database Selection ---
 Database Type : PostgreSQL
 Instance : localhost:5432/jiradb
 Connect As : jira / *****

I can connect to the server using psql from the command line as user jira using the password above, output as follows:

root@ws00:/opt/atlassian/jira# psql -h localhost -p 5432 -U jira -d jiradb
Password for user jira: 
psql (9.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

jiradb=>

What am I missing?

[William Nettmann · Accepted solution]

I have found the solution - I needed to specifically open port 5234 to 127.0.0.1 in my firewall config. All now works fine.

I had thought that as the connection from Jira was being requested from localhost to localhost it was not necessary to open a port. psql works fine without opening the port, Jira or maybe Java (which I know very little about) connects as if it is an external request in some way.

[Andy Heinzer]

I noticed in your psql results it looks like your making an SSL/TLS connection.  I can tell you that Jira does not expect this kind of encrypted connection to the database.  Out of the box, the expectation is that the database is reachable without using SSL or TLS encryption.

So I would actually recommend trying to make sure that the database will still accept incoming connections that are not required to use encryption (at least from the localhost/loopback address).

That said, it might be possible to establish an SSL connection to the database, but first you will need to add the SSL certificate to the keystore of the Java runtime Jira is using (see Connecting to SSL services), and I would expect the dbconfig will need an additional URL string parameter in order to connect to that database.

Take a look at https://community.atlassian.com/t5/Jira-questions/Connecting-Jira-to-Postgres-with-forced-SSL-is-failing/qaq-p/600991

I think the syntax for this would look like this:

<url>jdbc:postgresql://postgres.server.name:8888/jiradb?ssl=true</url>

[Grigory Salnikov]

Hello,

Have you configured pg_hba.conf to accept md5? Usually, it is located here:

var/lib/pgsql/<version>/data/pg_hba.conf

You should find this string at the end of the file and edit it:

host    all             all             127.0.0.1/32            md5

Hope this helps.

[William Nettmann]

Thanks for the response.  I have tried dozens of entries in pg_hba.conf with no luck. It currently looks like this:

# "local" is for Unix domain socket connections only
local all all md5 
# IPv4 local connections:
host all all 127.0.0.1/32 md5

I can connect using psql, so I am guessing that my Postgres config is probably OK. 

[Grigory Salnikov]

Stupid as it may sound, try to replace all the "localhost"s with "127.0.0.1". I don't actually know what happens then, but sometimes it helps me.

[Percy Vilela Perez]

the same thing happened to me when restarting the server
any solution?