Defence-in-depth for data-base backed websites: Users

When you log your users in all the way to the database, user management of course becomes a bit more involved than when a user is just a row in a table. Users need database accounts, which require passwords. Passwords need to be changeable, and old user accounts should be disabled. We'll look at these things one by one. The SQL here is a bit outside what I'd normally write (it uses stored procedures, for instance), but bear with me - after this post it will mostly be downhill.

When users log in, the web app does not check the password, but instead creates a connection to the database using the user's database username and the password that the user gave to the web app. If the database accepts it, then apparently the user is authentic, and the web app can allow it to attempt any authorized action. But what user name should the web app use when logging the user in? We probably don't want to use the application user name, since users may want to change user names, the web app may want to allow users to have user names that contain characters that the database does not permit in its user names etc. One solution is to use a mapping table that maps application user identities to the corresponding database user names, and do a translation when logging in. I put my mapping in a separate schema from the normal tables (which I keep in impl), since it's not strictly part of the application data, but only relates to the authentication mechanism.

CREATE SCHEMA mapping; 

CREATE TABLE mapping.users
(
  dbuser text primary key,
  appuser int unique
);

But how can we look things up in the database before we log the user in? We don't have the One Big Application User any more, but we can have specialized user accounts with fixed passwords known by the application as long as they are locked down to specific operations.

CREATE USER kroll_user_login WITH PASSWORD 'asdf';
CREATE SCHEMA kroll_login;
GRANT USAGE ON SCHEMA kroll_login to kroll_user_login;
ALTER USER kroll_user_login SET SEARCH_PATH TO kroll_login;

CREATE or replace FUNCTION
kroll_login.get_dbuser_by_appuser (app_username text)
RETURNS text AS $$
DECLARE
  db_username text;
BEGIN
  db_username := (
    select dbuser from mapping.users
    where appuser=(select id from impl.users where "name"=$1)
  );
  return db_username;
END;
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_login.get_dbuser_by_appuser(text)
TO kroll_user_login;

If an attacker would be able to seize control of a connection logged in as kroll_user_login, then the attacker can look up database user names all day long, but damage is limited to that.

The application code for logging the user in thus becomes:

1. Log in as kroll_user_login.
   
2. Run SELECT * FROM get_dbuser_by_appuser(?) with the username provided by the user.
   
3. Log in with the returned user name and the password that the user gave.
   
4. Create a session for the user, and store the connection as a session attribute.
   

So what about enrollment? We'd need to come up with a name for the database user, create it, create an application user, and set up the mapping between them. This can be done similarly to logging users in: a stored procedure and a special database user to run it:

CREATE USER kroll_user_signup WITH PASSWORD 'asdf';
CREATE SCHEMA kroll_signup;
GRANT USAGE ON SCHEMA kroll_signup
TO kroll_user_signup;
ALTER USER kroll_user_signup SET SEARCH_PATH TO kroll_signup;

CREATE SEQUENCE kroll_signup.create_user_seq;
GRANT SELECT, UPDATE on kroll_signup.create_user_seq TO kroll_user_signup;

CREATE or replace FUNCTION
kroll_signup.create_user (app_username text, password text, email text)
RETURNS void AS $$
DECLARE
  db_username text;
BEGIN
  db_username := 'kroll_user_' || nextval('create_user_seq');
  execute 'CREATE USER ' || db_username ||
         ' WITH PASSWORD ' || quote_literal(password) ||
         ' IN ROLE kroll_role_user';
  execute 'GRANT USAGE ON SCHEMA kroll_user TO ' || db_username;
  execute 'ALTER USER ' || db_username ||
    ' SET SEARCH_PATH TO kroll_user';
  INSERT INTO impl.users ("name", email)
    VALUES (app_username, quote_literal(email));
  INSERT INTO mapping.users (dbuser, appuser)
    VALUES (db_username, (select id from impl.users where "name"=app_username));
END;
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_signup.create_user(text,text,text)
TO kroll_user_signup;

Calling kroll_signup.create_user creates a database user named kroll_user_n that has access only to the kroll_user schema. A row is inserted into the application's users table, and a row mapping between the two identities is added to the mapping.users table.

The kroll_user_signup user is created without the CREATEROLE option, since it is not this user that creates the new user accounts, but the stored procedure, which is running with SECURITY DEFINER. That is: when running the procedure, the permissions are those of the administrator who ran the setup scripts.

As you can see from the code above, all users share a single schema. We are thus not doing much in the database per user in addition to what the application needs when implemented using a single shared login. Database users are on a per-database level though, so prefixing them with the application name is probably a good idea if you want to share a single database instance between many applications.

Changing passwords is a bit different. The usual protocol is that the user specifies the current password, and the new password twice. If the provided current password matches the login password and the two new passwords are the same, then the login password is changed to the new password. The application can compare the two new passwords, but it cannot directly compare the current password with the login password, since login passwords are now only handled by the database. This can be solved by having the application create a new connection to the database using the user's database user name and the provided password. If that succeeds, then the password must have been correct, and we can update the login password to the new password. In Java, this would look something like the following (error handling omitted):

Connection normal_connection=Util.getConnection(request);
PreparedStatement ps=normal_connection.prepareStatement(
  "select current_user"
);
ResultSet rs=ps.executeQuery();
String db_username=rs.getString(1);
Connection special_connection=createConnection(username, old_password);
PreparedStatement ps=special_connection.prepareStatement(
    "ALTER ROLE " + db_username + " WITH PASSWORD '" + new_password + "'"
);
ps.executeUpdate();

Decommissioning, finally, is a complex topic on the application side. What information about the user should be removed? Should anything be removed? For the forum I wrote, I decided to just keep all the application data. The only thing that is removed is the ability log in, that is: the database user and the mapping. Again, a stored procedure and a dedicated database user are used:

CREATE USER kroll_user_remove WITH PASSWORD 'asdf' CREATEROLE;
CREATE SCHEMA kroll_remove;
GRANT USAGE ON SCHEMA kroll_remove TO kroll_user_remove;
ALTER USER kroll_user_remove SET SEARCH_PATH TO kroll_remove;

CREATE or replace FUNCTION
kroll_remove.remove_db_user (app_username integer)
RETURNS void AS $$
DECLARE
  db_username text;
BEGIN
  select dbuser into db_username from mapping.users where appuser=$1;
  delete from mapping.users where appuser=$1;
  execute 'REVOKE USAGE ON SCHEMA kroll_user FROM ' || db_username;
  execute 'DROP USER ' || db_username;
END;
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_remove.remove_db_user(integer)
TO kroll_user_remove;

This was how user administration can be done when the application users are also database users. Next up will be reading and filtering application data.