I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user.
I have several authentication schemes right now, and design should be extensible with new authentication schemes. What I've currently is:
- Insecure (direct)
- One time code over SMS
- Time-based code via external authenticators (i.e. Google Authenticator)
To clarify how does those schemes work, I will give some examples:
Insecure authentication is performed by only user credentials. Those credentials are username and password. All users have those two credentials.
One time code over SMS is another authentication scheme, having taken users credentials, a one time code is generated by the server, and sent to the user's phone via SMS. Afterwards, user supplies that one time code to the server in order to complete the authentication. In practice, time-based code is no different than this authentication scheme.
Here are the DDTs of my current design (please note that they're canonical):
CREATE TABLE users( id serial PRIMARY KEY, -- surrogate key username character varying (25) NOT NULL, inserted_at timestamp without time zone DEFAULT now() NOT NULL ); -- Those tables are middleware artifacts CREATE TABLE one_time_codes( id serial PRIMARY KEY, -- surrogate key code integer NOT NULL, inserted_at timestamp without time zone DEFAULT now() NOT NULL, user_id integer REFERENCES users(id) NOT NULL ); CREATE TABLE insecure_authentication_handles( id serial PRIMARY KEY, -- surrogate key passkey character varying (255) NOT NULL, inserted_at timestamp without time zone DEFAULT now() NOT NULL, user_id integer REFERENCES users(id) NOT NULL ); -- Final product of successful authentication CREATE TABLE passphrases( id serial PRIMARY KEY, -- surrogate key passkey character (5) UNIQUE NOT NULL, inserted_at timestamp without time zone DEFAULT now() NOT NULL, user_id integer REFERENCES users(id) NOT NULL );
As you may understand, I used Martin Fowler's "Concrete Table Inheritance" in order to represent the concrete
InsecureAuthenticationHandle subclasses of
AuthenticationScheme abstract class in the database. Here, each
passphrase could be matched with either
insecure_authentication_handle depending on users preference of authentication.
insecure_authentication_handles are the middleware artifacts of the authentication, hence I created a view to list all of those artifacts.
CREATE VIEW concrete_authentication_middleware_artifacts AS SELECT user_id, NULL insecure_authentication_handle_passkey, code one_time_code, inserted_at FROM one_time_codes UNION SELECT user_id, passkey insecure_authentication_handle_passkey, NULL one_time_code, inserted_at FROM insecure_authentication_handles;
Hence, if I want to pair each middleware artifact with successful authentication artifact (passphrase), I would execute the query (assume all artifacts have a valid duration of 3 minutes):
SELECT cama.*, p.passkey passphrase FROM concrete_authentication_middleware_artifacts cama INNER JOIN passphrases p ON cama.user_id = p.user_id AND (GREATEST(p.inserted_at, cama.inserted_at) - LEAST(p.inserted_at, cama.inserted_at) < '3 minutes'::interval);
Everything works great, however:
- I could use foreign keys instead of comparing timestamps in order to join
passphrasestable and the view, however that would lead to creating n foreign keys in
passphrasestable, whereas only one may have non-
NULLvalue. I'm also unaware of creating external predicates in order to ensure database integrity on such conditions.
- The design does not favor the latter query with huge amount of data. This may not be a problem since it's executed rarely.
What kind of suggestions do you have? I'm not sure I reached to the most elegant design.