Shared sessions are database connections that are shared amongst many users. Typically these are used by web application servers. When a user does something that requires a database interaction, a database connection is released from the pool, temporarily assigned to that user's session, and when the interaction is complete, returned to the pool.
A further complication is that many applications will perform multiple parallel database operations for a page, each requiring its own connection.
What this means is that:
What the last item means is that the order of requests arriving at the database server may not, due to network lag and application task handling, be the same order in which the user session initiated those requests.
There is only one fundamental requirement of a session management protocol. That is, that the session should know at all times which user is accessing the system.
What this means is:
A further requirement is that it must all be done with as little overhead as possible.
Ordinarily, web systems aim to protect themselves from user-space only. It is assumed that we are protected from bad actors in the network layer by appropriate use of TLS, and that if the server is compromised the game is over anyway.
However, by placing our primary security in the database server, and the database server in a secured part of our network, we can actually hope to protect ourselves against compromised application servers.
If this sounds paranoid, then consider this:
Given that with Veil2 we
can attempt to protect ourselves from a
compromised server, we should try to do so.
This is what Veil2 provides,
out-of-the-box, for session management:
There are 3 functions that your application server's session management must call:
And the calling protocol has four distinct stages.
We begin a session by calling
create_session(). This returns a
session_id and
session_token which we must record and
use on subsequent calls.
Sessions remain active, with minimal overhead, until they
time-out. The timeout period is defined in the
veil2.system_parameters table, in the
parameter called shared session timeout.
Each time a connection is opened, the timeout is reset. If
a session times-out while a connection is active, the
connection will be unaffected, but re-connections will no
longer be possible.
Having created a session and been given a
session_id by
create_session(), we authenticate and
open the session by calling
open_connection() with parameters
appropriate to the chosen authentication method. If this
succeeds, our connection is considered open and we can start
executing queries.
What this means is that it takes 2 database round-trips before we can begin executing queries on the user's behalf. While this may seem an unreasonable overhead, it only occurs when we first create a session. Subsequent usage requires only a single round-trip, which, given that the database has to be informed of which user is connected, seems like a pretty reasonable and unavoidable overhead.
When we are done and are ready to return the database
connection to the pool for use by another user, we call
close_connection(). This is important.
If you do not do this everything will continue to work, but
the connection can be taken over by another user, thereby
inheriting the previous user's access rights.
As long as a session has not timed-out, new connections can be opened using that session. These re-opened connections do not require re-authentication using the original credentials. Instead we use a proof that the session is being used by the original authenticator. This may be computationally cheaper than providing the original credentials, but more importantly it means that those credentials are not being continually re-transmitted. To prevent replay attacks we use a nonce as part of the authentication token. Any attempt to re-use a nonce will be noticed and the connection attempt will fail.
Note that multiple simultaneous connections can be opened on the same session. The only requirement is that each is opened with its own nonce, and that the nonces are more-or-less sequentially numbered.
We provide the following parameters to
create_session():
username;
This is a string that uniquely identifies the user
within the context provided by the context parameters.
This is converted into an
accessor_id by the function
veil2.get_accessor()
which you must define as part of your implementation.
authent_type;
This is the name of an authentication method recorded
in veil2.authentication_types,
and for which an authentication function has been
provided.
context_type_id;
This is an integer identifying the type of context
that the user is authenticating within. See the veil2_demo
for how this is used to enable different users to
belong to different companies.
This will match a scope_type as defined in
veil2.scope_types.
context_id.
This is an integer identifying, in combination with
context_type_id, the context that
the user is authenticating within. See the veil2_demo
for how this is used to enable different users to
belong to different companies.
The function always appears to succeed, and it returns the following result columns:
session_id;.
This is an integer that identifies the session for
your connections. If your parameters successfully
identified a legitimate user, a record with this key
will have been inserted into
veil2.sessions. The user will not
be able to see this record, or determine that it is
absent.
The caller will need to provide this value as a
parameter in subsequent calls to
open_connection().
session_token;.
This is a randomly generated
base64
text value that the caller must use in subsequent,
continuation, calls to
open_connection().
Possession of this token is assumed to be proof that the holder is the same user that authenticated themselves to us.
session_supplemental..
This is a string containing extra information that some user-provided authentication methods may require. This is for your use. Be imaginative.
This call is used to authenticate a user to a previously
created session. It must be the next database call after
create_session() has returned.
You may wonder why this is not simply rolled into the
create_session() call, thereby reducing
the number of round trips. The answer is that some
authentication methods will require initial tokens to be
created before allowing authentication to proceed. An
example of this would be a
Diffie–Hellman Key Exchange-based protocol. If you
implement such a thing please contact the author who would love
to see it.
The parameters to this function are:
session_id;
This will be the value returned from
create_session().
nonce;
This will be an integer value. You should allocate this sequentially for each session, though it does not need to start at 1.
authent_token.
This is a string value. Its value will depend on the authentication method being used. Ideally that authentication method will not require a plaintext password to be sent. Note that the bcrypt authentication method does require the plaintext password. The only advantage of this over the plaintext authentication method is that the password cannot be easily extracted from the database.
This function returns the following result columns:
success;.
A boolean. The meaning should be obvious.
errmsg.
If authentication failed, this provides additional information to the caller. The possible values are:
AUTHFAIL;
This means that the user could not be
authenticated. This might be because the user
and context for the session were invalid or
because authent_token was
invalid. It could even mean that you haven't
called create_session() but
if not, where did you get the value for
session_id?
More information about the failure is recorded in the postgres logs, but this is not available to the user session.
If authent_token was invalid,
at least for the bcrypt
authentication method, you can retry with the
correct token.
EXPIRED;
This means that the session has timed-out. You
will need to create a new session using
create_session().
NONCEFAIL.
This should not be possible in this, the authentication call.
Once a session has been successfully authenticated,
subsequent calls to open_connection() are
considered to be continuations. Multiple open connections
for a session may be used simultaneously, but they must each
use their own nonce values.
The parameters to this function are:
session_id;
This is just as for the Authentication version of this function call.
nonce;
This integer must be previously unused for this session, and should be allocated sequentially in ascending order. They may arrive out of sequence but not by more than a value of 32.
authent_token.
This is created from the value of
authent_token returned from the
create_session() call that created
the session, along with the nonce. It should be
created as follows:
Concatenate authent_token
with the lower-case hexadecimal representation
of the nonce parameter.
Create a sha-1 hash of the concatenated string.
Encode the hash as a base64 string.
The open_connection() function will
create the same encoded hash internally and check that
it matches. If it does, we can be sure that the
caller is in possession of the original
authent_token from the creation of
the session.
This function returns the following result columns:
success;.
A boolean. The meaning should be obvious.
errmsg.
This provides similar results to those for authentication.
AUTHFAIL;
Assuming your session was previously valid, you
got the value of
authent_token wrong. You can
retry, but you will need a new nonce value.
More information about the failure is recorded in the postgres logs, but this is not available to the user session.
EXPIRED;
This means that the session has timed-out. You
will need to create a new session using
create_session().
NONCEFAIL.;
You have attempted to re-use a nonce that was previously used. Try again with a new nonce. Try adding 4 or more to the latest successfully-used value.
If you are accessing your secured database using dedicated database connections, things are much simpler than for shared sessions. With a dedicated database connection you are the sole user of the connection, and there is no need for a complex create-open-close protocol dance to prevent your credentials from being used by someone else.
Typically, dedicated database connections will be used for running reports or ad-hoc queries, and each user will have their own database credentials (typically username and password).
Veil2 associates a database user with its own
accessor record by recording the database username in the
veil2.accessors record's
username column.
For dedicated sessions, you just have to tell
Veil2 to load your accessor privileges by
making a single, parameterless function call:
select veil2.hello();
Sometimes you may need changes to a user's privileges to happen
immediately, rather than the next time that they connect. In
this case, your session should call veil2.reload_connection_privs().
This will rarely be needed in a shared session environment as connections are usually only held for as long as it takes to perform a query, and the next connection will reload the privileges anyway.
You would use Postgres' listen/notify
mechanism to inform the affected session that a reload was needed.