This backend uses a PostgreSQL Database to store user data and passwords.

Any database that contains basic user and group information could be used with DokuWiki. How to adapt the SQL statements to match your favorite database will be discussed in this page. All configuration and SQL statements are tested with Postgres 8.1 and are based on the example database structure.

Real world examples (like GForge) can be found at the bottom of this page.

The configuration options needs to be added to conf/local.php to become used.

It will be enabled with the configuration option

$conf['authtype'] = "pgsql";

While configuring the Postgres backend for the first time, you may want to enable it's debug mode to get meaningful feedback on whats going on behind the scenes. Be sure to disable this option again after everything is set up.

$conf['auth']['pgsql']['debug'] = 1;

This option defines the Postgres server to connect to. If you are running Postgres on the same server as your DokuWiki installation, 'localhost' would be sufficient. Otherwise put in your remote server here. Keep in mind that the remote database server must allow access from your DokuWiki server. See Postgres documentation for details on this issue.

$conf['auth']['pgsql']['server'] = 'localhost';

This option defines which user DokuWiki should use to access the database.

$conf['auth']['pgsql']['user'] = 'dbuser';

Set the database password for 'user' here. Because it is entered in clear text some additional security prophylaxes should be performed.

$conf['auth']['pgsql']['password'] = 'dbpassword';

Last but not least you need to specify the database that stores all the user information.

$conf['auth']['pgsql']['database'] = 'users';

Normally password encryption is done by DokuWiki (recommended) but for some reasons it might be useful to let the database do the encryption.

If you set 'forwardClearPass' to 1 the backend expects the database to do the crypting. It will forward the clear text password to the database. Be aware of the security risk.

Set 'forwardClearPass' to '0' and DokuWiki do the password encryption. Which encryption method is used is set by the passcrypt option.

$conf['auth']['pgsql']['forwardClearPass'] = 0;

The SQL statements in this section are necessary to use DokuWiki with the PgSQL authentication backend. They are the minimum set you have to define.

This statement is used to grant or deny access to the wiki. The result should be a resultset with exact one line containing at least the password of the user. If the result is empty or contains more than one row, access will be denied. The module accesses the password as 'pass' so an alias might be necessary.

If you set 'forwardClearPass = 1' the password must be verified from the database. An additional WHERE clause will do the job: “AND pass = MD5('%p')”. A much better way is and I really recommend to do it this way is to set 'forwardClearPass = 0' and let DokuWiki do the crypting.

• %{user} will be replaced by a user name
• %{pass} will be replaced by an encrypted or clear text password (depends on 'forwardClearPass')
• %{dgroup} will be replaced by the default group name

$conf['auth']['pgsql']['checkPass']   = "SELECT pass
                                           FROM usergroup AS ug
                                           JOIN users AS u ON u.uid=ug.uid
                                           JOIN groups AS g ON g.gid=ug.gid
                                          WHERE u.login='%{user}'
                                            AND g.name='%{dgroup}'";

If you share the user database with other applications it might be useful to be able to define which user may access the Wiki. Ok, the main spirit of a Wiki is it not to limit the access and give all users the possibility to participate. But hey, you are reading an article about access control, what did you expect? So let us get the donkey from the ice.

As already mentioned we have a user database but not every user in this database should be allowed to log into the wiki. The easiest way to check this is the DokuWiki defaultgroup. Every new DokuWiki user will automatically be a member of this group so all we have to do is to check the group besides the password at login time.

The above SQL statement already does this with “g.name='%g'”.

This statement should return a resultset with exact one row containing information about one user. The field needed are:

• pass containing the encrypted or clear text password
• name the user's full name
• mail the user's email address

Keep in mind that DokuWiki will access these information through the names listed above so aliases might be necessary.

• %{user} will be replaced by a user name

$conf['auth']['pgsql']['getUserInfo'] = "SELECT pass, fullname AS name, email AS mail
                                           FROM users
                                          WHERE login='%{user}'";

