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.