Re: Feature: Use DNS SRV records for connecting

> On 14 Aug 2019, at 23:01, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2019-08-13 10:43:07 -0400, Tom Lane wrote:
>> How would we get at that data without writing our own DNS client?
>> (AFAIK, our existing DNS interactions are all handled by getnameinfo()
>> or other library-supplied functions.)
>
>> Maybe that'd be worth doing, but it sounds like a lot of work and a
>> lot of new code to maintain, relative to the value of the feature.
>
> It might have enough independent advantages to make it worthwhile
> though.

Here is a patch prototyping SRV-based discovery for libpq, reviving
the idea from the 2019 thread.

Tom asked how we'd get SRV data without writing our own DNS client.
On POSIX, res_query(3) from libresolv does the lookup and
dn_expand(3) decompresses names in the response - the same library
that Kerberos and LDAP support already depend on. On Windows,
DnsQuery() from windns.h returns a typed linked list with no wire-format
parsing needed. The resolver is about 200 lines and uses no DNS logic
of our own.

Regarding non-blocking resolution raised by Andres: res_query() is
blocking, as is the existing getaddrinfo() call for regular hosts.
This patch does not make things worse, but does not improve them
either. Async DNS resolution is a separate, larger problem.

A new connection parameter "srvhost" (env: "PGSRVHOST") causes libpq
to query "_postgresql._tcp.<srvhost>" before connecting. I chose
"srvhost" over the original "dnssrv" suggestion to align with the
existing "host" parameter naming. Two URI schemes are supported as
shorthand:

psql "postgresql+srv://cluster.example.com/mydb?target_session_attrs=read-write"
psql "postgres+srv://cluster.example.com/mydb?target_session_attrs=read-write"

Given the DNS records:

_postgresql._tcp.cluster.example.com. SRV 10 50 5432 primary.example.com.
_postgresql._tcp.cluster.example.com. SRV 20 50 5432 standby1.example.com.
_postgresql._tcp.cluster.example.com. SRV 20 50 5432 standby2.example.com.

The resolved host list is sorted per RFC 2782 and injected into the
existing multi-host machinery before connhost[] is built, so
target_session_attrs, load_balance_hosts, and failover work on the
expanded list without any changes to PQconnectPoll.

"srvhost" is mutually exclusive with "host" and "hostaddr". Multiple
hosts in a "+srv" URI are rejected - expansion is DNS's responsibility.

I'm proposing this across the PostgreSQL driver ecosystem (pgx, pgjdbc,
npgsql, and now libpq). I would be happy to hear a feedback.

Best regards, Andrey Borodin.