Обсуждение: Question: Multiple pg clusters on one server can be reached with the standard port.

Hello all,

I am currently looking for a solution similar to Oracle Listener.

Current situation:
I have a system with multiple PostgreSQL clusters for different databases.
Each cluster is running on the same physical machine and is accessed through its own DNS alias and corresponding port.
I only have one single IP address available, and there is no possibility of obtaining additional addresses.

Here's an example:

   DNS ALIAS               Host
─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436


Desired solution:
I still want to use the same system with different PostgreSQL clusters for the various databases.
These clusters are internally accessible through different ports.
However, I would like a service on the server to receive all external requests on port 5432 and forward them to the
correspondinginternal cluster based on the DNS alias.  
It would also be desirable if this service could provide additional features like connection pooling or other
functionalities.
Similar to a reverse proxy.

Here's an example:

   DNS ALIAS                 HOST
─> pgs1.server.net:5432 ─┐   ┌──────────────────┬──> PG_Cluster1@localhost:5433
─> pgs2.server.net:5432 ─┤   │                  ├──> PG_Cluster2@localhost:5434
─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
─> pgs4.server.net:5432 ─┤   │                  ├──> PG_Cluster4@localhost:5436
─> pgs5.server.net:5432 ─┘   └──────────────────┴──> PG_Cluster5@localhost:5437


Is there a solution for this, and what are the advantages or limitations that arise from it?

Thank you in advance for your suggestions and help.

Regards,
   Michael

Hi

Am 16.06.23 um 11:40 schrieb Brainmue:
> Hello all,
>
> I am currently looking for a solution similar to Oracle Listener.
>
> Current situation:
> I have a system with multiple PostgreSQL clusters for different databases.
> Each cluster is running on the same physical machine and is accessed through its own DNS alias and corresponding
port.
> I only have one single IP address available, and there is no possibility of obtaining additional addresses.
>
> Here's an example:
>
>     DNS ALIAS               Host
> ─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
> ─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
> ─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
> ─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
> ─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436
>
>
> Desired solution:
> I still want to use the same system with different PostgreSQL clusters for the various databases.
> These clusters are internally accessible through different ports.
> However, I would like a service on the server to receive all external requests on port 5432 and forward them to the
correspondinginternal cluster based on the DNS alias.
 
> It would also be desirable if this service could provide additional features like connection pooling or other
functionalities.
> Similar to a reverse proxy.
>
> Here's an example:
>
>     DNS ALIAS                 HOST
> ─> pgs1.server.net:5432 ─┐   ┌──────────────────┬──> PG_Cluster1@localhost:5433
> ─> pgs2.server.net:5432 ─┤   │                  ├──> PG_Cluster2@localhost:5434
> ─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
> ─> pgs4.server.net:5432 ─┤   │                  ├──> PG_Cluster4@localhost:5436
> ─> pgs5.server.net:5432 ─┘   └──────────────────┴──> PG_Cluster5@localhost:5437
>
>
> Is there a solution for this, and what are the advantages or limitations that arise from it?
>
> Thank you in advance for your suggestions and help.
>
> Regards,
>     Michael
>
>

possible solutions:
* set up a firewall rule to forward connection
* use a tcp proxy (nginx can do that)
* check pg_bouncer

best regards
Thomas

16. Juni 2023 12:04, "Thomas Markus" <t.markus@proventis.net> schrieb:

