©2008-2017 David V Keeney All rights reserved.
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.
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.
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:
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.