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";
}

Example: a more powerful query filter

Here is a nifty example contributed by a user. It does the same thing as the filter "Filter out discontinued products" in the basic demo, but in a more reliable way.

let pkt = context.packet;

pkt.setQuery(pkt.getQuery().replace(

    // Look after the table name to see if we have an alias set, or if instead it is followed by a comma, semicolon, or other normal sql keyword

    /\bgallium_demo\.products\b(?=(\s*((\,|\;|join\b|inner\b|left\b|right\b|full\b|where\b|group\b|limit\b|offset\b|order\b|union\b|intersect\b|except\b)|--|\/\*)|\s+(as\s+)?(\S+)))/ig,

    function(m0, m1, m2, m3, m4, m5) {

        if (m5! == undefined) { // We found an alias already, no need to add one

            return "(select * from gallium_demo.products where status is null or status <> 'discontinued' )";

        } 

        else {   // No alias found - add one since it's needed for subquery - name should be unique

            return "(select * from gallium_demo.products where status is null or status <> 'discontinued' ) uniqueprodsalias12345";

        } 

    }

));

log.debug("Restricting query on products: " + pkt.getQuery());