This statement is used to get all groups a user is member of. The result should be a table containing all groups the given user is member of. The module accesses the group name as 'group' so an alias might be necessary.

• %{user} will be replaced by a user name

$conf['auth']['pgsql']['getGroups']   = "SELECT g.name as group
                                           FROM groups g, users u, usergroup ug
                                          WHERE u.uid = ug.uid
                                            AND g.gid = ug.gid
                                            AND u.login='%{user}'";

The SQL statements in this section are necessary to use the user manager plugin. They set up only basic support and you will only be able to get and display the user list.

This statement should return a table containing all user login names that meet certain filter criteria. The filter expressions will be added case dependent by the module. At the end a sort expression will be added.

Important is that this list contains no double entries to a user. Each user name is only allowed once in the table.

The login name will be accessed as 'user' to an alias might be necessary. No patterns will be replaced in this statement but following patters will be replaced in the filter expressions:

• %{user} in FilterLogin will be replaced by a user name
• %{name} in FilterName will be replaced by user's full name
• %{email} in FilterEmail will be replaced by user's email address
• %{group} in FilterGroup will be replaced by a group name

$conf['auth']['pgsql']['getUsers']    = "SELECT DISTINCT u.login AS user
                                           FROM users AS u 
                                      LEFT JOIN usergroup AS ug ON u.uid=ug.uid
                                      LEFT JOIN groups AS g ON ug.gid=g.gid";
$conf['auth']['pgsql']['FilterLogin'] = "u.login LIKE '%{user}'";
$conf['auth']['pgsql']['FilterName']  = "u.fullname LIKE '%{name}'";
$conf['auth']['pgsql']['FilterEmail'] = "u.email LIKE '%{email}'";
$conf['auth']['pgsql']['FilterGroup'] = "g.name LIKE '%{group}'";
$conf['auth']['pgsql']['SortOrder']   = "ORDER BY u.login";

You additionally need the SQL statements in this section if you want to add new users in the database either through openregister with the user manager.

This statement should add a user to the database. Minimum information to store are: login name, password, email address and full name.

• %{user} will be replaced by the user name
• %{pass} will be replaced by the password (encrypted or clear text, depends on 'forwardClearPass')
• %{email} will be replaced by user's email address
• %{name} will be replaced by user's full name

$conf['auth']['pgsql']['addUser']     = "INSERT INTO users
                                         (login, pass, email, fullname)
                                         VALUES ('%{user}', '%{pass}', '%{email}', '%{name}')";

Please keep in mind that if you set 'forwardClearPass = 1' the clear text password is filled in here. You should at least replace '%p' with MD5('%p') or better set 'forwardClearPass = 0' and let DokuWiki do the crypting. I really recommend the second method.

This statement should add a group to the database.

• %{group} will be replaced by a group name

$conf['auth']['pgsql']['addGroup']    = "INSERT INTO groups (name)
                                         VALUES ('%{group}')";

This statement should connect a user to a group (a user becomes member of that group).

• %{user} will be replaced by a user name
• %{uid} will be replaced by the id of a user data-set
• %{group} will be replaced by a group name
• %{gid} will be replaced by the id of a group data-set

