SQL session backend

SQL session backend can be used with many SQL databases such as:

Setup

Prepare the database

Your database must have a specific table to host sessions. Here are some examples for main databases servers.

If your database doesn't accept UTF-8 characters in 'text' field, use 'blob' instead of 'text'.

MySQL

Create a database if necessary:

mysqladmin create lemonldapng

Create sessions table:

CREATE TABLE sessions (
    id CHAR(32) NOT NULL PRIMARY KEY,
    a_session text
    );
Change char(32) by varchar(64) if you use the now recommended SHA256 hash algorithm. See Sessions for more details
You can change table name sessions to whatever you want, just adapt the parameter TableName in module options.
For a better UTF-8 support, use DBD::MariaDB with Apache::Session*::MySQL instead of DBD::mysql

PostgreSQL

Create user and role:

su - postgres
createuser lemonldap-ng -P
Entrez le mot de passe pour le nouveau rôle : <PASSWORD>
Entrez-le de nouveau : <PASSWORD>
Le nouveau rôle est-il un super-utilisateur ? (o/n) n
Le nouveau rôle doit-il être autorisé à créer des bases de données ? (o/n) n
Le nouveau rôle doit-il être autorisé à créer de nouveaux rôles ? (o/n) n

Create database:

createdb -O lemonldap-ng lemonldap-ng

Create table:

psql -h 127.0.0.1 -U lemonldap-ng -W lemonldap-ng
Mot de passe pour l'utilisateur lemonldap-ng :
[...]
lemonldap-ng=> create unlogged table sessions ( id char(32) not null primary key, a_session text );
lemonldap-ng=> q
Change char(32) by varchar(64) if you use the now recommended SHA256 hash algorithm. See Sessions for more details

Manager

Go in the Manager and set the session module (for example Apache::Session::Postgres for PostgreSQL) in General parameters » Sessions » Session storage » Apache::Session module and add the following parameters (case sensitive):

Required parameters
Name Comment Example
DataSource The DBI string dbi:Pg:dbname=sessions;host=10.2.3.1
UserName The database username lemonldapng
Password The database password mysuperpassword
Commit Required for PostgreSQL 1
TableName (Optional) Name of the table sessions

You must read the man page corresponding to your database (Apache::Session::MySQL, ...) to learn more about parameters. You must also install the database connector (https://metacpan.org/pod/DBD::Oracle, DBD::Pg,...)

For MySQL, you need to set additional parameters:
  • LockDataSource
  • LockUserName
  • LockPassword
For better performances, you can use specific browseable session backend.

Learn more at how to increase Data Base performances.

UTF8 support

If you may store some non-ASCII characters, you must add the parameter corresponding to your database.

Database Parameter name Value
MySQL mysql_enable_utf8 1
PostgreSQL pg_enable_utf8 1
SQLite sqlite_unicode 1

Security

Restrict network access to the database.

You can also use different user/password for your servers by overriding parameters globalStorage and globalStorageOptions in lemonldap-ng.ini file.