Fail to connect to database (psql)

SonarQube Server / Community Build
1

Hi,

I’m setting up a trial of SonarQube developer edition (sonarqube-developer-8.3.1.34397) in one of my test servers.

I’ve followed the documentations from here,

I had some issues but I was able to resolve them except 1, the web.log showed that it failed to connect to the database.

Here are the steps that I have done for the Postgres side of things,

sudo su postgres
createuser sonar
psql postgres -W
Password:
psql (10.4)
postgres=# ALTER USER sonar WITH ENCRYPTED password ‘password’;
ALTER ROLE
postgres=# CREATE DATABASE sonar WITH ENCODING ‘UTF8’ OWNER sonar;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------±---------±---------±------------±------------±----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
sonar | sonar | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

I have tried both scenarios where I run and did not run (both didn’t work),

ALTER USER sonar SET search_path to sonar

I have tested the manual Postgres login using sonar/password credentials to make sure the login information is correct,

psql sonar -W
Password:
psql (10.4)
Type “help” for help.
sonar=#

The config that I had put into the sonar.properties file (tried both of the following),

sonar.jdbc.username=sonar
sonar.jdbc.password=password
sonar.jdbc.url=jdbc:postgresql://localhost/sonar

sonar.jdbc.username=sonar
sonar.jdbc.password=password
sonar.jdbc.url=jdbc:postgresql://localhost:5432/sonar

Here are the logs,
20200512_sonarqube_web.log (12.3 KB) 20200512_sonarqube_sonar.log (2.2 KB) 20200512_sonarqube_es.log (3.8 KB)

The configuration seems correct but it’s still not able to connect to the local psql database.

Anyone had this issue before?

Thanks!

3

Hello @shasanuddin,

I am not a postgres expert so I’ll not be able to troubleshooting that deeply.
It seems you have done everything right. There’s only one thing I am suspicious about, it’s the encrypted sonar user password.
I suggest you do one thing at a time: Test first with unencrypted password to check if SonarQube can connect.

  • If it does, you have the root cause and you can find out how to connect to DBs
  • If it does not, then that’s not the problem and we’ll search further (please keep unencrypted password anyway for the duration of the troubleshooting, let’s not multiply possible problems

PS: Obviously when everything will be sorted out it should be possible to use encrypted passwords

4

Hi @OlivierK,

I’m running Postgres 10.4 which is well supported according to the documentation here,
https://docs.sonarqube.org/latest/requirements/requirements/

Unfortunately UNENCRYPTED password is no longer supported in Postgres 10+
https://www.postgresql.org/docs/10/sql-createuser.html

Kindly advise.

Thanks!

5

Hello,

OK, fair enough.
Please set sonar.verbose=true in your sonar.properties file, restart SonarQube and attach a zip of the full <SONARQUBE_HOME>/logs directory to this thread

Regards

6

Hi @OlivierK,

I have set verbose to true and restarted SonarQube.

Kindly find the attached logs.

Thankslogs.zip|attachment (5.3 KB)

7

Hello,
Somehow the attachment is broken, I can’t download it. Can you re-attach
Thanks

8

Hi @OlivierK,

Kindly find the attached.

Thanks!
sonar.log (2.2 KB)
es.log (3.8 KB)
web.log (12.3 KB)

9

OK, looking at the web.log file and in particular:
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (FATAL: Ident authentication failed for user "sonar")
I guess that the error is not a login/password error. It’s because postgres is configured to use the ident protocol to accept connections, not login/password.
See https://serverfault.com/questions/406606/postgres-error-message-fatal-ident-authentication-failed-for-user on this error and how to fix it (change postgres configuration)

Let me know if changing host all all all ident by host all all all md5 in your pg_hba.conf file fixes the problem. (Maybe not alleverywhere this depend on your DB access policy :thinking:

10

Hi @OlivierK,

If you take a look at my original post, I was able to login to psql using sonar user from the server locally, that’s because I had initially updated pg_hba.conf to handle this.

psql sonar -W
Password:
psql (10.4)
Type “help” for help.
sonar=#

# grep md5 /var/lib/pgsql/data/pg_hba.conf
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# Note that "password" sends passwords in clear text; "md5" or
local   all             all                                     md5

Kindly advise.

Thanks!

11

Hello,
Indeed I missed that detail in the original thread. However it’s clear that the SonarQube platform (through the Postgres JDBC drivers) attempts to connect in ident mode, you may want to look again at your pg_hba.conf to check for reasons for that.
I am no expert in postgres but you may want to change you configuration line with:

local all all trust

or

host all all all md5

To check if that works and then tighten the security progressively

13

I had this problem also, after spending hours and hours checking all sorts of files and configurations, I did a restart on sonarqube, had to wait for it to gracefully exit which took a while but when it restarted my ‘Fatal error Ident authication of user sonar’ was gone and all was good. I had changed a few things before the restart but nothing I hadn’t tried before