CAP_VIEWS support for the generic PostgreSQL backend (gpgsql)

[voyo]

Summary

CAP_VIEWS (Split-Horizon Views) is currently implemented only for the LMDB backend.
This proposes extending it to the generic PostgreSQL backend (gpgsqlbackend)
(which can be easily extended to cover other SQL backends (gmysql, gsqlite3))

Use cases

Views enable Split-Horizon DNS: serving different answers for the same zone
depending on the client's source IP. Common use cases:

• Multi-tenant SaaS DNS: each customer manages their own zones, isolated
  from others, even when zone names overlap (e.g. private internal domains).
• Internal vs. external resolution: the same domain resolves to private
  addresses for internal clients and public addresses for external ones —
  without running separate authoritative servers.
• RFC 1918 reverse zones: multiple tenants with overlapping private address
  space (several customers each with 10.0.0.0/8) get correct PTR records
  without non-standard zone naming that breaks RFC 2317 delegation.

The LMDB backend already supports this. The generic SQL backend — covering the
most widely deployed configurations — does not.

Related

• PDNS to support Views #63 — original Views feature request (closed)
• Fallback for zone views #17279 — downstream use case: fallback between view variants; this patch is a prerequisite
  • PR Bind-style views #15512 — "Bind-style views" (different approach, BIND backend only)

[miodvallat]

This is on the roadmap and some work has already been done on it by @Habbie.

[voyo]

Good to hear! That's actually why I opened this issue - I have a complete implementation ready to propose as a PR, but I wanted to run a few more tests against my production setup before submitting. The branch is here:
https://github.com/voyo/pdns/tree/views-gpgsql

It follows the existing GSQLBackend feature pattern (configurable query directives, declared in GSQLBackend itself so gmysql/gsqlite3 only need SQL defaults), with the networks/views tables matching the LMDB model.
CI is green on the fork (full regression matrix, including gpgsql expected results for the views/views-management/zone-variants tests), and it has been running in production since early July serving multi-tenant rDNS.
Happy to open the PR if useful - or if @Habbie's work is further along or takes a different approach, I'm just as happy to compare notes and test against my production use case.

[voyo]

Taking that as a "yes" :) - opening the PR now, it will reference this issue

[Habbie]

if @Habbie's work is further along

if yours is even barely functional, my work is not further along. We'll try to have a look at your work sometime soonish!