Re: Question: Multiple pg clusters on one server can be reached with the standard port. - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Question: Multiple pg clusters on one server can be reached with the standard port.
Date
Msg-id 20230619103335.xyagd43uhneducxz@hjp.at
Whole thread Raw
In response to Re: Question: Multiple pg clusters on one server can be reached with the standard port.  (Thomas Markus <t.markus@proventis.net>)
Responses Re: Question: Multiple pg clusters on one server can be reached with the standard port.
Re: Question: Multiple pg clusters on one server can be reached with the standard port.
List pgsql-general
On 2023-06-19 06:06:33 +0200, Thomas Markus wrote:
> Am 16.06.23 um 13:53 schrieb Brainmue:
> > With firewall you mean an additional software, right?
> > Because with iptables or netfilter I can't forward TCP packets based on the DNS alias name. Or is
> > that possible?
> >
> > I have the same problem with nginx. I just looked in the documentation again but I can't find a way
> > to distinguish which cluster to forward to based on the DNS alias.
> > Do you have an example for me?
> >
> > We have already looked at pgbouncer and it works with that but unfortunately you have to do the
> > authentication in pgbouncer. Which we don't like so much.
> >
> > Regards,
> > Michael
>
> You cant setup firewall rules basedon dns names. firewall rules are based on
> ip adresses and dns resolution happens on rule creation.
> I dont have an example for nginx. As I remember nginx resolves dns names
> only for variables. So setup a variable with your hostname and use this
> variable in your server definition.

As Francisco already pointed out, this can't work with nginx either. The
client resolves the alias and the TCP packets only contain the IP
address, not the alias which was used to get that address. So nginx
simply doesn't have that information and therefore can't act on it.

For HTTP this works because the HTTP protocol contains a Host field
which the client fills with the name it used. But the Postgres protocol
has no such information (and in any case nginx probably doesn't
understand that protocol anyway).

So (again, as Francisco already wrote) the best way is probably to write
a simple proxy which uses the database (not DNS) name for routing. I
seem to remember that nginx has a plugin architecture for protocols so
it might make sense to write that as an nginx plugin instead of a
standalone server, but that's really a judgement call the programmer has
to make. Another possibility would of course be to extend pgbouncer to
do what the OP needs.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: Evgeny Morozov
Date:
Subject: Re: "PANIC: could not open critical system index 2662" - twice
Next
From: Thomas Markus
Date:
Subject: Re: Question: Multiple pg clusters on one server can be reached with the standard port.