Query support for Database Collectors

[Blokje5]

Describe the rationale for the suggested feature.

Database systems like postgres have support for introspection through stats tables or information_schema. This can be useful for e.g. fetching current settings, getting information about the schema, etc.

Describe the feature

Allow to execute queries with Database connectors, e.g.:

apiVersion: troubleshoot.sh/v1beta2
kind: SupportBundle
metadata:
  name: sample
spec:
  collectors:
    - postgres:
        collectorName: pg
        uri: postgresql://user:password@hostname:5432/defaultdb?sslmode=require
        queries:
        - name: Get Lock Information
           statement: SELECT locktype, mode, count(*) FROM pg_locks GROUP BY locktype, mode ORDER BY count DESC;

Output:

{
    "isConnected": true,
    "error": "",
    "version": "10.12.0",
    "queries": [
           { "name": "Get Lock Information", "output": "....", "error": "" }
     ]
}

This requires adding a new optional field queries to the DatabaseCollector definition. Each Collector would have to implement query execution for the respective database. Output can be unrestricted (i.e. raw string output) to allow e.g. the query to determine formatting (e.g. using json_agg).

Describe alternatives you've considered

Currently there are two options to get this type of information:

Exec into the Postgres pods. This can work but has a few downsides:

• You can only collect information from stdout. If you want to analyse the data, you need to parse stdout output. Potentially it can be combined with the Copy collector to copy over files, but it means all queries need to be adapted (e.g. COPY (SELECT ... ) TO path).
• It requires exec permissions which will not be desirable in all environments.

Running a pod

• Output is whatever the application inside the pod logs. This gives flexibility but requires a custom application to e.g. execute debug queries & log the queries to the output.
• Requires create pod permissions
• In an unknown environment you might run into e.g. network policies blocking traffic from the pod.

Additional context