documentation:2.1:sqlconfbackend

Differences

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

Link to this comparison view

documentation:2.1:sqlconfbackend [2019/01/15 15:55] (current)
Line 1: Line 1:
 +====== SQL configuration backends ======
  
 +There is 2 types of SQL configuration backends for LemonLDAP::NG:
 +  * **CDBI**: very simple storage (recommended)
 +  * **RDBI**: triple store storage
 +
 +<note tip>You can use any database engine if it provides a Perl Driver. You will find here examples for MySQL and PostgreSQL, but other engines may also work.</note>
 +
 +See [[changeconfbackend|how to change configuration backend]].
 +
 +===== MySQL =====
 +
 +==== Perl Driver ====
 +
 +You need DBD::MySQL Perl module:
 +  * Debian:
 +<code>
 +apt install libdbd-mysql-perl
 +</code>
 +  * Red Hat:
 +<code>
 +yum install perl-DBD-MySQL
 +</code>
 +
 +==== Database and table creation ====
 +
 +Create database:
 +<code sql>CREATE DATABASE lemonldap-ng CHARACTER SET utf8;</code>
 +
 +Use database to create table:
 +<code sql>use lemonldap-ng</code>
 +
 +=== RDBI ===
 +
 +<code sql>
 +CREATE TABLE lmConfig (
 +    cfgNum int(11) NOT NULL,
 +    field varchar(255) NOT NULL DEFAULT '',
 +    value longtext,
 +    PRIMARY KEY (cfgNum,field)
 +    );
 +</code>
 +
 +=== CDBI ===
 +
 +<code sql>
 +CREATE TABLE lmConfig (
 +    cfgNum int not null primary key,
 +    data longtext
 +);
 +</code>
 +
 +==== Grant access ====
 +
 +You have to grant read/write access for the manager component. Other components needs just a read access. You can also use the same user for all.
 +
 +<note tip>You can use different dbiUser strings:
 +  * one with read/write rights for servers hosting the manager
 +  * one with just read rights for other servers
 +</note>
 +
 +For example (suppose that our servers are in 10.0.0.0/24 network):
 +<code sql>
 +GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES ON lemonldap-ng.lmConfig
 +  TO lemonldaprw@manager.host IDENTIFIED BY 'mypassword';
 +GRANT SELECT ON lemonldap-ng.lmConfig
 +  TO lemonldapro@'10.0.0.%' IDENTIFIED BY 'myotherpassword';
 +</code>
 +
 +===== Connection settings =====
 +
 +Change configuration settings in ''/etc/lemonldap-ng/lemonldap-ng.ini'' file (section configuration):
 +<code ini>
 +[configuration]
 +type = RDBI
 +dbiChain    = DBI:mysql:database=lemonldap-ng;host=1.2.3.4
 +dbiUser     = lemonldaprw
 +dbiPassword = mypassword
 +; optional
 +dbiTable    = mytablename
 +</code>
 +
 +
 +===== PostGreSQL =====
 +
 +==== Perl Driver ====
 +
 +You need DBD::Pg Perl module:
 +  * Debian:
 +<code>
 +apt install libdbd-pg-perl
 +</code>
 +  * Red Hat:
 +<code>
 +yum install perl-DBD-Pg
 +</code>
 +
 +==== Database and table creation ====
 +
 +Create database:
 +<code sql>CREATE DATABASE lemonldap-ng;</code>
 +
 +Use database to create table:
 +<code sql>use lemonldap-ng</code>
 +
 +=== RDBI ===
 +
 +<code sql>
 +CREATE TABLE lmconfig (
 +    cfgnum integer NOT NULL,
 +    field text NOT NULL,
 +    value text,
 +    PRIMARY KEY (cfgNum,field)
 +    );
 +</code>
 +
 +=== CDBI ===
 +
 +<code sql>
 +CREATE TABLE lmConfig (
 +    cfgnum integer not null primary key,
 +    data text
 +);
 +</code>
 +
 +===== Connection settings =====
 +
 +Change configuration settings in ''/etc/lemonldap-ng/lemonldap-ng.ini'' file (section configuration):
 +<code ini>
 +[configuration]
 +type = RDBI
 +dbiChain    = DBI:Pg:database=lemonldap-ng;host=1.2.3.4
 +dbiUser     = lemonldaprw
 +dbiPassword = mypassword
 +; optional
 +dbiTable    = mytablename
 +</code>