This blog will be continued over at:

http://ghost.rdbhost.com

The new host runs on Ghost.  It seems a bit more flexible, with regard to page formatting and including syntax-highlightin on code blocs.

Security Overview

Various aspects of how requests are secured are covered on the website, but there is no summary overview anywhere.  This post attempts to address that, as the security seems to be an issue for many casual reviewers of the product.


Let's approach the discussion by looking at all the ways a requested query can fail.  By 'fail', I mean fail to have any effect at all.  More to the point, these are ways that a malicious query would fail to cause any harm.

Roles

Any query request received by the server must indicate a role.  These are Postgres roles with additional constraints applied by the server software.

If the role is missing the server rejects the request with an error message.

The role must be a valid role for the account, one of the four created for the account, and that role must have been enabled from the account profile.

If the role is invalid, the server rejects the request with an error message.

 The roles are Super, Preauth, Auth and Reader.  Each has a distinct security system, and you choose a role based on what the query will accomplish, who will be using it, and which security system is most relevant.  As the roles are distinct in their behavior, we will go over them individually.

Super

The super role can run any query valid within a database (this excludes creating a database or creating roles), and is used for setting up tables, schemas, functions and other resources in the account database. 

The super role has an authorization code (authcode) of 50 random digits, required.

If the authcode is not received with a super-role query request, or the authcode is not the correct code, the request is rejected with an error message.

Do not share the authcode.   If you believe the authcode has been adversely disclosed, there is a function on the website to reset it (non-reversibly) to a new random authcode.

Preauth

The preauth role would be used by web applications available to the anonymous public.  Preauth role can execute only queries listed in a server-side white-list.   An anonymous web user can click through to your web application in his browser, and interact with the website.  Your app in his browser would request queries from your server using the preauth role.   If the user tampers with the application source, those altered queries would no pass the white-list.

If the submitted query is not already in the white-list, the request is rejected with an error message.

The preauth role, submitted by an anonymous user, cannot change the white-list, because the queries that change the white-list would have to have been white-listed.

There is an easy method to put queries into the whitelist:

1. Log into Rdbhost website
2. Put account in 'training mode' for your workstation's IP
3. Submit the preauth queries.
4. Login into Rdbhost website again, and turn 'training mode' off

Queries submitted in training mode, from registered workstations, get added to the white-list.

Reader

The reader role will not be needed  by most applications, and can be left disabled.  If used, the account owner must setup resource permissions for this role carefully.  The role has no authorization code, and no white-list.  Any query submitted with this role will be executed.

If the SQL query is not permitted by Postgres resource permissions, the request will fail, and an error message (containing the Postgres error code and message) will be returned to the client.

This role might be useful, for reporting applications, as SELECT queries can be created new, and altered to suit presentation requirements, without having to re-train the white-list for each tweak.

Summary

Each of the Postgres roles associated with the account has its own security model.  Each is safe, and one or another of them will allow you to do what you need to do.  SQL is a very powerful API.

Rdbhost and Angular

Rdbhost, for those of you unfamiliar, is a web application host that allows for the submission and conditional execution of SQL directly from browser code.  You can write a complete multi-user web application working entirely in the browser, with no server-side coding whatsoever.

The service integrates with a variety of front-end frameworks, including Angular.

This post will describe how to use Angular's tools to interact with an Rdbhost.com hosted database account.  It will take a multi-stage approach to the explanation, showing how to adapt jQuery based code to use Angular's alternative $http, and then to use the higher level $resource feature.

All the examples below assume that the libraries are included in your app, like below. If you use the Rdbhost generated boilerplate, all this will be included.

<script src="http://www.rdbhost.com/js/jquery.rdbhost.js"></script>
<script src="http://www.rdbhost.com/js/jquery.rdbhost.utils.js"></script>
<script src="js/rdbhostangular.js"></script>

and that the jQuery plugin is initialized like:

// tell rdbhost module what role and account we are using
//
$.rdbHostConfig({
    accountNumber : 929,
    domain        : "www.rdbhost.com",
    userName      : 'preauth'
});

var mod = angular.module('myApp.controllers', []);

jQuery Plugin