> Hi
>
> Am 16.06.23 um 11:40 schrieb Brainmue:
>
>> Hello all,
>>
>> I am currently looking for a solution similar to Oracle Listener.
>>
>> Current situation:
>> I have a system with multiple PostgreSQL clusters for different databases.
>> Each cluster is running on the same physical machine and is accessed through its own DNS alias and
>> corresponding port.
>> I only have one single IP address available, and there is no possibility of obtaining additional
>> addresses.
>>
>> Here's an example:
>>
>> DNS ALIAS Host
>> ─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
>> ─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
>> ─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
>> ─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
>> ─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436
>>
>> Desired solution:
>> I still want to use the same system with different PostgreSQL clusters for the various databases.
>> These clusters are internally accessible through different ports.
>> However, I would like a service on the server to receive all external requests on port 5432 and
>> forward them to the corresponding internal cluster based on the DNS alias.
>> It would also be desirable if this service could provide additional features like connection
>> pooling or other functionalities.
>> Similar to a reverse proxy.
>>
>> Here's an example:
>>
>> DNS ALIAS HOST
>> ─> pgs1.server.net:5432 ─┐ ┌──────────────────┬──> PG_Cluster1@localhost:5433
>> ─> pgs2.server.net:5432 ─┤ │ ├──> PG_Cluster2@localhost:5434
>> ─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
>> ─> pgs4.server.net:5432 ─┤ │ ├──> PG_Cluster4@localhost:5436
>> ─> pgs5.server.net:5432 ─┘ └──────────────────┴──> PG_Cluster5@localhost:5437
>>
>> Is there a solution for this, and what are the advantages or limitations that arise from it?
>>
>> Thank you in advance for your suggestions and help.
>>
>> Regards,
>> Michael
>
> possible solutions:
> * set up a firewall rule to forward connection
> * use a tcp proxy (nginx can do that)
> * check pg_bouncer
>
> best regards
> Thomas

Hello Thomas,

Thank you for your quick reply.

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
whichcluster 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

16. Juni 2023 12:04, "Thomas Markus" <t.markus@proventis.net> schrieb:

> Hi
>
> Am 16.06.23 um 11:40 schrieb Brainmue:
>
>> Hello all,
>>
>> I am currently looking for a solution similar to Oracle Listener.
>>
>> Current situation:
>> I have a system with multiple PostgreSQL clusters for different databases.
>> Each cluster is running on the same physical machine and is accessed through its own DNS alias and
>> corresponding port.
>> I only have one single IP address available, and there is no possibility of obtaining additional
>> addresses.
>>
>> Here's an example:
>>
>> DNS ALIAS Host
>> ─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
>> ─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
>> ─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
>> ─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
>> ─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436
>>
>> Desired solution:
>> I still want to use the same system with different PostgreSQL clusters for the various databases.
>> These clusters are internally accessible through different ports.
>> However, I would like a service on the server to receive all external requests on port 5432 and
>> forward them to the corresponding internal cluster based on the DNS alias.
>> It would also be desirable if this service could provide additional features like connection
>> pooling or other functionalities.
>> Similar to a reverse proxy.
>>
>> Here's an example:
>>
>> DNS ALIAS HOST
>> ─> pgs1.server.net:5432 ─┐ ┌──────────────────┬──> PG_Cluster1@localhost:5433
>> ─> pgs2.server.net:5432 ─┤ │ ├──> PG_Cluster2@localhost:5434
>> ─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
>> ─> pgs4.server.net:5432 ─┤ │ ├──> PG_Cluster4@localhost:5436
>> ─> pgs5.server.net:5432 ─┘ └──────────────────┴──> PG_Cluster5@localhost:5437
>>
>> Is there a solution for this, and what are the advantages or limitations that arise from it?
>>
>> Thank you in advance for your suggestions and help.
>>
>> Regards,
>> Michael
>
> possible solutions:
> * set up a firewall rule to forward connection
> * use a tcp proxy (nginx can do that)
> * check pg_bouncer
>
> best regards
> Thomas

Hello Thomas,

Thank you for your quick reply.

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

On Fri, 2023-06-16 at 09:40 +0000, Brainmue wrote:
> I am currently looking for a solution similar to Oracle Listener.

Can you explain why?  Perhaps there exists a good solution for the
underlying problem.

Yours,
Laurenz Albe

16. Juni 2023 14:13, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:

> On Fri, 2023-06-16 at 09:40 +0000, Brainmue wrote:
>
>> I am currently looking for a solution similar to Oracle Listener.
>
> Can you explain why? Perhaps there exists a good solution for the
> underlying problem.
>
> Yours,
> Laurenz Albe


Hello Laurenz,

I can try.
We want to minimise dependencies between the application and the associated PostgreSQL DB.
The idea is that the application gets its DB alias and this is then used as a connection string.
This way we can decide in the backend on which server the PostgreSQL DB is running.
We can manage very flexible moves without informing the application or even requiring changes from them.
Of course, there would also be the solution of always assigning a certain fixed port to each application, but we have a
lotof network segmentation with many firewalls in between. 
This would require a lot of organisational effort.
That is the idea behind it.

