Security of User accounts

At this backend as a service, user account management is something the account owner gets to code, in SQL and JavaScript. The latest Rdbhost feature is designed to make your user account management more secure.

Firstly, let's review how a typical user authentication would work. You would keep users in a table, with a unique identifier and a secret key. A query to do something on your database would read something like:

UPDATE posts p
   SET p.title = '%(title)', p.body = '%(body)'
  FROM auth.fedauth_accounts u
 WHERE = '%(postid)' 
   AND = u.identifier 
   AND u.identifier = '%(identifier)'
   AND u.key = '%(key)'

The key and identifier are included to ensure that only posts this user owns are affected.

Alternatively, you could check the key first, and then use a simpler query:

check_authentication('%(identifier)', '%(key)');
UPDATE posts p
   SET p.title = '%(title)', p.body = '%(body)'
 WHERE = '%(postid)' AND = '%(identifier)'; 

The check_authentication function would raise an exception upon not finding a user record for that key and identifier. The exception would terminate the request and return an error. The test could be anywhere in the request query, and the whole request (as one transaction) would be rolled back for no result.

One possible attack vector for an attacker is that they could use the above query to test for valid keys, submitting one possible value after another until they get a successful post. This is a brute-force attack, and as such is typically countered by throttling the rate of attempts.

This is where the new feature comes in. If the test raises an exception with message rdb77, the server adds the requester's IP to an authentication-failures table (hereafter auth-fail). When the number of failures from a given IP within a given interval exceeds a limit, that IP is blocked from requesting for the remainder of that interval.

You trigger the auth-fail counting by raising an exception with message = 'rdb77'. That is done in pl/pgSQL with code like:


The above mentioned check_authentication function could be written like:

CREATE OR REPLACE FUNCTION "auth"."check_authentication"
     (IN _identifier varchar, IN _key varchar)
AS $$
  PERFORM identifier FROM auth.fedauth_accounts 
     WHERE identifier = _identifier AND key = _key;

The above function will be provided in each database whenever Federated Identity is enabled, as of the next server push, but if not found in your account, cut and pasting the above into an RdbAdmin command window will work. If you are using your own account management, with passwords, the above function can be easily adapted.

Let's go over the numbers as to how this throttling system can help.

Rdbhost rules allow 120 auth-fails per 6 minute interval, per account.

The automatically generated keys in the Federated Identity logins are 25 base-64 characters. To test all values would need 1045 tries. At 1200 tries per hour, that would take 1038 years1. An attacker with 10 million bots (and thus 10 million IPs) would still need 1031 years. For comparison, planet Earth is only 109 years old.

The website
Reference on pl/pgSQL exceptions
Federated Logins at Rdbhost

  1. Time values represented to nearest order of magnitude.

comments powered by Disqus