Chapter 25. Performance

Adding Veil2 to your database introduces 2 specific overheads to your database performance.

  1. the overhead of session management;
  2. the overhead of privilege testing in your relations.

25.1. Data For Evaluating Performance

In order to get a feel for the overheads associated with Veil2, we need some realistic volumes of data for roles, privileges, and accessors with their associated mappings. The script, demo_bulk_data.sql, which can be found as described here provides a good starting point for this.

After creating a demo database (as described here) you can install this data using psql, eg:

marc:veil2$ psql -d vpd -f /usr/share/postgresql/12/veil2/demo_bulk_data.sql 
      

This will create around 8000 accessors, around 300 roles, and around 2000 privileges, which should be enough to exercise the privilege testing and session management functionality.

25.2. Session Management Overhead

The two biggest overheads in session management are the use of bcrypt() for authentication (which is CPU-intensive by intent), and the loading of session privileges. In the following evaluation, bcrypt() is avoided.

Session privileges once loaded (from veil2.session_privileges_v are cached (in veil2.accessor_privileges_cache). This means that subsequent loading of privileges should be considerably faster than the initial one for each accessor.

A simple performance checking script perf.sql is provided in the same directory as the bulk data loading script. Assuming the same directory as the example above you would run it as follows:

marc:veil2$ psql -d vpd -f /usr/share/postgresql/12/veil2/perf.sql 
Creating sessions: elapsed = 21 milliseconds.
Opening sessions: elapsed = 51 milliseconds.
Re-opening sessions: elapsed = 60 milliseconds.
marc:veil2$ 
      

The script creates 6 sessions, opens each of them, and then re-opens them. The elapsed times shown are cumulative, in this case giving the time to open and initialize the sessions as being approximately 30 milliseconds (5 milliseconds each), with the session reloads taking about 9 milliseconds (1.5 milliseconds each).

This is on an aging desktop PC with the following CPU spec:

vendor_id	: GenuineIntel
cpu family	: 6
model		: 58
model name	: Intel(R) Core(TM) i5-3570K CPU @ 3.40GHz
stepping	: 9
microcode	: 0xc
cpu MHz		: 3411.376
cache size	: 6144 KB
      

It may be possible to further improve the performance by re-implementing the session management functions in C, though initial experiments yielded only minor gains which, given the loss in flexibility resulting from a C implementation, were not felt to be worthwhile.

Until anyone claims otherwise, the author is going to claim that this is fast enough.

25.3. Privilege Testing Overhead

For testing the overhead of running privilege tests, you should load the performance evaluation data as described above.

The script perf2.sql sets up 3 identical tables for performance testing, and runs some simple queries. The tables are:

Here is the transcript of a performance testing run:

marc:veil2$ psql -d vpd -f  /usr/share/postgresql/12/veil2/perf2.sql
Setting up tables...
connecting as Alice...
31|7argrXo++w024hrMnrRwUAnZwvb4i/DN8ZFbF0MMjcg=||t|
Time: 24.037 ms
running tests...
...on x (3 times)...
1995
Time: 0.581 ms
1995
Time: 0.274 ms
1995
Time: 0.261 ms
...on y (3 times)...
1995
Time: 0.576 ms
1995
Time: 0.316 ms
1995
Time: 0.291 ms
result_counts (to keep us honest):
0|0
Time: 0.117 ms
...on z (3 times)...
1995
Time: 0.826 ms
1995
Time: 0.383 ms
1995
Time: 0.365 ms
result_counts again:
0|5985
Time: 0.140 ms
marc:veil2$ 
      

What this shows, for table z, is that the first query experiences some small overhead (approx 0.5 millisecs), and that subsequent queries have little more overhead than table y, which has the smallest possible overhead for a table with a security policy. Based on all of this, the author is going to claim that this is pretty damn fast.

The reason for the small initial overhead on the first test against table Z is that on the first call to a privilege testing function, the session's privileges must be loaded into session memory.

Since these results look too good to be true, we perform a query of veil2.result_counts() which shows the number of false and true results from the privilege testing functions. This should be seen as proof that the privilege tests were actually performed. Obviously, you can repeat this yourself.

25.3.1. Other Privilege Test Functions

The tests above only test veil2.i_have_global_priv(), which is the simplest of the privilege testing functions. While this may seem to be a cheat, testing privileges in other scopes is just as fast as testing in the global scope.

That said, those functions that check for privileges in superior scopes, run a query to discover the appropriate superior scopes, and so incur more overhead. Experience suggests that this will still be easily dwarfed by any fetches from uncached rows.

If your application uses a lot of superior scope tests, or those queries are used on large numbers of records, you should run your own tests to ensure that performance is adequate. If not you may need to perform some denormalizations to record the appropriate superior scope in each row.

Even with this caveat though, the author is still going to claim that it's fast.

25.4. In Conclusion

Oh my, it's fast!