Regards,
Michael

On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
> We want to minimise dependencies between the application and the associated PostgreSQL DB.
> The idea is that the application gets its DB alias and this is then used as a connection string.
> This way we can decide in the backend on which server the PostgreSQL DB is running.

There is an existing solution for that: the libpq connection service file:
https://www.postgresql.org/docs/current/libpq-pgservice.html

If you want to manage the connection strings centrally, you can use LDAP lookup:
https://www.postgresql.org/docs/current/libpq-ldap.html

Yours,
Laurenz Albe

On 6/16/23 07:50, Laurenz Albe wrote:
> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>> The idea is that the application gets its DB alias and this is then used as a connection string.
>> This way we can decide in the backend on which server the PostgreSQL DB is running.
> There is an existing solution for that: the libpq connection service file:
> https://www.postgresql.org/docs/current/libpq-pgservice.html

Do JDBC and ODBC recognize .pg_service.conf?  I know they don't recognize 
.pgpass.

> If you want to manage the connection strings centrally, you can use LDAP lookup:
> https://www.postgresql.org/docs/current/libpq-ldap.html
>
> Yours,
> Laurenz Albe

-- 
Born in Arizona, moved to Babylonia.

16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:

> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>
>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>> The idea is that the application gets its DB alias and this is then used as a connection string.
>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>
> There is an existing solution for that: the libpq connection service file:
> https://www.postgresql.org/docs/current/libpq-pgservice.html
>
> If you want to manage the connection strings centrally, you can use LDAP lookup:
> https://www.postgresql.org/docs/current/libpq-ldap.html
>
> Yours,
> Laurenz Albe


Thank you, I already know this solution, but the LDAP solution is out of the question for us and the file again means
anintervention on the client. And that's exactly what we don't want. 

On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>
> > On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
> >
> > > We want to minimise dependencies between the application and the associated PostgreSQL DB.
> > > The idea is that the application gets its DB alias and this is then used as a connection string.
> > > This way we can decide in the backend on which server the PostgreSQL DB is running.
> >
> > There is an existing solution for that: the libpq connection service file:
> > https://www.postgresql.org/docs/current/libpq-pgservice.html
> >
> > If you want to manage the connection strings centrally, you can use LDAP lookup:
> > https://www.postgresql.org/docs/current/libpq-ldap.html
>
> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
> the file again means an intervention on the client. And that's exactly what we don't want.

Okay.

Then why don't you go with your original solution, but use a unique TCP port number
for each database?  There are enough port numbers available.  That way, there is no
collision and no need for a proxy to map port numbers.

Yours,
Laurenz Albe

On Fri, 2023-06-16 at 09:04 -0500, Ron wrote:
> On 6/16/23 07:50, Laurenz Albe wrote:
> > On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
> > > We want to minimise dependencies between the application and the associated PostgreSQL DB.
> > > The idea is that the application gets its DB alias and this is then used as a connection string.
> > > This way we can decide in the backend on which server the PostgreSQL DB is running.
> > There is an existing solution for that: the libpq connection service file:
> > https://www.postgresql.org/docs/current/libpq-pgservice.html
>
> Do JDBC and ODBC recognize .pg_service.conf?  I know they don't recognize
> .pgpass.

JDBC doesn't, because it does not use libpq.

ODBC can use both .pgpass and .pg_service.conf, since it uses libpq.

Yours,
Laurenz Albe

On Fri, 2023-06-16 at 15:25 +0200, Dominique Devienne wrote:
> On Fri, Jun 16, 2023 at 2:50 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > There is an existing solution for that: the libpq connection service file:
> > https://www.postgresql.org/docs/current/libpq-pgservice.html
>
> The problem with the service and password files is that passwords are in plain text though.
> Yes there are restrictions on permissions of the password file (on Linux), still having some
> kind of encrypted token with an expiration date would be better IMHO. 

You don't store a password in the connection service file.

Yours,
Laurenz Albe

