This plugin allows to configure your wiki to authenticate against an existing database using PHP's PDO extension. Typical use cases are integrating DokuWiki with a web forum, blog or bug tracker.

Before you go forward to Configuration section, let's see quickly how many and what kind of real world configurations of PDO Authentication exist. The variation reflects the flexibility PDO is offering.

• Moodle – an e-learning system moodle.org
• phpBB3 – an open-source bulletin-board system phpbb.com
• Nextcloud – a private cloud system nextcloud.com
• Questions2Answers – Q&A Forum question2answer.org
• joomla – Joomla CMS
• Admidio – Admidio – Free online membership management software admidio.org
• Woltlab – commercial forum software woltlab.com

To configure this plugin, some experience with database queries and the SQL query language is required. Sample configurations for popular systems will soon be provided by the community.

The configuration consists mostly of SQL statements to access your database. The plugin itself is completely database agnostic. You can use it to access any database supported by PDO – refer to PDO Drivers to learn more.

In most of the statements named PDO placeholders are used (colon prefixed alphanumeric). These represent the actual data that will be inserted into the statement. The PDO driver takes care of proper escaping - you do not have to worry about it.

For select statements, the plugin expects certain values to be returned under specific names. Your database columns most probably differ from those required names. You need to use Aliases (with the AS keyword) in your statements to map your columns to the correct names.

Each option below comes with an example that shows how the statement would look for a fictive example database given at the end of this page.

This setting will output detailed error messages whenever something goes wrong. You most probably want to enable this during the initial configuration of the plugin. Once everything works you should definitely disable this again as it may leak important information to possible attackers.

The Data Source Name (DSN) specifies how to connect to your database. It contains the type of your database (eg. 'mysql') and where to find it (typically host and database name).

Examples:

mysql:host=localhost;dbname=mydatabase;charset=utf8
pgsql:host=localhost;port=5432;dbname=testdb
sqlite:/path/to/the/file.sqlite3

See also:

• PDO_MYSQL DSN
• PDO_PGSQL DSN
• PDO_SQLITE DSN

This option configures the user to use to connect to the database. You can leave it empty for sqlite databases.

This is the password for above user. You can leave it empty for sqlite databases.

There are a couple of settings you most probably want to tune when enabling authpdo:

• Configuration Setting: authtype – enable the plugin here
• Configuration Setting: superuser – all users are read from your database when enabling authpdo, your super user probably is named differently than your initial local wiki admin
• Configuration Setting: passcrypt – this setting defines how new passwords are saved in the database, it should match your database's application
• Configuration Setting: defaultgroup – This group is added by default to all users regardless of the groups that might be pulled from the database. Make sure that you don’t grant more rights than necessary to this group.

This plugin gains more capabilities the more of the configuration you fill in. You can leave out configuration at the expense of functionality. However a few statements are required to have minimal login functionality:

This statement is used to get a single user from the database. This statement should return exactly one row if the user exists. Or no row if it doesn't.

Example:

SELECT "id" AS "uid",
       "login" AS "user",
       "name",
       "pass" AS "hash",
       "mail"
  FROM "user"
 WHERE "login" = :user

This selects all group names a user is a member of. This statement should return as many rows as the user has groups.

Example:

SELECT "group"
  FROM "member" AS m,
       "group" AS g
 WHERE m."gid" = g."id"
   AND m."uid" = :uid

If your database uses one of the hashing mechanisms supported in DokuWiki you don't need (and shouldn't) set this option. It is only needed if you want to do the password check directly in a SQL statement. This is useful where other fields are used as salt.

This statement is used to check the password a user is providing upon login.

When you set up this setting, your select-user user statement does not need to return a hash or pass field.

This statement should return a single row on success and no rows on failure.

Example:

SELECT uid
  FROM user
 WHERE login = :user
   AND pass = MD5(CONCAT(MD5(user), MD5(:clear)))

The above minimal setup will allow your existing users to log into DokuWiki. If you want to have more functionality, you need to configure additional statements. Depending on which statements you configure, more features become available to you and your users automatically.

Here's a list on which additional statements you need for which feature:

