Remote access to databases blocked by Mac Sequoia

[wcage03]

I have recently been trying to resolve a problem connecting remotely to a database server (Firebird, MySQL, PostgreSQL, MariaDB). I am on a Mac running Sequoia. After many iterations (see thread "PostgreSQL 'No route to host'" for details) of troubleshooting involving multiple database platforms, builds of Lazarus, builds of FPC, and component sets, I have come to believe that the problem is a new security mechanism in MacOS Sequoia and later that governs access to local networks. My initial development was using a local SQLite3 database. The issues entered when I wanted to promote the solution to a server based database.

I am out of my depth on this topic so forgive me if my details are incomplete or possibly incorrect. My understanding is that in Sequoia and later, Apple requires applications using UDP multicast functionality to include a multicast entitlement. This triggers a registration of the application and a prompting of the user for permission to allow. It appears that database access for remote databases using Lazarus components is falling into this category. Lazarus applications don't have this entitlement set The net effect is that remote database access using Laz on a Mac is effectively disabled. From first glance, it appears that the process to include this entitlement into your codebase is pretty extensive.

From what I have read, this impacts multicast and not unicast and only on resources on your local network (databases on localhost are not a problem).  I don't know if multicast is required for database access as-is within Laz/FPC or if unicode could suffice. That is way beyond my capabilities. It is interesting to me that other applications that I use for database access don't have this issue. For example, I use DBeaver to manage databases. It uses JDBC and has no problem connecting and it has not registered and requested permission (Mac users can look at System Settings / Privacy & Security / Local Network to see the applications that have registered and their permission setting). That seems to me to indicate that database servers can be accessed in a way that doesn't require the multicast entitlement.

My scenario can be easily replicated. It requires starting Lazarus once as an application and once from the command line. Luckily FPCUpDeluxe sets this up when that tool is used to build environments. If you run Laz from the generated script via your terminal and create a simple form with a TSQLConnector, a TSQLTransaction, a TSQLQuery, and, if you want, a TDatasource and a data aware control. Configure the components appropriately to access a remote database of your choosing. When the components are set to Connected/Active everything works correctly. If you have never been prompted for local network access for your terminal, that will pop up when you first connect. If you compile the program, and run it, you will get the 'no route to the host' (or similar) error. If you shutdown Lazarus and restart using the application icon the 'no route to the host' error will occur when you try to connect in design. Again, this is because the terminal program has gained permissions but Lazarus has not. I don't know, but I suspect that all newly created applications will need to incorporate this "multicast entitlement" or there will need to be way to connect without using UDP multicast. I have run into this problem with remote database connectivity, but I imagine there are other possible areas where this new setting could impact solutions coded in Laz/FPC.

I am hopeful that people with far greater knowledge of this topic can weigh in.

From online discussions regarding this issue for MacOS developers...

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

[Zvoni]

What's UDP got to do with accessing a Database Server?

AFAIK, e.g. with MySQL it's not possible at all to use UDP.
https://dev.mysql.com/doc/refman/8.4/en/transport-protocols.html
And i'm not aware of any other Database Server using UDP

I'd rather look into firewall-rules

[wcage03]

I'd rather look into firewall-rules

You are right! The problem is that MacOS Sequoia has introduced what is essentially a different firewall in settings called "Local Network". Look at System Settings / Privacy & Security / Local Network if you are on MacOS Sequioa. There you will see the 3rd party applications that are essentially whitelisted. When a 3rd party application attempts to access "local" network resources, it prompts the user for permission. This is the only method to get an application whitelisted. If the application is not on the whitelist, access to local network resources is blocked. This is true for using Lazarus to access remote databases on your local network.

This is very easily verified. If Lazarus is started from the terminal, then, while you are in design mode, the terminal is checked against the Local Network setting. Since the delivered terminal program is not a 3rd party application, it does not require user approval, thus, Access to a remote database is allowed. You can verify this by connecting and using a DBGrid or similar to see that access to the remote database is occurring. Compile the program and run it and it will fail since the OS is checking to see if your compiled program (which is now a 3rd party application) is in the whitelist. It is not and because I (we) have not gone through whatever mechanism are required by Apple to enable it to invoke the question to the user to allow the application to be added to the whitelist, no access is granted. It is a quiet failure with no recourse.

If you use a 3rd party terminal like iTerm, the first time you run iTerm you will be prompted to allow local network access. iTerm will then be on the whitelist. If you start Laz via iTerm, you can enable and disable Local Network access via System Settings for iTerm and you will see that does indeed allow and prevent access to remote databases on the local network in design mode using Lazarus. There is simply no question that this setting is directly impacting the ability of Lazarus to develop programs on the Mac platform that access resources on the local network (e.g., remote databases on the local network).

The obvious answer is, add Laz to the whitelist! The information found doing searches says clearly there is no way to manually add an application to that list. Discussions on the web indicate that applications have to have a "multicast entitlement" and getting that seems to go through Apple Development. My comments regarding UDP were in the hope that there would be a way to code the database components to avoid the requirements of the Local Network restriction, but I have to admit that simply reflects my ignorance on what protocols are required by Lazarus to do a remote database connection and what exactly triggers Apple's new Local Network restrictions. First appearances seem to be that the focus is on "multicast" protocols, but that could be a red herring.