On 6/16/23 10:19, Laurenz Albe wrote:
> On Fri, 2023-06-16 at 09:04 -0500, Ron wrote:
>> On 6/16/23 07:50, Laurenz Albe wrote:
>>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>>>> The idea is that the application gets its DB alias and this is then used as a connection string.
>>>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>>> There is an existing solution for that: the libpq connection service file:
>>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>> Do JDBC and ODBC recognize .pg_service.conf?  I know they don't recognize
>> .pgpass.
> JDBC doesn't, because it does not use libpq.
>
> ODBC can use both .pgpass and .pg_service.conf, since it uses libpq.

All of the applications that we use are written in Java, so that wouldn't work.

-- 
Born in Arizona, moved to Babylonia.

16. Juni 2023 17:18, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:

> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>
>> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>>
>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>
>>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>>> The idea is that the application gets its DB alias and this is then used as a connection string.
>>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>>
>> There is an existing solution for that: the libpq connection service file:
>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>
>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>> https://www.postgresql.org/docs/current/libpq-ldap.html
>>
>> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
>> the file again means an intervention on the client. And that's exactly what we don't want.
>
> Okay.
>
> Then why don't you go with your original solution, but use a unique TCP port number
> for each database? There are enough port numbers available. That way, there is no
> collision and no need for a proxy to map port numbers.
>
> Yours,
> Laurenz Albe

Thank you for dealing with our wishes.

Because we are growing more and more and we have many databases in different networks.
Therefore, we are looking for a solution that will make the firewall problem more manageable for the future.
And currently I believe that managing one more service in automation would be the lesser of two evils for us.
But that's exactly why we're looking for a service that does that at all.

Regards
Michael

On 6/16/23 10:18, Laurenz Albe wrote:
> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>>
>>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>>
>>>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>>>> The idea is that the application gets its DB alias and this is then used as a connection string.
>>>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>>> There is an existing solution for that: the libpq connection service file:
>>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>>
>>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>>> https://www.postgresql.org/docs/current/libpq-ldap.html
>> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
>> the file again means an intervention on the client. And that's exactly what we don't want.
> Okay.
>
> Then why don't you go with your original solution, but use a unique TCP port number
> for each database?  There are enough port numbers available.  That way, there is no
> collision and no need for a proxy to map port numbers.

In practice, that gets very complicated is large organizations: every time 
you add another database, you must file another request with the CISO RISK 
office to get yet another non-standard port open from dozens of machines, 
and the network team implement them.

Operationally much simpler to have a listener handle that.

-- 
Born in Arizona, moved to Babylonia.

16. Juni 2023 17:41, "Ron" <ronljohnsonjr@gmail.com> schrieb:

> On 6/16/23 10:18, Laurenz Albe wrote:
>
>> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>>> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>>
>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>
>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>> The idea is that the application gets its DB alias and this is then used as a connection string.
>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>> There is an existing solution for that: the libpq connection service file:
>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>
>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>> https://www.postgresql.org/docs/current/libpq-ldap.html
>>> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
>>> the file again means an intervention on the client. And that's exactly what we don't want.
>>
>> Okay.
>>
>> Then why don't you go with your original solution, but use a unique TCP port number
>> for each database? There are enough port numbers available. That way, there is no
>> collision and no need for a proxy to map port numbers.
>
> In practice, that gets very complicated is large organizations: every time you add another
> database, you must file another request with the CISO RISK office to get yet another non-standard
> port open from dozens of machines, and the network team implement them.
>
> Operationally much simpler to have a listener handle that.
>
> -- Born in Arizona, moved to Babylonia.


Hello Ron,

I have to agree with you there as well. The workflow you have to go through is also often a time issue.
There are many places that have to agree and then application owners still have to provide justifications.
At the same time, we have to be flexible and fast and allocate the resources well at any time and provide the
applicationwith the maximum possible performance. 

Regards
Michael

16. Juni 2023 17:41, "Ron" <ronljohnsonjr@gmail.com> schrieb:

