Training your Server
You may have visited us after hearing the client-only web-programming pitch, where a database-backed web application can be written without any programming on the server whatsoever.
The secret to avoiding the whole server-programming burden is in the training process. Let's start by considering a couple of background issues.
SQL on the Client
The obvious concern is security; if any query can be submitted by any client, how do you prevent malicious acts? The security model has two layers, PostgreSQL role privilege setting and query pre-authorization requirements.
Postgresql Role Privileges
You can use Postgresql roles to limit what a query can accomplish. If a given role has no privilege to drop a given table, then Postgresql will raise an exception when the role is used with a query to drop that table. But the granularity of the role privileges is fairly coarse. If you mix records of different customers in a given file, for example, the privilege constraints apply to all records, and cannot be used to protect records of one customer from another customer.
Due to the limitations mentioned above, Rdbhost provides another security layer in front of Postgres, providing the ability to restrict which exact queries each role can execute. Of the four roles provided with each account, two allow free-form queries, and two allow only queries that are pre-authorized. A query received with a Preauth or an Auth role will be checked against tables of approved queries, and only queries found there may be executed. The tables amount to a white-list of approved queries. The queries can use parameters, so the same query can be used with different data on different calls. Both tables of pre-authorized queries are kept in your database, in the lookup schema. One table is called 'lookup.preauth_queries', and the other is called 'lookup.queries'. The queries table is meant for manual editing, using Rdbadmin, and the preauth_queries table is meant for automated entry, which we call 'training'.
There is an automatic way to populate the preauth_queries table, though, which we call 'training'. On the training page of the website, you can register your workstation by its IP address, and the server will handle queries received from your workstation specially. When a query that must be pre-authorized is received, it is checked against the pre-authorized table; if it is not found there, it is added before it is executed. So loading all necessary queries into the preauth_query table is a matter of enabling your workstation to train the server, and then running all necessary queries. Running all queries can be managed by running your test suite, if you have one, or by manually exercising all features of the application. After training the server, go back to the training page and remove the IP address. We allow multiple IP addresses, so that the server can stay in training mode during an extended development period, and multiple client machines can develop on the same account.
Creating a web app completely on the client is straightforward:
- create an account with its private database, using the website
- setup your 'hosts' file for immediate domain name configuration.
- register your IP address on the training page
- code your app on your workstation, using a very simple local 'server'; other developers can work with you, using their own local copy
- clear the preauth_queries table from the training page; this removes the imperfect queries created during testing.
- run the app's test suite, or manually use every app feature; this repopulates the preauth_queries table.
- remove your IP from the training page
- upload your source files to a hosting account, and setup DNS pointers in your DNS domain manager.
- distribute your app
All your editing and testing is done on your local machine, and the SQL queries are transmitted to the machine implicitly as part of executing them.