SonarQube with PostgreSQL

By:DALEEP SINGH

2020-Jun-24 11:06:15

In last article, we were able to deploy Sonarqube on a container and also run a basic source code test with it. When we logged in to the console, it gave a message that “Embedded database should be used for evaluation purpose only”. This is because, we were not using any external database with SonarQube.

In this article, we would be using PostgreSQL as database for Sonarqube. The database would be used to store the test results/issues, which can even be used to connect to Logstash and use Kibana to have a visual information of the gathered issues.

On our container host, first we will initiate Postgres container using community image from Docker Hub ( https://hub.docker.com/_/postgres ).

#podman run -d -e POSTGRES_PASSWORD=sonar -e POSTGRES_USER=sonar 

> -e POSTGRES_DATABASE=sonar --name postgresdb docker.io/postgres:latest

As the output shows, that postgresdb container is running. Now we will use the credentials and database name used to create the postgresdb container while initiating the SonarQube container. This way, when SonarQube is initialized, it will be able to connect to postgresdb container and create the database schema needed.

The standard image uses the embedded database. To enable connection to the database container and also pass the connection parameter as environment variable to the container, I modified the community image. It is available for download from my Quay repository             ( https://quay.io/repository/dbais76/sonarqube?tab=tags ) and the dockerfile is available on my Github page ( https://github.com/dbais76/sonarqube-postgre ).

Before initiating the SonarQube container, we need to get the IP Address assigned to the postgresdb container. 

#podman inspect -f '{{ .NetworkSettings.IPAddress }}' postgresdb

We will use the IP address from ouput of the above command while creating the SonarQube container now.

#podman run -d --name sonar -p 9000:9000 -e JDBC_USERNAME=sonar                                                                                                                                                                                                                                                                                                                          

> -e JDBC_PASSWORD=sonar -e JDBC_URL=jdbc:postgresql://10.88.0.120:5432/sonar  

> quay.io/dbais76/sonarqube:8.2-myver

If your container fails, check the logs using podman logs command to identify the root cause and troubleshoot. Sometimes, you might see below mentioned error message in the ouput.

To troubleshoot, run command sysctl -w vm.max_map_count=262144 

Once the SonarQube container is successfully up and running,  we can access SonarQube on localhost at port 9000 which was exposed during container creation.

Now when you access the SonarQube console, you will not be getting the message that we are using embedded database, as now we have made the connection to the postgresdb container using the IP address during initiation of sonar container. 

Lets connect to postgresdb container and login to the postgres. we will connect to sonar database, which we specified during SonarQube container creation. The username will be same as we specified in JDBC_USERNAME environment variable, which in this case is sonar.

Once connected to psql prompt, run command as shown below to get the list of tables in database sonar.

Let us now ceate one new user in SonarQube and check the information stored in relevant table in postgresdb container. As of now, for postgresdb container, we have not used a volume to persist the database, however, in production, you would be creating an external volume and will map it to postgres container to persist the database.

I create a user Daleep in SonarQube, as you can see from the below output.

The same information can also now be verified from the database on postgresdb container.

Now lets run some test on our source code and verify if they are available in the postgresdb container as well.

As of the 'issues' table don't have any record ( 0 rows ).

To demonstrate issues stored in postgresdb, I am quickly running the test, we did earlier as part of our last artlcle ( refer Code-Testing-With-SonarQube ).

As you can see from the below output, we have one issue reported in the SonarQube console.

Now if run the earlier command to see the 'issues' table, we will find that it has 01 record, which shows the same  error message as your SonarQube console output and also shows the status of the issue as "OPEN".

I now change the issue status to 'CONFIRMED'. 

The same status would be updated in the database as well as verified by below output.

This demonstration shows that all information's are now stored in postgresdb container, which can be used to connect and fetch information for further analysis. Hope this information would help you to persist the code issues reported by SonarQube in the PostgreSQL database.

Happy Reading !!