> On 6/16/23 10:18, Laurenz Albe wrote:
>
>> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>
> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>
>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>> The idea is that the application gets its DB alias and this is then used as a connection string.
>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>> There is an existing solution for that: the libpq connection service file:
>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>
>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>> https://www.postgresql.org/docs/current/libpq-ldap.html
>
> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
> the file again means an intervention on the client. And that's exactly what we don't want.
>> Okay.
>>
>> Then why don't you go with your original solution, but use a unique TCP port number
>> for each database? There are enough port numbers available. That way, there is no
>> collision and no need for a proxy to map port numbers.
>
> In practice, that gets very complicated is large organizations: every time you add another
> database, you must file another request with the CISO RISK office to get yet another non-standard
> port open from dozens of machines, and the network team implement them.
>
> Operationally much simpler to have a listener handle that.
>
> -- Born in Arizona, moved to Babylonia.

Hello Ron,

I have to agree with you there as well. The workflow you have to go through is also often a time
issue.
There are many places that have to agree and then application owners still have to provide
justifications.
At the same time, we have to be flexible and fast and allocate the resources well at any time and
provide the application with the maximum possible performance.

Regards
Michael

On 6/16/23 10:54, Brainmue wrote:
> 16. Juni 2023 17:41, "Ron" <ronljohnsonjr@gmail.com> schrieb:
>
>> On 6/16/23 10:18, Laurenz Albe wrote:
>>
>>> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>>
>>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>>> The idea is that the application gets its DB alias and this is then used as a connection string.
>>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>>> There is an existing solution for that: the libpq connection service file:
>>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>>
>>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>>> https://www.postgresql.org/docs/current/libpq-ldap.html
>> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
>> the file again means an intervention on the client. And that's exactly what we don't want.
>>> Okay.
>>>
>>> Then why don't you go with your original solution, but use a unique TCP port number
>>> for each database? There are enough port numbers available. That way, there is no
>>> collision and no need for a proxy to map port numbers.
>> In practice, that gets very complicated is large organizations: every time you add another
>> database, you must file another request with the CISO RISK office to get yet another non-standard
>> port open from dozens of machines, and the network team implement them.
>>
>> Operationally much simpler to have a listener handle that.
>>
>> -- Born in Arizona, moved to Babylonia.
> Hello Ron,
>
> I have to agree with you there as well. The workflow you have to go through is also often a time
> issue.
> There are many places that have to agree and then application owners still have to provide
> justifications.
> At the same time, we have to be flexible and fast and allocate the resources well at any time and
> provide the application with the maximum possible performance.

There's always The Cloud...  spinning up a new AWS RDS Postgresql is fast 
and simple.  (Costly, though.)

-- 
Born in Arizona, moved to Babylonia.

16. Juni 2023 17:59, "Ron" <ronljohnsonjr@gmail.com> schrieb:

> On 6/16/23 10:54, Brainmue wrote:
>
>> 16. Juni 2023 17:41, "Ron" <ronljohnsonjr@gmail.com> schrieb:
>
> On 6/16/23 10:18, Laurenz Albe wrote:
>> On Fri, 2023-06-16 at 14:49 +0000, Brainmue wrote:
>
> 16. Juni 2023 14:50, "Laurenz Albe" <laurenz.albe@cybertec.at> schrieb:
>> On Fri, 2023-06-16 at 12:35 +0000, Brainmue wrote:
>>
>> We want to minimise dependencies between the application and the associated PostgreSQL DB.
>> The idea is that the application gets its DB alias and this is then used as a connection string.
>> This way we can decide in the backend on which server the PostgreSQL DB is running.
>> There is an existing solution for that: the libpq connection service file:
>> https://www.postgresql.org/docs/current/libpq-pgservice.html
>>
>> If you want to manage the connection strings centrally, you can use LDAP lookup:
>> https://www.postgresql.org/docs/current/libpq-ldap.html
>
> Thank you, I already know this solution, but the LDAP solution is out of the question for us and
> the file again means an intervention on the client. And that's exactly what we don't want.
>> Okay.
>>
>> Then why don't you go with your original solution, but use a unique TCP port number
>> for each database? There are enough port numbers available. That way, there is no
>> collision and no need for a proxy to map port numbers.
>
> In practice, that gets very complicated is large organizations: every time you add another
> database, you must file another request with the CISO RISK office to get yet another non-standard
> port open from dozens of machines, and the network team implement them.
>
> Operationally much simpler to have a listener handle that.
>
> -- Born in Arizona, moved to Babylonia.
>> Hello Ron,
>>
>> I have to agree with you there as well. The workflow you have to go through is also often a time
>> issue.
>> There are many places that have to agree and then application owners still have to provide
>> justifications.
>> At the same time, we have to be flexible and fast and allocate the resources well at any time and
>> provide the application with the maximum possible performance.
>
> There's always The Cloud... spinning up a new AWS RDS Postgresql is fast and simple. (Costly,
> though.)
>
> -- Born in Arizona, moved to Babylonia.

