Database-Access-Proxy Server Copy link to clipboard

The Database-Access-Proxy server is a Spring Boot based web server that redirects SQL select queries to a relational database such as DB2 used by DOORS Next or PostgreSQL used by Polarion. The advantage of using the Database-Access-Proxy server is, that the itemis ANALYZE user does not need to know the credentials for the database. Instead, the Database-Access-Proxy server can offer the SQL query results to anyone, or only to users who are authorized by a DOORS Next/Polarion web server. If the user is authorized, then responses to received SQL queries are filtered according to the projects the DOORS Next/Polarion user has access to by the Database-Access-Proxy server. The Database-Access-Proxy server does the filtering, by appending additional where clauses to received SQL queries before passing them to the relational database.

Configuring the Database-Access-Proxy Server Copy link to clipboard

The Database-Access-Proxy server is configured by a yml configuration file stored in the folder that contains the executable JAR archive of the Database-Access-Proxy server.

The following example configuration is stored in a file called itemis.yml:

spring:
  datasource:
    db2:
      url: jdbc:db2://db2.itemis.de:25000/JTS
      driver-class-name: com.ibm.db2.jcc.DB2Driver
      username: TheDB2User
      password: TheDB2Password
      maximum-pool-size: 10
    postgresql:
      url: jdbc:postgresql://polarion.itemis.de:5433/polarion
      driver-class-name: org.postgresql.Driver
      username: ThePostgreSQLUser
      password: ThePostgreSQLPassword
      maximum-pool-size: 10
api:
  dng_timeout: 5s
  endpoints:
    - path: /sql
      datasource: db2
      authentication_type: none
    - path: /dng
      datasource: db2
      authentication_type: DNG
      authentication_server: https://db2.itemis.de:9443
    - path: /polarion
      datasource: postgresql
      authentication_type: Polarion
      authentication_server: https://polarion.itemis.de/polarion

Below spring.datasource, several connections to relational databases can be configured. In the example above, only two connections called db2 and postgresql are configured. Each relational database connection has the following properties:

• url: The JDBC URL to the relational database. The format of the URL depends on the used JDBC driver.
• driver-class-name: The JDBC driver class name.
• username: The user name to login on the relational database.
• password: The password to login on the relational database.
• maximum-pool-size: The maximal number of JDBC connections in the pool which is also the maximal number of concurrent HTTP sessions for that database that the Database-Access-Proxy server can handle.

Below api.endpoints several paths on the Database-Access-Proxy server can be configured. Each path accepts SQL select queries via HTTP get and post requests and provides results in JSON. For each path, the following properties must be configured:

• path: The path on the HTTP server.
• datasource: The relational database to use as reference to a connection configured at spring.datasource.
• authentication_type: Either none to accept any requests, or DNG to require HTTP basic authentication against a DOORS Next server, or Polarion to require HTTP basic authentication against a Polarion server. In case of DNG and Polarion, the responses are filtered according to the projects the DOORS Next/Polarion user has access to.
• authentication_server: The URL to the DOORS Next/Polarion server to use for authentication. The authentication requires that the user has the needed privileges to use the REST API of the DOORS Next/Polarion server.

The configuration api.dng_timeout specifies the timeout for requests to a DOORS Next server. If the timeout exceeds, HTTP basic authentication of incoming HTTP requests fails.

Notice that configurations can be overwritten or provided alternatively via command line arguments. E.g. the following command line arguments can be used to provide user name and password for the configured db2 and postgresql connections in the example above:

-Dspring.datasource.db2.username=TheDB2User
-Dspring.datasource.db2.password=TheDB2Password
-Dspring.datasource.postgresql.username=ThePostgreSQLUser
-Dspring.datasource.postgresql.password=ThePostgreSQLPassword

Launching the Database-Access-Proxy Server Copy link to clipboard

The Database-Access-Proxy server is an executable JAR file that can be launched using Java 21 or newer.

java -Dloader.path=PathToJcc-12.1.3.0.jar -Dspring.datasource.db2.username=TheDB2User -Dspring.datasource.db2.password=TheDB2Password -Dspring.datasource.postgresql.username=ThePostgreSQLUser
-Dspring.datasource.postgresql.password=ThePostgreSQLPassword -jar server-0.0.1.jar --spring.config.name=itemis --server.port=8080

The argument PathToJcc-12.1.3.0.jar is the path to the file jcc-12.1.3.0.jar with the DB2 JDBC driver which can be downloaded from mvnrepository.com .

The arguments -Dspring.datasource.db2.username=TheDB2User, -Dspring.datasource.db2.password=TheDB2Password, -Dspring.datasource.postgresql.username=ThePostgreSQLUser, and -Dspring.datasource.postgresql.password=ThePostgreSQLPassword are optional. Also other configurations can be overwritten/specified in this way.

The name of the configuration to use is specified by argument --spring.config.name.
If the configuration is not contained in the working directory, then the path to the folder that contains the configuration file must be provided as URI by argument --spring.config.location. E.g.
--spring.config.location="file:/C:/database-access-proxy-server/.

The argument --server.port specifies the port at which the Database-Access-Proxy server will listen for incoming HTTP get and post requests.