Best way to update remote database

Hello all.

I have a website based database (PostgreSQL) and many remote databases (also PostgreSQL). In most cases, the remote databases will need to periodically update the website based database. Originally I was thinking of using JSON to do this, but was also thinking that a direct connection from the remote to the website database may be better.

In some cases there will be only one or two records consisting of about 20 fields, but in other cases there may be hundreds of records that need to be sent to update the website based database.

Can anyone suggest the best way to do this sort of updating?

Thanks,
Tim

If you have ssh tunnel access to your webserver you can use the Chilkat plugin to create an ssh tunnel in your xojo program to update the data directly in your Webserver Postgres database.

Exposing the website database to direction connections over the internet creates some security issues. I would generally try to avoid it. Even if you know the IP addresses of the remote PCs that are connecting and can limit access very carefully, if any of those machines gets a virus, etc you could still be vulnerable.

From a security standpoint, a safer approach (although more work) is to create a middleware layer, like a REST API service for instance, to handle data coming in and out from your remote machines. It should include authentication and encryption. Depending on how complex the operations you are trying to perform are, it could be fairly simple to write your own web application in xojo to sit between the remote machines and the db. In the end I guess it depends on how important the security of the data on the website database is to you, and how much control you have over the remote machines. I do direct connections from my own machine to databases in ‘the cloud’ but I would definitely be hesitant about allowing another PC to do that.

1 Like

If its using an ssh tunnel it is a secure connection and is only open while your program is communicating with the database. It will be closed as soon as you disconnect. What is the difference between using Navicat to administer your database and using your own Xojo program to access your online database over the same secure connection applications like Navicat use?

Again, I think it depends on how much control you have over those remote connections. If they are you on your PC, or 4 PCs you set up and have control of, then it really doesn’t matter. If they are other users using your software from remote locations, I generally wouldn’t want them having direct connectivity to my database - but that is just me.

The difference is that with a middleware layer you have more control over what is possible to send into your database from the outside. That is more secure than not having that control - even when the intended purpose of the ssh tunnel is only for your app. Putting that layer in there is not that difficult - but really depends on your level of risk tolerance, and the specific circumstances, to determine if it is worth it or necessary.

Agreed. We do both. I would not go the ssh tunnel route with outside entity access. Only in house machines.

[quote]
Exposing the website database to direction connections over the internet creates some security issues. I would generally try to avoid it. [/quote]

Postgres allows you to open access to certain IPs only via its pg_hba.conf file though.

Hola Tim.

You can use the extension postgres_fdw

I would use functions in the slave server to fetch data from the master server and process it at convenience.

Mauricio

If you need SSH tunnel, you can use SSH2TunnelMBS class in MBS Xojo Network Plugin.

1 Like

on macOS & linux its a one liner to SSH in a shell :slight_smile:

https://www.ssh.com/ssh/tunneling/example

But we can do it all within the app. this include passing password or public key for authentication.