PostgreSQL & MySQL Policies

These methods exist on the policy.sql object as seen in the following example:

const PG = 'postgresql://pg-user@pghost:5432/db_name';
const MY = 'mysql://my-user@myhost:3306/db_name';
// ...
(policy) => {
  policy.sql.allowConnect(PG);
  policy.sql.allowQuery(PG, 'SELECT * FROM users WHERE name = $1::text');

  policy.sql.allowConnect(MY);
  policy.sql.allowQuery(MY, 'INSERT INTO users (name) VALUES (?)');
}

intrinsic.virtualizeLib('mysql')

Note that to enable MySQL we need to first call .virtualizeLib('mysql'). Intrinsic supports both the mysql and mysql2 npm modules in this manner.

// Intrinsic for Lambda
module.exports = new IntrinsicLambda()
  .virtualizeLib('mysql')
  .virtualizeLib('mysql2')
  // ...
  .run();

// Intrinsic for Node.js
intrinsic(__filename)
  .virtualizeLib('mysql')
  .virtualizeLib('mysql2')
  // ...
  .run();

Calling this method allows your application to load the mysql or mysql2 module and enable policies for it. Otherwise, the connection would be treated as an ordinary TCP connection without any inspection of the data being sent.

You will need to call this if you either see the following error, or if you otherwise want to enable MySQL connections to be established from your Node.js application:

[INTRINSIC] OutboundHostAndPortNetPolicyViolation: POLICY_VIOLATION 
  sb: "fallback" | tcp://myhost:3306 not in outbound net whitelist

If you need to determine which module your application depends on, such as when it is a transient dependency, you can run the following commands:

$ npm ls mysql
$ npm ls mysql2

Note that these policies are applicable not only if you are directly using the low-level mysql or mysql2 modules, but also if you are making use of higher level libraries like knex or sequelize.

intrinsic.virtualizeLib('pg')

Note that to enable PostgreSQL we need to first call .virtualizeLib('pg').

// Intrinsic for Lambda
module.exports = new IntrinsicLambda()
  .virtualizeLib('pg')
  // ...
  .run();

// Intrinsic for Node.js
intrinsic(__filename)
  .virtualizeLib('pg')
  // ...
  .run();

Calling this method allows your application to load the pg module and enable our policies for it. Otherwise, the connection would be treated as an ordinary TCP connection without any inspection of the data being sent.

You will need to call this if you either see the following error, or if you otherwise want to enable PostgreSQL connections to be established from your Node.js application:

[INTRINSIC] OutboundHostAndPortNetPolicyViolation: POLICY_VIOLATION 
  sb: "fallback" | tcp://pghost:5432 not in outbound net whitelist

Note that these policies are applicable not only if you are directly using the low-level pg module, but also if you are making use of higher level libraries like knex or sequelize.

policy.sql.allowConnect(connection)

The allowConnect() method allows your application to make a connection to a database instance as described by the connection string. This string needs to contain the hostname and a database, as well as a port if it differs from the default 5432 value used by pg or the default 3306 value used by mysql.

The sql policy determines which database is being whitelisted based on the protocol value. So the value mysql:// will whitelist connections used by the mysql module and the value postgresql:// will be used by pg.

const PG = 'postgresql://pg.example.org/database';
// ...
policy.sql.allowConnect(PG);

Chances are your application will establish a connection when it first runs, not when an incoming request is made. For this reason it is usually most common to call the pg.allowConnect() method from within the allRoutes policy.

This policy is required if you see the following violation in your logs:

SqlConnectionPolicyViolation: POLICY_VIOLATION sb: "fallback"
  | connection "postgresql://user@localhost:5432/mydb"
  not in outbound SQL connection whitelist

policy.sql.allowQuery(connection, query)

The allowQuery() method allows your application to run a query. It requires that the same connection string which was used with allowConnect() is passed in as the first argument. The second argument is the query to be executed.

Note: For security reasons the SQL whitelist currently does not support the glob syntax used by other policies such as HTTP and filesystem access.

This policy is required if you see the following violation in your logs:

[INTRINSIC] SqlQueryPolicyViolation: POLICY_VIOLATION sb: "fallback"
  | query "SELECT * FROM users WHERE name = $1::text" via connection
  "postgresql://user@localhost:5432/mydb" not in outbound SQL query whitelist

Parameterized Queries

Both the pg and mysql libraries offer support for parameterized queries. These are special placeholder values in the query string which are safely replaced by the underlying library to prevent SQL injections. Here are two examples:

const my = require('mysql').createConnection({...});
my.query('INSERT INTO users (name) VALUES (?)', ['Steve'], cb);

const pg = new require('pg').Client({...});
pg.query('INSERT INTO users (name) VALUES ($1::text)', ['Steve'], cb);

In these situations the query to be whitelist is exactly the string being passed to the .query() methods.

String Concatenation (Anti-Pattern)

It is also possible to generate SQL queries by manually concatenating strings together, bypassing the parameterized query functionality. This approach is not only dangerous from a security perspective but also incompatible with SQL policies. Consider the following example:

const my = require('mysql').createConnection({...});
// Warning: Dangerous Anti-Pattern!
my.query('INSERT INTO users (name) VALUES ("' + req.params.user + '")', cb);

As far as security goes, this example has enabled a SQL injection. Never generate queries in this manner in an application, with our without Intrinsic running. The reason this example doesn't work well with SQL policies is because you would need to create a new policy for every possible user name parameter.

The reason we don't support the glob syntax for queries is because it's difficult to designate what * or ** would mean. The glob syntax is based on using forward slashes as delimiters which doesn't apply in the case of SQL. To properly support dynamic queries in this manner we would need to use a custom SQL parser specially built for each database system. Such a parser would likely differ from the underlying DBMS implementation and lead to security vulnerabilities.

ORM Queries

Often times an application will make use of an ORM to generate SQL queries. In those cases it's not as straightforward to find the actual SQL query. However, by using monitor mode, the underlying SQL statements will be reported when a violation occurs. This will allow you to get the queries you need to add to the whitelist.

Here's an example of how we might use the Waterline ORM:

User.findOne({
  username: req.param('userId')
}, cb);

And here is the resulting PostgreSQL query it has generated:

SELECT "users"."id", "users"."createdAt", "users"."updatedAt",
  "users"."username", "users"."password", "users"."email" FROM "public"."users"
  AS "users"  WHERE LOWER("users"."username") = $1  LIMIT 1

The above query can then be used in your policy file.

Note: Most ORMs allow you to pass in dynamically generated objects to a query. These objects then generate dynamic queries. It is important to test the different permutations of queries when testing your application.

As an example of this, consider the following code using a fictional ORM tool:

const query = {
  username: req.query.username || 'default',
  is_admin: req.query.is_admin ? true : undefined
};
orm.find('user', query, cb);

In this example the username property is always provided to the ORM. However, the is_admin is sometimes provided to the ORM. This means there are most likely two different queries that will be generated:

SELECT * FROM user WHERE username = $1;
SELECT * FROM user WHERE username = $1 AND is_admin = true;

In these situations you need to whitelist both queries. Since whitelists are most often generated while doing local testing you will need to ensure that you've tested both code paths. Otherwise your application may behave improperly in production.