We know that too, but our data should/must currently remain in-house on our own hardware.
That is why we need a solution at our company.

Regards
Michael

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

On 6/16/23 11:05, Brainmue wrote:
> 16. Juni 2023 17:59, "Ron" <ronljohnsonjr@gmail.com> schrieb:
[snip]
> There's always The Cloud... spinning up a new AWS RDS Postgresql is fast and simple. (Costly,
> though.)
>
>
> We know that too, but our data should/must currently remain in-house on our own hardware.
> That is why we need a solution at our company.

Do we work for the same company???  :D

-- 
Born in Arizona, moved to Babylonia.

16. Juni 2023 18:19, "Jeff Ross" <jross@openvistas.net> schrieb:

>> 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 can set up pgbouncer to authenticate in postgres:
>
> https://www.2ndquadrant.com/en/blog/understanding-user-management-in-pgbouncer/
> See the "auth_query, auth_user" section.
>
> https://www.2ndquadrant.com/en/blog/pg-phriday-securing-pgbouncer/
>
> Jeff

Thanks for the links.
We'll take a second look then.
I remember that we had problems last time, but unfortunately I can't remember which ones.

Regards
Michael

16. Juni 2023 18:56, "Ron" <ronljohnsonjr@gmail.com> schrieb:

> On 6/16/23 11:05, Brainmue wrote:
>
>> 16. Juni 2023 17:59, "Ron" <ronljohnsonjr@gmail.com> schrieb:
>
> [snip]
>
>> There's always The Cloud... spinning up a new AWS RDS Postgresql is fast and simple. (Costly,
>> though.)
>>
>> We know that too, but our data should/must currently remain in-house on our own hardware.
>> That is why we need a solution at our company.
>
> Do we work for the same company???  :D
>
> -- Born in Arizona, moved to Babylonia.


The world is small.
But are not all companies the same as of 3 employees ;-)

Regards
Michael

Michael:

On Fri, 16 Jun 2023 at 20:26, Michael Weiller <michael@weiller.eu> wrote:

> Because with iptables or netfilter I can't forward TCP packets based on the DNS alias name. Or is that possible?

The dns alias name does not reach the listening tcp socket, it is
mapped to the target record, then finally to the A record and this is
what is used to start the tcp connection.

> I have the same problem with nginx.

In nginx you cannot use the dns alias, but if you are using http the
"normal" clients send it as host when they open an url. But there is
no "host" parameter in the pg protocol. There is a dbaname, which can
be used with the adequate software, like pgbouncer,  but you said you
do not like it.

You may try to write a small program which parses the startup message
and redirects the connection based on the info there. I'm not sure why
pgbouncer has not this option, but it may be because it has a lot more
option. It does not seem to be that difficult, receive the packet,
parse it, connect to remote, send it the packet and from there on just
forward traffic blindly. Parsing startup seems quite simple,
maintaining a socket pool and forwarding is simple, you could probably
prototype that in an afternoon ( if no guru replies me "but this won't
work because .... ).

> I just looked in the documentation again but I can't find a way to distinguish which cluster to forward to based on
theDNS alias.
 

As I asaid above, DNS alias is not avalilable to the listener. On any
protocol. Things like http work because the clients send the dns alias
on some place on the default usage, but you can write an http client
which sends Host: from the uri given but connects to a different IP
address.

Francisco Olarte.

16. Juni 2023 21:54, "Francisco Olarte" <folarte@peoplecall.com> schrieb:

