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 the lookup.preauth_queries table. This can be accomplished manually, or by an automated training process.

Lookup.Preauth_queries

Queries are looked up, in the preauth_queries table, by the actual text of the query, if available, or by the keyword. If the requested query is found and permitted for the requesting role, it is executed; if not, an error is returned.

Queries can be entered into the table by straightforward SQL INSERT statements, but there is a much easier way, almost zero effort. Register your workstation, by its IP address, as a trainer, and you can enter queries into lookup.preauth_queries by simply submitting them for execution. Queries received from a registered training client are assumed to be valid and entered into the preauth_queries table.

The web site's training page has buttons to quickly clear the preauth_queries table in two clicks.

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.

Table Fields

Generally, you would want to use the training mechanism to populate this table, but the fields are described here, in case you wish to manipulate them directly.

The five fields are:

See also:

The Roles page has more to say about PostgreSQL roles.