Our first example is to use the Rdbhost jQuery plugin to list records from a table, and to add a record to it.

mod.controller('DvdPreauthController', ['$scope', function($scope) {

    $scope.status = [];
    $scope.dvds = [];

    function add_status_line(ln) {

        $scope.status.push(ln);
        $scope.$apply();
    }

    var addDVDSQL =
        'INSERT INTO sandbox.dvds_jsintro       ' +
        '   (name, rating) VALUES (%s, %s)      ';

    var getDVDListSQL = 'SELECT name, rating FROM sandbox.dvds_jsintro';

    // submit handler for form
    //
    $scope.addDVD = function () {

        var args = [ $scope.newTitle, $scope.newRating ];

        R.preauthPostData( {

            'q'       : addDVDSQL,
            'args'     : args,
            'callback' : function (resp) {

                add_status_line('one DVD added');
                return resp;
            },
            'errback' : function(err) {

                add_status_line(err[0] + ' ' + err[1]);
                return err;
            }
        });
        return false;
    };

    // updater for list
    //
    $scope.listDVDs = function () {

        R.preauthPostData( {

            'q'       : getDVDListSQL,
            'callback' : function (resp) {

                $scope.dvds.push.apply($scope.dvds, resp.records.rows);
                add_status_line('DVDs listed');
                $scope.$apply();
                return resp;
            },
            'errback' : function(err) {

                add_status_line(err[0] + ' ' + err[1]);
                return err;
            }
        });

        return false;
    };
}]);

The method R.postPreauthData() is from the rdbhost.jquery.js plugin.  It submits the query and passes results to the callback.  The $scope.$apply() function is called to allow Angular to work.  Because Rdbhost is calling the callback instead of Angular, we use $apply to keep Angular 'in the loop'.

$http

Here is the same code, written to use Angular's lower level $http feature.  Well, almost.  Actually we use the Rdbhost variation of the $http feature, $rdbhttp.  It has the same methods as $http, but ensures that errors returned in the body of the response, as well as errors in the header, are handled by the error handler function.

// Produce a new rdbHttp object for interacting with Rdbhost
mod.factory('$rdbHttp', ['$http', '$q', RdbhostAngular.makeRdbHttp]);

// Change $httpProvider -remove unwanted headers add response transform
mod.config(['$httpProvider', RdbhostAngular.providerInit]);

mod.controller('DvdPreauthController', ['$scope', '$rdbHttp', function($scope, $http) {

    var R = window.Rdbhost;

    $scope.status = [];
    $scope.dvds = [];

    function add_status_line(ln) {
        $scope.status.push(ln);
    }

    var addDVDSQL = 'INSERT INTO sandbox.dvds_jsintro       ' +
                    '   (name, rating) VALUES (%s, %s)      ';
    var getDVDListSQL = 'SELECT name, rating FROM sandbox.dvds_jsintro';

    // submit handler for form
    $scope.addDVD = function () {

        var args = [ $scope.newTitle, $scope.newRating ];

        var opts = {
                q: addDVDSQL,
                userName: 'preauth',
                args: args,
                authcode: ''
            },
            gP = R.getPOST(opts),
            p = $http.post( gP.url, gP.data );

        p.success(function(data, status, headers, config) {
            add_status_line('DVD was added.');
        });

        p.error(function(data, status, headers, config) {
            add_status_line(data.error[0] + ' ' + data.error[1]);
        });
        return false;
    };

    // updater for list
    //
    $scope.listDVDs = function () {

        var opts = {
                q: getDVDListSQL,
                userName: 'preauth',
                authcode: ''
            },
            gP = R.getPOST(opts),
            p = $http.post( gP.url, gP.data );


        p.success(function(resp, status, headers, config) {
            $scope.dvds.length = 0;
            $scope.dvds.push.apply($scope.dvds, resp.records.rows);
            add_status_line('DVDs listed');
        });

        p.error(function(data, status, headers, config) {
            var err = data.error;
            add_status_line(err[0] + ' ' + err[1]);
        });

        return false;
    };
}]);

Notice the absence of $apply() calls.  Because Angular itself is calling the success and error callbacks, it invokes its own $apply function as necessary.  R.getPost generates the correct url and post data to retrieve the query results from the server.