> Michael:
>
> On Fri, 16. Juni 2023 at 13:53, brainmue <brainmue@weiller.eu> wrote:
>
>> Because with iptables or netfilter I can't forward TCP packets based on the DNS alias name. Or is
>> that possible?
>
> The dns alias name does not reach the listening tcp socket, it is
> mapped to the target record, then finally to the A record and this is
> what is used to start the tcp connection.
>
>> I have the same problem with nginx.
>
> In nginx you cannot use the dns alias, but if you are using http the
> "normal" clients send it as host when they open an url. But there is
> no "host" parameter in the pg protocol. There is a dbaname, which can
> be used with the adequate software, like pgbouncer, but you said you
> do not like it.
>
> You may try to write a small program which parses the startup message
> and redirects the connection based on the info there. I'm not sure why
> pgbouncer has not this option, but it may be because it has a lot more
> option. It does not seem to be that difficult, receive the packet,
> parse it, connect to remote, send it the packet and from there on just
> forward traffic blindly. Parsing startup seems quite simple,
> maintaining a socket pool and forwarding is simple, you could probably
> prototype that in an afternoon ( if no guru replies me "but this won't
> work because .... ).
>
>> 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.
>
> As I asaid above, DNS alias is not avalilable to the listener. On any
> protocol. Things like http work because the clients send the dns alias
> on some place on the default usage, but you can write an http client
> which sends Host: from the uri given but connects to a different IP
> address.
>
> Francisco Olarte.

Thank you for the detailed explanations.
I could not have explained it but it also seemed strange to me that it should go that way.
pgbouncer I will definitely look again carefully.
Maybe I already have my solution with it.

Regards
Michael

Hi,

Am 16.06.23 um 13:53 schrieb Brainmue:
>
>> possible solutions:
>> * set up a firewall rule to forward connection
>> * use a tcp proxy (nginx can do that)
>> * check pg_bouncer
>>
>> best regards
>> Thomas
> Hello Thomas,
>
> Thank you for your quick reply.
>
> 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.

best regards
Thomas

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!"

Hi,

Am 19.06.23 um 12:33 schrieb Peter J. Holzer:
> 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
yeah I know
I looked at his setup definition only and missed the point that he wants 
a single entry point for all.
The image doesnt match this. Looks like a simple forward proxy definition.

anyway, fw or nginx cant look into tcp streams nor does it makes sense 
to me. Maybe without tls

19. Juni 2023 13:17, "Dominique Devienne" <ddevienne@gmail.com> schrieb:

> On Mon, Jun 19, 2023 at 1:01 PM Thomas Markus <t.markus@proventis.net> wrote:
>
>> Am 19.06.23 um 12:33 schrieb Peter J. Holzer:
>>> As Francisco already pointed out, this can't work with nginx either.
>> anyway, fw or nginx cant look into tcp streams
> Traefik can proxy PostgreSQL, I've read, so maybe that's something you could try.

Thanks for the tip.
As I understand Traefik but he can also only the same as HAProxy.
So he does not read the protocol itself and can not make the distribution.
PGBouncer can actually do it and it also forwards to different backends.
But we are not finished with the tests yet.
But it looks already good.

Regards
Michael

On 6/19/23 05:33, Peter J. Holzer wrote:
[snip]
> 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.

How would this work with JDBC clients?


-- 
Born in Arizona, moved to Babylonia.

On 2023-06-19 07:49:49 -0500, Ron wrote:
> On 6/19/23 05:33, Peter J. Holzer wrote:
> > 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.
[...]
> > 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.
>
> How would this work with JDBC clients?

Same as with any other client, I guess. Any reason why it should be
different?

        hp

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

On 2023-06-19 07:49:49 -0500, Ron wrote:

On 6/19/23 05:33, Peter J. Holzer wrote:

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.

[...]

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.

How would this work with JDBC clients?

Same as with any other client, I guess. Any reason why it should be
different?

On 2023-06-19 16:09:34 -0500, Ron wrote:
> On 6/19/23 12:15, Peter J. Holzer wrote:
>     On 2023-06-19 07:49:49 -0500, Ron wrote:
>         On 6/19/23 05:33, Peter J. Holzer wrote:
>             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.
>
>         How would this work with JDBC clients?
>
>     Same as with any other client, I guess. Any reason why it should be
>     different?
>
>
> That goes to my ultimate point: why would this work, when the point of a
> database client is to connect to a database instance on a specific port like
> 5432, not connect to a web server.

