db connect strategy

[townsend]

I've been following the database connect question thread closely, as it pertains to my project directly.

I now understand MUCH better the limitations and dangers of connecting to a remote db directly from a compiled application. The two issues that concern me most are:

1- db access login and password could be easily cracked.
2- most virus protection software flag warnings on odd port usage.

I heard over and over again, how doing so is a bad idea, but given the requirements of my project, I'm compelled to look for a safe workaround. Let me briefly describe this project so you'll understand my motivation.

I've sold many desktop applications over the years. But the marketplace is very different now. With the advent of iOS and Android-- these days-- most applications are being sold for next to nothing. Plus-- there's a LOT more competition than 10 years ago. Thus selling an application, authored by a single developer, for $200+ is nearly impossible. But-- offering a client/server application could easily put me over the top on these higher licence fees, and put me in an arena where there is little competition. No longer would I be offering a application to a single user, but rather to a owner/moderator who would then be managing a community of potentially 100's.

While it's certainly possible that I could require all owner/managers who purchase the license to set up a On-Rev account, to avoid the pitfalls of a direct connection, it would complicate my support and setup work greatly. So I've come up with a possible solution to get around this, and I wanted to get some opinions of how viable this really is.

Each license would be locked to a domain name. The license and db user/password would be encrypted into license files specific to that domain. Thus I would not need to compile a new executable for each new licensed domain, but rather just a new license file.

The common login and password for the user client, would be restricted to a single new account que file. Since only a user with admin privileged can set up such a new account with the needed array of restricted permissions, in addition to the main user's client application, there would be a separate management/maintenance client app that only the owner/moderator of the domain would have. I pretty sure this is possible using mySQL or Postgres permissions.

Once the new accounts requests were written to a new account que file, it would be the the management client, with the admin rights, that would manually or automatically approve and set up the new user with the needed permissions. Thus all the critical management operations would be restricted to a owner/moderator's management client app. Then once a new user is approved, a unique user name and password would give users access to only creating new records based on their user ID. Again, I'm pretty sure db permissions can be set up like this, with these restrictions.

The point is, this way, only the owner/moderator has complete write and delete permissions. Users can only write records that include their user ID. What do you think? I know it requires a LOT more work up front, but is it viable?

And lastly, as for how to prevent each new user from having a odd port attempt warning from their anti-virus software, which is less critical, I still haven't figured that one out.

PS: The main reason I've been fighting the cloud only solution is because my app is very CPU intensive. Not so bad as a desktop app, but if all processing was done in the cloud, scaling up would become very problematic.

Note: edits include many clarifications. Thanks for the feedback.

[Mark]

Hi,

Could you please explain what a license would entitle people to do with your app? That would be useful to know before I give advice.

Kind regards,

Mark

[townsend]

Think of it this way. A license holder allows the owner/moderator to do all the admin stuff in a forum.
The users of the forum each have their own login and password which allows them to only post messages.

The owner/moderator and users each use different applications, referencing the same db,
so users never have access to admin or maintenance permissions.

[Mark]

Hi,

What I still don't understand is this: do you sell the server software together with the admin and user client apps or do you only sell admin and cient apps and give access to your own server?

Kind regards,

Mark

[townsend]

The owner/moderator's maintenance app creates all the tables from scratch.
They are responsible for their own PostgreSQL hosting,
which is available many places for very little.
Of course I provide instructions on how to initialize the PostgreSQL db.
The owner/moderator can distribute the main client app to as many users as they want.
They can optionally charge fees for participation.

I sell only the owner/moderator a set of 2 client apps.
One the keep for management, the other they distribute to their users.

[Mark]

Hi,

OK I now understand what it all is about. Since each administrator has his or her own server, it is possible to run PHP scripts on that server. The most secure way is to send codes from your software to PHP scripts. Each code corresponds with a query. The PHP script uses the code to decide which query to run and returns the data to your application.

If the above is for some reason impossible, you can send even long queries from your application to a PHP script, using the libUrlMultipartFormData function. Prepare the query with this function and then send it to the PHP script. The PHP script should take the query and execute it. It would be wise to encrypt the query before sending and to decrypt it on the server.

In both cases, it is the PHP script that makes the actual connection to the database. Although not strictly necessary, you might want to read the credentials from a file that's outside the public directory. There are several ways to do this and if you know something about PHP it should be easy for you.

In any case, never send the user name and password for your database from your software to the server. It is unnecessary and unsafe. Also, make sure that your license conditions reject any responsibility for security

Kind regards,

Mark

[mwieder]

I've been resisting adding to this conversation, but...

I wholeheartedly agree with what Mark posted here. It's also possible to do this with the livecode server as an alternative to php if you want to sell a complete solution (client end, LC script, livecode server) or to set up web services on the server as a php alternative. But in any case, avoid putting a public face to the database itself. Postgres only allows localhost access by default and you have to go out of your way to change that.

I now understand MUCH better the limitations and dangers of connecting to a remote db directly from a compiled application.

If you still want to go ahead with a public-facing database connection, then I doubt that statement is true.

[townsend]

In any case, never send the user name and password for your database from your software to the server.

So-- you're saying even if only the owner/moderator was the only one who was logging on to the server with full permissions, that single instance of a login and password would be still vulnerable. (Unless it could be done with a SSL connection, which I believe is not possible with livecode.)

As for PHP, I'd much rather just use LiveCode on a On-Rev.com server account. So-- sounds like the management work needs to be coded for an On-Rev.com server account.

As for the users desktop client, any individual login info might also be vulnerable-- but-- no more vulnerable than this forum.

Thanks for the feedback on this concept. I do understand this much better now that we're talked about it.

The good news is-- On-Rev.com accounts include use of a server license, which means I don't need to buy a separate LiveCode server license for each person who purchases my application. That was why I stopped using Alpha-Five.

PS: Looking forward to the LiveCode Small Bussiness Academy. Sounds like just what I need.

[Mark]

Hi,

Yes, it is always a bad idea to send a password to a server. Even if you use SSL, you might not want to do this. It is an unnecessary vulnerability.

Every hosting service provides PHP. Many hosting services will not allow installing a LiveCode server.

Kind regards,

Mark