©2008-2017 David V Keeney All rights reserved.
When the account is created, the super role, named s<id> (for example 's000000767'). This account has privileges to create tables and views, and it then owns those resources. This account can grant privileges to the other roles, after each has been created from the Role Manager page. In fact, the other roles are created with no privileges, and only gain them by GRANT from the Super role.
The Reader role can execute arbitrary SQL queries, in addition to lookup queries. This role has no password, so set the role privileges carefully: SELECT is generally safe. If you are using RdbHost as the database for a web application hosted elsewhere (GAE, maybe), then you can leave this and other roles disabled.
The Preauth role, like Auth, can only execute predefined lookup queries. This role has no authcode, so if it is enabled, anybody can use it. The predefined queries and the role privileges should be defined carefully and in combination.
The Auth role can only execute predefined lookup queries; SQL queries in the request will return an error message. Because the only queries executed with this role are those you define, the Postgresql privileges are less critical, and can be set more generously. For example, if the only predefined queries that delete records filter the records to delete, then allowing the DELETE privilege can be safe. This role has an authcode, so you can restrict its usage to select users or scripts.
You manage the security of your account using two mechanisms: the first is Postgresql's own role privileges, where you control which roles can read, add, modify or delete records for each table; the second is the query lookup system.
Postgresql role privileges are important, and should be set, but are sometimes too granular for an application's purposes. You may wish to allow a role to delete records from a specific table, but only the records belonging to a specific user. Postgresql role privileges cannot provide that restriction unaided.
The query lookup system provides a more precise access control, allowing you to define exactly what queries a role can execute. You could define a multitable delete query, for example, which only deletes records satisfying arbitrary constraints, and include in those constraints a match on a password field. Postgresql would allow deletions from that table by that role, but the lookup table restriction means the only query available to do the deletion only allows specific records to be deleted.
You can use multiple roles in the same script, so you could set safe limits on the Reader role, and use it with free-form queries for most data retrieving. For those operations needing to alter the database, you can define a Preauth or Auth role (both are limited to lookups), and define appropriate queries in the lookup table to safely alter the table data.
|May use lookup.queries||Yes||Yes||Yes||Yes|
|May use free-form queries||Yes||No||No||Yes|
|May train lookup.preauth_queries||No||Yes||Yes||No|