3.2.1 SonarQube - PostgreSQL Server Configuration

PostgreSQL Setup:

PostgreSQL Server for Windows can be installed using the PostgreSQL Installer that installs and updates all PostgreSQL products using Stackbuilder on Windows or the standalone .msi installation package.

Important

Installing pgAdmin4 using an Installer package requires either Administrator or Power User privileges.

Requirements for Windows

• Microsoft .NET Framework 4.5

• Microsoft Visual C++ 2015 Redistributable Package

• Java Development Kit

• ODBC Installer for PostgreSQL

Installation Using PostgreSQL Installer

The general PostgreSQL Installer download is available at https://www.postgresql.org/download/windows/ . The PostgreSQL Installer application can install, upgrade, and manage most PostgreSQL products, including pgAdmin4.

Once the installation is done configure the setup by changing the root password and remember for future purpose.

After the installation open the "pgAdmin4" and connect to the default instance by entering the root password.

Open a new query editor and run the query for user creation.

-- Role: sonar
-- DROP ROLE sonar;

CREATE ROLE sonar WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION
  ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:0RsMPl+De6xiEdIiQ24ePg==$w6UzL+2cfXr+9EhDxXb0Qnxtv2G3j34EaIlm29+4i1w=:yxYcgSpwgKNZtIDSOjOcrfliz/SpDf1ggA/F54HCilc=';

COMMENT ON ROLE sonar IS 'Sonarqube user';

Database creation

-- Database: sonarqube

-- DROP DATABASE sonarqube;

CREATE DATABASE sonarqube
    WITH 
    OWNER = sonar
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Navigate to the earlier download location of SonarQube. Right click the zip file and choose unblock.

• Unzip the file and copy the binaries to the folder C:\SonarQube\

• Open the SonarQube properties file sonar.properties from the following location C:\SonarQube\sonarqube-8.9.0.43852\conf.

• In the wrapper.conf look for the update in Java

  # Path to JVM executable. By default it must be available in PATH.
  # Can be an absolute path, for example:
  #wrapper.java.command=/path/to/my/jdk/bin/java
  wrapper.java.command=C:/Program Files/Amazon Corretto/jdk11.0.11_9/bin/java

In the sonar.properties file look for ‘#—– PostgreSQL Server. This needs to have the connection string for the PostgreSQL server database you intend to use.

• Update the section by adding the connection string of the database

# Property values can:
# - be overridden by environment variables. The name of the corresponding environment variable is the
#   upper-cased name of the property where all the dot ('.') and dash ('-') characters are replaced by
#   underscores ('_'). For example, to override 'sonar.web.systemPasscode' use 'SONAR_WEB_SYSTEMPASSCODE'.
# - be encrypted. See https://redirect.sonarsource.com/doc/settings-encryption.html

#--------------------------------------------------------------------------------------------------
# DATABASE
#
# IMPORTANT:
# - The embedded H2 database is used by default. It is recommended for tests but not for
#   production use. Supported databases are Oracle, PostgreSQL and Microsoft SQLServer.
# - Changes to database connection URL (sonar.jdbc.url) can affect SonarSource licensed products.

# User credentials.
# Permissions to create tables, indices and triggers must be granted to JDBC user.
# The schema must be created first.
sonar.jdbc.username=sonar
sonar.jdbc.password=sonar

#----- Embedded Database (default)
# H2 embedded database server listening port, defaults to 9092
#sonar.embeddedDatabase.port=9092


#----- Oracle 11g/12c/18c/19c
# The Oracle JDBC driver must be copied into the directory extensions/jdbc-driver/oracle/.
# Only the thin client is supported, and we recommend using the latest Oracle JDBC driver. See
# https://jira.sonarsource.com/browse/SONAR-9758 for more details.
# If you need to set the schema, please refer to http://jira.sonarsource.com/browse/SONAR-5000
#sonar.jdbc.url=jdbc:oracle:thin:@localhost:1521/XE


#----- PostgreSQL 9.3 or greater
# By default the schema named "public" is used. It can be overridden with the parameter "currentSchema".
sonar.jdbc.url=jdbc:postgresql://localhost/sonarqube?currentSchema=public


