Repository files navigation

This is an app for Nextcloud that offers user management and authentication witharbitrary SQL queries.

You can authenticate, create, delete users, change their password or displayname, basically do (almost) everything that Nextcloud can do with users.

In contrast to the appSQL user backend, you write the SQL queries yourself.You are not limited by assumptions that app authors made about how your db isstructured.

The app uses prepared statements and is written to be secure by default toprevent SQL injections. It understands the most popular standards for passwordhash formats: MD5-CRYPT, SHA256-CRYPT, SHA512-CRYPT, BCrypt, Argon2i andArgon2id. Because the various formats are recognized on-the-fly your db can canhave differing hash string formats at the same time, which eases migration tonewer formats.

This app primarily supports PostgreSQL and MariaDB/MySQL but the underlying PHPmechanism also supportsFirebird, MS SQL, Oracle DB, ODBC, DB2, SQLite, Informix and IBM databases. Byusing an appropriate DSN you should be able to connect to these databases. Thishas not been tested, though.

SeeCHANGELOG.md for changes in newer versions. This app followssemantic versioning and there should not be any breaking changes unless themajor version has changed.

You can findUser Backend SQL Raw in theSecurity category of the Nextcloudapp store inside your Nextcloud instance.

This app has no user interface. All configuration is done via Nextcloud's systemconfiguration inconfig/config.php. This app uses the config keyuser_backend_sql_raw. The following code shows a complete configuration withall optional parameters commented out.

