Re: [HACKERS] Client Connection redirection support for PostgreSQL

[Robert Haas]

On Thu, Nov 2, 2017 at 4:33 PM, Craig Ringer  wrote:
>> 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.
>
> pg_hba.conf is "host based access [control]" . I'm not sure it's
> really the right place.

Well, we could invent someplace else, but I'm not sure I quite see the
point (full disclosure: I suggested the idea of doing this via
pg_hba.conf in an off-list discussion).

I do think the functionality is useful, for the same reasons that HTTP
redirects are useful.  For example, let's say you have all of your
databases for various clients on a single instance.  Then, one client
starts using a lot more resources, so you want to move that client to
a separate instance on another VM.  You can set up logical replication
to replicate all of the data to the new instance, and then add a
pg_hba.conf entry to redirect connections to that database to the new
master (this would be even smoother if we had multi-master replication
in core).  So now that client is moved off to another machine in a
completely client-transparent way.  I think that's pretty cool.

> When this has come up before, one of the issues has been determining
> what exactly should constitute "read only" vs "read write" for the
> purposes of redirecting work.

Yes, that needs some thought.

> Backends used just for a redirect would be pretty expensive though.

Not as expensive as proxying the whole connection, as pgpool and other
systems do today.  I think the in-core use of this redirect
functionality is useful, but I think the real win would be optionally
using it in pgpool and pgbouncer.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Client Connection redirection support for PostgreSQL

[Satyanarayana Narlapuram]

> pg_hba.conf is "host based access [control]" . I'm not sure it's really the 
> right place.
I am open to have another configuration file, say routing_list.conf to define 
the routing rules, but felt it is easy to extend the hba conf file.

> But we now have a session-intent stuff though. So we could possibly do it at 
> session level.
Session intent can be used as an obvious hint for the routing to kick in. This 
can be a rule in the routing list to route the read intent sessions round robin 
across multiple secondary replicas.

> Backends used just for a redirect would be pretty expensive though.
It is somewhat expensive as the new process fork has to happen for each new 
connection. The advantage is that it makes proxies optional (if the middle tier 
can do connection management), and all the routing configurations can be within 
the server.
This also benefits latency sensitive applications not going through proxy.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Client Connection redirection support for PostgreSQL

[Satyanarayana Narlapuram]

> 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?
IMHO connection pooler is not great for latency sensitive applications. For 
small deployments, proxy is an overhead. For example, in the cloud environment, 
the proxy has to sit in one data center / region and has to server the client 
requests serving from other data centers.

> 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?
This is a great point, I haven't put much though into this beyond round robin / 
random shuffling. Providing the priority list of endpoints to the client from 
the server will allow client connections balanced accordingly. However, it is 
up to the client implementation to honor the list.

> How does this work with SSL?
The protocol doesn't change much with SSL, and after the handshake, startup 
message is sent to the server from the client, and the new message flow kicks 
on the server based on the routing list.

>>   1.  Bumping the protocol version - old server instances may not understand 
>> the new client protocol
> This sounds more attractive, assuming that the feature is.
I agree, bumping the protocol version makes things simple.

> > 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?
Either through optional parameter, or controlled configuration by the server 
administrator are the only choices.
Protocol bump seems to me is a better idea here.

> 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.
Possible, but I would say the server admin understands where the requests are 
coming from (old / new client) and does the capacity planning accordingly.

> Comments and feedback have begun.
Thank you :)

Thanks,
Satya


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Client Connection redirection support for PostgreSQL

[Craig Ringer]

On 2 November 2017 at 14:02, 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.

pg_hba.conf is "host based access [control]" . I'm not sure it's
really the right place.

> 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.

When this has come up before, one of the issues has been determining
what exactly should constitute "read only" vs "read write" for the
purposes of redirecting work.

There are a bunch of issues there. If you're doing "read only" txns
and then do something "read write" and get redirected, the destination
doesn't have your prepared statements, any WITH HOLD cursors, temp
tables, etc you were working with. Strangeness ensues.

But we now have a session-intent stuff though. So we could possibly do
it at session level.

Backends used just for a redirect would be pretty expensive though.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Client Connection redirection support for PostgreSQL

[David Fetter]

On Thu, Nov 02, 2017 at 06:02:43AM +, 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  http://fetter.org/
Phone: +1 415 235 3778

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Client Connection redirection support for PostgreSQL

[Satyanarayana Narlapuram]

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.
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...
The message flow

  1.  Client connects to the server, and server accepts the connections
  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
  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.
  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.
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
  *   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.
Thanks,
Satya