Re: Ability to listen on two unix sockets - Mailing list pgsql-hackers

On Wed, Jun 6, 2012 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, that's what I wanted to discuss before Honza starts coding.
> It's not obvious that there are any use-cases for more than two.
> It's also not clear whether there is any value in supporting run-time
> rather than build-time configuration of the socket locations.  The
> Fedora use-case has no need of that, but if people can point to other
> cases where it would be sensible, we can write the patch that way.
>
> You might think we should design this exactly like the TCP-socket
> multiple-listen-addresses case, ie just have a config variable
> containing a list of directory names.  The sticking point there is
> that the directories aren't really interchangeable.  In particular,
> there is still going to be one directory that is the one hard-wired
> into libpq.  So whereas multiple TCP sockets really are pretty much
> interchangeable, I think in the Unix-socket case we are going to have
> to think of it as being a primary socket and one or more alternate
> sockets.  Is there a reason to have more than one alternate, and if
> so what is the use-case?
>
> (BTW, we would probably just adopt the Debian solution if we were
> sure there were no non-libpq clients out there; but we aren't.)

I recently had an urge to make it possible for the postmaster to
listen on multiple ports and even went so far as to code up a patch to
allow that.  It still applies, with offsets, so I'll attach it here.
So I guess I'm +1 on the idea of allowing N UNIX sockets rather than
limiting it to N=2, and really I'd like to do one better and allow
listening on multiple TCP ports as well.  Since the PID file contains
the port number, multiple TCP sockets stop being interchangeable as
soon as you allow multiple ports, but that's not very difficult to
handle.  Now, you might ask whether this has any real-world value, and
obviously I'm going to say yes or I wouldn't be proposing it.  The
reason for wanting multiple UNIX sockets is because those sockets
might be in different places that are not all equally accessible to
everyone, because of things like chroot.  But of course the same thing
is possible in the network space using iptables and similar tools.
For example, you might want to have users connect to application A
using port 5432, and to  application B using port 15432.  Now you can
use network monitoring tools to see how much data each application is
sending and receiving, without needing deep packet inspection.  You
can firewall those ports differently to provide access to different
groups of users.  And you can even decide, if the database gets
overloaded, to cut off access to one of those ports, so that the
application causing the problem becomes inaccessible but the rest of
the database ceases being overloaded and you can still operate.  Of
course, you could also do that by changing pg_hba.conf, but for some
people it might be more convenient (or feel more bullet-proof) to do
it using network management tools.  There are probably other use
cases, as well.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company