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