$resource

The $resource feature wraps a server resource in a JavaScript class, and allows operations on the server through methods on the class.  The class can retrieve and send data to the server, and records received have methods to update or delete their own persistent values on the server.

With Rdbhost, you can use the $resource from the stock ngResource module, but you need transform functions passed into each of the actions.  You must use Angular 1.2 or later, as that version introduces the transformation functions.  See example here:

// Produce a new rdbHttp object for interacting with Rdbhost
mod.factory('$rdbHttp', ['$http', '$q', RdbhostAngular.makeRdbHttp]);

// Change $httpProvider -remove unwanted headers add response transform
mod.config(['$httpProvider', RdbhostAngular.providerInit]);

mod.controller('DvdPreauthController', ['$scope', '$rdbHttp', '$resource', function($scope, $http, $resource) {

    var R = window.Rdbhost,
        RA = R.Angular;

    $scope.status = [];
    $scope.dvds = [];

    function add_status_line(ln) {

        $scope.status.push(ln);
    }

    var addDVDSQL = 'INSERT INTO sandbox.dvds_jsintro  (name, rating) VALUES (%(name), %(rating))      ';

    var getDVDListSQL = 'SELECT name, rating FROM sandbox.dvds_jsintro';

    var role = 'p'+('000000000'+ $.rdbHostConfig.opts.accountNumber).substr(-10);


    var listResource = $resource('https://:domain/db/:userName',

        { // default params

            userName: role,
            domain: $.rdbHostConfig.opts.domain
        },

        { // actions

            query: {

                method: 'GET',
                params: {
                    q: getDVDListSQL,
                    format: 'json-easy'
                },
                isArray: true,

                transformRequest: RA.rdbhostTransformRequest,

                transformResponse: RA.rdbhostTransformResponseFactory(true)
            },

            save: {

                method: 'POST',
                params: {
                    q: addDVDSQL,
                    format: 'json-easy'
                },

                transformRequest: RA.rdbhostTransformRequest,

                transformResponse: RA.rdbhostTransformResponseFactory(false)
            }
        }
    );

    // updater for list
    $scope.listDVDs = function () {

        $scope.dvds = listResource.query({},
            function() {
                add_status_line('DVDs listed');
            },
            function() {
                add_status_line('DVD List Error');
            }
        );
        return false;
    };

    // submit handler for form
    $scope.addDVD = function () {

        var namedParams = {
            name: $scope.newTitle,
            rating: $scope.newRating
        };

        listResource.save(namedParams,
            function() {
                add_status_line('DVD was added.');
            },
            function(err) {
                add_status_line(err.message);
            }
        );

        return false;
    };

}]);

The rdbhostTransformRequest and rdbhostTransformResponseFactory functions handle converting the http requests as Angular generates them into what Rdbhost.com expects, and converts the Rdbhost.com results into the form Angular expects.  Other than providing those functions to your $resource constructor, and providing the SQL and parameters in the constructors action list, $resource acts like $resource.  If you provide a save or delete action, that action will be available on the retrieved objects, as $save or $delete. The rdbhostTransformResponseFactory takes one parameter isArray.

If you have your own response transform functions, be sure to include them in the transform function list ahead of the Rdbhost functions and do not serialize as part of your transformation, as the Rdbhost functions will serialize the response at the end of the transformation chain.

The Rdbhost Website

An Alternate Method of Enabling White-list Training

With the latest updates to the rdbhost.jquery.js jQuery plugin, coding front-end applications using the full power of SQL is even easier.

For those readers not familiar with the Rdbhost.com system, let me recap:

Rdbhost provides SQL databases accessible, safely and directly, from browser code.    Just write SQL inline in your JavaScript application, the SQL will be submitted to the Rdbhost server as needed, validated, and executed.  Results or an error message are then returned to the client.

Validating the SQL submitted is managed by comparing SQL queries with a white-list, and that white-list is populated by a training process.   Previously, that training process was enabled by going to the Rdbhost.com website, and enabling training for your client IP.   The white-list would then be populated by running the client application to submit necessary queries.  After the training is complete, the training would be disabled from the website account management pages.

