Re: Client Connection redirection support for PostgreSQL

On Thu, Nov 02, 2017 at 06:02:43AM +0000, Satyanarayana Narlapuram wrote:
> Proposal:
> Add the ability to the PostgreSQL server instance to route the
> traffic to a different server instance based on the rules defined in
> server's pg_bha.conf configuration file. At a high level this
> enables offloading the user requests to a different server instance
> based on the rules defined in the pg_hba.conf configuration file.
> Some of the interesting scenarios this enables include but not
> limited to - rerouting traffic based on the client hosts, users,
> database, etc. specified, redirecting read-only query traffic to the
> hot stand by replicas, and in multi-master scenarios.

What advantages do you see in doing this in the backend over the
current system where the concerns are separated, i.e. people use
connection poolers like pgbouncer to do the routing?

> The rules to route the traffic will be provided in the pg_hba.conf
> file. The proposal is to add a new optional field 'RoutingList' to
> the record format. The RoutingList contains comma-seperated list of
> one or more servers that can be routed the traffic to. In the
> absence of this new field there is no change to the current login
> code path for both the server and the client. RoutingList can be
> updated for each new connection to balance the load across multiple
> server instances

> RoutingList format:
> server_address1:port, server_address2:port...

Would it make sense also to include an optional routing algorithm or
pointer to a routing function for each RoutingList, or do you see this
as entirely the client's responsibility?

> The message flow
>
> 1. Client connects to the server, and server accepts the connections

How does this work with SSL?

> 2. Client sends the startup message
> 3. Server looks at the rules configured in the pg_hba.conf file and
> * If the rule matches redirection
> i. Send a special message with the RoutingList described above
> ii. Server disconnects
>
> * If the rule doesn't have RoutingList defined
>
> i. Server proceeds in the existing code path and sends auth request
>
> 1. Client gets the list of addresses and attempts to connect to a
> server in the list provided until the first successful connections
> is established or the list is exhausted. If the client can't
> connect to any server instance on the RoutingList, client reports
> the login failure message.
>
> Backward compatibility:
> There are a few ways to provide the backward compatibility, and each
> approach has their own advantages and disadvantage and are listed
> below
>
> 1. Bumping the protocol version - old server instances may not
> understand the new client protocol

This sounds more attractive, assuming that the feature is.

> 2. Adding additional optional parameter routing_enabled, without
> bumping the protocol version. In this approach, old Postgres
> server instances may not understand this and fail the connections.

Silently changing an API without bumping the protocol version seems
like a bad plan, even when it's an additive change as you propose here.

> 3. The current proposal - to keep it in the hba.conf and let the
> server admin deal with the configuration by taking conscious
> choice on the configuration of routing list based on the clients
> connecting to the server instance.

How would clients identify themselves as eligible without a protocol
version bump?

> Backward compatibility scenarios:
>
> * The feature is not usable for the existing clients, and the
> new servers shouldn't set the routing list if they expect any
> connections from the legacy clients. We should do either (1) or
> (2) in the above list to achieve this. Otherwise need to rely on
> the admin to take care of the settings.
> * For the new client connecting to the old server, there is no
> change in the message flow

So to DoS the server, what's required is a flock of old clients? I
presume there's a good reason to reroute rather than serve these
requests.

> * For the new clients to the new server, the message flow will be based on the routing list filed in the configuration.
> This proposal is in very early stage, comments and feedback is very much appreciated.

Comments and feedback have begun.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate