DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:authpdo

authPDO Plugin

Compatible with DokuWiki

(bundled)

plugin

Last updated on
2016-01-30
Provides
Auth

Similar to authmysql, authpgsql

Tagged with !bundled, mysql, postgres, sqlite

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.

Real World Examples

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.

Configuration

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.

General Settings

Option 'debug'

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.

Option 'dsn'

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:

Option 'user'

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

Option 'pass'

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:

Minimal SQL Setup

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:

Option 'select-user'

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.

Guaranteed Placeholders
:user the username of the user whose data is requested
Mandatory fields to return
user the user name as written in the database
hash or clear either the hashed password of the user or the clear text password1)
If you do not return this field you have to implement the Option check-pass instead.
mail the email address of the user
name the full name of the user
Optional fields to return
* you may return any arbitrary field you may need later to identify the user. Typically a user ID makes sense. Eg. we're returning a uid in the example below

Example:

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

Option 'select-user-groups'

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

Guaranteed Placeholders
:user the username of the user whose data is requested
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid from above in our example.
Mandatory fields to return
group the name of the group

Example:

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

Option 'check-pass'

:!: 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.

Guaranteed Placeholders
:user the username of the user who is trying to login
:clear the clear text password the user entered
:hash the hashed password using the hashing defined in passcrypt
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon).

Example:

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

Additional SQL Setup

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

Option 'select-groups'

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

Mandatory fields to return
group the group name as written in the database
Optional fields to return
* you may return any arbitrary field you may need later to identify the group. Typically a group ID makes sense. Eg. we're returning a gid in the example below

Example:

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

Option 'insert-user'

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

Guaranteed Placeholders
:user the username of the new user
:hash and :clear the hashed and clear text password of the user. use the placeholder you need.
:name the full name of the new user
:mail the email address of the new user

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)

Option 'delete-user'

This statement removes a user from the database.

Guaranteed Placeholders
:user the username of the user to be deleted
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid from above in our example.

Example:

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

Option 'list-users'

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.

Guaranteed Placeholders
:user A % wrapped search string to search in logins
:group A % wrapped search string to search in group names
:name A % wrapped search string to search in user full names
:mail A % wrapped search string to search in email addresses
:start The start offset to limit the results
:limit The wanted number of results
:end The end offset of wanted results (start+limit)
Mandatory fields to return
user The 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

Option 'count-users'

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.

Guaranteed Placeholders
:user A % wrapped search string to search in logins
:group A % wrapped search string to search in group names
:name A % wrapped search string to search in user full names
:mail A % wrapped search string to search in email addresses
Mandatory fields to return
count Number of users
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

Option 'update-user-login'

This statement updates the login name of a single user.

Guaranteed Placeholders
:user the current username of the user to be changed
:newlogin the new username
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid from above in our example.

Example:

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

Option 'update-user-info'

This statement updates the profile information of a single user.

Guaranteed Placeholders
:user the username of the user to be changed
:name the new full name
:mail the new email address
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid from above in our example.
Mandatory fields to return
group the name of the group

Example:

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

Option 'update-user-pass'

This statement updates the password of a single user.

Guaranteed Placeholders
:user the username of the user to be changed
:hash and :clear the hashed and clear text password of the user. use the placeholder you need.
Optional Placeholders
* Any field you returned in Option 'select-user' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid from above in our example.

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

Option 'insert-group'

This statement creates a new group.

Guaranteed Placeholders
:group the name of the new group to create
INSERT INTO "group" ("group")
     VALUES (:group)

Option 'join-group'

This statement adds a user to an existing group.

Guaranteed Placeholders
:user the username of the user to put into the group
:group the name of the group to use
Optional Placeholders
* Any field you returned in Option 'select-user' and Option 'select-groups' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid and :gid from above in our example.

Example:

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

Option 'leave-group'

This statement removes a user from a group.

Guaranteed Placeholders
:user the username of the user to remove from the group
:group the name of the group to leave
Optional Placeholders
* Any field you returned in Option 'select-user' and Option 'select-groups' is available as a placeholder here (prefixed by a colon). Eg. we're using the :uid and :gid from above in our example.
DELETE FROM "member"
      WHERE "uid" = :uid
        AND "gid" = :gid

Example Database

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
);

A Warning on MySQL MyISAM

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.

1)
you really shouldn't store clear text passwords!
plugin/authpdo.txt · Last modified: 2024-06-25 10:59 by andi

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki