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