I simplified the patch and for now just allowed one server. Please find the attached patches, and the commit message.
Thanks, Satya -----Original Message----- From: Robert Haas <robertmh...@gmail.com> Sent: Monday, November 6, 2017 5:56 AM To: Craig Ringer <cr...@2ndquadrant.com> Cc: Satyanarayana Narlapuram <satyanarayana.narlapu...@microsoft.com>; PostgreSQL-development <pgsql-hack...@postgresql.org> Subject: Re: [HACKERS] Client Connection redirection support for PostgreSQL On Thu, Nov 2, 2017 at 4:33 PM, Craig Ringer <cr...@2ndquadrant.com> 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: https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com&data=02%7C01%7CSatyanarayana.Narlapuram%40microsoft.com%7Caafef2039b194d9c02c308d5251e12bb%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636455733453945798&sdata=8qystAJQ6UhnB7WRQh5i4nF8cyBUvKc9QIBfy59y%2FX8%3D&reserved=0 The Enterprise PostgreSQL Company
redirection_be.patch
Description: redirection_be.patch
redirection_fe_doc.patch
Description: redirection_fe_doc.patch
Adding 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. A new
optional authorization option called "redirect" was added to the host-based
authorization (HBA) entry as part of this change. If "redirect" is specified as
the authorization method, the server will continue to parse the HBA entry to
obtain the target server name and port. The following is an example of an HBA
entry with this change:
host all all 0.0.0.0/32 redirect
<target_server_address>, <target_server_port>
If any other authorization method is specified, the server will work as before
this change.
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 <target_server_address>,
<target_server_port> described above
ii. Server disconnects
* If the rule doesn't have "redirect" specified as the
authorization mechanism
i. Server proceeds in the existing code path and sends
auth request
4. Client obtains the <target_server_name>, <target_server_port> tuple
and attempts to connect to it. If the client could not connect to the server
instance, it reports the login failure message.
BACKWARD COMPATIBILITY
The pgwire protocol version has been bumped to 3.1 as part of this
change. As a result, the feature is not usable for the existing clients, and
the new servers should NOT specify "redirect" if they expect any connections
from legacy clients.
Therefore, there is a need to rely on the admin to take care of the
settings.
* For new client connecting to the old server, there is no
change in the message flow.
* For new clients to the new server, the message flow will be
based on the <target_server_name>, <target_server_port> specified in the
configuration.
* For old clients to the new server, there is no change in the
message flow if the server does NOT specify "redirect". Otherwise, the client
will exit with an "unrecognized message type" error.
Satyanarayana Narlapuram
Discussion:
https://www.postgresql.org/message-id/CY1PR21MB00246DE1F9E9C58455A78A37915C0%40CY1PR21MB0024.namprd21.prod.outlook.com
