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.