'user_backend_sql_raw' =>array('dsn' =>'pgsql:host=/var/run/postgresql;dbname=theNameOfYourUserDb',//'db_user' => 'yourDatabaseUser',//'db_password' => 'thePasswordForTheDatabaseUser',//'db_password_file' => '/path/to/file/ContainingThePasswordForTheDatabaseUser','queries' =>array('get_password_hash_for_user' =>'SELECT password_hash FROM users_fqda WHERE fqda = :username','user_exists' =>'SELECT EXISTS(SELECT 1 FROM users_fqda WHERE fqda = :username)','get_users' =>'SELECT fqda FROM users_fqda WHERE (fqda ILIKE :search) OR (display_name ILIKE :search)',//'set_password_hash_for_user' => 'UPDATE users SET password_hash = :new_password_hash WHERE local = split_part(:username, \'@\', 1) AND domain = split_part(:username, \'@\', 2)',//'delete_user' => 'DELETE FROM users WHERE local = split_part(:username, \'@\', 1) AND domain = split_part(:username, \'@\', 2)',//'get_display_name' => 'SELECT display_name FROM users WHERE local = split_part(:username, \'@\', 1) AND domain = split_part(:username, \'@\', 2)',//'set_display_name' => 'UPDATE users SET display_name = :new_display_name WHERE local = split_part(:username, \'@\', 1) AND domain = split_part(:username, \'@\', 2)',//'count_users' => 'SELECT COUNT (*) FROM users',//'get_home' => '',//'create_user' => 'INSERT INTO users (local, domain, password_hash) VALUES (split_part(:username, \'@\', 1), split_part(:username, \'@\', 2), :password_hash)',  ),//'hash_algorithm_for_new_passwords' => 'bcrypt', ),

There are three types of configuration parameters:

thatUser Backend SQL Raw will connect to.

• dsn: check how to construct DSNs forPostgreSQL andMySQL.
• db_user: user that will be used to connect to the database
• db_password: password for the user that will be used to connect to the database
• db_password_file: Can be set to read the password from a file
  • Only the first line of the file specified bydb_password_file is read.
  • Not more than 100 characters of the first line are read.
  • Whitespace-like characters aretrimmed fromthe beginning and end of the read password.

There are two methods to configure the database connection:

1. Setdsn to a DSN that contains the entire db connnection configuration including the db user and db password
2. Setdsn to a DSN that contains everythingbut the db user and db password and then setdb_user anddb_password/db_password_file

PostgreSQL works with method 1 and 2. MySQL works only with method 2. If you usedb_password_file also setdb_user (even for PostgreSQL) and don't put the username in the DSN. This is because, the underlying PDO classes have some quirks and diverge from the documented behaviour. So, better don't mix both methods.db_password_file has higher priority thandb_password, but lower priority than password in DSN. But it's better to only set one source for the password, for the same reasons.

• connect to PostgreSQL via a socket with ident authentication which requires no user or password at all:

  'dsn' =>'pgsql:host=/var/run/postgresql;dbname=theNameOfYourUserDb',

• connect to PostgreSQL via TCP and user/password authentication:

  'dsn' =>'pgsql:host=localhost;port=5432;dbname=theNameOfYourUserDb;user=theNameOfYourDbUser;password=thePasswordForTheDbUser',

• connect to PostgreSQL via TCP and user/password authentication and use password file:

  'dsn' =>'pgsql:host=localhost;port=5432;dbname=theNameOfYourUserDb','db_user' =>'theNameOfYourDbUser','db_password_file' =>'/path/to/password_file',

• connect to MySQL via socket which requires no user or password at all:

  'dsn' =>'mysql:unix_socket=/var/run/mysql/mysql.sock;dbname=theNameOfYourUserDb',

• connect to MySQL via TCP and user/password authentication:

  'dsn' =>'mysql:host=localhost;port=3306;dbname=testdb','db_user' =>'theNameOfYourDbUser','db_password' =>'thePasswordForTheDbUser',// or db_password_file instead

For other databases check theirPDO driver documentation pages which in-turn link to their respective DSN references. They either use method 1 or method 2 AFAICS.

that will be used to read/write data.

• queries use named parameters. You have to use the exact names as shown in the examples. Forexample, to retrieve the hash for a user, the query namedget_password_hash_for_user will beused. Write your custom SQL query and simply put:username where you are referring to theusername (aka uid) of the user trying to login.
• You don't need to supply all queries. For example, if you use the default user home simply leavethe queryget_home commented. This app will recognize this andcommunicateto Nextcloud that this feature is not available.
  • user_exists andget_users are required, the rest is optional.
  • For user authentication (i.e. login) you need at leastget_password_hash_for_user,user_exists andget_users.
• For all queries that read data, only the first column is interpreted.
• Two queries require a little bit of attention:
  1. user_exists should return a boolean. See the example on how to do this properly.
  2. get_users is a query that searches for usernames (e.g.bob) and display names (e.g.BobBobson) and returns usernames
     • make sure the query looks through both usernamesand display names, see example config
     • do case insensitive pattern matching, i.e.ILIKE (ILIKE only available in PostgreSQL)
     • query must not already contain aLIMIT orOFFSET. They will be added to the end ofyour query by this app
     • specify theLIKE without%, they will be added by the app. This is due to how preparedstatements work. Again, see the example.
• Technical Info: Queries are passed verbatim to theprepare() method of a PDO object.

used for the creation of new passwords.

• is optional and, if you leave it empty, defaults tobcrypt ($2y$).
• Other supported hash algorithms are MD5-CRYPT, SHA-256-CRYPT, SHA-512-CRYPT, Argon2i and Argon2id.The config values aremd5,sha256,sha512,argon2i,argon2id respectively, e.g.'hash_algorithm_for_new_passwords' => 'argon2id',. Or you can explicitly setbcrypt.
• This parameter only sets the hash algorithm for the creation of new passwords. Forchecking an existing password the hash algorithm will bedetected automaticallyand all common crypt formats are recognized.
  • This means, that your db can have different hash formats simultaneously. Whenever auser's password is changed, it will be updated to the configured hash algorithm. This easesmigration to more modern algorithms.

• Password length is limited to 100 characters to prevent denial of service attacks against theweb server. Without a limit, malicious users could feed your Nextcloud instance with passwords that have a length of tens of thousands of characters, which could cause a veryhigh load due to expensive password hashing operations.
• The username during user creation (create_user) and the display name (set_display_name) arenot limited in length. You should limit this on the db layer.

• TL;DR: check the log file
• This app has no UI, therefore all error output (exceptions and explicit logs) is written toNextcloud's log,by default/var/www/nextcloud/data/nextcloud.log or/var/log/syslog. Log level 3 is sufficient for all non-debug output.
• There are no semantic checks for the SQL queries. As soon as a query stringis not empty the app assumes that it is a query and executes it. It's likely that you willhave typos in your SQL queries. Check the log to find out if and why SQL queries fail.
• Pro Tip: usejq to parse and format Nextcloud's JSON logfile

  • if not installed:apt install jq

  • watch logfile starting at the bottom:

    jq -C'select (.app=="user_backend_sql_raw")' /var/www/nextcloud/data/nextcloud.log| less -R +G

    • -C enables colored output, later forless-R keeps it
    • theselect defines a filter to only show entries where the keyapp is set to this app's name
    • +G jumps to end of file
    • less does not auto-update, you need to quit usingq and start again
• This app also logs non-SQL configuration errors, e.g. missing db name.