sqlconfbackend.html 12.5 KB
Newer Older
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
1 2 3 4 5 6
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
  <meta charset="utf-8" />
  <title>documentation:2.0:sqlconfbackend</title>
<meta name="generator" content="DokuWiki"/>
Xavier Guimard's avatar
Xavier Guimard committed
7
<meta name="robots" content="index,follow"/>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
8 9 10 11 12
<meta name="keywords" content="documentation,2.0,sqlconfbackend"/>
<link rel="search" type="application/opensearchdescription+xml" href="lib/exe/opensearch.html" title="LemonLDAP::NG"/>
<link rel="start" href="sqlconfbackend.html"/>
<link rel="contents" href="sqlconfbackend.html" title="Sitemap"/>
<link rel="stylesheet" type="text/css" href="lib/exe/css.php.t.bootstrap3.css"/>
Xavier Guimard's avatar
Xavier Guimard committed
13 14 15 16 17 18 19 20 21
<!-- //if:usedebianlibs
  <link rel="stylesheet" type="text/css" href="/javascript/bootstrap/css/bootstrap.min.css" />
//elsif:useexternallibs
  <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"></script>
//elsif:cssminified
  <link rel="stylesheet" type="text/css" href="/static/bwr/bootstrap/dist/css/bootstrap.min.css" />
//else -->
  <link rel="stylesheet" type="text/css" href="/static/bwr/bootstrap/dist/css/bootstrap.css" />
<!-- //endif -->
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
22 23 24
<script type="text/javascript">/*<![CDATA[*/var NS='documentation:2.0';var JSINFO = {"id":"documentation:2.0:sqlconfbackend","namespace":"documentation:2.0"};
/*!]]>*/</script>
<script type="text/javascript" charset="utf-8" src="lib/exe/js.php.t.bootstrap3.js"></script>
Xavier Guimard's avatar
Xavier Guimard committed
25 26 27 28 29 30 31 32 33 34 35 36 37 38
<!-- //if:usedebianlibs
<script type="text/javascript" src="/javascript/jquery/jquery.min.js"></script>
//elsif:useexternallibs
<script type="text/javascript" src="http://code.jquery.com/jquery-2.2.0.min.js"></script>
//elsif:jsminified
<script type="text/javascript" src="/static/bwr/jquery/dist/jquery.min.js"></script>
//else -->
<script type="text/javascript" src="/static/bwr/jquery/dist/jquery.js"></script>
<!-- //endif -->
<!-- //if:usedebianlibs
  <script type="text/javascript" src="/javascript/jquery-ui/jquery-ui.min.js"></script>
//elsif:useexternallibs
  <script type="text/javascript" src="http://code.jquery.com/ui/1.10.4/jquery-ui.min.js"></script>
//elsif:jsminified
Xavier Guimard's avatar
Xavier Guimard committed
39
  <script type="text/javascript" src="/static/bwr/jquery-ui/jquery-ui.min.js"></script>
Xavier Guimard's avatar
Xavier Guimard committed
40
//else -->
Xavier Guimard's avatar
Xavier Guimard committed
41
  <script type="text/javascript" src="/static/bwr/jquery-ui/jquery-ui.js"></script>
Xavier Guimard's avatar
Xavier Guimard committed
42
<!-- //endif -->
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
43 44 45 46 47 48 49 50 51
</head>
<body>
<div class="dokuwiki export container">
<!-- TOC START -->
<div id="dw__toc">
<h3 class="toggle">Table of Contents</h3>
<div>

