Dependency Check client fails (due to postgresql database schema?)

[ptschack]

Describe the bug We run the OWASP dependency check client inside a corporate network. The app consists of 2 docker containers:

• postgres:9.6.20 (from hub.docker.com, this container's database should hold the NVD)
• a custom docker container running the dependency check client with gradle

While running v6.1.0 of the OWASP dependency check, the client fails, ostensibly because the DB schema does not seem to fit the one expected by the client. This is not an upgrade, I am trying to set up everything from scratch.

Version of dependency-check used The problem occurs using version 6.1.0 of the org.owasp.dependencycheck.gradle.DependencyCheckPlugin

Log file

• Docker logs (excerpt) of container when using initialize_postgres.sql: https://gist.github.com/ptschack/9acef77f5680d6155f2382ac4526d73b
• Docker logs (excerpt) of container when using initialize.sql: https://gist.github.com/ptschack/b460cd11addd10acd426b4057eb59802

To Reproduce Steps to reproduce the behavior:

1. Run a docker postgres container (postgres:9.6.20), with exposed port 5432 and variables POSTGRES_PASSWORD, POSTGRES_USER and POSTGRES_DB set, running on a bridge network
2. Inside the postgres container, run one of the following:
   • psql --username postgres --dbname postgres --no-password -f /initialize.sql
   • psql --username postgres --dbname postgres --no-password -f /initialize_postgres.sql
3. Create a custom docker container containing gradle and the following files:
   • gradle.properties (values in {{}} are substituted by ansible)

     systemProp.proxySet="true"
     systemProp.http.proxyHost="{{proxy_host}}"
     systemProp.http.proxyPort="{{proxy_port}}"
     systemProp.http.proxyUser="{{proxy_user}}"
     systemProp.http.proxyPassword="{{proxy_pwd}}"
     systemProp.http.nonProxyHosts="{{no_proxy}}"
     systemProp.https.proxyHost="{{proxy_host}}"
     systemProp.https.proxyPort="{{proxy_port}}"
     systemProp.https.proxyUser="{{proxy_user}}"
     systemProp.https.proxyPassword="{{proxy_pwd}}"
     systemProp.https.nonProxyHosts="{{no_proxy}}"

   • database.gradle

     
     buildscript {
     repositories {
     maven { url "http://m2repo.[DOMAIN REDACTED]/content/groups/full" }
     }
     dependencies {
     classpath 'org.owasp:dependency-check-gradle:6.1.0'
     classpath 'org.postgresql:postgresql:42.2.18'
     }
     }

apply plugin: org.owasp.dependencycheck.gradle.DependencyCheckPlugin

dependencyCheck { cveValidForHours = 0 data { connectionString = "jdbc:postgresql://dependencyDB:5432/postgres?useSSL=false" driver = "org.postgresql.Driver" username = "postgres" password = "DATABASE_PASSWORD" } }

* **update.sh**
`gradle --info --debug --stacktrace --no-daemon -b database.gradle dependencyCheckUpdate`
4. Run the custom container. The entrypoint is `update.sh`. The container is also connected to the bridge network.

**Expected behavior**
The dependency check plugin should be able to download the NVD into the attached PostgreSQL database.

**Additional context**
The whole setup is automated with Ansible.
I will post further logs/info upon request. The size limit on Gists prevented me from posting everything.

[jeremylong]

With the postgres instance created with the postgres initialization script - were there any errors encounted running the initialization script? Can you run:

SELECT value FROM properties WHERE id = 'version'

[ptschack]

Hello @jeremylong,

Thanks for answering! I mistakenly thought the DB initscripts were applied successfully, but after your comment I executed them manually. Both threw errors, here are the logs:

• for initialize_postgres.sql: https://gist.github.com/ptschack/d992e21d8651fdcaf299d62fb86a5462
• for initialize.sql: https://gist.github.com/ptschack/4416af52058bf675b6786f095214817e

The SELECT value FROM properties WHERE id = 'version' command returns the following output:

• After using initialize.sql:

  FROM properties WHERE id = 'version'\""
  value 
  -------
  5.0
  (1 row)

• After using initialize_postgres.sql:

  ERROR:  relation "properties" does not exist
  LINE 1: SELECT value FROM properties WHERE id = 'version'

[jeremylong]

If you are using postgres - there is no need to run the initialize.sql. Just use the initialize_postgres.sql. Also, I'm not sure what is going on as the errors you are reporting are referencing line numbers that do not exist in the file; for instance psql:/initialize_postgres.sql:565. The initialize_postgres.sql is only 210 lines long.

[ptschack]

Hi @jeremylong,

I only used either initialize.sql or initialize_postgres.sql, never both at the same time. The different line numbers were because VSCode formatted the sql files, sorry for the confusion! I have reset everything and run initialize_postres.sql again (this time with the original formatting), this is the output:

CREATE DATABASE
You are now connected to database "dependencycheck" as user "postgres".
CREATE ROLE
psql:/initialize_postgres.sql:5: ERROR:  syntax error at or near ";"
LINE 1: DROP FUNCTION IF EXISTS public.save_property;
                                                    ^
psql:/initialize_postgres.sql:6: ERROR:  syntax error at or near ";"
LINE 1: DROP FUNCTION IF EXISTS public.update_vulnerability;
                                                           ^
psql:/initialize_postgres.sql:7: ERROR:  syntax error at or near ";"
LINE 1: DROP FUNCTION IF EXISTS public.insert_software;
                                                      ^
psql:/initialize_postgres.sql:8: ERROR:  syntax error at or near ";"
LINE 1: DROP FUNCTION IF EXISTS public.merge_ecosystem;
                                                      ^
psql:/initialize_postgres.sql:9: NOTICE:  table "software" does not exist, skipping
DROP TABLE
psql:/initialize_postgres.sql:10: NOTICE:  table "cpeentry" does not exist, skipping
DROP TABLE
psql:/initialize_postgres.sql:11: NOTICE:  table "reference" does not exist, skipping
DROP TABLE
psql:/initialize_postgres.sql:12: NOTICE:  table "properties" does not exist, skipping
psql:/initialize_postgres.sql:13: NOTICE:  table "cweentry" does not exist, skipping
DROP TABLE
DROP TABLE
psql:/initialize_postgres.sql:14: NOTICE:  table "vulnerability" does not exist, skipping
DROP TABLE
DROP TABLE
psql:/initialize_postgres.sql:15: NOTICE:  table "cpeecosystemcache" does not exist, skipping
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
GRANT
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
INSERT 0 1

This is the output of SELECT value FROM properties WHERE id = 'version':

ERROR:  relation "properties" does not exist
LINE 1: SELECT value FROM properties WHERE id = 'version'
                          ^

[ptschack]

Problem also present when using files from OWASP Dependency Check 6.1.1

[ptschack]

Upgraded to Dependency Check v6.1.2, problem persists.

[jeremylong]

I just recreated my local postgres and did not experience any issues. I know - not helpful; but it works on my machine ;)

I'm using PostgreSQL 12.3:

SELECT version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit
(1 row)

[mprins]

it seems you messd up somthing in "initialize_postgres.sql" file as your log shows

CREATE DATABASE
You are now connected to database "dependencycheck" as user "postgres".
CREATE ROLE

but there are only comments, no statements like that in https://github.com/jeremylong/DependencyCheck/blob/28dee17e9c923e63716cc91ac4dafcfa9ca435ae/core/src/main/resources/data/initialize_postgres.sql#L1-L17

so my gues is you introducend an error hence the initial command stops at the first statement ending (";")

[ptschack]

Hi @mprins , thank you for your answer, you solved my problem! I was mistakenly uncommenting the top three lines, when I just had to uncomment & customize the CREATE USER ... statement :-/