The prior two paragraphs describe how the system has worked until now, and that method will continue to be available.  However, there is now an easier way, a way that contributes to an even nicer workflow.

The rdbhost.utils.jquery.js plugin includes some new methods, allowing training to be authorized from the client app:


$.preauthPostData({opts...});

The options object can have exactly the same elements as the $.postData method, used in the same way.  The difference from $.postData is that when the request fails on a white-list error, the $.postData method will just return an error, bu the $.preauthPostData call will allow you to enable training mode briefly and resubmit the request.  It will show you an html dialog box for receiving the account email and password, get those from you, and then use them to enable training briefly (8 seconds), and immediately resubmit the request.  The second submission would successfully white-list the query.


There is a similar method for form submissions, that works analogously:


$.preauthPostFormData(form, {opts...});

The parameters are the same as $.postFormData, and used the same way.  It prepares the form just as $.postFormData does, but if the submission results in a white-list error, it responds as above to temporarily enable training, and resubmit the form.

The significance of these two new methods is that you no longer need to log-in to the Rdbhost website to enable training for each session, but can enable it on a query by query basis.   If the once per session enabling seems more efficient, it is still available.

Automated Backups

Regularly Scheduled Backups to S3

Backing up your account to Amazon S3 has been a feature here for years.  Now, you can schedule automated backups, where your Rdbhost account is dumped to your S3 account on a regular basis.

You can arrange the backup to be done on a regular interval from 15 minutes to 7 days.  The backups are put in the S3 bucket you designate in your S3 config page, and are named with the date and time, as well as the account id.

Generally speaking, managing the dump files in your S3 account is your responsibility, manageable through Amazon's own management pages.  However, we do offer one tool to help.  After each backup, you can have Rdbhost prune the backup file collection, based on simple rules.

The first rule is a maximum age, where every backup over that age gets deleted.   The second rule is a percentage limit; each backup cannot be older than a given percentage of the next older backup.  For example, if your oldest backup is 10 months old and your configured percentage is 90%, the next oldest cannot be older than 9 months, and the one before that can only be 8 months, 3 days old, and so forth.

This pruning can help in keeping your S3 account from getting too large, too quickly.

TodoMVC, Rdbhost style

The TodoMVC project was created to help people choose a JavaScript MVC framework.  It includes a couple dozen implementations of the same HTML5 app, a Todo list.   Each implementation uses a different JavaScript framework.  Each of them uses the browser's localstorage for Todo storage, avoiding any specific back-end dependence.

Rdbhost has its own version of this TodoMVC project.  Here, the intention is to show how each framework can be used with the Rdbhost.com backend.   So far, I have implemented versions for the most popular 5 frameworks, AngularJS, Backbone, KnockoutJS, Ember and CanJS.   I may add more in time.

Each back-end interface is fairly minimal;  For AngularJS and KnockoutJS, it uses a singleton blob in the database for all Todo records.  Backbone, Ember and CanJS have a CRUD type back-end interface, and for each of those, I created a table with one record per Todo, and a CRUD-style client-side adapter to put and get data.  Each framework project includes a page 'initialize.html', that requests the Rdbhost account password, gets the super authcode with that, and sets up the necessary tables.

AngularJS seems to include an integrated backend data retrieval API, and I may re-implement the AngularJS TodoMVC to use a more sophisticated approach.


Find the working site at:  http://todomvc.rdbhost.com .
Code is at: https://github.com/rdbhost/todomvc .

Enjoy

#nobackend

#nobackend

The 'nobackend' meme has gotten some discussion among the G+ and twitter accounts I follow.  I was excited at initial review, as it seemed to be very inline with Rdbhost's goal of enabling Web App development without any server coding.

On further examination, I found the key documents to be hazier then I had briefly hoped;  the expressed goal is to incite discussion on the objective of writing front-end code 'without thinking about the back-end'.

Rdbhost's power is in moving the critical custom back end design elements, such as database schema and database queries, to the front-end.  Moving them to the front-end is more pragmatic than ignoring them altogether, though perhaps less appealing to many front-end devs.  ;)

