Tracking and throttling failed sign in attempts in cassandra.

[Jacob Rhoden <ja...@me.com>]

Hi Guys,

If anyone has some wisdom to contribute to the following, I would very much appreciate it.

Our existing small web application has some _basic_ mechanisims to prevent abuses such as repeated password guessing, sending spam, and so on, using a MySQL table. Im looking at if it is practical to implment this in cassandra, but I am not yet familiar enough with cassandra to know if the below suggesting is a good idea, or plain stupid. :D

Note that we are not overly worried about the situation where there are multiple nodes and the data is not fully replicated, an extra failed password attempt every now and then is not a big deal.

    -- Table to keep track of currently locked accounts, IP addresses, etc
    create table throttle_lock(
        uid text primary key 
    );

    --  This IP got locked for 300 seconds
    insert into throttle_lock (uid) values('4.5.5.6') using ttl 300;

    -- Table to keep track of recent suspicious activity
    create table throttle_counter(
        uid text,
        time timeuuid,
        primary key (uid, time)
    );

    -- Store recent activity, such as sign in failure, for 60 seconds
    insert into throttle_counter (uid,time) values('1.2.3.4', now()) using ttl 60;
    insert into throttle_counter (uid,time) values('hacker@example.com', now()) using ttl 60;
    insert into throttle_counter (uid,time) values('1.2.3.4', now()) using ttl 60;
    insert into throttle_counter (uid,time) values('1.2.3.4', now()) using ttl 60;

    -- The following logic occurs, for example, on sign in.
    is_locked(uid) {
        results = 'select * from throttle_lock where uuid='1.2.3.4'';
        if(results > 0) {
            return locked; 
        }   
        results = 'select * from throttle_counter where uid='1.2.3.4'';
        if(results > 3) {
            insert into throttle_counter (uid) values('4.5.5.6') using ttl 300;
            return locked; 
        } else {
            return not_locked;
        }   
    }

Are tombstones going to be a big issue, I know they might be if we get a serious load or attack, however if our site becomes that popular, I dare say we will need some dedicated hardware to handle this kind of security.