Обсуждение: [HACKERS] 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


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

On 2 November 2017 at 14:02, Satyanarayana Narlapuram
<Satyanarayana.Narlapuram@microsoft.com> 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

> 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.
Forexample, in the cloud environment, the proxy has to sit in one data center / region and has to server the client
requestsserving 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
prioritylist of endpoints to the client from the server will allow client connections balanced accordingly. However, it
isup 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
thanserve these requests. 
Possible, but I would say the server admin understands where the requests are coming from (old / new client) and does
thecapacity 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

> 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
extendthe 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
routethe 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
proxiesoptional (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

On Thu, Nov 2, 2017 at 4:33 PM, Craig Ringer <craig@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: 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

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 <robertmhaas@gmail.com> 
Sent: Monday, November 6, 2017 5:56 AM
To: Craig Ringer <craig@2ndquadrant.com>
Cc: Satyanarayana Narlapuram <Satyanarayana.Narlapuram@microsoft.com>; PostgreSQL-development
<pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Client Connection redirection support for PostgreSQL

On Thu, Nov 2, 2017 at 4:33 PM, Craig Ringer <craig@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
doingthis 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
youhave 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
replicateall of the data to the new instance, and then add a pg_hba.conf entry to redirect connections to that database
tothe new master (this would be even smoother if we had multi-master replication in core).  So now that client is moved
offto 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
thisredirect 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

On Mon, Feb 12, 2018 at 1:56 PM, Satyanarayana Narlapuram
<Satyanarayana.Narlapuram@microsoft.com> wrote:
> 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

Robert Haas <robertmhaas@gmail.com> 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?

            regards, tom lane

On Tue, Feb 13, 2018 at 5:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> 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

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
mostpart, before seems better, because it seems a bit silly to force people to authenticate just so that you can tell
themto go someplace else.  Also, that would lead to double authentication,          which might for example result in
multiplepassword prompts, which users might either dislike or find confusing.  
 

    Yes, redirection before authentication would avoid multiple password prompts.

Thanks,
Satya

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
themost part, before seems better, because it seems a bit silly to force people to authenticate just so that you can
tellthem to go someplace else.  Also, that would lead to double authentication,          which might for example result
inmultiple 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

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
themost part, before seems better, because it seems a bit silly to force people to authenticate just so that you can
tellthem to go someplace else.  Also, that would lead to double authentication,          which might for example result
inmultiple 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

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