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