JavaScript response filter - Vertica

The JavaScript request filter is the most flexible of all response filters. It can potentially be called for any packet type, and can modify traffic in any way it sees fit.

Parameters

• Query pattern: optional. If specified, this filter will be invoked only if the current SQL matches this. Can be a string or a regular expression.

• Packet types : optional. If specified, a comma-separated list of packet types for which this filter should be called, for instance:  DataRow,ErrorResponse. Spaces are ignored.

• Client IPs: optional. If specified, a comma-separated (or line break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses.

• Users: optional. If specified, a comma-separated (or line break separated) list of user names or regular expressions for user names.

Example: change a column name in a result set

We can intercept packets of type RowDescription and change them so that a column will show up differently.

The JavaScript response filter will have the parameter:

Packet types: RowDescription

The filter's code might then look like:

let fieldDesc = context.packet.getFieldByName("store_region");

if (fieldDesc !== null) {

    fieldDesc.fieldName = "Store location";

}

Obviously, you must ensure that this type of logic does not break the database clients.

Example: record database errors

You can catch response packets of type ErrorResponse and log them to a file or some other system to keep track of certain problems:

let FileWriter = Java.type("java.io.FileWriter");
let BufferedWriter = Java.type("java.io.BufferedWriter");
let outFile = new FileWriter("/var/log/ErrorTrace.log", true);
let out = new BufferedWriter(outFile);
out.write("" + new Date() + " - Error packet received: " + context.packet + "\n");
out.close();

Note that, if performance is critical, it may be a good idea to store the out object in the connection context to avoid opening and closing it all the time:

let out = context.connectionContext.ErrorLogFilter_out;
if ( ! out) {
    let FileWriter = Java.type("java.io.FileWriter");
    let outFile = new FileWriter("/var/log/ErrorTrace.log", true);
    let BufferedWriter = Java.type("java.io.BufferedWriter");
    out = new BufferedWriter(outFile);
    context.connectionContext.ErrorLogFilter_out = out;
}
out.write("" + new Date() + " - Error packet received: " + context.packet + "\n");
out.flush();

Example: track who accesses what data

A filter can intercept packets of type RowDescription to keep track of what data is being accessed, and by whom. We'll assume we just want to send that information to a server that's listening for it. In real life, you might use a JSON message and HTTP, or some other API.

The JavaScript response filter will have the parameter:

Packet types: RowDescription

The filter's code might then look like:

// Send the row description to some server

const InetAddress = Java.type("java.net.InetAddress");

let serverAddress = InetAddress.getByName("myserver.acme.com");

const Socket = Java.type("java.net.Socket");

let sock = new Socket(serverAddress, 8181);

let msg = context.connectionContext.userName + " - " + context.packet.toLongString() + "\r\n";

let msgBytes = context.utils.getUTF8BytesForString(msg);

sock.getOutputStream().write(msgBytes);

sock.getOutputStream().flush();

// Read the response from the server

let responseBytes = context.utils.allocateByteArray(5000);

let numRead = sock.getInputStream().read(responseBytes);

let responseStr = context.utils.stringFromUTF8Bytes(responseBytes, 0, numRead);

log.info("Response: " + responseStr);

sock.close();

This is a rather inefficient example, since it opens and closes the socket every time. It would be much more efficient to keep the socket open and store it in the filter context or the project context -- see the previous example for how to do this.