SQL Client Auth Provider

We provide an implementation of AuthenticationProvider as well as AuthorizationProvider which uses the Vert.x SqlClient to perform authentication and authorization against any SQL compliant database. To use this project, add the following dependency to the dependencies section of your build descriptor:

Maven (in your pom.xml):

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-auth-sql-client</artifactId>
 <version>4.2.7</version>
</dependency>

Gradle (in your build.gradle file):

compile 'io.vertx:vertx-auth-sql-client:4.2.7'

To create an instance you first need an instance of SqlClient. To learn how to create one of those please consult the documentation for the SQL client.

Once you’ve got one of those you can create a SqlAuthentication instance as follows:

SqlAuthenticationOptions options = new SqlAuthenticationOptions();
// SQL client can be any of the known implementations
// *. Postgres
// *. MySQL
// *. etc...
AuthenticationProvider authenticationProvider =
  SqlAuthentication.create(sqlClient, options);

Once you’ve got your instance you can authenticate and authorize with it just like any AuthenticationProvider.

The out of the box config assumes certain queries for authentication and authorization, these can easily be changed with the operations setAuthenticationQuery, setPermissionsQuery and setRolesQuery, if you want to use them with a different database schema.

The basic data definition for the storage should look like this:

--
-- Take this script with a grain of salt and adapt it to your RDBMS
--
CREATE TABLE users (
 username VARCHAR(255) NOT NULL,
 password VARCHAR(255) NOT NULL
);

CREATE TABLE users_roles (
 username VARCHAR(255) NOT NULL,
 role VARCHAR(255) NOT NULL
);

CREATE TABLE roles_perms (
 role VARCHAR(255) NOT NULL,
 perm VARCHAR(255) NOT NULL
);

ALTER TABLE users ADD CONSTRAINT pk_username PRIMARY KEY (username);
ALTER TABLE users_roles ADD CONSTRAINT pk_users_roles PRIMARY KEY (username, role);
ALTER TABLE roles_perms ADD CONSTRAINT pk_roles_perms PRIMARY KEY (role, perm);

ALTER TABLE users_roles ADD CONSTRAINT fk_username FOREIGN KEY (username) REFERENCES users(username);

Hashing Strategy

The provider uses the phc sf spec to hash passwords.

If you already have a running legacy application switching the strategies will break your existing passwords. The new format will not for suffer from this. In order to upgrade request users to reset their password and update the record appropriately.

It is advised to always store your passwords as hashes in your database tables which have been created with a salt which should be stored in the row too. A strong hashing algorithm should be used. It is strongly advised never to store your passwords as plain text.

Vertx Auth SQL Client and GDPR

GDPR is a regulation from the common European Union law. It overrides/supercedes national data protection laws and extents the previously existing directives. This section of the manual is by no means a thorough walkthrough of the regulation, it is just a small summary how this component adheres to the requirements. Companies not adhering to the requirements can be fined on 4% of the turnover or 20 million euro. Therefore we want to make sure that as a user of Vert.x Auth SQL Client you’re are on the good track to comply.

The law defines certain terminology:

Data Subject - Person whose personal data is processed (e.g.: User)
Personal Data - Any data about an identifiable or identified person
Data Processing - Any operation (manual or automated) on personal data
Controller - The entity (company) that requests and uses the data
Processors - Any entity that processes data on behalf of a controller (e.g.: cloud service provider)

GDPR defines the following functionality:

"Forget me" - Right to erasure
Mark profile as restricted - Right to restriction of processing
Export data - Right to portability
Allow profile editing - Right to rectification
See all my data - Right to access
Consent checkboxes
Age checks
Data destruction - Data minimization principle

This module complies with the GDPR by not storing any identifiable information about a data subject. The only reference is the username which is not linked to any personal data.

In order to add personal data to your application you should create your own data schema and use the username column as a foreign key to your data. As a tip you should have a boolean flag to mark the personal data as restricted to comply to the right to restriction of processing which means that if you need to handle the data, e.g.: send a bulk email from a mailing list you are not allowed to do so if the flag is true.

The right to erasure does not mean that you must wipe all records from your application, e.g.: in a bank this right cannot be used to erase a running loan or debt. You are allowed to keep your application data but must erase the personal data. In case of Vert.x Auth SQL you should delete your table but can still use a foreign key to the username as long as is not possible to link the username to the personal data.

Important note is that this must survive backups! As a tip backup the data, and data erasure on different archives so they can be replayed individually.

Hashing passwords

Like any application there will be a time where you need to store new users into the database. As you might know, passwords are not stored in plain text but hashed according to the hashing strategy. The same strategy is required to hash new password before storing it to the database. Doing it is a 3 step task.

Generate a salt string
Hash the password given the salt string
Store it to the database

String hash = sqlAuth.hash(
  "pbkdf2", // hashing algorithm (OWASP recommended)
  VertxContextPRNG.current().nextString(32), // secure random salt
  "sausages" // password
);

// save to the database
sqlClient
  .preparedQuery("INSERT INTO users (username, password) VALUES ($1, $2)")
  .execute(Tuple.of("tim", hash))
  .onSuccess(rowset -> {
    // password updated
  });

Authentication

When authenticating using this implementation, it assumes username and password fields are present in the authentication info:

JsonObject authInfo = new JsonObject()
  .put("username", "tim")
  .put("password", "sausages");

authProvider.authenticate(authInfo)
  .onSuccess(user -> System.out.println("User: " + user.principal()))
  .onFailure(err -> {
    // Failed!
  });

Authorisation - Permission-Role Model

Although Vert.x auth itself does not mandate any specific model of permissions (they are just opaque strings), this implementation assumes a familiar user/role/permission model, where a user can have zero or more roles and a role can have zero or more permissions.

If validating if a user has a particular permission simply match the user against a given permission as follows:

sqlAuthZ.getAuthorizations(user)
  .onSuccess(v -> {
    if (PermissionBasedAuthorization.create("commit_code").match(user)) {
      // Has permission!
    }
  });

Webauthn

MongoDB Auth