The four roles on each Rdbhost account

Rdbhost provides up to four PostgreSQL roles for each database account.  Each account has exactly one database, and the roles are associated exclusively with the database.

NAMING


They are named uniformly, so that when a role, a database name, or an account id is received by the server, the other identifiers (roles, db names, account id) can be deduced.   The database name is the prefix 'db' followed by the account id (an integer) as a '0' left-padded 10 digit string.   The role names are 's', 'a', 'p', or 'r' followed by the same '0' padded 10 digit string.

The 's' role (Super) is created with the database, and owns every object in the database, including the 'lookup' schema, if present.  The other roles are optional, created at your request via the '/mbr/role_manager' page, and their privileges are whatever you set, via SQL 'GRANT' and 'REVOKE' commands.  Below we present a guideline for how you should set privileges for each.

There are important differences between the roles, though, aside from the differing privileges you grant each.  Some are permitted to submit arbitrary queries, and some are limited to pre-authorized queries.  Let us look at each in turn.

ROLES

1. The 's'-role, as mentioned above, is the owner of everything in the database.  The 's' is a mnemonic for Super, as it is the do-anything 'super' role within the database.   An authcode must  be submitted with each query request, and that authcode is your security, to prevent unauthorized users from damaging database resources.  You do not need to create any privileges for this role, as its ownership of each resource permits any operation.
2. The 'a'-role, like the Super role, has an authcode, and that authcode must be provided with every request.  'a' is a mnemonic for Authenticated, as the authcode authenticates the requester to use this role.  This role is limited to preauthorized queries (that is, queries found in either the lookup.queries or the lookup.preauth_queries tables), so it can be restrained to only run safe queries.  The web site uses the name Auth to refer to this role.
3. The 'p' role is like the Auth role in that it only executes preauthorized queries, but is *not* authenticated.  Any query preauthorized for this role can be executed by any member of the public.  'p' is a mnemonic for Preauthorized.  (It could also be a mnemonic for Public, reflecting the unauthenticated quality, but don't confuse it with the PostgreSQL role 'public').  Securing this role is a combination of setting role privileges and prequalifying safe queries.  For example a blog site might allow APPEND privilege to this role for the 'comments' table, so that anonymous readers of the blog can comment, and the query saved to the lookup.preauth_queries table would be written to require the user IP address to be entered into the 'comments' table to facilitate anti-spam maintenance.  The web site uses the name Preauth to refer to this role.
4. Last but not least, the 'r'-role is also *not* authenticated, and allows free-form queries.  Your only security with this role is in setting PostgreSQL privileges carefully.   Generally, you would grant SELECT privilege on tables that anonymous users need to read, and no other privilege.  'r' is a mnemonic for Reader.

PRE-AUTHORIZED QUERIES

The Auth and Preauth roles may only execute queries that have been preauthorized by entering them into one of the lookup tables, lookup.queries or lookup.preauth_queries.   The first is intended for manual entry, and the second is intended for automated entry.

Automated entry is done by the training mechanism.  See link below for more on that.

Manual entry into the lookup.queries is done by the Super role, using either the SQL_Form or the RdbAdmin tool.  Queries in the lookup.queries table must be accessed by their tag, not by the query string itself.  Use the 'kw' request parameter rather than 'q'.  Unfortunately, the Python DB API library (Rdbhdb) at this time does not support 'kw' lookups.


LINKS

• The roles
• Training
• Lookup.queries