The lookup schema and 'borrowed' privilege

In an earlier post, I discussed the need Rdbhost has for a sudo type function, where a lower privileged role can 'borrow' greater privilege for specific limited functionality.

What has been implemented (read on) can be used to that effect, but is not implemented quite that way. Instead, we created additional roles (with and without authentication) that are restricted to running predefined queries only. You can do most querying using a low privilege role (typically the Reader role), and then adopt one of these new roles to do predefined operations that require higher privilege. Because these roles are restricted to certain queries, they can be granted greater PostgreSQL privileges safely.

The two roles are Auth and Public, written like 'a0000000878' and 'p0000000878', incorporating the account id. Auth requires an authcode, and Public does not. They are both restricted, in that either one can only run queries from the lookup.queries table. The roles can be created from the /mbr/role_manager page, and when you create one, the lookup schema and the queries table are both created for you. Add records to them using your Super role; the Rdbadmin utility can be useful here.

Queries in the lookup.queries table are invoked just like free-form queries, except that a 'kw' parameter is provided in lieu of a 'q' parameter. The 'kw' parameter value has the tag name for the desired query. If there are any substitution tokens in the looked-up query, there must be a corresponding number of 'arg###' parameters.

There are now four roles defined for each account. They are Super, Reader, Auth, and Public. The first two can run arbitrary free-form queries against the database, in addition to the predefined lookup queries. Typically, the Reader role would have very restrictive permissions to protect the database, and the Super role would only be used by the account owner. The Auth and Super roles are authenticated, requiring an authcode to be submitted with the request. The Reader and Public roles are not authenticated.

An example of a lookup query, taken from www.Freshfaves.com, is:

tag: update

query: UPDATE faves f SET f.link = %s
   WHERE f.id = %s
     AND f.acctid IN (SELECT id FROM accounts WHERE key = %s)

The role used, Public, has privilege to UPDATE the faves table. But because the Public role can only execute queries from the lookup.queries table, the only updating the user can do is the query above, and that only changes a record if the key value matches. That is, the only records updateable are those for the account with the provided key. The table is safe from malicious or accidental changes to other users' accounts. The query above is invoked using a a javascript code sequence like the following (the example uses jQuery):

var dbUrl = 'http://www.rdbhost.com/db/p0000000878?callback=?';
$.getJSON(dbUrl,
      {kw:'update', arg000:link, arg001:id, arg002:key},
      function(d) {
        alert('account updated');
   };
);

The Freshfaves application just predefines all queries, but it could have defined the Reader role and done free-form queries with it.

var dbUrl = 'http://www.rdbhost.com/db/p0000000878?callback=?';
$.getJSON(dbUrl,
     {q:'SELECT * FROM faves WHERE id = %s', arg000:faveid},
     function(d) {
       // code here to handle data retrieved
   };
);

Between free-form queries on one unprivileged role, and predefined queries only on the other role, an application can manipulate databases as necessary without embedding any role authentication into the javascript.

Related Reading

Rdbhost Roles page

Rdbhost Lookup page

The FreshFaves website

FreshFaves source code .zip

Postgresql upgrade

The Postgresql server behind Rdbhost has been upgraded to the latest 8.3 maintenance release, version 8.3.8.

Freshfaves: perishable bookmarks

Fresh Faves

Freshfaves is an online bookmarking tool that features perishable bookmarks. Add pages to your bookmark list using a bookmarklet in your favorites/bookmarks list. The bookmarks are kept there for following later, and when a bookmark goes 30 days without being clicked, it gets dropped from the bookmarks page.

Hence, a clutter-free bookmark list. It is useful for keeping bookmarks that are of only temporary use, or are of uncertain usefulness.

Bookmarks of temporary value include shopping list type bookmarks, tracking a product option until you decide where to buy; once the purchase is done, the bookmarks lose their usefulness.

If you find a page is of uncertain value, you can bookmark it here; if you never find time to follow up, or if it proves on the first couple visits to not be worth keeping, it will go unvisited and drop off the list in time. If such a link does prove to be of value, we make it one-click easy to copy it to your accounts on other (more permanent) bookmarking sites. Of course, we also provide a delete function to remove it immediately, if you so choose.

There are no login accounts or passwords; the authentication information is in the bookmarklet itself. We do make it easy to email that bookmarklet to yourself (for safe-keeping) or a friend (for sharing).

Rdbhost

This service is implemented using Rdbhost, of course. I used the JSONP data format, as it allows making cross-site requests (in this case, from www.freshfaves.com to www.rdbhost.com). It does not permit POST mode requests, but for this low-security application, that is ok. I am working on an AJAX-Rdbhost toolkit to make cross-site requests straightforward in any mode, and that will be announced here in its time.

The source code is available in a zip file, here.

Sudo and SQL

Rdbhost databases can be queried from Javascript applications. Aside from the cross-site-scripting safeties, there is one major design obstacle to hosting your javascript applications data here.

PostgreSQL provides the ability to setup multiple roles, but generally you do not want to create a new role for each web user. Most web users are anonymous, in fact, and could not be correlated with a particular role. Generally, for a web application, you would set up a small number of PostgreSQL roles, and then provide additional programming layers to authenticate users and allow each only appropriate operations on the database.

Unfortunately, for a Javascript application hosted on Rdbhost, you would have to embed the Postgres role password (authcode) in the javascript, in order to provide it to the server with each SQL request. Even if you did not distribute it with the javascript app, but retrieved it dynamically conditional on the user entering a passphrase, say, it would still be in the javascript temporarily. If the client were provided the authcode temporarily, for some legitimate purpose (say, to add themself to, or remove themself from, a members table), you could not prevent them from submitting that authcode with different SQL later, perhaps malicious SQL. If you were to provide them a role and authcode to remove their records from a table, you could not prevent them from removing others records as well.

Now, there are useful javascript apps that can be written to work safely under these constraints. For example a blog application could allow anonymous users to use a role that permits INSERT and SELECT on the comments table. The blogger himself or herself would use a super role that permits everything, but the anonymous web user would be able to add comments. That much can be controlled via PostgreSQL roles privileges. Many apps, though, need a more refined security model.

Unix and its kin have long had a tool called sudo. Sudo allows a user to run specific commands using the privileges of another user. The specific commands to be permitted must be listed, in advance, by a user with greater privilege.

Rdbhost needs a feature akin to sudo for use by javascript programmers. The programmer can configure the necessary queries that can be run, what role (privileges) they will run as, and who can be permitted to run them. Then the javascript app can use a low privilege role for most purposes, and then use the sudoish feature to do higher privilege operations that are predefined to operate safely. A javascript programmer can write a javascript application that is widely distributed and widely studied, and use a hosted shared database for application data, without exposing that database to malice.

Comments welcome.

Rdbhdb 0.9.1

The DB API module, Rdbhdb, has been re-released. Only two changes in this version:

1. https (TLS) is used by default.
2. A bug causing incompatility with Python 2.4 was fixed. 2.4 does not have an 'any' builtin, so the module provisionally provides one.

Available here or from Pypi.

http://www.rdbhost.com/downloads/rdbhdb-0.9.1.zip
http://pypi.python.org/pypi/rdbhdb/0.9.1