Veil2 provides built-in mechanisms for
authentication and session management. You can choose to use
these as they are, extend them, or replace them entirely.
Look for "STEP 3" in the file
veil2_demo--<version>.sql.
If you need an authentication method other than
bcrypt you will need to define it as a type
and then create authentication functions for it.
New authentication methods should be created by creating new
authentication functions and new records in veil2.authentication_types.
New authentication functionality is created by creating new
functions with the same signature as veil2.authenticate_bcrypt().
The Veil2 demo chooses to use plaintext
authentication which is a really terrible idea but makes for an
easy implementation. Don't do this.
Veil2 determines the authentication context
for an accessor through the veil2.accessor_contexts
view. Since the actual association between them is provided by
your database, you must provide a custom version of this view
(called veil2.my_accessor_contexts).
To make your version of the view take effect, as described here, use the veil2.init() function.
Note that veil2.implementation_status()
will also install your user-defined functions and views as it
itself calls veil2.init().
For user authentication purposes Veil2 needs
the accessor_id for the accessors record that
represents the user. Since Veil2 does not
itself store usernames, email addresses and whatever else that
might be used to uniquely identify a user in their authentication context,
we need a function that returns an
accessor_id for a username. The
get_accessor()
function is that function.
Your version of this function will be called
veil2.my_get_accessor(). As described above
you make it current by calling veil2.init().
If your application requires any special session management functionality, this is the time to create it. There is little we can say here about what that functionality might be, as it will very much depend on your needs. This section simply tries to provide some useful background information.
There are 2 distinct types of sessions provided:
shared-connection sessions;
These are used when a single database user account is used to handle multiple connected users. Each database connection will typically be shared among many users through some sort of connection-pooling system. This is the type of session generally used by web applications.
It is important with such systems that we can reliably identify which user is connected, and that the privileges of one user are not inadvertently transferred to another. This means that when the connection starts to be used by a new user there must be some form of secure identification of the user.
dedicated sessions.
These are typically used for reporting applications, where
each reporting user will have their own database account.
In this case, Veil2 determines the
user's access rights based upon database roles, and there
is no need for further authentication - we assume that
database authentication is sufficient.
Within a shared connection environment, such as provided by most web applications, a database connection will be shared amongst many users. When the application needs to perform some database action on behalf of a user, the connection will be retrieved from the pool, and possibly re-initialized, before executing one or more queries on the user's behalf.
Unless we provide the connection with extra information, the database cannot know which user it is executing queries for.
Veil2 provides 3 session management
functions to identify and keep track of users:
veil2.create_session();veil2.open_connection();veil2.close_connection().
Your application server must call
create_session() to identify each new user,
and open_connection() before the user begins
issuing queries. close_connection() should be
called before returning the database connection to the pool.
A detailed description of this can be found in the Authentication Protocols appendix, which describes the functions, their parameters, and describes error handling.
If you have users that connect directly to your database, perhaps through some reporting tool, you will want to set them up as users of dedicated sessions.
This is done by recording the database username in the
username field of the veil2.accessors
record.
To begin a dedicated session after connecting to the database,
the user must call veil2.hello(), which
will return true if Veil2 has successfully
authenticated the user.
select veil2.hello();
If your reporting tool does not allow such function calls, you will need to create a view that calls it, which the reporting tool can call. Something like this would work:
create or replace view session_start as select veil2.hello();
or, if you need the view to return no rows:
create or replace view session_start as with session as ( select 1 as result from veil2.hello() ) select * from session where result = 0;
For an example of this, take a look at the
Veil2 unit tests (in file
tests/test_session.sql). The database
user veil2_alice is set up as a dedicated
session user (as well as a shared session user).