# SQL session backend

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

- 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.

#### MySQL

Create a database if necessary:

    mysqladmin create lemonldapng

Create sessions table:

    CREATE TABLE sessions (
        id CHAR(32) NOT NULL PRIMARY KEY,
        a_session BLOB
        );

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

#### 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 table sessions ( id char(32) not null primary key, a_session text );
    lemonldap-ng=> q

Change char(32) by char(64) if you use the now recommanded 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

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 (DBD::Oracle, DBD::Pg,…)

For MySQL, you need to set additional parameters:

- LockDataSource
- LockUserName
- LockPassword

If you choose to use MySQL, read how to increase MySQL performances.

## 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.