[Bug]: sftpgo reports "NOT NULL constraint failed: users_groups_mapping.group_id" during login

[patschi]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration issue.
• This issue is not already reported on Github (I've searched it).

Bug description

After a successful login via LDAP, sftpgo fails with error:

NOT NULL constraint failed: users_groups_mapping.group_id

If I provide wrong username or LDAP-server data, it will fail with a different error. The fact it accepts the right username + password combination proofs for me that LDAP itself is working fine, only the group mapping is failing.

As the error (as in the mapping) is within sftpgo, I think this is rather a issue in sftpgo with the beta-plugin system and the auth plugin.

Steps to reproduce

1. Configure LDAP plugin
2. Configure group prefix as per LDAP auth documentation
3. Login and face error 1) Invalid credentials, please retry in UI, and 2) above error in server logs

The user is not created in the SQLite database during this login attempt.

Expected behavior

Login to work.

SFTPGo version

2.6.6-6825db76-2025-02-24T18:46:33Z

Data provider

sqlite

Installation method

Community Docker image

Configuration

services:
  sftpgo:
    image: drakkan/sftpgo:v2.6-alpine
    restart: always
    environment:
      - SFTPGO_CONFIG_DIR=/etc/sftpgo/config/
      - SFTPGO_DATA_PROVIDER__USERS_BASE_DIR=/srv/sftpgo/data/
      - SFTPGO_COMMON_TEMP_PATH=/home/sftpgo/_temp/
      - SFTPGO_COMMON_SETSTAT_MODE=1
      - SFTPGO_COMMON__DEFENDER__ENABLED=1
      - SFTPGO_COMMON__DEFENDER__DRIVER=memory
      - SFTPGO_COMMON__DEFENDER__BAN_TIME=60
      - SFTPGO_COMMON__DEFENDER__BAN_TIME_INCREMENT=100
      - SFTPGO_COMMON__DEFENDER__THRESHOLD=8
      - SFTPGO_COMMON__DEFENDER__OBSERVATION_TIME=60
      - SFTPGO_COMMON__DEFENDER__SCORE_NO_AUTH=1
      - SFTPGO_COMMON__DEFENDER__SCORE_INVALID=3
      - SFTPGO_SFTPD__ENABLED_SSH_COMMANDS="*"
      - SFTPGO_PLUGINS__0__TYPE=auth
      - SFTPGO_PLUGINS__0__AUTH_OPTIONS__SCOPE=7
      - SFTPGO_PLUGINS__0__CMD=/usr/local/bin/sftpgo-plugin-auth
      - SFTPGO_PLUGINS__0__ARGS=serve
      - SFTPGO_PLUGINS__0__AUTO_MTLS=1
      - SFTPGO_PLUGIN_AUTH_LDAP_URL=ldaps://dc01.example.com:636
      - SFTPGO_PLUGIN_AUTH_LDAP_BASE_DN=OU=EXAMPLE,DC=EXAMPLE,DC=local
      - SFTPGO_PLUGIN_AUTH_LDAP_USERNAME=CN=sftpgo-ro,OU=Service Accounts,OU=Users,OU=EXAMPLE,DC=EXAMPLE,DC=local
      - SFTPGO_PLUGIN_AUTH_LDAP_PASSWORD=PASSWORD
      - SFTPGO_PLUGIN_AUTH_LDAP_SEARCH_QUERY=(&(objectClass=user)(sAMAccountType=805306368)(sAMAccountName=%username%))
      - SFTPGO_PLUGIN_AUTH_LDAP_GROUP_ATTRIBUTES=memberOf
      - SFTPGO_PLUGIN_AUTH_MEMBERSHIP_GROUP_PREFIX=ShareMedia
      - SFTPGO_PLUGIN_AUTH_REQUIRE_GROUPS=1
      - SFTPGO_PLUGIN_AUTH_USER_REQUIREMENTS=0
      - SFTPGO_PLUGIN_AUTH_CACHE_TIME=60
      - SFTPGO_PLUGIN_AUTH_SKIP_TLS_VERIFY=1
    ports:
      - 8080
      - 2022
    volumes:
      - /opt/sftpgo/config:/etc/sftpgo
      - /opt/sftpgo/plugins/sftpgo-plugin-auth-linux-amd64:/usr/local/bin/sftpgo-plugin-auth

Auth plugin from here: https://github.com/sftpgo/sftpgo-plugin-auth

Relevant log output

Error during login:

sftpgo-1    | {"level":"debug","time":"2025-05-03T15:28:36.339","sender":"dataprovider_sqlite","message":"plugin auth completed for user \"TestUser\", elapsed: 46.91192ms, auth scope: 1"}
sftpgo-1    | {"level":"debug","time":"2025-05-03T15:28:36.402","sender":"connection_failed","client_ip":"192.168.0.64","username":"TestUser","login_type":"password","protocol":"HTTP","error":"NOT NULL constraint failed: users_groups_mapping.group_id"}

(only above lines, nothing else)

What are you using SFTPGo for?

Private user, home usecase (home backup/VPS)

Additional info

To rule out a error on my side, I did following:

1. Thoroughly read the documentation. No mistake found by now.
2. Researched the internet and found discussion LDAP/AD Primary, Secondary, Membership Group #1625 with other having similar issues but no resolution. One workaround was a exact name match with group, but that hasn't been working for me either. Also, it is not intended behavior.
3. Made sure the tested groups do exist in sftpgo AND on the LDAP server with exact same name.
4. Tried different group names: "ShareMedia", "Share-Media", "Share Media". No difference.
5. Looked through the code, but not able to figure out anything wrong on my end.
6. Re-deployed sftpgo and resetted the sqlite DB to start from scratch (ruling out any database corruption/issue).
7. Tested the LDAP server (which is Microsoft Active Directory) via ldapsearch with same LDAP server, same credentials, same baseDN, same bindDN, with memberOf, etc and it works too:

   $ ldapsearch -x -H "ldaps://dc01.example.com:636" -P 3 -LLL -o ldif_wrap=no -x -b "OU=EXAMPLE,DC=EXAMPLE,DC=local" -D "CN=sftpgo-ro,OU=Service Accounts,OU=Users,OU=EXAMPLE,DC=EXAMPLE,DC=local" -w "PASSWORD" "(&(objectClass=user)(sAMAccountType=805306368)(sAMAccountName=TestUser))" memberOf
   dn: CN=TestUser,OU=Users,OU=Users,OU=EXAMPLE,DC=EXAMPLE,DC=local
   memberOf: CN=ShareMedia,OU=Shares,OU=Groups,OU=EXAMPLE,DC=EXAMPLE,DC=local
   

[patschi]

I got some help and we noticed following:

1. Lowercase Group Matching

As per auth-plugin's description it states: "Groups are always matched in lower case.". So I was under the impression, that both sftpgo-group and LDAP-group will be converted to lower-case before matching.

This does not seem to be the case. Once I changed sftpgo-group from ShareMedia to sharemedia, it worked. So it looks like the group matching is not performing a lower-case match in either the plugin or the core code.

2. Prefix-setting not working

Even though the documentation mentions that SFTPGO_PLUGIN_AUTH_SECONDARY_GROUP_PREFIX=Share Media is the prefix, meaning it will take all groups into consideration which start with the name Share Media, this does not seem to work either.

I had group Share Media ReadOnly with above setting and it did not work. Defining share media readonly by specifying the full name, it worked.