I am sure some of my details about what specifically Apple is doing with this new "feature" are wrong, but the fundamental problem within the Laz/FPC community is accurate. As a 3rd party application Lazarus and probably any applications generated by Lazarus will have to get on the Local Network whitelist to be able to access local network resources. My problem is with remote database access, my guess is that there are other situations that will be impacted also. The process for addressing access is not clear to me.

[wcage03]

Here is more detailed information about the local network privacy/security from the Apple developer network. It does include code (I assume in Swift) to trigger the local network alert so that the application can be added to the whitelist. Basically it is forcing a connection to a socket. This is beyond my skills to translate this into pascal. Once triggered, the application can be whitelisted and local network access is not blocked. See the section named "Trigger the Local Network Alert"

https://developer.apple.com/documentation/technotes/tn3179-understanding-local-network-privacy

[duralast]

The firewall is disabled by default.

[wcage03]

This is a newly introduced "feature" in Sequoia. It cannot be disabled. It is in addition to any traditional firewall you may be running. If you are a Mac user, look at System Settings / Privacy & Security / Local Network. You may have noticed that you are getting prompted periodically when you run an application to allow access to local network resources. If you are prompted, the OS adds the device to Local Network list and captures whether you allow or disallow access. The user can manage this selection. What the user can't manage is adding or removing applications from this list. Lazarus does not cause the OS to prompt for inclusion. Lazarus is then blocked from access to local network resources. For me this means I can't access databases on a remote server within my home network. It likely will block access to other devices like printers that are attached to your home network and not directly to your computer.

Surely there are other Lazarus users that are running on MacOS Sequoia that can attest to this.

[duralast]

Others have reported success with the following temporary solutions.

1. Manually Whitelist App:
You can use following Terminal command to add applications to the firewall exceptions list, bypassing the issues in the graphical user interface (GUI).
Example

Code: [Select]

sudo /usr/libexec/ApplicationFirewall/socketfilterfw --add /Applications/Frefox.app
2. Disable the Firewall:
If the terminal command doesn’t work, or if the issue persists across multiple apps, you can temporarily disable the firewall:

Go to System Settings > Network > Firewall.
Click the padlock to unlock the settings and disable the firewall.
While this is not an ideal solution but it allows your apps to function until Apple releases an official patch.

[wcage03]

I appreciate the information. This is not the firewall. I have my firewall disabled. This is a new "feature" in Sequoia. Browse through the information in this link to get an idea of what it entails. This is not a bug that will be patched. It is something that we will have to adapt to. Applications will have to trigger the OS to ask the user if they would like to enable Local Network access. This will add the application to the list shown at System Settings / Privacy & Security / Local Network. There is no manual way to do this. My first glance at how to do the trigger seems to involve registering to be an Apple developer and running your application through their certification process, but it is unclear.

Look at the information in this link for some details on Local Network
https://developer.apple.com/documentation/technotes/tn3179-understanding-local-network-privacy

[Zvoni]

hmm.... from that link

Build-time considerations

Local network privacy uses your main executable UUID as part of its implementation. If your main executable has no UUID, or shares a UUID with other programs, local network privacy may behave weirdly. To fix that, make sure your main executable has a UUID and that it’s unique. For more about this, see TN3178: Checking for and resolving build UUID problems.

[wcage03]

Yeah, the next two paragraphs are interesting too: "Reset local network state" and "Trigger the local network alert." The first of these has the statement

On macOS there’s no way to reset your program’s Local Network privilege to the undetermined state (FB14944392). One alternative is to run your program in a virtual machine (VM). To retest, restore the VM from a snapshot taken before you installed your program.

and the second says

There’s no API to explicitly bring up the local network alert (FB8711182), but you can do this implicitly by performing a local network operation.

This sounds like a real problem brewing. In the section "Trigger the local network" Apple provides some code to presumably trigger it. I don't know Swift, but I do know ChatGPT! I used ChatGPT to translate the Swift code to Free Pascal. Then went to work trying to get that to work in Lazarus. It was not easy to resolve all of the issues, but I got it to compile and execute. I was hoping for the dialog to pop up when I ran it aking if I wanted access to the Local Network services. No luck.

The one thing that I am most sure of is that I had no idea what I was doing so I am absolutely sure that I did plenty wrong, but in the end, I have the sinking suspicion that my attempt at triggering was simply running into the block and not doing anything. I am in need of people smarter and more knowledgeable than me. Neither of those are high bars to clear!

I don't know what the UUID mention means. I will go and do some more hunting and run down a dozen other rabbit holes, but that is the joy of coding right?  ...right?  ;-)

[duralast]

In reply #5 you said the firewall can't be disabled, and in reply #7 you said it is disabled.

[wcage03]

I don't know how to be more clear: this is not a firewall. This is a new Privacy & Security feature in MacOS Sequioa that governs access to services on the local network. It is a part of the OS. I have posted a link to some of the Apple developer documentation. If you read that it should be more clear what the issue is. I have posted the link again below.
https://developer.apple.com/documentation/technotes/tn3179-understanding-local-network-privacy