pac4j allows you to validate username/password and create, update and delete users on a SQL database.

1) Dependency

You need to use the following module:pac4j-sql.

Example (Maven dependency):

<dependency><groupId>org.pac4j</groupId><artifactId>pac4j-sql</artifactId><version>${pac4j.version}</version></dependency>

2)DbProfileService

TheDbProfileService allows you to:

• validate a username/password on a relational database (it can be defined for HTTP clients which deal withUsernamePasswordCredentials)
• create, update or delete a user in the database.

It works with aDbProfile.

It is built from ajavax.sql.DataSource.

Example:

DataSourcedataSource=JdbcConnectionPool.create("jdbc:h2:mem:test",dbuser,dbpwd);DbProfileServicedbProfileService=newDbProfileService(dataSource);

Theusers table in the database must be created with the following script:

CREATETABLEusers(idvarchar(255),usernamevarchar(255),passwordvarchar(255),linkedidvarchar(255),serializedprofilevarchar(10000));ALTERTABLEusersADDPRIMARYKEY(id),ADDKEYusername(username),ADDKEYlinkedid(linkedid);

The name of the table in the database can be changed via thesetUsersTable method. As well as theid,username andpassword columns using thesetIdAttribute,setUsernameAttribute andsetPasswordAttribute methods.

The attributes of the user profile can be managed in the database in two ways:

• either each attribute is explicitly saved in a specific column and all these columns are defined as a list of column names separated by commas via thesetAttributes method (it’s the legacy mode existing since version 1.9)
• or the whole user profile is serialized and saved in theserializedprofile column.

ThisDbProfileService supports the use of a specificPasswordEncoder to encode the passwords in the database.