<ul class="toc">
Clément OUDOT's avatar
Clément OUDOT committed
52
<li class="level1"><div class="li"><a href="#mysql">MySQL</a></div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
53
<ul class="toc">
Clément OUDOT's avatar
Clément OUDOT committed
54 55
<li class="level2"><div class="li"><a href="#perl_driver">Perl Driver</a></div></li>
<li class="level2"><div class="li"><a href="#database_and_table_creation">Database and table creation</a></div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
56 57 58 59 60
<ul class="toc">
<li class="level3"><div class="li"><a href="#rdbi">RDBI</a></div></li>
<li class="level3"><div class="li"><a href="#cdbi">CDBI</a></div></li>
</ul>
</li>
Clément OUDOT's avatar
Clément OUDOT committed
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
<li class="level2"><div class="li"><a href="#grant_access">Grant access</a></div></li>
</ul>
</li>
<li class="level1"><div class="li"><a href="#connection_settings">Connection settings</a></div></li>
<li class="level1"><div class="li"><a href="#postgresql">PostGreSQL</a></div>
<ul class="toc">
<li class="level2"><div class="li"><a href="#perl_driver1">Perl Driver</a></div></li>
<li class="level2"><div class="li"><a href="#database_and_table_creation1">Database and table creation</a></div>
<ul class="toc">
<li class="level3"><div class="li"><a href="#rdbi1">RDBI</a></div></li>
<li class="level3"><div class="li"><a href="#cdbi1">CDBI</a></div></li>
</ul>
</li>
</ul>
</li>
<li class="level1"><div class="li"><a href="#connection_settings1">Connection settings</a></div></li>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
77 78 79 80 81 82 83 84 85
</ul>
</div>
</div>
<!-- TOC END -->

<h1 class="sectionedit1" id="sql_configuration_backends">SQL configuration backends</h1>
<div class="level1">

<p>
Clément OUDOT's avatar
Clément OUDOT committed
86
There is 2 types of SQL configuration backends for LemonLDAP::NG:
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
87 88
</p>
<ul>
Xavier Guimard's avatar
Xavier Guimard committed
89
<li class="level1"><div class="li"> <strong>CDBI</strong>: very simple storage (recommended)</div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
90
</li>
Xavier Guimard's avatar
Xavier Guimard committed
91
<li class="level1"><div class="li"> <strong>RDBI</strong>: triple store storage</div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
92 93
</li>
</ul>
Xavier Guimard's avatar
Xavier Guimard committed
94
<div class="notetip">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.
Clément OUDOT's avatar
Clément OUDOT committed
95 96 97 98
</div>
<p>
See <a href="changeconfbackend.html" class="wikilink1" title="documentation:2.0:changeconfbackend">how to change configuration backend</a>.
</p>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
99 100

</div>
Clément OUDOT's avatar
Clément OUDOT committed
101 102
<!-- EDIT1 SECTION "SQL configuration backends" [1-423] -->
<h2 class="sectionedit2" id="mysql">MySQL</h2>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
103 104
<div class="level2">

Clément OUDOT's avatar
Clément OUDOT committed
105 106 107 108 109
</div>
<!-- EDIT2 SECTION "MySQL" [424-442] -->
<h3 class="sectionedit3" id="perl_driver">Perl Driver</h3>
<div class="level3">

Clément OUDOT's avatar
New doc  
Clément OUDOT committed
110
<p>
Clément OUDOT's avatar
Clément OUDOT committed
111
You need DBD::MySQL Perl module:
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
112 113
</p>
<ul>
Clément OUDOT's avatar
Clément OUDOT committed
114
<li class="level1"><div class="li"> Debian:</div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
115
</li>
Clément OUDOT's avatar
Clément OUDOT committed
116 117 118 119
</ul>
<pre class="code">apt install libdbd-mysql-perl</pre>
<ul>
<li class="level1"><div class="li"> Red Hat:</div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
120 121
</li>
</ul>
Clément OUDOT's avatar
Clément OUDOT committed
122 123 124 125 126 127
<pre class="code">yum install perl-DBD-MySQL</pre>

</div>
<!-- EDIT3 SECTION "Perl Driver" [443-611] -->
<h3 class="sectionedit4" id="database_and_table_creation">Database and table creation</h3>
<div class="level3">
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
128 129

<p>
Clément OUDOT's avatar
Clément OUDOT committed
130
Create database:
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
131
</p>
Clément OUDOT's avatar
Clément OUDOT committed
132
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">DATABASE</span> lemonldap<span class="sy0">-</span>ng <span class="kw1">CHARACTER</span> <span class="kw1">SET</span> utf8;</pre>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
133

Clément OUDOT's avatar
Clément OUDOT committed
134 135 136 137
<p>
Use database to create table:
</p>
<pre class="code sql"><span class="kw1">USE</span> lemonldap<span class="sy0">-</span>ng</pre>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
138 139 140 141 142 143 144 145

</div>

