Building a Veil-based secure database

This section describes the steps necessary to secure a database using Veil. The steps are:

Determine your Policies

You must identify which security contexts exist for your application, and how privileges should be assigned to users in those contexts. You must also figure out how privileges, roles, and the assignment of roles to users are to be managed. You must identify each object that is to be protected by Veil, identify the security contexts applicable for that object, and determine the privileges that will apply to each object in each possible mode of use. Use the Veil demo application (The Veil Demo Application) as a guide.

For data access controls, typically you will want specific privileges for select, insert, update and delete on each table. You may also want separate admin privileges that allow you to grant those rights.

At the functional level, you will probably have an execute privilege for each callable function, and you will probably want similar privileges for individual applications and components of applications. Eg, to allow the user to execute the role_manager component of admintool, you would probably create a privilege called exec_admintool_roleman.

The hardest part of this is figuring out how you will securely manage these privileges. A useful, minimal policy is to not allow anyone to assign a role that they themselves have not been assigned.

Design Your Database-Level Security

Veil operates within the security provided by PostgreSQL. If you wish to use Veil to protect underlying tables, then those tables must not be directly accessible to the user. Also, the Veil functions themselves, as they provide privileged operations, must not be accessible to user accounts.

A sensible basic division of schema responsibilities would be as follows:

An "owner" user will own the underlying objects (tables, views, functions, etc) that are to be secured. Access to these objects will be granted only to "Veil". The "owner" user will connect only when the underlying objects are to be modified. No-one but a DBA will ever connect to this account, and generally, the password for this account should be disabled.
A "Veil" user will own all secured views and access functions (see Secured Views and Access Functions). Access to these objects will be granted to the "Accessor" user. Like the "owner" user, this user should not be directly used except by DBAs performing maintenance. It will also own the Veil API, ie this is the account where Veil itself will be installed. Direct access to Veil API functions should not be granted to other users. If access to a specific function is needed, it should be wrapped in a local function to which access may then be granted.
"Accessor" users are the primary point of contact. These must have no direct access to the underlying objects owned by owner. They will have access only to the secured views and access functions. All applications may connect to these user accounts.

Design Your Access Functions

Provide a high-level view of the workings of each access function. You will need this in order to figure out what session and shared variables you will need. The following is part of the design from the Veil demo application:

Access Functions are required for:
- Global context only (lookup-data, eg privileges, roles, etc)
- Personal and Global Context (personal data, persons, assignments, etc)
- Project and Global (projects, project_details)
- All 3 (assignments)

Determining privilege in Global Context:

User has priv X, if X is in role_privileges for any role R, that has
been assigned to the user.

Role privileges are essentially static so may be loaded into memory as a
shared variable.  When the user connects, the privileges associated with
their roles may be loaded into a session variable.

Shared initialisation code:
  role_privs ::= shared array of privilege bitmaps indexed by role.
  Populate role_privs with:
    select bitmap_array_setbit(role_privs, role_id, privilege_id)
    from   role_privileges;

Connection initialisation code:
  global_privs ::= session privileges bitmap
  Clear global_privs and then initialise with:
    select bitmap_union(global_privs, role_privs[role_id])
    from   person_roles
    where  person_id = connected_user;

i_have_global_priv(x):
  return bitmap_testbit(global_privs, x);

This gives us the basic structure of each function, and identifies what must be provided by session and system initialisation to support those functions. It also allows us to identify the overhead that Veil imposes.

In the case above, there is a connect-time overhead of one extra query to load the global_privs bitmap. This is probably a quite acceptable overhead as typically each user will have relatively few roles.

If the overhead of any of this seems too significant there are essentially 4 options:

Simplify the design.
Defer the overhead until it is absolutely necessary. This can be done with connection functions where we may be able to defer the overhead of loading relational context data until the time that we first need it.
Implement a caching solution (check out pgmemcache). Using an in-memory cache will save data set-up queries from having to be repeated. This is pretty complex though and may require you to write code in C.
Suffer the performance hit.