Nonetheless, I want Rdbhost.com to be part of the discussion, so I have implemented their sample app, an html5  invoicing app, using Rdbhost as the backend.  Read about the reference app, and the while #nobackend promise, at nobackend.org.

The Rdbhost version of the app is at http://invoice.rdbhost.com ,  and the source is at http://github.com/rdbhost/EditableInvoice .  The entire code base for the app is in that Github repository. There was *no* code deployed to the server in support of this specific app.

I used OpenId as the only login option.  The code to setup the necessary server-side tables is in /initialize.html .

Find the JavaScript API at: github.com/rdbhost/Rdb.Js/.

David Keeney

The Rdbhost server was upgraded this weekend.

Now, it supports Postgresql 9.2.   Aside from the general goodness of having the latest version available, this version supports some new embedded languages.  The new languages are not available just yet on rdbhost, but stay tuned.

David
dkeeney@rdbhost.com

Attention Rdbhost Clients

If you have an active JavaScript application relying on an Rdbhost.com account, you should consider upgrading your app, to use current libraries.

If you are using jquery.rdbhost.exdm.js from www.rdbhost.com, loaded by your app from our server, and you do not use the openId log-in methods, and you use a recent version of jquery (>= 1.5) then you are probably ok as-is.  Your app will load the current version, and all is well.  Please check, this week, and again next week, to verify correct behavior.

Otherwise, upgrade.  Use either the current jquery.rdbhost.exdm.js from our server, or get the current version from our github repository.  Load it after a recent version of jQuery (version >= 1.5).

The compelling reason to upgrade is that server-side support for cookie processing is being dropped soon.  The protocol included the ability to interpolate cookie values into queries, as a convenience to the programmer.  Unfortunately, this facilitates clients creating Cross-Site-Forged-Request (CSFR) vulnerabilities.   So, to avoid this hazard, I re-coded the JavaScript module to handle cookies client-side.  Now, it converts the cookie tokens into regular named parameters, and adds the cookie values to the named parameters hash. Now, the server should never see cookie tokens in any query, and in a week or so, the server side support will be dropped.

Other than this, there are numerous improvements.  OpenId logins work better now, as a reliance on iframes has been removed.  The Rdbhost jQuery plugin methods now return Deferred promises, as appropriate;  this behavior is similar to current jQuery ajax behavior.  You (the account owner) can now login to your account via an API call.

Cookies are Harmful (in single-page apps)

Do not use cookies in your single-page app.

At least, don't have your server use cookies from your single-page app.  Having the app write a cookie provides some state persistence when the user insists on rattling the reload button.  The server should ignore the cookie, relying only on input submitted with each request explicitly.

Cookies are obsolete, dating from the early 1990s, when Netscape invented them to add state to a stateless protocol.   Their purpose was, and is, to allow the server to track a user session between page requests.

Your single-page app has no use for cookies.  Just keep state in variables and in the DOM in the browser, and send the authentication with each and every ajax request.  It is simple, reliable, and safe.

Why the fuss?  Well, using cookies (or web server authentication, for that matter) sets your application up for a cross-site-request-forgery  (CSRF) attack.   You don't want that.    A CSRF attack involves another website 's page secretly submitting a request to your site, using passive authentication credentials stored in the user's browser, to do things within the users account on your site without the user's knowledge or approval.

The way to avoid CSRF attacks is simply to avoid passive authentication methods, such as cookies or web server authentication.  I call these passive, because they do not require explicit inclusion in a given request; the browser automatically adds them to any request on your site.  The absence of passive authentication credentials means no successful CSRF attack is possible.

We should consider cookies obsolete and dangerous, in the context of any single page app, and single-page apps are where the web is, now, in 2013.

OK, maybe your website is a small collection of single-page apps, and needs to maintain session between these multiple apps;  if so, then maybe you need to use cookies to maintain state, but don't tell the server.  One app can create a cookie client-side, and the browser will make it available to all single-page apps on the domain.  The apps know user session state from the browser cookie.  That the cookie gets sent to the server regularly is merely an undesirable side effect, and that side effect can be ignored.  Just don't read that cookie in any server code; it cant be used in a CSRF attack if the server refuses to recognize it.  If your app is an html5 app, not needing legacy compatibility, then use web databases or some cookie alternative that doesn't get sent to the server.