<h4 id="rdbi">RDBI</h4>
<div class="level4">
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> lmConfig <span class="br0">&#40;</span>
    cfgNum <span class="kw1">INT</span><span class="br0">&#40;</span><span class="nu0">11</span><span class="br0">&#41;</span> <span class="kw1">NOT</span> <span class="kw1">NULL</span><span class="sy0">,</span>
    <span class="kw1">FIELD</span> <span class="kw1">VARCHAR</span><span class="br0">&#40;</span><span class="nu0">255</span><span class="br0">&#41;</span> <span class="kw1">NOT</span> <span class="kw1">NULL</span> <span class="kw1">DEFAULT</span> <span class="st0">''</span><span class="sy0">,</span>
Clément OUDOT's avatar
Clément OUDOT committed
146
    <span class="kw1">VALUE</span> longtext<span class="sy0">,</span>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
147 148 149 150 151 152 153 154 155
    <span class="kw1">PRIMARY</span> <span class="kw1">KEY</span> <span class="br0">&#40;</span>cfgNum<span class="sy0">,</span><span class="kw1">FIELD</span><span class="br0">&#41;</span>
    <span class="br0">&#41;</span>;</pre>

</div>

<h4 id="cdbi">CDBI</h4>
<div class="level4">
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> lmConfig <span class="br0">&#40;</span>
    cfgNum <span class="kw1">INT</span> <span class="kw1">NOT</span> <span class="kw1">NULL</span> <span class="kw1">PRIMARY</span> <span class="kw1">KEY</span><span class="sy0">,</span>
Clément OUDOT's avatar
Clément OUDOT committed
156
    <span class="kw1">DATA</span> longtext
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
157 158 159
<span class="br0">&#41;</span>;</pre>

</div>
Clément OUDOT's avatar
Clément OUDOT committed
160 161
<!-- EDIT4 SECTION "Database and table creation" [612-1104] -->
<h3 class="sectionedit5" id="grant_access">Grant access</h3>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
162 163 164 165 166
<div class="level3">

<p>
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.
</p>
Clément OUDOT's avatar
Clément OUDOT committed
167
<div class="notetip">You can use different dbiUser strings:<ul>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
168 169 170 171 172 173 174 175
<li class="level1"><div class="li"> one with read/write rights for servers hosting the manager</div>
</li>
<li class="level1"><div class="li"> one with just read rights for other servers</div>
</li>
</ul>

</div>
<p>
Clément OUDOT's avatar
Clément OUDOT committed
176
For example (suppose that our servers are in 10.0.0.0/24 network):
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
177
</p>
Xavier Guimard's avatar
Xavier Guimard committed
178
<pre class="code sql"><span class="kw1">GRANT</span> <span class="kw1">SELECT</span><span class="sy0">,</span><span class="kw1">INSERT</span><span class="sy0">,</span><span class="kw1">UPDATE</span><span class="sy0">,</span><span class="kw1">DELETE</span><span class="sy0">,</span><span class="kw1">LOCK</span> <span class="kw1">TABLES</span> <span class="kw1">ON</span> lemonldap<span class="sy0">-</span>ng<span class="sy0">.</span>lmConfig
Clément OUDOT's avatar
Clément OUDOT committed
179
  <span class="kw1">TO</span> lemonldaprw@manager<span class="sy0">.</span>host <span class="kw1">IDENTIFIED</span> <span class="kw1">BY</span> <span class="st0">'mypassword'</span>;
Xavier Guimard's avatar
Xavier Guimard committed
180
<span class="kw1">GRANT</span> <span class="kw1">SELECT</span> <span class="kw1">ON</span> lemonldap<span class="sy0">-</span>ng<span class="sy0">.</span>lmConfig
Clément OUDOT's avatar
Clément OUDOT committed
181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272
  <span class="kw1">TO</span> lemonldapro@<span class="st0">'10.0.0.%'</span> <span class="kw1">IDENTIFIED</span> <span class="kw1">BY</span> <span class="st0">'myotherpassword'</span>;</pre>

</div>
<!-- EDIT5 SECTION "Grant access" [1105-1759] -->
<h2 class="sectionedit6" id="connection_settings">Connection settings</h2>
<div class="level2">

