auth:pgsql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
auth:pgsql [2022-08-16 15:25] – Insert “will” in “Dokuwiki will do the” 2600:6c50:1b40:2700:590c:1779:bdda:d0ce | auth:pgsql [Unknown date] (current) – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== PostgreSQL Authentication Backend ====== | ||
- | | Since the release 2013-05-10 “Weatherwax”\\ see [[plugin: | ||
- | |||
- | 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|example database]] structure. | ||
- | |||
- | [[#Real world examples]] (like GForge) can be found at the bottom of this page. | ||
- | |||
- | ===== Configuration ===== | ||
- | |||
- | The configuration options needs to be added to conf/ | ||
- | |||
- | ==== Enable PgSQL ==== | ||
- | |||
- | It will be enabled with the configuration option | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | 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. | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | This option defines the Postgres server to connect to. If you are running Postgres on the same server as your DokuWiki installation, | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | This option defines which user DokuWiki should use to access the database. | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | Set the database password for ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | Last but not least you need to specify the database that stores all the user information. | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== Option ' | ||
- | |||
- | 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 ' | ||
- | |||
- | Set ' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ===== SQL User Authentication ===== | ||
- | |||
- | 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. | ||
- | |||
- | ==== checkPass ==== | ||
- | |||
- | 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 ' | ||
- | |||
- | If you set ' | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | 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=' | ||
- | AND g.name=' | ||
- | </ | ||
- | |||
- | 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 '' | ||
- | |||
- | The above SQL statement already does this with " | ||
- | |||
- | ==== getUserInfo ==== | ||
- | |||
- | This statement should return a resultset with exact one row containing information about one user. The field needed are: | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | Keep in mind that DokuWiki will access these information through the names listed above so aliases might be necessary. | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM users | ||
- | WHERE login=' | ||
- | </ | ||
- | |||
- | ==== getGroups ==== | ||
- | |||
- | 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 ' | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | FROM groups g, users u, usergroup ug | ||
- | WHERE u.uid = ug.uid | ||
- | AND g.gid = ug.gid | ||
- | AND u.login=' | ||
- | </ | ||
- | |||
- | ===== SQL Basic User Manager Support ===== | ||
- | |||
- | 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. | ||
- | |||
- | ==== getUsers ==== | ||
- | |||
- | 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 ' | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | 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[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ===== SQL Support for Add User ===== | ||
- | |||
- | You additionally need the SQL statements in this section if you want to add new users in the database either through [[config: | ||
- | |||
- | ==== addUser ==== | ||
- | |||
- | This statement should add a user to the database. Minimum information to store are: login name, password, email address and full name. | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | Please keep in mind that if you set ' | ||
- | |||
- | ==== addGroup ==== | ||
- | |||
- | This statement should add a group to the database. | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | </ | ||
- | |||
- | ==== addUserGroup ==== | ||
- | |||
- | This statement should connect a user to a group (a user becomes member of that group). | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | | ||
- | </ | ||
- | |||
- | ==== delGroup ==== | ||
- | |||
- | This statement should remove a group from the database. | ||
- | |||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE gid=' | ||
- | </ | ||
- | |||
- | ==== getUserID ==== | ||
- | |||
- | This statement should return the database index of a given user name. The module will access the index with the name ' | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== getGroupID ==== | ||
- | |||
- | This statement should return the database index of a given group name. The module will access the index with the name ' | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ===== SQL Support for Delete User ===== | ||
- | |||
- | You additionally need the SQL statements in this section if you want to remove users from the database with the user manager. | ||
- | |||
- | ==== delUser ==== | ||
- | |||
- | This statement should remove a user from the database. | ||
- | |||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | </ | ||
- | |||
- | ==== delUserRefs ==== | ||
- | |||
- | This statement should remove all connections from a user to any group (a user quits membership of all groups). | ||
- | |||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | </ | ||
- | |||
- | ===== SQL Support for Modify User ===== | ||
- | |||
- | ==== updateUser ==== | ||
- | |||
- | This statements should modify a user entry in the database. The statements UpdateLogin, | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | ==== delUserGroup ==== | ||
- | |||
- | This statement should remove a single connection from a user to a group (a user quits membership of that group). | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | WHERE uid=' | ||
- | AND gid=' | ||
- | </ | ||
- | |||
- | ===== Real World Examples ===== | ||
- | |||
- | ==== GForge 4 ==== | ||
- | |||
- | To have basic connectivity to a gforge database, place the following in your '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | FROM users | ||
- | WHERE user_name=' | ||
- | |||
- | $conf[' | ||
- | FROM users | ||
- | WHERE user_name=' | ||
- | |||
- | $conf[' | ||
- | 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=' | ||
- | </ | ||
- | |||
- | The remaining user functions (add/ | ||
- | |||
- | ==== GForge 5 ==== | ||
- | |||
- | To have basic connectivity to a gforge database, place the following in your '' | ||
- | |||
- | <code php> | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | |||
- | $conf[' | ||
- | SELECT password_md5 AS pass | ||
- | FROM \" | ||
- | WHERE u.unix_name = ' | ||
- | |||
- | $conf[' | ||
- | SELECT password_md5 AS pass, (firstname || ' ' || lastname) AS name, email AS mail | ||
- | FROM \" | ||
- | WHERE u.unix_name = ' | ||
- | |||
- | /* Simple method: return all groups that a user is a member of | ||
- | | ||
- | | ||
- | */ | ||
- | $conf[' | ||
- | FROM project, user_project, | ||
- | WHERE project.project_id = user_project.project_id | ||
- | AND user_project.user_id = \" | ||
- | AND \" | ||
- | AND \" | ||
- | /* More complex: return all groups that a user is a member of, and return group:role info as well | ||
- | | ||
- | | ||
- | */ | ||
- | $conf[' | ||
- | SELECT p.unix_name || ':' | ||
- | FROM project p | ||
- | INNER JOIN user_project up ON p.project_id = up.project_id | ||
- | INNER JOIN \" | ||
- | 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 = ' | ||
- | UNION SELECT p.unix_name as group | ||
- | FROM project p | ||
- | INNER JOIN user_project up ON p.project_id = up.project_id | ||
- | INNER JOIN \" | ||
- | WHERE | ||
- | u.unix_name = ' | ||
- | |||
- | /* This part is optional, but allows read-only user browsing via the User Manager plugin */ | ||
- | $conf[' | ||
- | 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 \" | ||
- | WHERE u.status = 1"; | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | $conf[' | ||
- | </ | ||
- | |||
- | The remaining user functions (add/ | ||
- | |||
- | ===== Example Database ===== | ||
- | |||
- | This is a very simple example database. | ||
- | |||
- | <code sql> | ||
- | 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) | ||
- | ); | ||
- | </ |