Re: [HACKERS] Client Connection redirection support for PostgreSQL
On Fri, Jul 13, 2018 at 04:20:15PM -0400, Dave Cramer wrote: > However it would be far better to have a startup parameter which indicated > that we wanted to connect to a read only database. At that point > pools could redirect to a secondary. Given the proliferation of cloud based > implementations I can see this being a useful feature. The thread has died here... Without any input from the author for many months. So I am marking it as returned with feedback. -- Michael signature.asc Description: PGP signature
Re: [HACKERS] Client Connection redirection support for PostgreSQL
> > > > One thing where I can see a feature like this being quite helpful is > planned failovers, reducing the time to reconnect (for existing > connections) and rediscover (for new connections, which need to > write). But that'd require that the redirect needs to be able to be sent > in an established connection too. > > Somewhat related to this feature is the notion of giving a hint as to whether a connection is read only. Currently we can create a read only transaction which at this point pgpool through some machinations which are less than favourable IMO can connect to a secondary. This even works with the JDBC driver which has setReadOnly facility on connections. However it would be far better to have a startup parameter which indicated that we wanted to connect to a read only database. At that point pools could redirect to a secondary. Given the proliferation of cloud based implementations I can see this being a useful feature. Regards, Dave Cramer
Re: [HACKERS] Client Connection redirection support for PostgreSQL
On 2018-07-13 23:00:04 +0300, Heikki Linnakangas wrote: > On 05/03/18 22:18, Satyanarayana Narlapuram wrote: > > Please see the attached patch with the comments. > > > > Changes in the patch: > > A client-side PGREDIRECTLIMIT parameter has been introduced to control > > the maximum number of retries. > > BE_v3.1 sends a ProtocolNegotiation message. FE_v3.1 downgrades to v3.0 > > upon receipt of this message. > > FE falls back to v3.0 if 3.1 is not supported by the server. > > > > > > >> I hadn't really thought deeply about whether redirection should > > happen before or after authentication. For the most part, before seems > > better, because it seems a bit silly to force people to authenticate just > > so that you can tell them to go someplace else. Also, that would lead to > > double authentication,which might for example result in multiple > > password prompts, which users might either dislike or find confusing. > > > > Yes, redirection before authentication would avoid multiple password > > prompts. FWIW, I think it's quite dangerous to do the redirect before authentication, and more importantly, certificate validation / channel binding. > FWIW, if we were to do this, I think pg_hba.conf would be a fine place for > this. That's where you currently have configuration for what happens when a > client with certain host/username/database tries to connect. In addition to > "accept" or "reject", it seems logical to add "redirect" as an outcome, > instead of e.g. adding a whole new configuration file fore this. I'd personally argue that it'd also make sense to have this as actual database level option. One thing where I can see a feature like this being quite helpful is planned failovers, reducing the time to reconnect (for existing connections) and rediscover (for new connections, which need to write). But that'd require that the redirect needs to be able to be sent in an established connection too. Greetings, Andres Freund
Re: [HACKERS] Client Connection redirection support for PostgreSQL
On 05/03/18 22:18, Satyanarayana Narlapuram wrote: Please see the attached patch with the comments. Changes in the patch: A client-side PGREDIRECTLIMIT parameter has been introduced to control the maximum number of retries. BE_v3.1 sends a ProtocolNegotiation message. FE_v3.1 downgrades to v3.0 upon receipt of this message. FE falls back to v3.0 if 3.1 is not supported by the server. >> I hadn't really thought deeply about whether redirection should happen before or after authentication. For the most part, before seems better, because it seems a bit silly to force people to authenticate just so that you can tell them to go someplace else. Also, that would lead to double authentication, which might for example result in multiple password prompts, which users might either dislike or find confusing. Yes, redirection before authentication would avoid multiple password prompts. I think we should have this feature. I can see a redirect being useful in some similar cases like HTTP redirects are useful, but a database server is not a web server. There are no redirects in IMAP or most other protocols, either. This would also require modifying every client library to honor the redirect. How would the redirect behave with TLS certificate verification? If you are redirected from "foo-server" to "bar-server", but the original connection string was "host=foo-server sslmode=verify-full", would the connection be allowed? FWIW, if we were to do this, I think pg_hba.conf would be a fine place for this. That's where you currently have configuration for what happens when a client with certain host/username/database tries to connect. In addition to "accept" or "reject", it seems logical to add "redirect" as an outcome, instead of e.g. adding a whole new configuration file fore this. But overall, IMHO we should mark this patch "rejected". - Heikki
RE: [HACKERS] Client Connection redirection support for PostgreSQL
Please see the attached patch with the comments. Changes in the patch: A client-side PGREDIRECTLIMIT parameter has been introduced to control the maximum number of retries. BE_v3.1 sends a ProtocolNegotiation message. FE_v3.1 downgrades to v3.0 upon receipt of this message. FE falls back to v3.0 if 3.1 is not supported by the server. >> I hadn't really thought deeply about whether redirection should happen before or after authentication. For the most part, before seems better, because it seems a bit silly to force people to authenticate just so that you can tell them to go someplace else. Also, that would lead to double authentication,which might for example result in multiple password prompts, which users might either dislike or find confusing. Yes, redirection before authentication would avoid multiple password prompts. Thanks, Satya redirection_v2.patch Description: redirection_v2.patch
Re: [HACKERS] Client Connection redirection support for PostgreSQL
On Tue, Feb 13, 2018 at 5:23 PM, Tom Lanewrote: > Robert Haas writes: >> -- might need some defense against the redirected-to server getting >> the same password as was sent to the original server. Is that a >> security risk? Does HTTP have a rule about this? > > Without having read any of the previous discussion ... I'd say that if the > redirect info is placed in pg_hba.conf then I would expect a redirect to > happen before any authentication exchange, so that this is not an issue. > Perhaps it would be a good security measure for clients to refuse a > redirect once they've sent any auth-related messages. > > But ... pg_hba.conf? Really? Surely that is a completely random and > inappropriate place to control redirection? Where would you suggest? My thought was that if, for example, you migrated one database off of a multiple database cluster to a new location, you'd want to redirect anyone trying to connect to that database to the new server, so you need to put the redirection facility someplace where we can make decisions about whether or not to redirect based on rules involving database names. The other things we expose in pg_hba.conf seem like they could potentially be useful, too, although maybe less so. For instance, if you've got several standbys (or several masters connected via some MMR solution) you could redirect connections which come from the "wrong" IP block to the server to which they are local. I think of pg_hba.conf as a place where we decide what to do with connections, and redirecting them seems like one thing we might decide to do. I hadn't really thought deeply about whether redirection should happen before or after authentication. For the most part, before seems better, because it seems a bit silly to force people to authenticate just so that you can tell them to go someplace else. Also, that would lead to double authentication, which might for example result in multiple password prompts, which users might either dislike or find confusing. The only contrary argument that comes to mind is that someone could argue that there's a security leakage --- if someone has a redirect rule that only engages for a particular user or database name, then you can perhaps guess that the user or database name exists on the target system, or that in general it's one that they care about. However, reject rules already have the same exposure. Similarly, you might also be able to infer something based on the type of authentication request that you get from the server. So I don't see this argument as compelling. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Client Connection redirection support for PostgreSQL
Robert Haaswrites: > -- might need some defense against the redirected-to server getting > the same password as was sent to the original server. Is that a > security risk? Does HTTP have a rule about this? Without having read any of the previous discussion ... I'd say that if the redirect info is placed in pg_hba.conf then I would expect a redirect to happen before any authentication exchange, so that this is not an issue. Perhaps it would be a good security measure for clients to refuse a redirect once they've sent any auth-related messages. But ... pg_hba.conf? Really? Surely that is a completely random and inappropriate place to control redirection? regards, tom lane
Re: [HACKERS] Client Connection redirection support for PostgreSQL
On Mon, Feb 12, 2018 at 1:56 PM, Satyanarayana Narlapuramwrote: > I simplified the patch and for now just allowed one server. Please find the > attached patches, and the commit message. This patch -- -- doesn't include nearly sufficient documentation updates. For example, the new message type is not documented in the list of message types. The documentation of which messages are legal in which contexts is not updated to mention this new message. The new ConnStatusType is not documented (and is it really needed?). The documentation for the new pg_hba.conf parameter does not explain how to specify the alternate server to which you wish to connect. -- includes no tests. -- does include irrelevant whitespace differences. -- doesn't include any provision for clients to fall back to 3.0 if 3.1 is not supported. -- doesn't seem to have proper provisions for the server to handle older clients. The code looks like just skips over hba.conf redirect lines if the client is older, which seems like not what we want. The proposed commit message claims we just go ahead and send redirects to older clients that aren't expecting them, which is pretty much missing the entire point of having minor protocol versions. I think the right way to handle this case is to throw FATAL with the error text suggesting the host/port to which the user should try connecting. -- probably needs defenses against infinite redirect loops. Most likely we should see how this is normally handled by HTTP clients and do something similar here. -- probably needs some way for clients to express unwillingness to follow redirects. Possibly that could be combined with the previous item by having a new connection parameter indicating the number of redirects the client is willing to follow, with the default being, say, 10 (browsers apparently have a limit of 10 or 20 for HTTP, but 20 seems overly generous for a database connection) and 0 disabling. -- might need some defense against the redirected-to server getting the same password as was sent to the original server. Is that a security risk? Does HTTP have a rule about this? -- might need some way for clients to discover whether they got redirected and, if so, the server to which they were redirected. For example, if I connect with psql, get redirected, and then type \conninfo, do I get the information on the server to which I think I connected, or the server to which I got redirected? Maybe we should display both? If the connection gets retried, do we retry the original server or the server to which we were redirected? I'd argue for the former, but maybe other people think differently. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
RE: [HACKERS] Client Connection redirection support for PostgreSQL
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=02%7C01%7CSatyanarayana.Narlapuram%40microsoft.com%7Caafef2039b194d9c02c308d5251e12bb%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636455733453945798=8qystAJQ6UhnB7WRQh5i4nF8cyBUvKc9QIBfy59y%2FX8%3D=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: hostall all 0.0.0.0/32redirect , 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 , 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 , 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