SQL session backend

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

• MariaDB / MySQL

• PostgreSQL

• Oracle

• Informix

• Sybase

• …

Setup

Prepare the database

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

Attention

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 lemonldap-ng

Create sessions table:

CREATE TABLE sessions (
    id char(32) not null primary key,
    a_session text
    );

Attention

Change char(32) by varchar(64) if you use the now recommended SHA256 hash algorithm. See Sessions for more details

Tip

You can change table name sessions to whatever you want, just adapt the parameter TableName in module options.

Attention

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

Attention

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	lemonldap-ng
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,…)

Attention

For MySQL, you need to set additional parameters:

• LockDataSource

• LockUserName

• LockPassword

Tip

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.