Run custom SQL commands in ODG¶

Goal¶

Establish a psql connection to the ODG database for executing custom SQL queries.

Outcome¶

  • An interactive psql shell connected to the ODG database

  • The ability to execute arbitrary SQL commands against ODG

Prerequisites¶

  • A running ODG instance

  • kubectl CLI tool

  • A kubeconfig privileged to exec into the ODG database pod (create on pods/exec subresource)

Steps¶

Spawn a shell in the database pod¶

First, connect to the pod running the PostgreSQL database:

kubectl exec --namespace <odg-namespace> -it delivery-db-0 -- sh

This spawns a shell in the pod using the Kubernetes API and container runtime, creating an interactive session that streams:

  • Your terminal’s STDIN to the container’s process

  • The container’s STDOUT/STDERR to your terminal

Connect to the database using psql¶

The psql client is installed in the container and available on the PATH. Establish a connection with:

psql -U postgres

Note

The password is already configured via environment variables in the container, so you do not need to specify it.

You now have an active psql session. Explore the database schema using commands such as \dt:

               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | artefact_metadata | table | postgres
 public | blob_store        | table | postgres
 public | cache             | table | postgres
 public | user_identifiers  | table | postgres
 public | users             | table | postgres
(5 rows)

Querying vulnerabilities for a component¶

Note

Use the “\x” command to enable extended psql display mode for improved readability of query results in the terminal.

Query the most recent vulnerability entry for a component:

SELECT * FROM artefact_metadata
WHERE TYPE = 'finding/vulnerability'
AND component_name = 'acme.org/sovereign/postgres'
ORDER BY creation_date DESC LIMIT 1;

This returns the raw low-level database entry. To extract the CVE identifier specifically, query the data field:

SELECT data->>'cve' AS CVE FROM artefact_metadata
WHERE TYPE = 'finding/vulnerability'
AND component_name = 'acme.org/sovereign/postgres'
ORDER BY creation_date DESC LIMIT 1;

This produces a more focused output, for example:

-[ RECORD 1 ]-------
cve | CVE-2026-42504

Search for a CVE across multiple components¶

To reverse the previous query and find all components affected by a specific CVE:

SELECT component_name FROM artefact_metadata
WHERE TYPE = 'finding/vulnerability'
AND data->>'cve' = 'CVE-2026-42504';

This produces output such as:

-[ RECORD 1 ]--+---------------------------------------
component_name | opendesk.poc.sap.com/jitsi
-[ RECORD 2 ]--+---------------------------------------
component_name | acme.org/sovereign/postgres
-[ RECORD 3 ]--+---------------------------------------
component_name | opendesk.poc.sap.com/services-external
-[ RECORD 4 ]--+---------------------------------------
component_name | opendesk.poc.sap.com/jitsi
-[ RECORD 5 ]--+---------------------------------------
component_name | ocm.software/ocmcli

More advanced queries¶

To effectively query ODG at the SQL level, you need to understand the underlying data model. See Correlating metadata to OCM for details.