$conf['auth']['pgsql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
                                         VALUES ('%{uid}', '%{gid}')";

This statement should remove a group from the database.

• %{group} will be replaced by the group name
• %{gid} will be replaced by the id of a group data-set

$conf['auth']['pgsql']['delGroup']    = "DELETE FROM groups
                                          WHERE gid='%{gid}'";

This statement should return the database index of a given user name. The module will access the index with the name 'id' so an alias might be necessary.

• %{user} will be replaced by the user name

$conf['auth']['pgsql']['getUserID']   = "SELECT uid AS id FROM users WHERE login='%{user}'";

This statement should return the database index of a given group name. The module will access the index with the name 'id' so an alias might be necessary.

• %{group} will be replaced by the group name

$conf['auth']['pgsql']['getGroupID']  = "SELECT gid AS id FROM groups WHERE name='%{group}'";

You additionally need the SQL statements in this section if you want to remove users from the database with the user manager.

This statement should remove a user from the database.

• %{user} will be replaced by the user name
• %{uid} will be replaced by the id of a user data-set

$conf['auth']['pgsql']['delUser']     = "DELETE FROM users
                                          WHERE uid='%{uid}'";

This statement should remove all connections from a user to any group (a user quits membership of all groups).

• %{uid} will be replaced by the id of a user data-set

$conf['auth']['pgsql']['delUserRefs'] = "DELETE FROM usergroup
                                          WHERE uid='%{uid}'";

This statements should modify a user entry in the database. The statements UpdateLogin, UpdatePass, UpdateEmail and UpdateName will be added to updateUser on demand. Only changed parameters will be used.

• %{user} will be replaces by the user name
• %{pass} will be replaced by the encrypted or clear text password (depends on 'forwardClearPass')
• %{email} will be replaced by the email address
• %{name} will be replaced by the user's full name
• %{uid} will be replaced by the user id that should be updated

$conf['auth']['pgsql']['updateUser']  = "UPDATE users SET";
$conf['auth']['pgsql']['UpdateLogin'] = "login='%{user}'";
$conf['auth']['pgsql']['UpdatePass']  = "pass='%{pass}'";
$conf['auth']['pgsql']['UpdateEmail'] = "email='%{email}'";
$conf['auth']['pgsql']['UpdateName']  = "fullname='%{name}'";
$conf['auth']['pgsql']['UpdateTarget']= "WHERE uid=%{uid}";

This statement should remove a single connection from a user to a group (a user quits membership of that group).

• %{user} will be replaced by a user name
• %{uid} will be replaced by the id of a user data-set
• %{group} will be replaced by a group name
• %{gid} will be replaced by the id of a group data-set

$conf['auth']['pgsql']['delUserGroup']= "DELETE FROM usergroup
                                          WHERE uid='%{uid}'
                                            AND gid='%{gid}'";

To have basic connectivity to a gforge database, place the following in your ./conf/local.php:

$conf['useacl']      = 1;                //Use Access Control Lists to restrict access?
$conf['openregister']= 0;                //Should users to be allowed to register?
$conf['authtype']    = 'pgsql';          //which authentication DB should be used (currently plain only)
$conf['auth']['pgsql']['server']   = '**yourserver**';
$conf['auth']['pgsql']['user']     = 'gforge';
$conf['auth']['pgsql']['password'] = '**yourpasswd**';
$conf['auth']['pgsql']['database'] = 'gforge';
$conf['auth']['pgsql']['forwardClearPass'] = 0;
$conf['passcrypt'] = 'md5';
$conf['auth']['pgsql']['checkPass']= "SELECT user_pw AS pass
                                      FROM users
                                      WHERE user_name='%{user}'";
 
$conf['auth']['pgsql']['getUserInfo'] = "SELECT user_pw AS pass, realname AS name, email AS mail
                                      FROM users
                                      WHERE user_name='%{user}'";
 
$conf['auth']['pgsql']['getGroups']   = "SELECT unix_group_name as group
                                      FROM groups a, users b, user_group c
                                      WHERE b.user_id = c.user_id
                                      AND a.group_id = c.group_id
                                      AND b.user_name='%{user}'";

The remaining user functions (add/remove/modify) should be done via the Gforge interface.

To have basic connectivity to a gforge database, place the following in your ./conf/local.php:

$conf['useacl']      = 1;                //Use Access Control Lists to restrict access?
$conf['openregister']= 0;                //Should users to be allowed to register?
$conf['authtype']    = 'pgsql';          //which authentication DB should be used (currently plain only)
$conf['auth']['pgsql']['server']   = '**yourserver**';
$conf['auth']['pgsql']['user']     = 'gforge';
$conf['auth']['pgsql']['password'] = '**yourpasswd**';
$conf['auth']['pgsql']['database'] = 'gforge';
$conf['auth']['pgsql']['forwardClearPass'] = 0;
$conf['passcrypt'] = 'md5';
 
$conf['auth']['pgsql']['checkPass'] = "
    SELECT password_md5 AS pass
    FROM \"user\" u
    WHERE u.unix_name = '%{user}' AND u.status = 1";
 
$conf['auth']['pgsql']['getUserInfo'] = "
    SELECT password_md5 AS pass, (firstname || ' ' || lastname) AS name, email AS mail
    FROM \"user\" u
    WHERE u.unix_name = '%{user}' AND u.status = 1";
 
/* Simple method: return all groups that a user is a member of
 *                (normalized to lower case)
 *  e.g.: docs test kernel
 */
$conf['auth']['pgsql']['getGroups']   = "SELECT project.unix_name as group
                                         FROM project, user_project, \"user\"
                                         WHERE project.project_id = user_project.project_id
                                               AND user_project.user_id = \"user\".user_id
                                               AND \"user\".unix_name = '%{user}'
                                               AND \"user\".status = 1";
/* More complex: return all groups that a user is a member of, and return group:role info as well
 *               (normalized to lower case and spaces converted to underscores)
 *  e.g.: docs docs:administrator test test:developer kernel kernel:senior_developer
 */
$conf['auth']['pgsql']['getGroups'] = "
    SELECT p.unix_name || ':' || replace(lower(r.role_name), ' ', '_') as group
    FROM project p
        INNER JOIN user_project up ON p.project_id = up.project_id
        INNER JOIN \"user\" u ON up.user_id = u.user_id
        INNER JOIN role r ON p.project_id = r.project_id
        INNER JOIN user_project_role upr ON upr.role_id = r.role_id AND upr.user_project_id = up.user_project_id
    WHERE
        u.unix_name = '%{user}' AND u.status = 1
    UNION SELECT p.unix_name as group
    FROM project p
        INNER JOIN user_project up ON p.project_id = up.project_id
        INNER JOIN \"user\" u ON up.user_id = u.user_id
    WHERE
        u.unix_name = '%{user}' AND u.status = 1";
 
/* This part is optional, but allows read-only user browsing via the User Manager plugin */
$conf['auth']['pgsql']['getUsers']    = "
    SELECT DISTINCT u.unix_name AS user
    FROM project p
        INNER JOIN user_project up ON p.project_id = up.project_id
        INNER JOIN role r ON p.project_id = r.project_id
        INNER JOIN user_project_role upr ON upr.role_id = r.role_id AND upr.user_project_id = up.user_project_id
        RIGHT JOIN \"user\" u ON up.user_id = u.user_id
    WHERE u.status = 1";
$conf['auth']['pgsql']['FilterLogin'] = "u.unix_name LIKE '%{user}'";
$conf['auth']['pgsql']['FilterName']  = "(u.firstname || ' ' || u.lastname) LIKE '%{name}'";
$conf['auth']['pgsql']['FilterEmail'] = "u.email LIKE '%{email}'";
$conf['auth']['pgsql']['FilterGroup'] = "p.unix_name || ':' || replace(lower(r.role_name), ' ', '_') LIKE '%{group}'";
$conf['auth']['pgsql']['SortOrder']   = "ORDER BY u.unix_name";

The remaining user functions (add/remove/modify) should be done via the Gforge interface.

This is a very simple example database.

CREATE TABLE users (
  uid SERIAL NOT NULL,
  login VARCHAR(20) NOT NULL,
  pass VARCHAR(255) NOT NULL,
  fullname VARCHAR(255) NOT NULL DEFAULT '',
  email VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (uid),
  UNIQUE (login)
);
 
CREATE TABLE groups (
  gid SERIAL NOT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY  (gid),
  UNIQUE (name)
);
 
CREATE TABLE usergroup (
  uid INTEGER NOT NULL REFERENCES users, 
  gid INTEGER NOT NULL REFERENCES groups,
  PRIMARY KEY  (uid,gid)
);