PostgreSQL "no route to host"

[wcage03]

I apologize upfront for the length of this post. I want to try to capture as many details as possible and hopefully someone has insight on the problem. First, I am having trouble connecting to a remote database. I have tried Firebird, MySQL, MariaDB, and PostgreSQL. The problems are similar in each case.

my environment. I am using FPC 3.2.0 and Lazarus built from FPCUpDeluxe using the Fixes-3.0 branch. Shows as version 3.9 on the Lazarus splash screen. I am running all of this on a Mac running Sequoia. I have tried to use newer versions of FPC, but there is a known bug that is apparently tied to newer releases of Xcode that prevents a successful build of the latest versions of FPC.

I am working on an application that I initially used SQLite3 as the database. All was well, but I wanted to move to a multi-user database on a server that I can ultimately expose to the network. For each db (Firebird, MySQL, MariaDB, PostgreSQL) I have installed the server on a linux box (ubuntu). Configured the server for access via my home network and used a client on the server to create users and simple databases to test. All works. Again, for each, I installed at least the client software on the Mac. In most cases the only option was the full server and client install. I did that when necessary. I have tested with a variety of database connection components and have had various problems but I can't seem to get a connection.

The first, universal problem is finding the appropriate library. I have become very adept and finding the library and doing a sym link in the appropriate location to solve that problem for each of the DBs. After that I have run into problems that I can't solve. I use DBeaver for my local database SQL tool. I am able to connect to each of the servers/databases using that tool by providing similar parameters that the laz components require, host, port, database, user, password. DBeaver uses JDBC to connect. It connects and works flawlessly (is there a way to adopt JDBC in Las?).

I have narrowed my efforts down to only PostgreSQL for now. I tried them all, hoping one would break through and I would adopt that one. I am not wedded to Postgres and would happily switch to anything that works.

Right now I have PostgreSQL v16 installed on my Linux server. I have PostgeSQL v16 installed on my Mac (server and client). I have a simple test LAZ project that has only a TSQLConnector component and a TSQLTransaction component.

The TSQLConnector is configured as follows:
ConnectorType: PostgreSQL
Databasename: testdb
Hostname: <my Linux box>. -- note, I have used both the hostname and IP address in various tests
Name: SQLConnector1
Password: <my password for testdb>
Transaction: SQLTransaction1 -- the only other component on the form
-- all other values are default

The TSQLTransaction is configured as follows:
Database: SQLConnector1
Name: SQLTransaction1
-- all other values are default

When I attempt to change the Connected property on SQLConnector1 to True, I get an error that says
"Connection to database failed (PostgreSQL: connection to server at <my host> port 5432 failed: No route to host, Is the server running on that host and accepting TCP/IP connections?)

All of the parameters are correct. It accurately decodes my host name to the correct IP address and shows that in the error message. The Port is correct. The UID and Password are correct. Is there something that I need to be doing regarding TCP/IP that I am unaware of? While I don't know how to identify if DBeaver or the local PostgreSQL client are using TCP/IP connections (I can't imagine they are not) but important to note that they both work from the same machine where I am writing Laz code. For those familiar with PostgreSQL setup, I have set the config for "listen_addresses = '*'" and "host   all   all   192.168.1.1/24   password" in pg_hba.conf (for those that are security conscious, I plan to tighten that later). These config values have to be correct or I would not be able to connect remotely using the PostgreSQL client and DBeaver.

As best I can tell, the problem resides in Lazarus, FPC, or most likely, something that I should know to do that I just don't. I have been banging my head on this for the better part of a week. At this point, I have tried many different components, all the different databases, and all of the advice I can glean from searching the web. I really hope someone can point out whatever stupid mistake I am making. Fell free to point me down rabbit holes, I have been trying anything that is within my limited ability and I have managed to learn a lot of stuff. Unfortunately, I have not learned the answer to how to connect to a remote database of any stripe.

Thanks

[dseligo]