<p>
Change configuration settings in <code>/etc/lemonldap-ng/lemonldap-ng.ini</code> file (section configuration):
</p>
<pre class="code ini"><span class="re0"><span class="br0">&#91;</span>configuration<span class="br0">&#93;</span></span>
<span class="re1">type</span> <span class="sy0">=</span><span class="re2"> RDBI</span>
<span class="re1">dbiChain</span>    <span class="sy0">=</span><span class="re2"> DBI:mysql:database=lemonldap-ng</span><span class="co0">;host=1.2.3.4</span>
<span class="re1">dbiUser</span>     <span class="sy0">=</span><span class="re2"> lemonldaprw</span>
<span class="re1">dbiPassword</span> <span class="sy0">=</span><span class="re2"> mypassword</span>
<span class="co0">; optional</span>
<span class="re1">dbiTable</span>    <span class="sy0">=</span><span class="re2"> mytablename</span></pre>

</div>
<!-- EDIT6 SECTION "Connection settings" [1760-2090] -->
<h2 class="sectionedit7" id="postgresql">PostGreSQL</h2>
<div class="level2">

</div>
<!-- EDIT7 SECTION "PostGreSQL" [2091-2114] -->
<h3 class="sectionedit8" id="perl_driver1">Perl Driver</h3>
<div class="level3">

<p>
You need DBD::Pg Perl module:
</p>
<ul>
<li class="level1"><div class="li"> Debian:</div>
</li>
</ul>
<pre class="code">apt install libdbd-pg-perl</pre>
<ul>
<li class="level1"><div class="li"> Red Hat:</div>
</li>
</ul>
<pre class="code">yum install perl-DBD-Pg</pre>

</div>
<!-- EDIT8 SECTION "Perl Driver" [2115-2274] -->
<h3 class="sectionedit9" id="database_and_table_creation1">Database and table creation</h3>
<div class="level3">

<p>
Create database:
</p>
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">DATABASE</span> lemonldap<span class="sy0">-</span>ng;</pre>

<p>
Use database to create table:
</p>
<pre class="code sql"><span class="kw1">USE</span> lemonldap<span class="sy0">-</span>ng</pre>

</div>

<h4 id="rdbi1">RDBI</h4>
<div class="level4">
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> lmconfig <span class="br0">&#40;</span>
    cfgnum <span class="kw1">INTEGER</span> <span class="kw1">NOT</span> <span class="kw1">NULL</span><span class="sy0">,</span>
    <span class="kw1">FIELD</span> text <span class="kw1">NOT</span> <span class="kw1">NULL</span><span class="sy0">,</span>
    <span class="kw1">VALUE</span> text<span class="sy0">,</span>
    <span class="kw1">PRIMARY</span> <span class="kw1">KEY</span> <span class="br0">&#40;</span>cfgNum<span class="sy0">,</span><span class="kw1">FIELD</span><span class="br0">&#41;</span>
    <span class="br0">&#41;</span>;</pre>

</div>

<h4 id="cdbi1">CDBI</h4>
<div class="level4">
<pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> lmConfig <span class="br0">&#40;</span>
    cfgnum <span class="kw1">INTEGER</span> <span class="kw1">NOT</span> <span class="kw1">NULL</span> <span class="kw1">PRIMARY</span> <span class="kw1">KEY</span><span class="sy0">,</span>
    <span class="kw1">DATA</span> text
<span class="br0">&#41;</span>;</pre>

</div>
<!-- EDIT9 SECTION "Database and table creation" [2275-2725] -->
<h2 class="sectionedit10" id="connection_settings1">Connection settings</h2>
<div class="level2">

<p>
Change configuration settings in <code>/etc/lemonldap-ng/lemonldap-ng.ini</code> file (section configuration):
</p>
<pre class="code ini"><span class="re0"><span class="br0">&#91;</span>configuration<span class="br0">&#93;</span></span>
<span class="re1">type</span> <span class="sy0">=</span><span class="re2"> RDBI</span>
<span class="re1">dbiChain</span>    <span class="sy0">=</span><span class="re2"> DBI:Pg:database=lemonldap-ng</span><span class="co0">;host=1.2.3.4</span>
<span class="re1">dbiUser</span>     <span class="sy0">=</span><span class="re2"> lemonldaprw</span>
<span class="re1">dbiPassword</span> <span class="sy0">=</span><span class="re2"> mypassword</span>
<span class="co0">; optional</span>
<span class="re1">dbiTable</span>    <span class="sy0">=</span><span class="re2"> mytablename</span></pre>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
273 274

</div>
Clément OUDOT's avatar
Clément OUDOT committed
275
<!-- EDIT10 SECTION "Connection settings" [2726-] --></div>
Clément OUDOT's avatar
New doc  
Clément OUDOT committed
276 277
</body>
</html>