Pre-authorization of Queries
Some roles (Preauth and Auth) are restricted to running only queries that have been pre-authorized. The pre-authorization has the form of entering the query in one of two tables on the server. One of these is the lookup.queries table, and the other is the lookup.preauth_queries table. The lookup.preauth_queries table is described on its own page, and is referenced by the query text, rather than by a keyword.
Lookup.Queries
The lookup requests are made in the same fashion as other queries, except that the 'q' parameter is replaced by a 'kw' parameter. The 'kw' parameter is the key that looks up the desired query in the lookup.queries table. If the retrieved query has '%s' substitution tokens, the request should have sufficient numbered arguments (arg000, arg001...) to complete it.
Schema Lookup
To avoid name collisions with tables you might be porting from elsewhere, we put the lookup tables in their own schema, called lookup. This schema, and the queries tables, are created whenever you enable the Auth or Preauth roles.
Queries
This table is created with the lookup schema.
Your Super role has INSERT and UPDATE permissions on the lookup.queries table, and you can use the SQL_form, or the Rdbadmin utility to add records.
The five fields are:
- tag
- The keyword by which the query is found. This keyword will be provided in the request as the 'kw' parameter.
- query
- The SQL query itself. It may optionally include interpolation tokens '%s', cookie values '%{cookiename}' or cgi-vars %[cgi-vars].
- format
- The default format for returning the result. This is optional, and can be overridden by a 'format' parameter in the request.
- authcheck
- If this boolean field is true, the users ip address will be temporarily blacklisted after a minimum number of consecutive requests without results. Use this to protect queries that make up your authentication system.
- nopermit
- This varchar (string) field can contain any combination of 'a','p', and 'r', representing the 'Auth', 'Preath', and 'Reader' roles. If the requesting role matches, the query will not be retrieved.
See also:
A more general overview of how to query is in How It Works, and there are more specific pages for Javascript and Python. Questions and Answers may be helpful also. The Roles page has more to say about Postgresql roles.