Query filter - Postgres

The query filter is invoked whenever the client send a piece of SQL to be executed, either immediately or later. This includes two packet types:

Parse : for prepared statements

Query : for immediate execution

A typical query filter would be used to reject certain queries, or alter certain queries.

Parameters

  • User pattern: optional. A string which must match the current database user name. This is for an exact match only, including case, unless it starts with regex: in which case this is treated as a Java regular expression, or REGEX: for a strict regular expression.

  • Query pattern: optional. A string which must match the current SQL command. This is for an exact match only, including case, unless it starts with regex: in which case this is treated as a Java regular expression, or REGEX: for a strict regular expression.


Example: add limit

A simple example showing how to modify a query on the fly:

// Add a limit to a select on Customers, if not already present
var sql = context.packet.getQuery().trim();
if ( ! sql.startsWith("select ")) { // This could also be done with the Query pattern parameter
return;
}
var re = /\bCustomers\b/g;
if (sql.match(re)) {
// Does the query already end with "limit xxx" ?
var limitRe = /limit\s+\d+/g;
if ( ! sql.match(limitRe)) {
log.info("Adding limit to query: " + sql);
context.packet.setQuery(sql + " limit 10");
}
}

Example: reject certain queries

A simplistic example showing how to reject a specific delete

var sql = context.packet.getQuery().trim();
if ( ! sql.startsWith("delete from Customers")) {
return;
}
var re = /\bMozart\b/g;
if (sql.match(re)) {
context.result.success = false;
context.result.errorMessage = "You cannot delete Mozart";
}