Implement the Initialisation Function

Proper initialisation of veil is critical. There are two ways to manage this. The traditional way is to write your own version of veil_init(doing_reset bool), replacing the supplied version. The newer, better, alternative is to register your own initialisation functions in the table veil.veil_init_fns, and have the standard veil_init(doing_reset bool), call them. If there are multiple initialisation functions, they are called in order of their priority values as specified in the table veil.veil_init_fns.

The newer approach has a number of advantages:

it fully supports the PostgreSQL extension mechanism, allowing extensions to be created and dropped;
it allows different security subsystems to have their own separate initialisation routines, allowing more modular code and better separation of responsibilities;
it is way cooler.

Initialisation functions veil_init(doing_reset bool) are critical elements. They will be called by automatically by Veil, when the first in-built Veil function is invoked. Initialisation functions are responsible for three distinct tasks:

Initialisation of session variables
Initialisation of shared variables
Re-initialisation of variables during reset

The boolean parameter to veil_init (which is passed to registered initialisation functions) will be false on initial session startup, and true when performing a reset (veil_perform_reset()).

Shared variables are created using share(name text). This returns a boolean result describing whether the variable already existed. If so, and we are not performing a reset, the current session need not initialise it.

Session variables are simply created by using them. It is worth creating and initialising all session variables to "fix" their data types. This will prevent other functions from misusing them.

If the boolean parameter to an initialisation fuction is true, then we are performing a memory reset, and all shared variables should be re-initialised. A memory reset will be performed whenever underlying, essentially static, data has been modified. For example, when new privileges have been added, we must rebuild all privilege bitmaps to accommodate the new values.

Implement the Connection Functions

The connection functions have to authenticate the connecting user, and then initialise the user's session.

Authentication should use a secure process in which no plaintext passwords are ever sent across the wire. Veil does not provide authentication services. For your security needs you should probably check out pgcrypto.

Initialising a user session is generally a matter of initialising bitmaps that describe the user's base privileges, and may also involve setting up bitmap hashes of their relational privileges. Take a look at the demo (The Veil Demo Application) for a working example of this.

Implement the Access Functions

Access functions provide the low-level access controls to individual records. As such, their performance is critical. It is generally better to make the connection functions to more work, and the access functions less. Bear in mind that if you perform a query that returns 10,000 rows from a table, your access function for that view is going to be called 10,000 times. It must be as fast as possible.

When dealing with relational contexts, it is not always possible to keep all privileges for every conceivable relationship in memory. When this happens, your access function will have to perform a query itself to load the specific data into memory. If your application requires this, you should:

Ensure that each such query is as simple and efficient as possible
Cache your results in some way

You may be able to trade-off between the overhead of connection functions and that of access functions. For instance if you have a relational security context based upon a tree of relationships, you may be able to load all but the lowest level branches of the tree at connect time. The access function then has only to load the lowest level branch of data at access time, rather than having to perform a full tree-walk.

Caching can be very effective, particularly for nested loop joins. If you are joining A with B, and they both have the same access rules, once the necessary privilege to access a record in A has been determined and cached, we will be able to use the cached privileges when checking for matching records in B (ie we can avoid repeating the fetch).

Implement the views and instead-of triggers

This is the final stage of implementation. For every base table you must create a secured view and a set of instead-of triggers for insert, update and delete. Refer to the demo (The Veil Demo Application) for details of this.

Testing

Be sure to test it all. Specifically, test to ensure that failed connections do not provide any privileges, and to ensure that all privileges assigned to highly privileged users are cleared when a more lowly privileged user takes over a connection. Also ensure that the underlying tables and raw veil functions are not accessible from user accounts.

Automatic code generation

Note that the bulk of the code in a Veil application is in the definition of secured views and instead-of triggers, and that this code is all very similar. Consider using a code-generation tool to implement this.

Next: A Full Example Application: The Veil Demo