PGXN

Generally, with a well-behaved application and well-implemented database design, Veil2 adds little performance overhead. This is because it, mostly, only applies permission checks on the records that the user sees. If the user is only going to see a few records, the overhead of checking those few records is going to be small.

However, if you make Veil2 do your query filtering for you instead of writing properly crafted where-clauses, performance is going to suck. Consider the following queries:

select stuff
  from parties
 where org_id = 20
   and party_name like '%Bob%';

select stuff
 from parties
where party_name like '%Bob%';
      

Assume that we are in a Veil2-protected database and that we have select access only to org_id 4.

Both queries will return the same number of rows. But in a database with lots of parties, the second query will be slow.

In the second query, a full-table scan will be performed, and many Bobs from other orgs might be returned, only to be discarded by the Veil2-based security policy.

The first query will, most likely, use an index and return a much smaller set of records, which will then be filtered looking for Bob, and finally checked against the security policy. In this case the security policy has only had to check records that the user was allowed to see anyway.

For most applications, any time that a security policy blocks something, the application has done something wrong. You should consider this a bug in the application. Although no harm will be done, this is an indication of one of two things:

In either case, it makes sense to note the occurrence and investigate. Note that in a reporting environment where ad-hoc reports can be run, this may prove less useful.

Adding such a check is pretty straightforward. Consider this policy:

create policy wibble__select
    on wibble
   for select
 using (veil2.i_have_global_priv(42));
      

To add logging on error, you can simply add a final or to the policy with a function call to a logging function (that always returns false), eg:

create policy wibble__select
    on wibble
   for select
   using (veil2.i_have_global_priv(42) or
          log_unwanted_access('wibble', <concatenation of key fields>));
      

Your logging function needs to take enough parameters to identify the record to which access has been attempted, must return false, and should identify the accessor by querying from veil2_session_context. Performance should not be a great concern as you expect the function to rarely be called.

If you have automated tests, you should run them against both secured and unsecured databases. If the tests pass in both instances, then your application and the security implementation are in harmony, and you should be very pleased with yourselves. Encourage your employer to provide handsome bonuses.

If you find that your security policies require joins to other tables (possibly through function calls), then your security system's performance may suffer. Consider adding scope_id columns to some tables to improve the performance of security tests. You might also consider doing this just to simplify those tests.

You may find that multi-table queries encounter performance issues as the security policy has to be applied for some tables on more rows than are ultimately returned by the query. If you cannot find a way to improve the query by rewriting, then consider replacing it with a secured view instead.

The secured view would implement the joins and whatever filtering can be written in to the view, and would then apply a security policy only to the resulting rows, rather than to intermediate rows that are discarded by subsequent joins in the original query.

While you are integrating Veil2 with your application and continuing to develop it, you are likely to create and drop your own database objects as they change. Be aware that through the veil2.accessors table and veil2.superior_scopes view, veil2 (and possibly other objects) is tightly coupled with your application schema.

This may mean that it is impossible to to drop some of your database objects because Veil2 database objects depend on them. Using the cascade option to drop will allow the drop to proceed but will result in dependent Veil2 objects being dropped as well. Recovering from this can be a tedious and potentially error-prone process.

We recommend avoiding the use of drop... cascade and instead scripting a drop and rebuild process that will explicitly deal with each dependent object. By making each drop explicit you can ensure that you correctly manage the re-building of those objects.