Feature Request: Direct PostgreSQL Integration as Data Source for Kyverno Reports Server

[defimenko-ops]

Is your feature request related to a problem? Please describe.

Currently, Policy Reporter uses Kubernetes API server as its only data source for PolicyReport and ClusterPolicyReport resources. It watches/listens for these resources via API server, then stores and processes them in its own backend database (SQLite by default, or PostgreSQL/MySQL/MariaDB optionally).

This architecture becomes problematic when Kyverno Reports Server is enabled. Reports Server moves policy reports out of etcd and stores them exclusively in PostgreSQL. These reports are no longer available via Kubernetes API server (or only cached/limited views exist).

The problem:

• Policy Reporter cannot read reports directly from Kyverno Reports Server's PostgreSQL
• It still tries to watch reports via API server, where they no longer exist
• The existing PostgreSQL support in Policy Reporter is only for its own backend storage, not for sourcing data
• This makes Policy Reporter effectively unusable in clusters where Reports Server is enabled

Describe the solution you'd like

I propose adding support for Policy Reporter to use Kyverno Reports Server's PostgreSQL database as a direct data source, completely bypassing the Kubernetes API server for report retrieval.

Requirements:

1. New data source type - Add reportsServer or kyvernoReportsDB as a native data source option
2. PostgreSQL reader implementation - Directly query the same PostgreSQL instance used by Kyverno Reports Server
3. Configuration - Add configuration block for Reports Server PostgreSQL connection:

dataSources:
  kubernetesAPI:
    enabled: true  # existing mode, keep for backward compatibility
  kyvernoReportsDB:
    enabled: false
    type: "postgres"  # Reports Server uses PostgreSQL
    host: "kyverno-reports-postgresql:5432"
    database: "reports"  # default Reports Server database name
    username: "..."
    password: "..."
    sslMode: "disable"
    # Reports Server schema is fixed, no need for Policy Reporter to create/migrate tables
    readOnly: true  # enforce read-only access
    pollInterval: "30s"  # or use LISTEN/NOTIFY if supported

4. Schema compatibility - Support the specific schema used by Kyverno Reports Server
5. Dual mode - Allow both API server and Reports Server sources simultaneously (e.g., for migration periods)
6. Performance - Leverage PostgreSQL queries for filtering/aggregation instead of fetching all reports

Describe alternatives you've considered

1. Continue using API server only - Not possible, reports aren't in etcd with Reports Server
2. Mirror reports back to etcd - Defeats the purpose of Reports Server, recreates etcd pressure
3. Extend Kyverno Reports Server with REST API - Out of Policy Reporter's scope, shifts complexity
4. Patch Kyverno to dual-write - Would require upstream Kyverno changes, not ideal
5. Use Policy Reporter's existing PostgreSQL backend as a workaround - This still requires reports to be fetched via API server first, doesn't solve the root problem

Important distinction:

✅ Policy Reporter ALREADY supports PostgreSQL as a BACKEND storage - for caching processed reports
❌ Policy Reporter does NOT support PostgreSQL as a DATA SOURCE - cannot read from external PostgreSQL databases that it doesn't control

This proposal is about the latter: PostgreSQL as a source/producer, not just as a backend.

Additional context

From Kyverno Reports Server documentation:

"With reports stored in a relational database, report consumers could instead query the underlying database directly, using more robust query syntax. This would improve the implementation of, or even replace the need for, certain exporters, and enable new reporting use cases."

This explicitly envisions tools like Policy Reporter moving to direct database queries as a data source.

Schema information:
Kyverno Reports Server creates its own schema. Based on the implementation, it typically includes tables for:

• policy_reports
• cluster_policy_reports
• Various related tables for results, metadata, etc.

Policy Reporter would need to:

1. Read from these existing tables (read-only)
2. Transform the data into its internal model
3. Optionally store in its own backend DB for efficient querying

Proposed architecture:

Current:
Kyverno → etcd → API Server → Policy Reporter → PolicyReporter's DB → UI
With Reports Server (current state):
Kyverno → PostgreSQL (Reports Server) → ??? 
                                         ↑
                              (Policy Reporter cannot read)
Proposed:
Kyverno → PostgreSQL (Reports Server) → Policy Reporter (new DB source) → PolicyReporter's DB (optional) → UI

Benefits:

• Enables Policy Reporter in large-scale Kyverno deployments with Reports Server
• Zero load on Kubernetes API server for report collection
• Better performance for large report volumes
• Aligns with Kyverno's architectural roadmap
• Opens possibilities for more efficient analytical queries

Questions for maintainers:

1. Architectural alignment: Does this fit with Policy Reporter's vision, or is it considered out of scope?
2. Prior work: Is there any existing work/plans/PRs for external data sources?
3. Contribution: Would you accept a community contribution implementing this feature?
4. Schema stability: How to handle potential schema changes in Kyverno Reports Server? Should we version-lock or implement schema discovery?
5. Dependency concerns: Adding direct PostgreSQL dependency as a source - any concerns about vendor lock-in or operational complexity?

Implementation considerations I'm willing to help with:

• Creating a new source package for PostgreSQL reader
• Abstract data source interface to support multiple sources
• Transformation layer from Reports Server schema to Policy Reporter models
• Configuration validation
• Documentation and examples
• Migration guide for users switching from API to DB source

Impact assessment:

• Breaking change? No - existing API source remains fully supported
• New dependency? Yes - but PostgreSQL client is already a dependency for backend storage
• Performance impact? Positive - reduces API server load, faster queries
• Maintenance burden? Moderate - need to track Kyverno Reports Server schema changes

---

I plan to use Kyverno Reports Server in a production environment and am currently unable to use Policy Reporter. I'm willing to invest significant time in implementing this feature if the developers find it useful and the approach sound. I look forward to your feedback.

[fjogeleit]

Hey @defimenko-ops,

thanks for your requests and details. Some explanations / details:

Using Kyverno Report Server should still allow the access of PolicyReports via the Kubernetes API and not limit the functionality. I never heard of this issue before and I know that many other users using Policy Reporter and the Reports Server together.

I still understand the benefit of this feature but it has several challenges:

• The Schema in the ReportsServer is to basic, it just persists the complete report in a single column, this makes it impossible to use for the Policy Reporter UI, queries would be way to slow and pagination filtering needs to be done completely in the backend. Policy Reporter already persist its data redundant in different formats to allow performant data fetching in larger clusters, so there is no way to do it with a single column representation.

• Working directly with the DB Tables would be totally different from the current workflow which operates on events which lets Policy Reporter know when results/reports are added/updated/removed. This means the app needs to query the data regularly to detect changes. This adds a lot of load on the Database.

Which features of Policy Reporter do you are mainly using?