The four roles on each Rdbhost account

Rdbhost provides up to four PostgreSQL roles for each database account.  Each account has exactly one database, and the roles are associated exclusively with the database.

NAMING


They are named uniformly, so that when a role, a database name, or an account id is received by the server, the other identifiers (roles, db names, account id) can be deduced.   The database name is the prefix 'db' followed by the account id (an integer) as a '0' left-padded 10 digit string.   The role names are 's', 'a', 'p', or 'r' followed by the same '0' padded 10 digit string.

The 's' role (Super) is created with the database, and owns every object in the database, including the 'lookup' schema, if present.  The other roles are optional, created at your request via the '/mbr/role_manager' page, and their privileges are whatever you set, via SQL 'GRANT' and 'REVOKE' commands.  Below we present a guideline for how you should set privileges for each.

There are important differences between the roles, though, aside from the differing privileges you grant each.  Some are permitted to submit arbitrary queries, and some are limited to pre-authorized queries.  Let us look at each in turn.

ROLES

1. The 's'-role, as mentioned above, is the owner of everything in the database.  The 's' is a mnemonic for Super, as it is the do-anything 'super' role within the database.   An authcode must  be submitted with each query request, and that authcode is your security, to prevent unauthorized users from damaging database resources.  You do not need to create any privileges for this role, as its ownership of each resource permits any operation.
2. The 'a'-role, like the Super role, has an authcode, and that authcode must be provided with every request.  'a' is a mnemonic for Authenticated, as the authcode authenticates the requester to use this role.  This role is limited to preauthorized queries (that is, queries found in either the lookup.queries or the lookup.preauth_queries tables), so it can be restrained to only run safe queries.  The web site uses the name Auth to refer to this role.
3. The 'p' role is like the Auth role in that it only executes preauthorized queries, but is *not* authenticated.  Any query preauthorized for this role can be executed by any member of the public.  'p' is a mnemonic for Preauthorized.  (It could also be a mnemonic for Public, reflecting the unauthenticated quality, but don't confuse it with the PostgreSQL role 'public').  Securing this role is a combination of setting role privileges and prequalifying safe queries.  For example a blog site might allow APPEND privilege to this role for the 'comments' table, so that anonymous readers of the blog can comment, and the query saved to the lookup.preauth_queries table would be written to require the user IP address to be entered into the 'comments' table to facilitate anti-spam maintenance.  The web site uses the name Preauth to refer to this role.
4. Last but not least, the 'r'-role is also *not* authenticated, and allows free-form queries.  Your only security with this role is in setting PostgreSQL privileges carefully.   Generally, you would grant SELECT privilege on tables that anonymous users need to read, and no other privilege.  'r' is a mnemonic for Reader.

PRE-AUTHORIZED QUERIES

The Auth and Preauth roles may only execute queries that have been preauthorized by entering them into one of the lookup tables, lookup.queries or lookup.preauth_queries.   The first is intended for manual entry, and the second is intended for automated entry.

Automated entry is done by the training mechanism.  See link below for more on that.

Manual entry into the lookup.queries is done by the Super role, using either the SQL_Form or the RdbAdmin tool.  Queries in the lookup.queries table must be accessed by their tag, not by the query string itself.  Use the 'kw' request parameter rather than 'q'.  Unfortunately, the Python DB API library (Rdbhdb) at this time does not support 'kw' lookups.


LINKS

• The roles
• Training
• Lookup.queries

Django?

I suspect that the Django ORM gets more use than either SQLObject or SQLAlchemy, just because it is part of the Django stack, included in Django installs by default. For this reason, I would like to include Rdbhost support in Django.

A day plus spent in pursuit of that goal has been fruitless. This post is a summary of my experience.

Before I announce support for an ORM or framework, I try to ensure that as much as possible of the framework's tests pass with this database. I found Django's testing tools difficult to understand and difficult to use.

I started testing in an app, with 'manage.py test', until someone cued me in to the runtests.py utility; the Pip/easy_install version of Django does not include most of the test suite, so when I found out, I switched to the tarball version.

Testing with runtests.py worked better, but still not great. Firstly, it seemed to ignore the '--fastfail' switch when the failure was an exception (an Error, in testing parlance), rather than a negative result of an assertion (a Fail). Most 'failures', for me, will be exceptions, and --fastfail should abort on them, as do unittest and py.test with the -x switch.

runtests.py doesn't abort on Error, and its error message is just 'ERROR', without a test name or line number or anything. Aborting with a ctl-C will abort the test run, but will not display the test report for prior failures. So the only way to get a test result (with test name and stack-trace) displayed for an exception is to wait for the batch of tests to complete, which could be 20-50 tests and take upwards of 10 minutes. This is an extremely tedious way to diagnose problems.

When you do have the name of a test, like 'modeltests.aggregration.tests.BaseAggregateTestCase', and try to run it specifically, with 'runtests.py modeltests.aggregration.tests.BaseAggregateTestCase', you get an error about 'test labels' and 'app...'

When I tried to runtests.py with a simple 'sqlite3' based settings file, I still got many errors, failing on 25-35% of the tests. This casts doubt on whether the test suite itself is maintained, and it is very frustrating to try to get a component to comply with test expectations when you cannot be sure the tests aren't themselves broken. The test runner seemed to find the json fixtures when running with the sqlite3 settings, but not with the rdbhost settings. Why would retrieving data from a file in the distro depend on what backend was configured?

The test suite looks fairly comprehensive, but without a test runner that will run specific tests in a predictable way, developing to the tests is not practical. The Django compatibility effort here is on the shelf for a while.

Page hosting at Rdbhost

We have long presented our service as a database hosting service, providing data querying for applications hosted somewhere else.

Now, you can host the whole thing right here.

Just stuff your html and JavaScript files into database blob fields, point your domain at our server
and register the domain name as belonging to your account here.  The registration is done from the 'Domain Alias' page, linked from the profile page.

Rdbhost will interpret a plain-jane URL as identifying specific data elements, and deliver your html and JavaScript blobs as pages to your users' browsers.

Setting the whole thing up involves a few steps, so it is probably not the easiest plan to develop incrementally with an Rdbhosted configuration;  instead, develop using a local server, as discussed in the DNS page, and move the JavaScript and html files to Postgresql field blobs for production deployment.

See a discussion of the features at:

http://www.rdbhost.com/user_domains.html

Query Timing

A small change has been made to add a data element to the data returned by queries.  The change applies to both the JSON variants and XML.

There is a new top-level element called 'times' which is an array of two decimal values.  The first decimal value is the duration, in seconds, of the processing the website front-end does prior to passing the query to the database back-end. The second is the duration of the database operation in the database backend.

A sample excerpt for JSON:

"status": [
        "complete", 
        "OK"
    ], 
    "times": [
        "0.036002", 
        "0.000000"
    ]

There is a third major element in the on-site processing of the query, and that is the time spent serializing the results into JSON or XML.  This time is not included in the time record, as the time array has to be populated prior to serializing.

You can measure the total round-trip time, yourself, from the client, but that time includes internet overhead.  These data items may be helpful in optimizing your queries.

As always, comments are welcome.

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.

Remote Database Access from the Browser Made Easy

You can now access Rdbhost databases from your in-browser javascript code without any special domain pre-configuration.

jquery.rdbhost.cors.js


The jquery.rdbhost.cors.js library works in modern browsers only, those that support the CORS extension to HTTP. If you are building an html5'ish site, you are already constrained to modern browsers anyway, and this library may serve you very well.

The API, the set of functions and methods, is the same as our other javascript library, jquery.rdbhost.js. You can switch between them by changing the script tag; the remaining code will generally still be valid. Obviously there are differences, otherwise why have two library? Read on for more detail.

Since you, as a web developer, are very likely using the latest browser version, you can start using this library as a dropin. Setup an account on the www.rdbhost.com website, if you haven't already, add your development host url to the remote hosts config field on the website, add a script tag to your html page including the library, and you are in business.
If the project develops to the point where compatibility with older browsers matters, or if you eventually need features like binary uploading that ajax does not support, you then easily swap out the jquery.rdbhost.cors.js module for the jquery.rdbhost.js module, and your code will be otherwise unchanged.

Changes to the website to support this include adding the CORS compliant headers to those page requests that present 'Origin' headers, and adding a field to the account records that tracks which domains are permitted to access each account. There is a new status line on the profile page to report what domains are registered, and a new form page, 'remote_hosts', to permit changing that domain list.

Differences

The regular library is both more capable and more hassle to setup.

• Requires your domain (possibly 'local.host') to have a subdomain that points at our server.
• Allows binary data uploads to database using file fields, as well as raw binary downloads.
• Works in nearly all browsers in common use, including Internet Explorer 6.

The cors library is easier to use, but has significant limits

• Domain configuration is NOT necessary
• No binary upload, though binary downloads of database contents is still possible.
• Requires a newer browser, Firefox 3.5+, Chrome 5+, Safari 4+, maybe Opera

Links:

•   Information about DNS subdomain configuration  
•   The library, in Git, on github.com  
•   The w3c working draft on CORS  
•   The Rdbhost Javascript howto page.  

How does Rdbhost.com compare to other services?

I get asked, occasionally, questions along the line of 'Why should I use Rdbhost instead of a VPS?' or '... instead of Appengine?'...

Now there is a page on the website that attempts to address that class of questions, at:

http://www.rdbhost.com/comparisons.html

I am not going to rehash each comparison here in this blog-post, but am going to cover some generalities.

Most of the competitors are variations of web hosting or web server accounts. Each involves a fair amount of setup and custom configuration before it serves useful data. Most involve setting up an SQL database, and then migrating the initial database tables, views and other resources to that database.

Rdbhost databases are created with JSON and XML encoding built-in. Figuratively, ready 'out of the box', but literally 'already in the box'. Http request parsing and database querying, already in there.

The SQL queries themselves, you have to write, as they are necessarily custom to the project. What we can setup generically, we do setup. We even provide a means to restrict specific database roles to running only pre-approved queries.

There is also a cost difference, in that the hosting or server accounts charge by the month, so the minimum cost is always non-zero. For most business developers, this would be the least concern.

One competitor I looked at provided a web-service specifically limited to databases. CloudDB seemed to use a non-standard query language, which would lock users in to their service, once the code was written. I say 'seemed', as they have not seen fit to give me an account. If any of you do have accounts there, I would appreciate reading a review, or at least, corrections or enhancements to what I have been able to glean from the documents.

Comments are welcome, especially those that suggest competitors thay might be more of a straight-up substitute for Rdbhost.com.