Nextcloud SQL user authentication.
Use external database as a source for Nextcloud users and groups. Retrieve the users and groups info. Allow the users to change their passwords. Sync the users' email addresses with the addresses stored by Nextcloud.
-
SSH into your server.
-
Get into the apps folder of your Nextcloud installation, for example /var/www/nextcloud/apps.
-
Git clone this project:
git clone https://github.com/nextcloud/user_sql.git. -
Login to your Nextcloud instance as admin.
-
Navigate to Apps from the menu then find and enable the User and Group SQL Backends app.
-
Navigate to Admin from menu and switch to Additional Settings, scroll down the page and you will see SQL Backends settings.
You can skip the first three steps as this app is available in the official Nextcloud App Store.
Below are detailed descriptions of all available options.
This section contains the database connection parameters.
| Name | Description | Details |
|---|---|---|
| SQL driver | The database driver to use. Currently supported drivers are: mysql, pgsql. | Mandatory. |
| Hostname | The hostname on which the database server resides. | Mandatory. |
| Database | The name of the database. | Mandatory. |
| Username | The name of the user for the connection. | Optional. |
| Password | The password of the user for the connection. | Optional. |
Here are all currently supported options.
| Name | Description | Details |
|---|---|---|
| Allow display name change | With this option enabled user can change its display name. The display name change is propagated to the database. | Optional. Default: false. Requires: user Display name column. |
| Allow password change | Can user change its password. The password change is propagated to the database. See Hash algorithms. | Optional. Default: false. |
| Allow providing avatar | Can user provide its avatar. The value is used when column Provide avatar is not set. | Optional. Default: false. |
| Case-insensitive username | Whether user query should be case-sensitive or case-insensitive. | Optional. Default: false. |
| Reverse active column | Reverse value of active column in user table. | Optional. Default: false. |
| Use cache | Use database query results cache. The cache can be cleared any time with the Clear cache button click. | Optional. Default: false. |
| Hash algorithm | How users passwords are stored in the database. See Hash algorithms. | Mandatory. |
| Email sync | Sync e-mail address with the Nextcloud. - None - Disables this feature. This is the default option. - Synchronise only once - Copy the e-mail address to the Nextcloud preferences if its not set. - Nextcloud always wins - Always copy the e-mail address to the database. This updates the user table. - SQL always wins - Always copy the e-mail address to the Nextcloud preferences. |
Optional. Default: None. Requires: user Email column. |
| Quota sync | Sync user quota with the Nextcloud. - None - Disables this feature. This is the default option. - Synchronise only once - Copy the user quota to the Nextcloud preferences if its not set. - Nextcloud always wins - Always copy the user quota to the database. This updates the user table. - SQL always wins - Always copy the user quota to the Nextcloud preferences. |
Optional. Default: None. Requires: user Quota column. |
| Home mode | User storage path. - Default - Let the Nextcloud manage this. The default option. - Query - Use location from the user table pointed by the home column. - Static - Use static location pointed by the Home Location option. |
Optional Default: Default. |
| Home Location | User storage path for the Static Home mode. The %u variable is replaced with the username of the user. |
Mandatory if the Home mode is set to Static. |
The definition of user table. The table containing user accounts.
| Name | Description | Details |
|---|---|---|
| Table name | The table name. | Mandatory for user backend. |
| Username | Username column. | Mandatory for user backend. |
| E-mail column. | Mandatory for Email sync option. | |
| Quota | Quota column. | Mandatory for Quota sync option. |
| Home | Home path column. | Mandatory for Query Home sync option. |
| Password | Password hash column. | Mandatory for user backend. |
| Display name | Display name column. | Optional. |
| Active | Flag indicating if user can log in. | Optional. Default: true. |
| Provide avatar | Flag indicating if user can change its avatar. | Optional. Default: false. |
| Salt | Salt which is appended to password when checking or changing the password. | Optional. |
| Append salt | Append a salt to the password. | Optional. Default: false. |
| Prepend salt | Prepend a salt to the password. | Optional. Default: false. |
The group definitions table.
| Name | Description | Details |
|---|---|---|
| Table name | The table name. | Mandatory for group backend. |
| Is admin | Flag indicating if its the admin group | Optional. |
| Display name | Display name column. | Optional. |
| Group name | Group name column. | Mandatory for group backend. |
Associative table which maps users to groups.
| Name | Description | Details |
|---|---|---|
| Table name | The table name. | Mandatory for group backend. |
| Username | Username column. | Mandatory for group backend. |
| Group name | Group name column. | Mandatory for group backend. |
The basic functionality requires only one database table: User table.
For all options to work three tables are required:
If you already have an existing database you can always create database views which fits this model, but be aware that some functionalities requires data changes (update queries).
If you don't have any database model yet you can use below tables (MySQL):
CREATE TABLE sql_user
(
username VARCHAR(16) PRIMARY KEY,
display_name TEXT NULL,
email TEXT NULL,
quota TEXT NULL,
home TEXT NULL,
password TEXT NOT NULL,
active TINYINT(1) NOT NULL DEFAULT '1',
provide_avatar BOOLEAN NOT NULL DEFAULT FALSE,
salt TEXT NULL
);
CREATE TABLE sql_group
(
name VARCHAR(16) PRIMARY KEY,
display_name TEXT NULL,
admin BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE sql_user_group
(
username VARCHAR(16) NOT NULL,
group_name VARCHAR(16) NOT NULL,
PRIMARY KEY (username, group_name),
FOREIGN KEY (username) REFERENCES sql_user (username),
FOREIGN KEY (group_name) REFERENCES sql_group (name),
INDEX sql_user_group_username_idx (username),
INDEX sql_user_group_group_name_idx (group_name)
);
Thanks to this app, Nextcloud can easily integrate with Wordpress.
In the Nextcloud user table settings of SQL Backends, configure it as:
User table: wp_users
Username column: user_login
Password column: user_pass
Hash algorithm: Unix (Crypt) or Portable PHP password
It is very easy to integrate Nextcloud with JHipster.
Follow the Using the Database instructions in Using Jhipster in development to configure your database. Assume you chose MySQL as JHipster database. In the Nextcloud user table settings of SQL Backends, configure it as:
User table: jhi_users
Username column: login
Password column: password_hash
Email column: email
Active column: activated
Hash algorithm: Unix (Crypt)
Below is a table containing all of the supported hash implementations with example hashes. The hashed password is "password", the salt if required have been generated randomly.
| Hash name | Details | Hash example value |
|---|---|---|
| Cleartext | Never use this. Only for development. | password |
| Courier base64-encoded MD5 | No salt supported. | {MD5RAW}5f4dcc3b5aa765d61d8327deb882cf99 |
| Courier hexadecimal MD5 | No salt supported. | {MD5}X03MO1qnZdYdgyfeuILPmQ== |
| Courier base64-encoded SHA1 | No salt supported. | {SHA}W6ph5Mm5Pz8GgiULbPgzG37mj9g= |
| Courier base64-encoded SHA256 | No salt supported. | {SHA256}XohImNooBHFR0OVvjcYpJ3NgPQ1qq73WKhHvch0VQtg= |
| Unix (Crypt) | See crypt. | $2y$10$5rsN1fmoSkaRy9bqhozAXOr0mn0QiVIfd2L04Bbk1Go9MjdvotwBq |
| Argon2 (Crypt) | Requires PHP >= 7.2. See password_hash. | $argon2i$v=19$m=1024,t=2,p=2$NnpSNlRNLlZobnJHUDh0Sw$oW5E1cfdPzLWfkTvQFUyzTR00R0aLwEdYwldcqW6Pmo |
| Blowfish (Crypt) | See password_hash. | $2y$10$5rsN1fmoSkaRy9bqhozAXOr0mn0QiVIfd2L04Bbk1Go9MjdvotwBq |
| Extended DES (Crypt) | cDRpdxPmHpzS. | |
| MD5 (Crypt) | $1$RzaFbNcU$u9adfTY/Q6za6nu0Ogrl1/ | |
| SHA256 (Crypt) | $5$rounds=5000$VIYD0iHkg7uY9SRc$v2XLS/9dvfFN84mzGvW9wxnVt9Xd/urXaaTkpW8EwD1 | |
| SHA512 (Crypt) | $6$rounds=5000$yH.Q0OL4qbCOUJ3q$Xry5EVFva3wKnfo8/ktrugmBd8tcl34NK6rXInv1HhmdSUNLEm0La9JnA57rqwQ.9/Bz513MD4tvmmISLUIHs/ | |
| Standard DES (Crypt) | yTBnb7ab/N072 | |
| Drupal 7 | See phpass. | $S$DC7eCpJQ3SUQtW4Bp.vKb2rpeaffi4iqk9OpYwJyEoSMsezn67Sl |
| Joomla MD5 Encryption | Generates 32 chars salt. | 14d21b49b0f13e2acba962b6b0039edd:haJK0yTvBXTNMh76xwEw5RYEVpJsN8us |
| MD5 | No salt supported. | 5f4dcc3b5aa765d61d8327deb882cf99 |
| Portable PHP password | See phpass. | $P$BxrwraqNTi4as0EI.IpiA/K.muk9ke/ |
| Redmine | Requires salt. Salt value for hash in the next column is 'salt'. | 48b75edeffd8e413341d7734f0f3391e7a5da994 |
| SHA-1 | No salt supported. | 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8 |
| SHA-256 | No salt supported. | 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8 |
| SHA-512 | No salt supported. | b109f3bbbc244eb82441917ed06d618b9008dd09b3befd1b5e07394c706a8bb980b1d7785e5976ec049b46df5f1326af5a2ea6d103fd07c95385ffab0cacbc86 |
| SHA-512 Whirlpool | No salt supported. | a96b16ebb691dbe968b0d66d0d924cff5cf5de5e0885181d00761d87f295b2bf3d3c66187c050fc01c196ff3acaa48d3561ffd170413346e934a32280d632f2e |
| SSHA256 | Generates 32 chars salt. | {SSHA256}+WxTB3JxprNteeovsuSYtgI+UkVPA9lfwGoYkz3Ff7hjd1FSdmlTMkNsSExyR21KM3NvNTZ5V0p4WXJMUjFzUg== |
| SSHA512 | Generates 32 chars salt. | {SSHA512}It+v1kAEUBbhMJYJ2swAtz+RLE6ispv/FB6G/ALhK/YWwEmrloY+0jzrWIfmu+rWUXp8u0Tg4jLXypC5oXAW00IyYnRVdEZJbE9wak96bkNRVWFCYmlJNWxrdTA0QmhL |
| WoltLab Community Framework 2.x | Double salted bcrypt. | $2a$08$XEQDKNU/Vbootwxv5Gp7gujxFX/RUFsZLvQPYM435Dd3/p17fto02 |
| Whirlpool | 74dfc2b27acfa364da55f93a5caee29ccad3557247eda238831b3e9bd931b01d77fe994e4f12b9d4cfa92a124461d2065197d8cf7f33fc88566da2db2a4d6eae |
There is a vagrant box which you can use at development stage.
Add a new class in the OCA\UserSQL\Platform namespace which extends the AbstractPlatform class.
Add this driver in admin.php template to $drivers variable and in method getPlatform(Connection $connection)
of PlatformFactory class.
Create a new class in OCA\UserSQL\Crypto namespace which implements IPasswordAlgorithm interface.
Do not forget to write unit tests.
This repository contains continuation of work done in this repo. This plugin was heavily based on user_imap, user_pwauth, user_ldap and user_redmine!
Since version 4.0.0 the whole core implementation has been rewritten.
- Andreas Boehler for releasing the first version of this application
- Johan Hendriks provided his user_postfixadmin
- Ed Wildgoose for fixing possible SQL injection vulnerability