Configure SFTP to home directories for all users

Trying to setup SFTP for all users of a new Nextcloud installation.

We have users authenticating with LDAP/AD integration and they are able to login fine.

What we want to have happen is the following:

User logs in and they have an external storage pre-configured to mount their own home directory from an SFTP server, which uses the same username and password since it is LDAP/AD integrated.

I set External Storage to SFTP.
I set Authentication to: Login-in Credentials, save in session
I set Configuration to the sftp server name and the path to /path/to/home/folders/$user (actual path /mnt/tank/share/$user)

I was hoping that putting $user at the end of the path would change to the current login user’s name, thereby mounting their own Home Directory.

I get a Green Dot showing it is working, but when I then login as a user, that external storage does not work. It just refreshes and shows the regular root of their nextcloud folder.

If I login as a user and setup a personal external storage using the same settings, but using the path of /mnt/tank/share/torda (actual username) it works.

The problem is, we are trying to roll this out to students, and we don’t want them all to have to configure this setup, but want it to work from being setup globally from all users.

Is there some other variable I should be using instead of $user?

Thanks!
Neil


Nextcloud version : 12.0.0
Operating system and version : Red Hat Enterprise Linux 3.10.0-514.26.2.el7
Apache or nginx version : Apache/2.4.6 (Red Hat Enterprise Linux)
PHP version : PHP 7.0.20

I modified the path so it is just /mnt/tank/share/ and it will then show a list of all possible folders. This isn’t desirable as there will be a few hundred users.

If I put in the path /mnt/tank/share/torda it shows the torda folder when logged in as torda, but when logging in as a different user, it won’t show the folder (because the credentials don’t match)

Is there any variable I can use at the end of the path so that it will automatically substitute the currently logged in user’s name?

Trying to bump this one more time… Anyone have an idea about how the admin can setup an external SFTP storage, but have it map to currently logged in user’s home folder on the SFTP server?

I only found a feature request for S3 storage:

I haven’t found anything for SFTP. In earlier versions it was possible to modify the mount.json manually to hack this in (I’m not sure if this still works, I wouldn’t use it in a productive environment).

@nickvergessen @jospoortvliet can give you perhaps a few more details if and when such an option is available.

Thanks tflidd,

I will look into the mount.json file to see what can be done. Reading the other thread it looks like they are talking about adding the external storage so it shows up at the root of the nextcloud folder (instead of in a subfolder)

That isn’t exactly what I am looking for. What I need is so that the site admin can configure an external storage that points to an SFTP server, and replaces a variable in the ‘path’ so that it mounts the currently logged in user’s home folder on the SFTP server. The user’s nextcloud userid is the same as the sftp userid.

for example:
my userid on nextcloud is torda
my userid on sftp server is torda
my home directory on the sftp server is: /path/to/homes/torda

In the admin External Storage configuration section, there is a field where you put in the path to the folder you want to mount, and it uses the authentication mechanism you choose. The authentication mechanism I am choosing is “Log-in credentials, save in session”

What I am looking to do is have that same "log-in credintials’ variable set at the end of the path for the mount.

using the example above, the path might say:
/path/to/homes/‘log-in credentials’ and it would then replace that ‘log-in credentials’ with torda

Some parts were moved to the database, these changes were made before the fork of Nextcloud:

ownCloud has an app to realize this in the enterprise version. I’m not sure what the best way would be for NC, if there is an option to create such an app easily. During the day you can perhaps catch one of the developers on IRC.

Just wanted to give an update on this issue.

We ended up looking at all the DB tables that were updated when setting up a personal external storage.
We then crafted an sql query that would pull the SAMAccount name, UUID and Folder information and generate the correct entries into all the necessary tables.
Took a while to figure out what we needed to add where, but once we had it figured out, we configured all the connections necessary by adding it right to the database.
Tested with several random users and spot checks. Seems to have done the trick.
Thanks for the assistance

Would you be willing to provide/post the SQL query(ies) necessary to perform this? It would be very helpful.