Consider this scenario:

You have several databases scattered across several hosts and ports:

db1  host1.example.com:5432
db2  host1.example.com:5433
db3  host2.example.com:5432
db4  host3.example.com:5432

Then you have your proxy/gateway/bouncer (whatever you want to call it)
listening on proxy.example.com:5432.

The clients all connect to proxy.example.com:5432.

The proxy does the TLS handshake (if necessary) and reads the first
packet. This contains the database name. The proxy then uses the
database name to look up where that database resides (e.g. for db3 it
gets host2.example.com:5432) opens a connection to that port (plus TLS
handshake, if necessary) and forwards the packet. After that it just has
to forward packets in both directions until the connection is closed.

The client never knows that the databases are actually on different
hosts and/or ports. As far as it is concerned, all the databases are on
proxy.example.com:5432.

There is one caveat: All the database names need to be unique.

Such a proxy should be straightforward to write. It only needs to
understand two requests of postgresql protocol (initiate TLS and
connect). It would be much simpler than e.g. pg_bouncer which has to
know about authentication, transactions, etc.[1]. Depending on the
expected number of parallel connections and throughput you might want to
consider what programming language and concurrency model
(multi-threaded, async, state machine, ...) to use.

        hp

[1] But of course, if pgbouncer already does what you want, don't
    reinvent the wheel.

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

On 6/20/23 09:54, Peter J. Holzer wrote:
> On 2023-06-19 16:09:34 -0500, Ron wrote:
>> On 6/19/23 12:15, Peter J. Holzer wrote:
>>      On 2023-06-19 07:49:49 -0500, Ron wrote:
>>          On 6/19/23 05:33, Peter J. Holzer wrote:
>>              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.
>>
>>          How would this work with JDBC clients?
>>
>>      Same as with any other client, I guess. Any reason why it should be
>>      different?
>>
>>
>> That goes to my ultimate point: why would this work, when the point of a
>> database client is to connect to a database instance on a specific port like
>> 5432, not connect to a web server.
> Consider this scenario:
>
> You have several databases scattered across several hosts and ports:
>
> db1  host1.example.com:5432
> db2  host1.example.com:5433
> db3  host2.example.com:5432
> db4  host3.example.com:5432
>
> Then you have your proxy/gateway/bouncer (whatever you want to call it)
> listening on proxy.example.com:5432.

Proxies/gateways are great. My question is about why you mentioned nginx.

-- 
Born in Arizona, moved to Babylonia.

On 2023-06-20 10:10:47 -0500, Ron wrote:
> On 6/20/23 09:54, Peter J. Holzer wrote:
> > On 2023-06-19 16:09:34 -0500, Ron wrote:
> > > On 6/19/23 12:15, Peter J. Holzer wrote:
> > >      On 2023-06-19 07:49:49 -0500, Ron wrote:
> > >          On 6/19/23 05:33, Peter J. Holzer wrote:
> > >              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.
> > >
> > >          How would this work with JDBC clients?
> > >
> > >      Same as with any other client, I guess. Any reason why it should be
> > >      different?
> > >
> > >
> > > That goes to my ultimate point: why would this work, when the point of a
> > > database client is to connect to a database instance on a specific port like
> > > 5432, not connect to a web server.
> > Consider this scenario:
> >
> > You have several databases scattered across several hosts and ports:
> >
> > db1  host1.example.com:5432
> > db2  host1.example.com:5433
> > db3  host2.example.com:5432
> > db4  host3.example.com:5432
> >
> > Then you have your proxy/gateway/bouncer (whatever you want to call it)
> > listening on proxy.example.com:5432.
>
> Proxies/gateways are great. My question is about why you mentioned nginx.

Somebody else mentioned nginx as a possible solution. I wrote that I
don't think that nginx can do that out of the box but it might be
possible to write a plugin/module. Personally. I wouldn't (learning how
to write nginx modules almost certainly takes longer than writing a
simple proxy from scratch), but if somebody is already familiar with
nginx modules and/or has other reasons to use nginx ...

        hp

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