There is a common counter-CSRF technique that calls for including some arbitrary value in both a cookie and either the request header or request fields.  If you can put the cookie content into the request in addition to the cookie, you can put the content in the request instead of the cookie.

Cookies are bad for your single page app.  Avoid them.

Indexing AJAX content

Search Engines

One of the more compelling reasons to stay server-side and avoid putting all business logic on the client is the matter of search engines.   Google and the other search engines have not traditionally indexed dynamic content, as they don't run the client side JavaScript necessary to realize the dynamic content.

We now support the search engine indexing of AJAX pages, so this reason is now not so compelling.

Ajax Indexing

A year or so ago, Google introduced the Ajax crawling specification, which described a cooperative method where the site can enable indexing of dynamic content by Google.  Basically, Google interprets hash-bang '#!' hash fragments, where it finds them while scanning html, as a request for special treatment; the special treatment is to rephrase the URL replacing the fragment with a specially named GET parameter carrying the fragment value.  The site can then generate, server-side, a version of the page containing the content, for Google to index.  In Google's search listings, the fragment is again a hash fragment.

Rdbhost now provides the server-side component of the Ajax indexing process, for any site hosted on Rdbhost.  Use the '#!' prefix for your internal links, Google will request them with the special '_escaped_fragment_' parameter, and Rdbhost will use a server-side 'headless' browser to generate a content-full version of your page for Google.

Ease of Implementation

Rdbhost intends to make easy web-sites as easy as possible; now we make Ajax-content index able with no effort to you.  The only requirement of you is that you use the '#!' notation for your internal links.

See for Yourself

You can verify this yourself, by entering such a URL in the browser.  If the link is 'http://demos.noservercoding.com/hashtest.html#showmore', the special request URL would be 'http://demos.noservercoding.com/hashtest.html?_escaped_fragment_=showmore', and requesting that URL with your browser will provide a pre-rendered page.  Use 'show source' with each version of the URL will show the difference.

Links:
  Ajax Client-Side Version
  Server-Side Version

Easier Hosting

There are a couple of new features to tell you about this week;  One will be discussed here today, and the other will be in another post in a couple of days.

Rdbhost has always been flexible about where you can host your website's static files:  You can host them on any static server, including free servers, on Localhost for development, and even on GitHub or Assembla for git-commit ease of deployment.  Your SQL database account can be accessed using the JavaScript/jQuery module, from any host.

Static Hosting

You can also host your static content here at Rdbhost.  While we do not make file space available to our customers (we are a Database hosting service!), we do provide a facility to put file contents into database blobs, and those blobs can be retrieved using natural short URLs.  Your static content can be hosted in static 'pseudo-file's, and the dynamic content in SQL database tables.

SFTP Server

The new feature makes these pseudo files easier to manage.  We now support an SFTP server, for managing your static content.   This server is on port 24 at www.rdbhost.com, and functions like any other SFTP server, except that the files you manipulate are actually just blobs in the pseudofiles table.  The server also uses 'magic' to determine the correct mime-type for the content you store; when you retrieve the blobs from a browser or other web client, the stored mime-type is sent as the Content-Type header.


The SFTP server uses account and password logins only; the login is your Super role-name (such as 's0000000907'), and the corresponding authcode.  These are available on your Roles page.  To create the 'lookup.pseudofiles' table to start, enter a domain alias from the profile page;  You should have a domain registered for the new site.  Setting the domain alias tells Rdbhost.com that you intend to serve static content under that domain.

Quirks

In minor ways, this SFTP server is different than others you have use.

The server will maintain empty directories during an SFTP session, but they are forgotten at the end of the session.  Similarly, file attributes can be changed, but the changes are forgotten at the end of the session; the 'files' are always readable to the public, and writable by the Super account only (unless you have granted specific privileges otherwise).

Testing

The SFTP client has been tested with the following clients:
   WinSCP 5.1.2
   Expandrive 2.4.0
   CoreFTP LE 2.2
   BitKinex 3.2.3


The RdbAdmin utility is an alternative way to enter data into the psuedofiles table.

See User Domains
       RdbAdmin Video
       SFTP Server