Authentication and access control

2017-12-06 - Future - Tony Finch

The IP Register database is an application hosted on Jackdaw, which is a platform based on Oracle and Apache mod_perl.

IP Register access control

Jackdaw and Raven handle authentication, so the IP Register database only needs to concern itself with access control. It does this using views defined with check option, as is briefly described in the database overview and visible in the SQL view DDL.

There are three levels of access to the database:

• the registrar table contains privileged users (i.e. the UIS network systems team) who have read/write access to everything via the views with the all_ prefix.

• the areader table contains semi-privileged users (i.e. certain other UIS staff) who have read-only access to everything via the views with the ra_ prefix.

• the mzone_co table contains normal users (i.e. computer officers in other institutions) who have read-write access to their mzone(s) via the views with the my_ prefix.

Apart from a few special cases, all the underlying tables in the database are available in all three sets of views.

IP Register user identification

The first part of the view definitions is where the IP Register database schema is tied to the authenticated user. There are two kinds of connection: either a web connection authenticated via Raven, or a direct sqlplus connection authenticated with an Oracle password.

SQL users are identified by Oracle's user function; Raven users are obtained from the sys_context() function, which we will now examine more closely.

Porting to PostgreSQL

We are fortunate that support for create view with check option was added to PostgreSQL by our colleague Dean Rasheed.

The sys_context() function is a bit more interesting.

The Jackdaw API

Jackdaw's mod_perl-based API is called WebDBI, documented at https://jackdaw.cam.ac.uk/webdbi/

There's some discussion of authentication and database connections at https://jackdaw.cam.ac.uk/webdbi/webdbi.html#authentication and https://jackdaw.cam.ac.uk/webdbi/webdbi.html#sessions but it is incomplete or out of date; in particular it doesn't mention Raven (and I think basic auth support has been removed).

The interesting part is the description of sessions. Each web server process makes one persistent connection to Oracle which is re-used for many HTTP requests. How is one database connection securely shared between different authenticated users, without giving the web server enormously privileged access to the database?

Jackdaw authentication - perl

Instead of mod_ucam_webauth, WebDBI has its own implementation of the Raven protocol - see jackdaw:/usr/local/src/httpd/Database.pm.

This mod_perl code does not do all of the work; instead it calls stored procedures to complete the authentication. On initial login it calls raven_auth.create_raven_session() and for a returning user with a cookie it calls raven_auth.use_raven_session().

Jackdaw authentication - SQL

These raven_auth stored procedures set the authenticated user that is retrieved by the sys_context() call in the IP Register views - see jackdaw:/usr/local/src/httpd/raven_auth/.

Most of the logic is written in PL/SQL, but there is also an external procedure written in C which does the core cryptography - see jackdaw:/usr/local/oracle/extproc/RavenExtproc.c.

Porting to PostgreSQL - reprise

On the whole I like Jackdaw's approach to preventing the web server from having too much privilege, so I would like to keep it, though in a simplified form.

As far as I know, PostgreSQL doesn't have anything quite like sys_context() with its security properties, though you can get similar functionality using PL/Perl.

However, in the future I want more heavy-weight sessions that have more server-side context, in particular the "shopping cart" pending transaction.

So I think a better way might be to have a privileged session table, keyed by the user's cookie and containing their username and jsonb session data, etc. This table is accessed via security definer functions, with something like Jackdaw's create_raven_session(), plus functions for getting the logged-in user (to replace sys_context()) and for manipulating the jsonb session data.

We can provide ambient access to the cookie using the set session command at the start of each web request, so the auth functions can retrieve it using the current_setting() function.