Add support for variables from mod_sql in mod_sftp

[Sebbo94BY]

What I Did

Configure the SQL and SFTP module and try to access the homedir, which gets returned from the SQL module / query when authenticating a user.

What I Expected/Wanted

I would like to use the returned values of the SQL query in form of variables in the SFTP configuration.

Example:

# /etc/proftpd/conf.d/sql.conf 
<IfModule mod_sql.c>
    SQLBackend          mysql
    SQLPasswordEngine   on
    SQLEngine           on
    SQLPasswordEncoding hex
    SQLAuthenticate     users
    SQLAuthTypes        Crypt
    SQLConnectInfo      database_name@database_host:3306 db_user db_pass
    SQLUserInfo         webdav_user user_name password user_id gid homedir shell
</IfModule>

The above SQL configuration returns a homedir, which I need for the SFTP pubkey configuration:

# /etc/proftpd/conf.d/sftp.conf
<IfModule mod_sftp.c>
    <VirtualHost 0.0.0.0>
        SFTPEngine on
        Port 2223

        Include /etc/proftpd/conf.d/sql.conf

        SFTPAuthMethods password publickey
        RequireValidShell off

        SFTPAuthorizedUserKeys file:/nfs/%{env:sql.homedir}/.ssh/authorized_keys

        ...
    </VirtualHost>
</IfModule>

%u is here unfortunately not sufficient as the entire folder structure can be very dynamic, so it does not only the username differ in the path.

ProFTPD Version and Configuration

