documentation:2.1:sqlsessionbackend

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

documentation:2.1:sqlsessionbackend [2019/06/28 14:28]
documentation:2.1:sqlsessionbackend [2019/06/28 14:28] (current)
Line 1: Line 1:
 +====== SQL session backend ======
 +
 +SQL session backend can be used with many SQL databases such as:
 +  * [[https://metacpan.org/pod/Apache::Session::MySQL|MariaDB / MySQL]]
 +  * [[https://metacpan.org/pod/Apache::Session::Postgres|PostgreSQL]]
 +  * [[https://metacpan.org/pod/Apache::Session::Oracle|Oracle]]
 +  * [[https://metacpan.org/pod/Apache::Session::Informix|Informix]]
 +  * [[https://metacpan.org/pod/Apache::Session::Sybase|Sybase]]
 +  * ....
 +
 +===== Setup =====
 +
 +==== Prepare the database ====
 +
 +Your database must have a specific table to host sessions. Here are some examples for main databases servers.
 +
 +<note important>
 +If your database doesn't accept UTF-8 characters in 'text' field, use 'blob' instead of 'text'.
 +</note>
 +
 +=== MySQL ===
 +
 +Create a database if necessary:
 +<code>
 +mysqladmin create lemonldap-ng
 +</code>
 +
 +Create sessions table:
 +<file sql>
 +CREATE TABLE sessions (
 +    id char(32) not null primary key,
 +    a_session text
 +    );
 +</file>
 +
 +<note important>Change ''char(32)'' by ''varchar(64)'' if you use the now recommended SHA256 hash algorithm. See [[documentation:latest:sessions|Sessions]] for more details</note>
 +
 +<note tip>You can change table name ''sessions'' to whatever you want, just adapt the parameter ''TableName'' in module options.</note>
 +
 +<note important>For a better UTF-8 support, use [[https://metacpan.org/pod/DBD::MariaDB|DBD::MariaDB]] with Apache::Session*::MySQL instead of DBD::mysql</note>
 +
 +=== PostgreSQL ===
 +
 +Create user and role:
 +<code>
 +su - postgres
 +createuser lemonldap-ng -P
 +</code>
 +
 +<code>
 +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
 +</code>
 +
 +Create database:
 +<code>
 +createdb -O lemonldap-ng lemonldap-ng
 +</code>
 +
 +Create table:
 +<code>
 +psql -h 127.0.0.1 -U lemonldap-ng -W lemonldap-ng
 +</code>
 +
 +<code>
 +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
 +</code>
 +
 +<note important>Change ''char(32)'' by ''varchar(64)'' if you use the now recommended SHA256 hash algorithm. See [[documentation:latest:sessions|Sessions]] for more details</note>
 +
 +==== Manager  ====
 +
 +Go in the Manager and set the session module (for example [[https://metacpan.org/pod/Apache::Session::Postgres|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 [[https://metacpan.org/pod/DBI|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 ([[https://metacpan.org/pod/Apache::Session::MySQL|Apache::Session::MySQL]], ...) to learn more about parameters. You must also install the database connector ([[https://metacpan.org/pod/DBD::Oracle]], [[https://metacpan.org/pod/DBD::Pg|DBD::Pg]],...)
 +
 +<note important>
 +For MySQL, you need to set additional parameters:
 +  * LockDataSource
 +  * LockUserName
 +  * LockPassword
 +</note>
 +
 +<note tip>
 +For better performances, you can use specific [[browseablesessionbackend|browseable session backend]].
 +
 +Learn more at [[performances#apachesession_performances|how to increase Data Base performances]].
 +</note>
 +
 +=== 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.