Right now I have PostgreSQL v16 installed on my Linux server. I have PostgeSQL v16 installed on my Mac (server and client). I have a simple test LAZ project that has only a TSQLConnector component and a TSQLTransaction component.

So you have Postgre installed on your computer with Lazarus. Can you try to create some database and then connect to localhost from Lazarus?

When I attempt to change the Connected property on SQLConnector1 to True, I get an error that says

Do you change 'Connected' property in Lazarus' Object Inspector or when running your program?

Can you try to connect with ZEOSdbo components? It should work with both, but maybe you'll get some other error message which could shed some light what actual problem is.

[MarkMLl]

Can you try to connect with ZEOSdbo components? It should work with both, but maybe you'll get some other error message which could shed some light what actual problem is.

It certainly worked properly when I last tried it. Example at https://github.com/MarkMLl/testdb, I have to say that that reimplements a couple of bits to allow access to some things which are normally internal but I have absolutely no reason to believe that that magically unbreaks some problem with the standard libraries and components.

MarkMLl

[wcage03]

I have tried the ZEOS components previously but I will give ether another shot. I will also create a local db and connect to that. I will provide those results shortly. Thanks for the suggestions.

One addition to this, I was unaware that Lazarus came with the Data Desktop tool. For grins I compiled and ran the application. I was able to successfully use that to connect to my Postgre database on my Linux server. That says to me that the problem is not unique to my version of FPC and/or Lazarus. It also says that there is no network issue between my client and my server. The problem has to be code.

This is good, right? All I have to do is look at the Data Desktop source and follow the bouncing ball. Well, that is coded at a level beyond my proficiency. It does not seem to use the normal drag and drop components. Some components are being created in code so I will have to seed those out. First glance at the code I am coming across a call to RegisterPostgreSQLDDengine.

This does not appear to be defined within the code for the Data Desktop. Any insight into what this is?

Thanks for helping. I will try your suggestions an get back shortly.

[wcage03]

OK, I have installed the ZEOS package. I get the error "none of the dynamic libraries can be found...Use TZConnectionLibraryLocation..." I generally do a sym link to the libpg.dylib into /usr/local/lib. I checked, that is in place. The other packages were specific about which library it was looking for, ZEO is less specific.

I put the directory name /usr/local/lib into the property LibraryLocation. That did not resolve the issue. I tried pointing to the install directory for Postgres where I have been linking the lib. Same error.

BTW to answer a previous question, I am doing the connect in the component property. I will add a button and do the connect in code to see if that gives a different result. Again, the question is how is the Lazarus tool, Database Desktop able to overcome this problem. Same libraries are installed.

[wcage03]

OK. As suggested, I attempted to use the local Postgres server to create a database and point Laz to it. With ZEOS, still getting the lib errors. Went back and used the SQLdb controls and it worked! For grins, I changed the hostname from localhost to my Linux server and ...it worked too! This is great, but also concerning. Why after a million attempts is it working?

What is going on?
- the server is initiated and running locally now. I stopped it and tested again, it still works. I thought maybe having the local server running made the difference. Apparently not.
- Going to the local server and accessing a database first may have loaded required libraries into memory and they are now accessible. I will reboot the computer to flush memory and see if that impacts my testing.
- Was I doing something else stupid? The betting line says...
- Again, why could Liz Database Desktop always access the remote db? That seems to be the most reliable connection (at least now). I would really appreciate if smart person could boil down what that program is doing into simple instructions for the lesser talented ;-)

Again, thanks for your help on this.

[dseligo]

OK. As suggested, I attempted to use the local Postgres server to create a database and point Laz to it. With ZEOS, still getting the lib errors. Went back and used the SQLdb controls and it worked! For grins, I changed the hostname from localhost to my Linux server and ...it worked too! This is great, but also concerning. Why after a million attempts is it working?

It should work with ZEOS too, so maybe you have some lib issues which you accidentally solved for SQLdb.

[duralast]

Don't know about Postgres or Firebird, but for MySQL, is Host set to '%' or your IP so that anyone can connect?