$ sudo proftpd -V
Compile-time Settings:
  Version: 1.3.8 (stable)
  Platform: LINUX [Linux 6.1.0-13-cloud-amd64 x86_64]
  OS/Release:
    PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
    NAME="Debian GNU/Linux"
    VERSION_ID="12"
    VERSION="12 (bookworm)"
    VERSION_CODENAME=bookworm
    ID=debian
  Built: Wed Jun 28 2023 13:17:54 UTC
  Built With:
    configure  '--infodir=/share/info' '--disable-option-checking' '--disable-silent-rules' '--libdir=/lib/x86_64-linux-gnu' '--disable-dependency-tracking' '--prefix=/usr' '--with-pkgconfig=lib/pkgconfig' '--with-includes=/usr/include/postgresql:/usr/include/mariadb:/usr/include/mariadb/mysql' '--mandir=/usr/share/man' '--sysconfdir=/etc/proftpd' '--localstatedir=/run' '--libexecdir=/usr/lib/proftpd' '--enable-sendfile' '--enable-facl' '--enable-dso' '--enable-autoshadow' '--enable-ctrls' '--enable-openssl' '--enable-ipv6' '--enable-nls' '--enable-memcache' '--with-lastlog=/var/log/lastlog' '--enable-pcre2' '--disable-strip' '--enable-redis' '--build' 'x86_64-linux-gnu' '--with-shared=mod_unique_id:mod_site_misc:mod_load:mod_ban:mod_quotatab:mod_sql:mod_sql_mysql:mod_sql_postgres:mod_sql_sqlite:mod_sql_odbc:mod_dynmasq:mod_quotatab_sql:mod_ldap:mod_quotatab_ldap:mod_ratio:mod_tls:mod_rewrite:mod_radius:mod_wrap:mod_wrap2:mod_wrap2_file:mod_wrap2_sql:mod_quotatab_file:mod_quotatab_radius:mod_facl:mod_ctrls_admin:mod_copy:mod_deflate:mod_ifversion:mod_geoip:mod_exec:mod_sftp:mod_sftp_pam:mod_sftp_sql:mod_shaper:mod_sql_passwd:mod_ifsession:mod_auth_otp:mod_tls_redis:mod_wrap2_redis:mod_redis:mod_memcache:mod_tls_memcache:mod_readme:mod_snmp:mod_digest:mod_ident:mod_log_forensic:mod_qos:mod_statcache:mod_tls_fscache:mod_tls_shmcache:mod_dnsbl' 'build_alias=x86_64-linux-gnu' 'CFLAGS=-g -O2 -ffile-prefix-map=/src/proftpd-dfsg-1.3.8+dfsg=. -fstack-protector-strong -Wformat -Werror=format-security' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -ffile-prefix-map=/src/proftpd-dfsg-1.3.8+dfsg=. -fstack-protector-strong -Wformat -Werror=format-security'

  CFLAGS: -g2 -g -O2 -ffile-prefix-map=/src/proftpd-dfsg-1.3.8+dfsg=. -fstack-protector-strong -Wformat -Werror=format-security -Wall -fno-omit-frame-pointer -fno-strict-aliasing -Werror=implicit-function-declaration
  LDFLAGS: -Wl,-L$(top_srcdir)/lib,-L$(top_builddir)/lib -Wl,-z,relro -Wl,-z,now -rdynamic  -L/usr/lib/x86_64-linux-gnu/ -L/usr/lib/x86_64-linux-gnu
  LIBS: -lacl  -lpcre2-posix -lpcre2-8 -lssl -lcrypto -lsodium -lcap  -lpam -lattr -lidn2 -lnsl -lresolv -lresolv -lcrypt -lhiredis -lmemcachedutil -lmemcached 

  Files:
    Configuration File:
      /etc/proftpd/proftpd.conf
    Pid File:
      /run/proftpd.pid
    Scoreboard File:
      /run/proftpd.scoreboard
    Header Directory:
      /usr/include/proftpd
    Shared Module Directory:
      /usr/lib/proftpd

  Info:
    + Max supported UID: 4294967295
    + Max supported GID: 4294967295

  Features:
    + Autoshadow support
    + Controls support
    + curses support
    - Developer support
    + DSO support
    + IPv6 support
    + Largefile support
    + Lastlog support
    + Memcache support
    + ncursesw support
    + NLS support
    + OpenSSL support (OpenSSL 3.0.11 19 Sep 2023)
    - PCRE support
    + PCRE2 support
    + POSIX ACL support
    + Redis support
    + Sendfile support
    + Shadow file support
    + Sodium support
    + Trace support
    + xattr support

  Tunable Options:
    PR_TUNABLE_BUFFER_SIZE = 1024
    PR_TUNABLE_DEFAULT_RCVBUFSZ = 8192
    PR_TUNABLE_DEFAULT_SNDBUFSZ = 8192
    PR_TUNABLE_ENV_MAX = 2048
    PR_TUNABLE_GLOBBING_MAX_MATCHES = 100000
    PR_TUNABLE_GLOBBING_MAX_RECURSION = 8
    PR_TUNABLE_HASH_TABLE_SIZE = 40
    PR_TUNABLE_LOGIN_MAX = 256
    PR_TUNABLE_NEW_POOL_SIZE = 512
    PR_TUNABLE_PATH_MAX = 4096
    PR_TUNABLE_SCOREBOARD_BUFFER_SIZE = 80
    PR_TUNABLE_SCOREBOARD_SCRUB_TIMER = 30
    PR_TUNABLE_SELECT_TIMEOUT = 30
    PR_TUNABLE_TIMEOUTIDENT = 10
    PR_TUNABLE_TIMEOUTIDLE = 600
    PR_TUNABLE_TIMEOUTLINGER = 10
    PR_TUNABLE_TIMEOUTLOGIN = 300
    PR_TUNABLE_TIMEOUTNOXFER = 300
    PR_TUNABLE_TIMEOUTSTALLED = 3600
    PR_TUNABLE_XFER_SCOREBOARD_UPDATES = 10

[Castaglia]

Hmm. I'll take a look to see if this can be done simply -- it may not.

My first thought was that you might consider using mod_sftp_sql, such that the SFTP public keys are also stored in your SQL database, rather than on the filesystem (NFS-mounted or otherwise).

[Castaglia]

If you are looking to have more of your ProFTPD configured obtained from a SQL database, you might also look into https://github.com/Castaglia/proftpd-mod_conf_sql Although that module is more for pulling the entire ProFTPD configuration, at startup time, from a SQL database, rather than querying for only user-specific portions of configuration from a SQL database in the middle of some session.