Re: [HACKERS] Making server name part of the startup message

Thank you, Alvaro, Andres, Magnus, Peter, and Tom for your thoughts! I
consolidated all the responses, and provided the high level overview of the
Azure PostgreSQL database architecture, and provided additional details at
the bottom of the message.

> Tom Lane:  We don't actually have any concept of a server name at the
> moment, and it isn't very clear what introducing that concept would buy.
> Please explain.


>  Tom Lane: I think for instance you could have one pgbouncer instance (or
> whatever pooler) pointing >to several different servers.  So the client
> connects to the pooler and indicates which of the servers to >connect to.
>  Magnus: The normal one to use for pgbonucer today is, well, "database
> name". You can then have >pgbouncer map different databases to different
> backend servers. It's fairly common in my experience >to have things like
> "dbname" and "dbname-ro" (for example) as different
> database names with one mapping to the master and one mapping to a
> load-balanced set of standbys, >and things like that. ISTM that using the
> database name is a good choice for that.

PgBouncer for example assumes that the database names are unique across the
database clusters it is serving. Our front-end Gateways can serve tens of
thousands of Postgres servers spanning multiple customers, and
organizations, and enforcing the database names being unique is not possible
for the users of the service.

> Magnus: For the original idea in this thread, using something like
> dbname@server seems a more logical choice than username@server.
We considered this option but connecting to the database from the GUI tools
is not very intuitive / possible. Also /c option now requires including full
cluster_name every time user connect.

> Tome Lane: I should think that in such cases, the end client is exactly
> not what you want to be choosing which server it gets redirected to.
> You'd be wanting to base that on policies defined at the pooler.  There
> are
> already plenty of client-supplied attributes you could use as inputs for
> such policies (user name and application name, for instance). Why do we
> need to incur a protocol break to add another one?

This is optional and is not a protocol break. This doesn’t make the cluster
name field mandatory in the startup message. If the client specifies the
extra parameter in the connection string to include the server name in the
startup message then only it will be included otherwise it is not. In a
proxy scenario, end clients startup message doesn’t need to include the
server name in it, and for proxy it is optional to include this field while
sending the startup message to the server. It is preferred to set the field
for the Azure PostgreSQL service instead of appending the cluster name to
the user name.

Yes, there are other fields like application name, but it is not easy to use
them from GUI tools like Pg Admin. Overloading a field is also not always
intuitive to the users, and some of the applications potentially using them
for different purposes. Default database name is the user name for some of
the clients, and as we are overloading user name today and the startup
message has user@clustername in it. This behavior causing logins to fail
with invalid database name as the database doesn’t exist on the server.
Using database name may not be ideal because GUI tools doesn’t assume
database has server name in it.

> Peter: I think this could be useful if it's something like what HTTP uses.
The proposal is similar to http host header field in HTTP1.1. This allows
the origin server or gateway to differentiate between internally-ambiguous
URLs, such as the root "/" URL of a server for multiple host names on a
single IP address. For reference,
http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html#sec14.23  Azure
database for PostgreSQL follows the similar pattern where all the database
cluster dns records points to our gateways nodes, and we would like to
resolve them based on the server name field.


High level overview of Azure Database For PostgreSQL service.

Azure database for PostgreSQL is a cloud service that hosts several
PostgreSQL database clusters a.k.a. servers with in a region, potentially
tens of thousands of database clusters. We have several front-end proxies
(called Gateways) deployed to proxy the customer connections to the
appropriate database cluster. Each database cluster has a unique name, and a
DNS record is provisioned with that name. For example, if pgserver is the
name of the database cluster, then it has a dns record
pgserver.postgres.database.azure.com associated with it. The DNS record of a
customer database server will be pointing to the front-end Gateways, and the
customer request reaches these Gateways. Gateway requires database cluster
name to proxy the connection to the appropriate database cluster. In the
absence of this it is impossible for us to proxy the request. Startup
message containing the server name helps us route the requests to the right
database clusters, without customers overloading the user name field in the
startup message. For a friction free user experience, we are trying to make
the changes in the client code, PostgreSQL code, and minimize the changes
required in the application.

Here is the logical flow of server creation, and the connection
establishment:

Database cluster provisioning workflow:
1.    Customer requests Azure to create a cluster
2.    Azure registers the request, and adds the necessary metadata in the
central metadata store
3.    Create a database cluster with the customer requested size, and with the
provided user name and password combination
4.    DNS record created for the server for the customer to connect to. This
points to our front-end gateway nodes
5.    Make proxies aware of the location of the database cluster created, and
proxies caches this info, and refreshes if location changes

Connectivity workflow:
1.    Client uses the host name provided to connect to the database cluster
provisioned in Azure, and today they include database cluster name in the
user name (username@pgserver)
2.    Request reaches the Gateway (proxy)
3.    Gateway parses the packet, does the lookup, and creates a proxy
connection to the right database cluster (in the lookup success path)
4.    SSL handshake happens between the Postgres process, and the Gateway
5.    Gateway sends the startup packet removing the hostname from the startup
message
6.    Postgres responds with the password authentication request message
7.    Gateway sends the auth request back to the client
8.    Client response with the password message
9.    Gateway forwards the same to the database cluster
10.    Database cluster authenticates the client and sends success / failure
response to the client


LibPQ Connection string format:

host=localhost port=5432 dbname=mydb connect_timeout=10
include_cluster_name=true

include_cluster_name is an optional parameter and setting this true includes
cluster_name in the startup message, and will be ignored otherwise.

Connection via psql:

Today customers connect as follows:
psql -h pgserver.postgres.database.azure.com -U pgadmin@pgserver -d Postgres

After the proposed change the same command looks like:
psql -h pgserver.postgres.database.azure.com -U pgadmin -d Postgres
--includeClusterName
or
psql -h pgserver.postgres.database.azure.com -U pgadmin -d Postgres -N

-N / -- includeClusterName is optional here.







--
View this message in context:
http://www.postgresql-archive.org/Making-server-name-part-of-the-startup-message-tp5966904p5967347.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.