• Adding new users
  • select-groups
  • insert-user
  • insert-group
  • join-group
• Deleting users
  • select-groups
  • leave-group
  • delete-user
• Changing user's login names
  • update-user-login
• Changing user's passwords and password resets
  • update-user-pass
• Update user profiles (full names and email addresses)
  • update-user-info
• Modify user groups
  • select-groups
  • leave-group
  • join-group
  • insert-group
• List users in the user manager
  • list-users
  • count-users

This statement takes no place holders. It should return all groups currently available in the database.

Example:

SELECT "id" AS "gid", "group"
  FROM "group"

This statement should create a new user entry in the database.

Note: the :hash placeholder is created with the mechanism set in Configuration Setting: passcrypt - be sure it fits your database.

Example:

INSERT INTO "user" ("login", "pass", "name", "mail")
     VALUES (:user, :hash, :name, :mail)

This statement removes a user from the database.

Example:

DELETE FROM "user" WHERE "id" = :uid

This statement is used to list and optionally filter the user list in the user manager. It is expected to return a list of login names matching the given filters and offsets, ordered by login name.

  SELECT DISTINCT "login" AS "user"
    FROM "user"   AS U,
         "member" AS M,
         "group"  AS G
   WHERE U."id"  = M."uid"
     AND M."gid" = G."id"
     AND G."group" LIKE :group
     AND U."login" LIKE :user
     AND U."name"  LIKE :name
     AND U."mail"  LIKE :mail
ORDER BY "login"
   LIMIT :start,:limit

This is similar to 'list-users' and should handle the same input, but instead of returning a list of users it should return only the number of users matching the given filter. There are no offset parameters here. A single result is expected.

SELECT COUNT(DISTINCT "login") AS "count"
  FROM "user"   AS U,
       "member" AS M,
       "group"  AS G
 WHERE U."id"  = M."uid"
   AND M."gid" = G."id"
   AND G."group" LIKE :group
   AND U."login" LIKE :user
   AND U."name"  LIKE :name
   AND U."mail"  LIKE :mail

This statement updates the login name of a single user.

Example:

UPDATE "user"
   SET "login" = :newlogin
 WHERE "id"    = :uid

This statement updates the profile information of a single user.

Example:

UPDATE "user"
   SET "name" = :name,
       "mail" = :mail
 WHERE "id"   = :uid

This statement updates the password of a single user.

Note: the :hash placeholder is created with the mechanism set in Configuration Setting: passcrypt - be sure it fits your database.

Example:

UPDATE "user"
   SET "pass" = :hash
 WHERE "id"   = :uid

This statement creates a new group.

INSERT INTO "group" ("group")
     VALUES (:group)

This statement adds a user to an existing group.

Example:

INSERT INTO "member" ("uid", "gid")
     VALUES (:uid, :gid)

This statement removes a user from a group.

DELETE FROM "member"
      WHERE "uid" = :uid
        AND "gid" = :gid

All Examples above are for the following very minimal sqlite database. Your real world application's database is probably more complicated than that, but this should help you to get your bearings when following the configuration info above.

CREATE TABLE user (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "login" UNIQUE NOT NULL,
    "pass" NOT NULL,
    "name" NOT NULL DEFAULT (''),
    "mail" NOT NULL DEFAULT ('')
);
CREATE TABLE member (
    "uid" INTEGER REFERENCES "user" ("id") NOT NULL,
    "gid" INTEGER REFERENCES "group" ("id") NOT NULL,
    PRIMARY KEY ("uid", "gid")
);
CREATE TABLE "group" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "group" UNIQUE NOT NULL
);

This plugin uses transactions to ensure data integrity. When something goes wrong, it will roll back the transaction and everything will be as before. Unfortunately transactions are not supported on tables using the MyISAM type in MySQL. Most modern software uses the available better storage systems from MySQL (like InnoDB) but some older software still uses MyISAM.

This means that if you let this plugin do write operations on your database (by configuring any INSERT, UPDATE or DELETE statements above), there's a slight chance of data corruption (meaning things like there could be a user created that has no group, or a user is deleted but their group membership is not). This should still be relatively rare but you have been warned.