Restoring OWASP Dependency Check DB in Sandbox
We have two owaspdependencycheck databases that store CVE information that is then used in Jenkins pipelines to scan our code.
If the sandbox owasp dependency check flexible server is deleted and recreated, there will be very little CVE data contained in it.
Checking the database
On sandbox and prod, you can list the tables in the database by running:
SELECT * FROM pg_catalog.pg_tables;
And then view the rows in the vulnerability table by running:
SELECT * FROM vulnerability
ORDER BY cve DESC
This can be done using Azure Data Studio or another tool of your choosing.
If you compare sandbox with prod, sandbox will be missing many CVEs and will need updated.
Because the vulnerability table in the owaspdependencycheck database needs many rows added and the SKU of the server is not able to cope, the pipeline that updates the database will not work.
You could resize the flexible server, run the update and then downsize or you could restore the data from prod as the two environments should match.
The method listed below involves restoring from a backup.
Delete the sandbox database
Go to the Azure portal and find the flexible server.
Click on Settings > Databases and select the owaspdependencycheck database.
Click Delete.
Once it’s deleted, run the pipeline to create a new db: https://sandbox-build.hmcts.net/job/HMCTS_a_to_c_Sandbox/job/cnp-owaspdependencycheck-database/job/master/
Dump the database from prod
Connect to the VPN and run this command:
pg_dump owaspdependencycheck -h owaspdependency-prod.postgres.database.azure.com -U pgadmin -F c > ~/Desktop/owaspdependencycheck.dump
You will be prompted for the password which you can find in keyvault.
Now you will have a dump of the owaspdependencycheck database.
Restore the database to sandbox
You can then restore this to the sandbox flexible server:
pg_restore -v --no-owner --host=owaspdependency-sandbox.postgres.database.azure.com --port=5432 --username=pgadmin --dbname=owaspdependencycheck ~/Desktop/owaspdependencycheck.dump
You should see output similar to the below:
pg_restore: creating SCHEMA "public"
pg_restore: creating FUNCTION "public.insert_software(integer, character, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, boolean)"
pg_restore: creating FUNCTION "public.merge_ecosystem(character varying, character varying, character varying)"
pg_restore: creating PROCEDURE "public.merge_knownexploited(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)"
pg_restore: creating FUNCTION "public.save_property(character varying, character varying)"
pg_restore: creating FUNCTION "public.update_vulnerability(character varying, character varying, character varying, numeric, numeric, boolean, boolean, boolean, boolean, boolean, numeric, character varying, character varying, character varying, character varying, character varying, character varying, character varying, numeric, numeric, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, numeric, character varying, character varying)"
pg_restore: creating TABLE "public.cpeecosystemcache"
pg_restore: creating TABLE "public.cpeentry"
pg_restore: creating SEQUENCE "public.cpeentry_id_seq"
pg_restore: creating ACL "public.TABLE vulnerability"
pg_restore: from TOC entry 4094; 0 0 ACL TABLE vulnerability pgadmin
pg_restore: error: could not execute query: no connection to the server
Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.vulnerability TO dcuser;
pg_restore: creating ACL "public.SEQUENCE vulnerability_id_seq"
pg_restore: from TOC entry 4096; 0 0 ACL SEQUENCE vulnerability_id_seq pgadmin
pg_restore: error: could not execute query: no connection to the server
Command was: GRANT SELECT,USAGE ON SEQUENCE public.vulnerability_id_seq TO dcuser;
The database should be restored and if you run the below command, you should see the latest vulnerabilities listed.
SELECT * FROM vulnerability
ORDER BY cve DESC
The pipeline to update the database with new vulnerabilities should now work again.