#----- Microsoft SQLServer 2014/2016/2017 and SQL Azure
# A database named sonar must exist and its collation must be case-sensitive (CS) and accent-sensitive (AS)
# Use the following connection string if you want to use integrated security with Microsoft Sql Server
# Do not set sonar.jdbc.username or sonar.jdbc.password property if you are using Integrated Security
# For Integrated Security to work, you have to download the Microsoft SQL JDBC Driver 9.2.0 package from
# https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15#92
# and copy mssql-jdbc_auth-9.2.0.x64.dll to your path.
#sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true

# Use the following connection string if you want to use SQL Auth while connecting to MS Sql Server.
# Set the sonar.jdbc.username and sonar.jdbc.password appropriately.
#sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar


#----- Connection pool settings
# The maximum number of active connections that can be allocated
# at the same time, or negative for no limit.
# The recommended value is 1.2 * max sizes of HTTP pools. For example if HTTP ports are
# enabled with default sizes (50, see property sonar.web.http.maxThreads)
# then sonar.jdbc.maxActive should be 1.2 * 50 = 60.
#sonar.jdbc.maxActive=60

# The maximum number of connections that can remain idle in the
# pool, without extra ones being released, or negative for no limit.
#sonar.jdbc.maxIdle=5

# The minimum number of connections that can remain idle in the pool,
# without extra ones being created, or zero to create none.
#sonar.jdbc.minIdle=2

# The maximum number of milliseconds that the pool will wait (when there
# are no available connections) for a connection to be returned before
# throwing an exception, or <= 0 to wait indefinitely.
#sonar.jdbc.maxWait=5000

#sonar.jdbc.minEvictableIdleTimeMillis=600000
#sonar.jdbc.timeBetweenEvictionRunsMillis=30000



#--------------------------------------------------------------------------------------------------
# WEB SERVER
# Web server is executed in a dedicated Java process. By default heap size is 512MB.
# Use the following property to customize JVM options.
#    Recommendations:
#
#    The HotSpot Server VM is recommended. The property -server should be added if server mode
#    is not enabled by default on your environment:
#    http://docs.oracle.com/javase/8/docs/technotes/guides/vm/server-class.html
#
#    Startup can be long if entropy source is short of entropy. Adding
#    -Djava.security.egd=file:/dev/./urandom is an option to resolve the problem.
#    See https://wiki.apache.org/tomcat/HowTo/FasterStartUp#Entropy_Source
#
#sonar.web.javaOpts=-Xmx512m -Xms128m -XX:+HeapDumpOnOutOfMemoryError

# Same as previous property, but allows to not repeat all other settings like -Xmx
#sonar.web.javaAdditionalOpts=

# Binding IP address. For servers with more than one IP address, this property specifies which
# address will be used for listening on the specified ports.
# By default, ports will be used on all IP addresses associated with the server.
sonar.web.host=0.0.0.0

# Web context. When set, it must start with forward slash (for example /sonarqube).
# The default value is root context (empty value).
#sonar.web.context=
# TCP port for incoming HTTP connections. Default value is 9000.
sonar.web.port=9000


# The maximum number of connections that the server will accept and process at any given time.
# When this number has been reached, the server will not accept any more connections until
# the number of connections falls below this value. The operating system may still accept connections
# based on the sonar.web.connections.acceptCount property. The default value is 50.
sonar.web.http.maxThreads=50

# The minimum number of threads always kept running. The default value is 5.
#sonar.web.http.minThreads=5

# The maximum queue length for incoming connection requests when all possible request processing
# threads are in use. Any requests received when the queue is full will be refused.
# The default value is 25.
#sonar.web.http.acceptCount=25

# By default users are logged out and sessions closed when server is restarted.
# If you prefer keeping user sessions open, a secret should be defined. Value is
# HS256 key encoded with base64. It must be unique for each installation of SonarQube.
# Example of command-line:
# echo -n "type_what_you_want" | openssl dgst -sha256 -hmac "key" -binary | base64
#sonar.auth.jwtBase64Hs256Secret=

# The inactivity timeout duration of user sessions, in minutes. After the configured
# period of time, the user is logged out.
# The default value is set to 3 days (4320 minutes).
# It must be set between 5 minutes and 3 months.
# Value must be strictly positive.
sonar.web.sessionTimeoutInMinutes=4320

Save and Start the Server to get the sonarqube service running.