Various databases can be used as long as they implement the database plugin interfaces.

Database Tables (scroll down for complete schema and discussion):

Note that, in users, othergroups is a comma-separated list of group ids.

Here is how to insert some example data in your database using its REPL:

 1INSERT INTO ldapgroups(name, gidnumber)
 2  VALUES('superheros', 5501);
 3INSERT INTO ldapgroups(name, gidnumber)
 4  VALUES('svcaccts', 5502);
 5INSERT INTO ldapgroups(name, gidnumber)
 6  VALUES('civilians', 5503);
 7INSERT INTO ldapgroups(name, gidnumber)
 8  VALUES('caped', 5504);
 9INSERT INTO ldapgroups(name, gidnumber)
10  VALUES('lovesailing', 5505);
11INSERT INTO ldapgroups(name, gidnumber)
12  VALUES('smoker', 5506);
13INSERT INTO includegroups(parentgroupid, includegroupid)
14  VALUES(5503, 5501);
15INSERT INTO includegroups(parentgroupid, includegroupid)
16  VALUES(5504, 5502);
17INSERT INTO includegroups(parentgroupid, includegroupid)
18  VALUES(5504, 5501);
19INSERT INTO users(name, uidnumber, primarygroup, passsha256)
20  VALUES('hackers', 5001, 5501,
21    '6478579e37aff45f013e14eeb30b3cc56c72ccdc310123bcdf53e0333e3f416a');
22INSERT INTO users(name, uidnumber, primarygroup, passsha256)
23  VALUES('johndoe', 5002, 5502,
24    '6478579e37aff45f013e14eeb30b3cc56c72ccdc310123bcdf53e0333e3f416a');
25INSERT INTO users(name, mail, uidnumber, primarygroup, passsha256)
26  VALUES('serviceuser', "serviceuser@example.com", 5003, 5502,
27    '652c7dc687d98c9889304ed2e408c74b611e86a40caa51c4b43f1dd5913c5cd0');
28INSERT INTO users(name, uidnumber, primarygroup, passsha256, othergroups)
29  VALUES('user4', 5004, 5504,
30    '652c7dc687d98c9889304ed2e408c74b611e86a40caa51c4b43f1dd5913c5cd0',
31    '5505,5506');
32INSERT INTO capabilities(userid, action, object)
33  VALUES(5001, "search", "ou=superheros,dc=glauth,dc=com");
34INSERT INTO capabilities(userid, action, object)
35  VALUES(5003, "search", "*");

This should be equivalent to this configuration:

 1[[users]]
 2  name = "hackers"
 3  uidnumber = 5001
 4  primarygroup = 5501
 5  passsha256 = "6478579e37aff45f013e14eeb30b3cc56c72ccdc310123bcdf53e0333e3f416a" # dogood
 6    [[users.capabilities]]
 7    action = "search"
 8    object = "ou=superheros,dc=glauth,dc=com"
 9 
10[[users]]
11  name = "johndoe"
12  uidnumber = 5002
13  primarygroup = 5502
14  passsha256 = "6478579e37aff45f013e14eeb30b3cc56c72ccdc310123bcdf53e0333e3f416a" # dogood
15 
16[[users]]
17  name = "serviceuser"
18  mail = "serviceuser@example.com"
19  uidnumber = 5003
20  passsha256 = "652c7dc687d98c9889304ed2e408c74b611e86a40caa51c4b43f1dd5913c5cd0" # mysecret
21  primarygroup = 5502
22    [[users.capabilities]]
23    action = "search"
24    object = "*"
25 
26[[users]]
27  name = "user4"
28  uidnumber = 5003
29  primarygroup = 5504
30  othergroups = [5505, 5506]
31  passsha256 = "652c7dc687d98c9889304ed2e408c74b611e86a40caa51c4b43f1dd5913c5cd0" # mysecret
32    [[users.customattributes]]
33    employeetype = ["Intern", "Temp"]
34    employeenumber = [12345, 54321]
35 
36[[groups]]
37  name = "superheros"
38  gidnumber = 5501
39 
40[[groups]]
41  name = "svcaccts"
42  gidnumber = 5502
43 
44[[groups]]
45  name = "civilians"
46  gidnumber = 5503
47  includegroups = [ 5501 ]
48 
49[[groups]]
50  name = "caped"
51  gidnumber = 5504
52  includegroups = [ 5502, 5501 ]

and LDAP should return these memberOf values:

 1uid: hackers
 2ou: superheros
 3memberOf: cn=caped,ou=groups,dc=militate,dc=com
 4memberOf: cn=civilians,ou=groups,dc=militate,dc=com
 5memberOf: cn=superheros,ou=groups,dc=militate,dc=com
 6 
 7uid: johndoe
 8ou: svcaccts
 9memberOf: cn=caped,ou=groups,dc=militate,dc=com
10memberOf: cn=svcaccts,ou=groups,dc=militate,dc=com
11 
12uid: serviceuser
13ou: caped
14memberOf: cn=caped,ou=groups,dc=militate,dc=com
15 
16uid: user4
17ou: caped
18memberOf: cn=caped,ou=groups,dc=militate,dc=com
19memberOf: cn=lovesailing,ou=groups,dc=militate,dc=com
20memberOf: cn=smoker,ou=groups,dc=militate,dc=com

If you have the ldap client package installed, this can be easily confirmed by running

1ldapsearch -LLL -H ldap://localhost:3893 -D cn=serviceuser,ou=svcaccts,dc=glauth,dc=com -w mysecret -x -bdc=glauth,dc=com cn=hackers

and so on.

Database Schema

users table

this table contains all LDAP information pertaining to user accounts, including links to other tables

ldapgroups table

this table represents primary and secondary LDAP groups

includegroups table

this table is used to represent groups containing other groups and inheriting their attributes

capabilities table

this table is used to retrieve capabilities granted to users linked to it from the users table

Discussion: database schema

While GLAuth is not meant to support millions of user accounts, some decent performance is still expected! In fact, when searching through records using a database query, we should see a performance of O(log n) as opposed to, when searching through a flat config, O(n).

While it would be friendlier to offer related attributes in joined tables, we may end up re-creating a “browse” scenario unintentionally.

For instance, when retrieving custom attributes, we could go through an attribute table: custattr[userid, attribute, value#n]

However, this means that a join statement between the account table and the custom attribute table would yield the cartesian product of each account x attributes; we would need to iterate through the results and collate them.

Alternatively, in Postgres and MySQL, we could rely on the database engine’s built-in support for crosstab which pivots the second table’s results into corresponding columns. This would not be supported in SQLite and would also mean building pretty nasty execution plans.

So, what’s the decision?

In GLAuth 2.x, when including information that does not benefit from being normalized (e.g. custom attributes) we are following the “nosql” trend (irony!) of storing this data in a JSON structure.