user_sql

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.

Getting Started

1. SSH into your server.

2. Get into the apps folder of your Nextcloud installation, for example /var/www/nextcloud/apps.

3. Git clone this project: git clone https://github.com/nextcloud/user_sql.git.

4. Login to your Nextcloud instance as admin.

5. Navigate to Apps from the menu then find and enable the User and Group SQL Backends app.

6. 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.

Configuration

Below are detailed descriptions of all available options.

Database connection

This section contains the database connection parameters.

Options

Here are all currently supported options.

User table

The definition of user table. The table containing user accounts.

Group table

The group definitions table.

User group table

Associative table which maps users to groups.

Integrations

The basic functionality requires only one database table: User table.

For all options to work three tables are required:

• User table,
• Group table,
• User group table.

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). Please note that the optional username above really is only used for password matching and defaults to be equal to the uid column. You also may want to compare with the oc_users and oc_groups table from you Nextcloud instance.

CREATE TABLE sql_user
(
  uid            VARCHAR(64) 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',
  disabled       TINYINT(1)  NOT NULL DEFAULT '0',
  provide_avatar BOOLEAN     NOT NULL DEFAULT FALSE,
  salt           TEXT        NULL
);

CREATE TABLE sql_group
(
  gid   VARCHAR(64)  PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  admin BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE sql_user_group
(
  uid   VARCHAR(64),
  gid   VARCHAR(64),
  PRIMARY KEY (uid, gid),
  FOREIGN KEY (uid) REFERENCES sql_user (uid),
  FOREIGN KEY (gid) REFERENCES sql_group (gid),
  INDEX user_group_username_idx (uid),
  INDEX user_group_group_name_idx (gid)
);

WordPress

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

JHipster

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)

Hash algorithms

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.

Development

Testing environment

There is a vagrant box which you can use at development stage.

New database driver support

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.

New hash algorithm support

Create a new class in OCA\UserSQL\Crypto namespace which implements IPasswordAlgorithm interface. Do not forget to write unit tests.

Acknowledgments

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.

Credits

• Andreas Boehler for releasing the first version of this application
• Johan Hendriks provided his user_postfixadmin
• Ed Wildgoose for fixing possible SQL injection vulnerability
• Brandon Lee for implementing feature to separate uid from username resolving issues #108 & #121