[wcage03]

Yes. In Postgres you have to set something like "host   all   all   192.168.1.1/24   password" in pg_hba.conf to allow remote users. Host means the rule applies to both SSL and non-SSL connects. The next two "all"s say make the rule applicable to all users and all target databases. The IP identifies the IP addresses this rule applies to and "password" defines the method of authentication. This is set. As noted in another

[wcage03]

Making some progress. I will continue to update in case this can help someone else.

As mentioned, I use FPCUpdateDeluxe to build my environments. I have been using FPC 3.2.0 due to a problem with building. Researching the issue with 3.2.2 it appeared to be a known issue so I did a fall back and was able to work fine. With the database connection problems, (among dozens of tests) I decided to use FPCUpdateDeluxe with the Trunk builds of Las and FPC. I was hopeful that the FPC build bug on the Mac was fixed. It appears that the bug is indeed fixed and FPC 3.2.2 and the latest Las built.

If you use FPCUpdateDeluxe one huge benefit is that you can specify different directories to house different builds. It "try" to put an icon on the desktop and always puts a script in your home directory to allow you to start the Laz/FPC build that you want to use. After building with the Trunk build, I noticed that there was no icon on the desktop. So, I used the script to start Laz and  I went to my database connect test in design mode and was able to connect! My first reaction was that the problem was solved with the new release.

Since I truly had not discovered the problem, I did another install on a separate machine to see if I could replicate. After the install I started Laz using the desktop icon. No luck. I realized that I started the first one from the command line and this one from the icon. I gave it a shot. Sure enough, when Las is started with the icon it fails but started from the command line using the script it works... at least in design mode. I did further testing to see if the compiled result works. It does not, irrespective of which way Laz is started.

I think this is an environment issue now. Further, I think this might be due to Mac's local network security setting. It could be something related to FPCUpdateDeluxe, but I looked at the script being executed and it is simple. In the Mac security setting my terminal program is set to allow access to the local network. Las is not listed to enable. I will chase this aspect down, but this does appear to be a security issue within the Mac world (go figure).

For those that I haven't already bored to death, I will update as I resolve this problem so that it might help others that find themselves in this quandary.

[wcage03]

OK, this is tough. Looks like Apple has locked down local multicast network access in Sequoia and later.

"Local Network Privacy is new in Sequoia. The developer would have to update the app with a "multicast entitlement" so that it can be granted access by the user. "

 A quick look at how one would go about complying with this requirement looks involved. My understanding is that from an operational standpoint the Mac OS blocks UDP multicast network access by default now. If an application is run that has this multicast entitlement set, the OS will pop up a request to the user to allow the application to use resources on the local network. This registers the application (see System Settings / Privacy & Security / Local Network) and allows the user to allow/disallow access.. My understanding is that there is no way for a user to add an application manually. It has to be triggered through the application itself. This is why when I run Laz from the script it works in design mode -- it is running in my terminal program and that program has requested and received permission. When I compile and try to run the compiled program it fails since the new program does not have the multicast entitlement process built in so the application is not registered and permission is not requested from the user. The network access is simply blocked.

Basically, this effectively shuts down the ability to do things like connect to remote databases in a compiled Laz/FPC program on a Mac with a current OS installed. This is the reason the error message was "no route to the host."

More information - this is way beyond my capabilities.
from https://developer.apple.com/forums/thread/663875

What operations require the multicast entitlement?
The general rule is that sending or receiving multicast or broadcast traffic requires the multicast entitlement (com.apple.developer.networking.multicast). Common scenarios include:
Sending a UDP unicast — no
Sending a UDP multicast — yes
Sending a UDP broadcast — yes
Receiving an incoming UDP unicast — no
Receiving an incoming UDP multicast — yes
Receiving an incoming UDP broadcast — yes

I am not qualified to know if attaching to a remote database requires multicast or not. It looks like UDP unicast does not trigger the requirement. I will start another post for this topic. Thanks all for following along and pitching in ideas.