How to Train Your Database Server

Rdbhost allows SQL queries to be submitted from the browser and executed.   A cursory consideration of that model suggests security problems, in that whatever is in the browser can be manipulated, and user-generated queries can be submitted by any user using authentication information extracted from your source code.

We thought of that, and provide means to authenticate individual queries and prevent execution of unapproved queries.  The Rdbhost service supports four different roles; two of them allow free-form queries, and the other two only allow pre-authorized queries.

Roles for Free-form Queries
The first two, the s- (super) and r- (read) roles allow any arbitrary query to be executed.  The first is all-powerful, owning every resource in the database, and relies on an authcode (a long non-memorable password) to prevent use by arbitrary non-authorized users.  The second, the r- role, allows any query from any user, and relies on the Postgresql role privileges being set to prevent unwanted changes to the database.  Basically the r- role would have only SELECT privileges on relevant tables, allowing data to be read, but erroring on attempts to change the data.  The r- role provides the convenience of being able to change queries in your client source code without retraining the server.  Web applications, in my experience, tend to have many more read queries than write queries, both in access count and in number of distinct query strings.

Roles for Mutating Queries
The other two roles allow only pre-authorized queries, and are the roles subject to training.  These roles may
submit queries with either the SQL query verbatim, or with a keyword that retrieves the SQL from a lookup table.  The query SQL itself must already be in either the keyword lookup table or in a table of pre-authorized queries.  The keyword lookup table is called 'lookup.queries' and is intended for manual entry and maintenance.  The other table is called 'lookup.preauth_queries', and is intended for automated query entry.  These two roles are prefixed with 'p' and 'a'.  'p' is a mnemonic for public and has no authcode,  while 'a' is a mnemonic for 'authenticated', and does have an authcode.  Most apps will use 'r-' role and 'p-' roles exclusively.

Training
Queries are entered into the preauth_queries table via the training process.  The website has a page in the members area for enabling training.  Training is enabled for individual client IPs separately, so some clients can be using the table in the usual safe way, while your workstation client, registered as a trainer, is entering queries into it.   Once your workstation's IP is added to the trainers list, you can run any query from any role; when a query is received for a p- or a- role it is added to the preauth_queries table before executing.  So executing your apps test suite or simply exercising every feature of the app will put all necessary queries into the preauth_queries table.  You can then disable the training mode, test your app again, and release it.

Overview
The general approach to application development in Javascript is:

1. Create an account, with its own database, on www.Rdbhost.com
2. Create necessary roles from role_manager page
3. Enable training for your workstation from training page
4. Configure your hosts file to include necessary local and Rdhost server entries
5. Run a local server; a tiny Python 8-liner will do
6. Develop your application
7. Develop your application (it's in here twice, because it's big)
8. Develop a test suite (optional)
9. Clear the preauth_queries table on the server; this removes development cruft
10. Run your test suite, or just exercise all SQL-using features of the app
11. Disable training for your workstation, from training page
12. Test application, either by test suite or manually
13. Release and distribute your app

A process of thirteen steps seems long, but only steps 6-8,10 and 12 are more than a few mouse-clicks worth of effort.

Related Links:

1. Training Page
2. PostgreSQL Roles on Rdbhost
3. Javascript API module for Rdbhost

Your comments are welcome, to dkeeney@rdbhost.com, or in the blogspot comment facility below.