One of my co-workers actually did the insertions using a powershell script. He removed the names of our servers/databases/passwords, but you should be able to see what he was doing with the attached script.
A few bits to point out:

  1. near the end of the script you will see /path/to/homes… replace that with whatever your home directory path on the sftp server equals.
  2. “Vault Desktop” is the label for the external storage we give the students. This can be whatever you want, it is just what we are calling it.

Hope this information helps:

function Connect-MySQL {

Param(
  [Parameter(Mandatory = $true)][string]$Username,
  [Parameter(Mandatory = $true)][string]$Password,
  [Parameter(Mandatory = $true)][string]$Hostname,
  [Parameter(Mandatory = $true)][string]$Database
)

$ConnectionString = "server=" + $Hostname + ";port=3306;uid=" + $Username + ";pwd=" + $Password + ";database="+$Database

Try {
    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()

    Write-Host "`nConnected to $Database on $Hostname as $Username`n" -ForegroundColor cyan

    $Connection
  }

Catch {
  Write-Host "Unable to connect to $Database on $Hostname using $Username`n$($Error[0])" -ForegroundColor red
 }

}

function Get-MySQL {

Param(
  [Parameter(Mandatory = $true)][string]$Query,
  [Parameter(Mandatory = $true)]$Connection
)

Try {
    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data")
    $DataSet.Tables[0]
}

Catch {
  Write-Host "Error with query '$Query'`n$($Error[0])" -ForegroundColor red
 }

}

function Disconnect-MySQL {

Param(
  [Parameter(Mandatory = $true)]$Connection
)

$Connection.Close()
Write-Host "`nConnection to $($Connection.DataSource) closed`n" -ForegroundColor cyan

}

$ProdDBConnection = @{ Username=“dbuser”;Password=“dbpass”;Database=“dbname”;Hostname=“dbhost” }

if ($c = Connect-MySQL @ProdDBConnection) {

$mountID = (Get-MySQL -Connection $c -Query "SELECT MAX(mount_id) AS maxmount FROM oc_external_applicable").maxmount

$accounts = Get-MySQL -Connection $c -Query "SELECT * FROM oc_accounts WHERE uid NOT IN (SELECT a.value FROM oc_external_applicable AS a LEFT JOIN oc_external_mounts AS m ON a.mount_id = m.mount_id WHERE m.mount_point = '/Vault Desktop')"

ForEach ($account in $accounts) {

    $accountID = $account.uid

    if ($accountID -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")) {

        $mountID++

        $accountUsername = ($account.data | ConvertFrom-JSON).email.value.Split("@")[0]

        Write-Host ("[{0}] {1} - {2}" -f $mountID.ToString("000000"),$accountID,$accountUsername)

        Get-MySQL -Connection $c -Query "INSERT INTO oc_external_applicable (``mount_id``,``type``,``value``) VALUES ('$mountID',3,'$accountID');"
        Get-MySQL -Connection $c -Query "INSERT INTO oc_external_options (``mount_id``,``key``,``value``) VALUES ($mountID,'encrypt','true'),($mountID,'previews','true'),($mountID,'enable_sharing','false'),($mountID,'filesystem_check_changes','1'),($mountID,'encoding_compatibility','false');"
        Get-MySQL -Connection $c -Query "INSERT INTO oc_external_mounts (``mount_id``,``mount_point``,``storage_backend``,``auth_backend``,``priority``,``type``) VALUES ($mountID,'/Vault Desktop','sftp','password::sessioncredentials',100,2)"
        Get-MySQL -Connection $c -Query "INSERT INTO oc_external_config (``mount_id``,``key``,``value``) VALUES ($mountID,'host','vault'),($mountID,'root','/path/to/homes/$accountUsername')"

    }

}

Disconnect-MySQL -Connection $c

}

1 Like

Thank you very much for your assistance and the willingness to post your
work. I only needed the insert commands, but I’m sure the rest of the
script will be helpful to the community.