Example: hide some customer records

Hiding certain rows in a result set can be very simple. For instance, if we simply want to hide rows from the customers table that are in Canada, we could simply define a query filter that modifies the queries:

let sql = context.packet.getQuery();
if (sql.includes(" where ")) {
context.packet.setQuery(sql + " and country not in ('CA')");
}
else {
context.packet.setQuery(sql + " where country not in ('CA')");
}

Another possibility is to hide the customer records when result sets are returned. This is a bit more expensive because we have to look at every row on its way to the database client, and remove it from the result set if it does not qualify. On the other hand, it is more flexible because it will cover most queries that return customer data.

The Postgres result set filter allows you to specify the user, the schema and table, and a simple predicate to hide the corresponding rows.

If this is not sufficiently flexible, you can add any conditions in the code.

But how do we know whether we should in fact hide Canadian customers? There are several possibilities:

1 - The database user determines what to do

This is of course the simplest scenario: if the user name is sufficient to determine what to do, then the filter practically writes itself. In the example above, we showed using a regular expression to distinguish which database users should trigger this filter: us_.+ means any user name that starts with "us_".

2 - You can look up what access the database user has

This is a common scenario, in which the database user can be looked up in some external resources (maybe an LDAP directory or some other database) to determine what type of access that user has.

This scenario assumes that the database user (which is always available in filter logic as context.connectionContext.userName) is enough to determine what behavior the filter should adopt. This may not be the case if your database client uses connection pooling, for instance.

3 - There is some information in the interactions between database client and server that tells us what to do

It's often possible to have some logic that observes the client's requests and responses and deduces what type of user it is. It can be from specific parameterized queries, for instance, at the beginning of an application's database interaction. In that case, it is often possible to extract those parameters from the queries or from the responses to gather the information required to determine what behavior the filter(s) should have.

4 - There is an explicit declaration from the client

Because Gallium Data receives all requests from the client, it can decide what to do with them. If your database client can be modified to send some "magic command", you can create a query filter that looks specifically for that magic string, e.g. "User is allowed access to countries X,Y,Z", perhaps in a comment in an otherwise normal request, and parse out the information it needs from that request remove it from the request. The database server will never see it, and your database client now has communicated relevant information to your Gallium Data logic, which can adjust its behavior accordingly.

For instance, you could have the client issue a query like the following (this is Node for Mongo, but the technique is universal):

const companies = db.collection('companies');
companies.find({name: 'Zoho', meta__countries:'CA,MX,US'}).toArray(function(err, docs){
console.log(docs);
});

You could then have a filter (in this case a message request filter for Mongo) looking for that information with a JSON expression parameter of:

filter.meta__countries != null

The code for the filter can then extract that information from the query and remove it:

let filter = context.packet.getSection(0).getBody().filter;

log.debug("Extract information from request: " + filter.meta__countries);

let metaCountries = filter.meta__countries;

delete filter.meta__countries;

You would typically store that information in the connection context for later use.