Monday, November 25, 2013

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.



Tuesday, November 19, 2013

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