How to handle locked/disabled user accounts with Dancer2::Plugin::Auth::Extensible?

I'm currently migrating a CGI application to Dancer2. I previously used a "hand-crafted" authentication mechanism using MySQL and a user table with the attributes email, password, and a state. The state indicates whether the account is active or locked. locked means the account is disabled (logically deleted).

I also have tables roles and user_roles to implement my two roles: admin and user.

Everything works like a charm, with one exception:

With my old "hand-crafted" mechanism I was able to lock users, i.e. logically delete them without removing them from the database. A login was only successful if email and hash_of(password) matched and the account was not locked.

How do I implement that with Dancer2::Plugin::Auth::Extensible and Dancer2::Plugin::Auth::Extensible::Provider::Database?

I hoped that the hook after_authenticate_user could return true or false to overwrite the result of authenticate_user, but that is not the case. At least, it is not documented.

One thing I thought of was to have an additional role active and then – for every route – require_role active instead of just require_login.

So my question is: How can I make Dancer2::Plugin::Auth::Extensible consider only active users?

Borodin suggested to create a view and use that as the user table. I've done some testing and can say that that is indeed the easiest way to achieve this.

Warning: because of the nature of views, this makes it impossible for the application to modify or add users!

Consider the following Dancer2 application. I started with the dancer2 create script.

$ dancer2 gen -a Foo
$ cd Foo

I created the following simple sqlite database.

$ echo "
CREATE TABLE users (
    id       INTEGER     PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(32) NOT NULL UNIQUE,
    password VARCHAR(40) NOT NULL,
    disabled TIMESTAMP   NULL
);

CREATE VIEW active_users (id, username, password) AS
    SELECT id, username, password FROM users WHERE disabled IS NULL;

INSERT INTO users ( username, password, disabled )
VALUES  ( 'foo', 'test', null),
        ( 'bar', 'test', '2017-10-01 10:10:10');
" | sqlite3 foo.sqlite

There is only a users table with the default columns as suggested by the plugin, plus a column disabled, which can be NULL or a timestamp. I thought it would be easier to illustrate with disabled than with active.

Then I made the following changes to lib/Foo.pm. All of this is basically from the documentation of Dancer2::Plugin::Auth::Extensible and Dancer2::Plugin::Auth::Extensible::Provider::Database.

package Foo;
use Dancer2;
use Dancer2::Plugin::Database;
use Dancer2::Plugin::Auth::Extensible;

our $VERSION = '0.1';

get '/' => sub {
    template 'index' => { 'title' => 'Foo' };
};

get '/users' => require_login sub {
    my $user = logged_in_user;
    return "Hi there, $user->{username}";
};

true;

Next, the plugins needed to go into the config. Edit config.yml and replace it with this.

appname: "Foo"
layout: "main"
charset: "UTF-8"
template: "simple"
engines:
  session:
    Simple:
      cookie_name: testapp.session

# this part is interesting
plugins:
    Auth::Extensible:
        realms:
            users:
                provider: 'Database'

############### here we set the view
                users_table: 'active_users'
    Database:
        driver: 'SQLite'
        database: 'foo.sqlite'
        on_connect_do: ['PRAGMA foreign_keys = ON']
        dbi_params:
            PrintError: 0
            RaiseError: 1

Now we're all set to try.

$ plackup bin/app.psgi
HTTP::Server::PSGI: Accepting connections at http://0:5000/

Visit http://localhost:5000/users in your browser. You'll see the default login form.

Enter foo and test. This should work, and you should see the /users route. (Or not, as in my case, where the redirect seems to be broken...).

Now go to http://localhost:5000/logout to get rid of foo's cookie and open http://localhost:5000/users again. This time, enter bar and test.

You will see that the login does not work.

To make a counter-test, replace the users_table in config.yml and restart the app.

# config.yml
                users_table: 'users'

Now the user foo will be able to log in.

This method is not only easy to implement, it should also by far be the way with the highest performance, as the database handles all the logic (and has most likely already cached it).

Your application, and especially the authentication plugin, do not need to know about the existence of the active or disabled fields at all. They don't need to care. Stuff will just work.