Linux implements vsFTP service based on MySQL storage user

Store ftp virtual accounts based on database

  • Use text comparison low
  • It is more convenient to use mysql to backup and manage
  • Use 8.0 without encryption function, can not encrypt
  • Realization of database connection based on pam module
  • The pam_mysql module system does not have it by default
  • Not supported on cnetOS8
  • Database CHAR does not support case distinction by default, need to add binary

Environmental preparation

  • vsftp CentOS7 10.0.0.183
  • mysql (below 8.0) or mariadb 10.0.0.199

Install and deploy the database

Install the database and set it to start on boot

yum -y install mariadb-server.x86_64
systemctl enable --now mariadb.service

Create ftp virtual user database and table in the database

MariaDB [(none)]> create database vsftpd;
Query OK, 1 row affected (0.00 sec)

MariaDB [vsftpd]> create table users(
    -> id int auto_increment not null primary key,
    -> name char(50) binary not null,
    -> passwd char(50) binary not null);
Query OK, 0 rows affected (0.00 sec)


MariaDB [vsftpd]> create table ftper(
    -> id int auto_increment not null primary key,
    -> uname char(50) binary not null,
    -> passwd char(50) binary not null);
Query OK, 0 rows affected (0.00 sec)

Insert a row in the table, create an ftp user and corresponding password

MariaDB [vsftpd]> insert into users(name,passwd) values('a',passwd('aaaaaa'));
Query OK, 1 row affected (0.01 sec)

MariaDB [vsftpd]> insert into users(name,passwd) values('b',passwd('bbbbbb'));
Query OK, 1 row affected (0.00 sec)

Add a virtual user. For security, you should use the passwd function to encrypt its password and store it

MariaDB [vsftpd]> insert into ftper(uname,passwd) values('a','aaaaaa');
Query OK, 1 row affected (0.00 sec)

MariaDB [vsftpd]> insert into ftper(uname,passwd) values('b','bbbbbb');
Query OK, 1 row affected (0.01 sec)
  • The results of using the passwd function and not using the inserted data are significantly different
MariaDB [vsftpd]> select * from users;
+----+------+-------------------------------------------+
| id | name | passwd                                    |
+----+------+-------------------------------------------+
|  1 | a    | *B1461C9C68AFA1129A5F968C343636192A084ADB |
|  2 | b    | *43FF523A5DA566E2AA8F14756C0BBD08C522F5F1 |
+----+------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [vsftpd]> select * from ftper;
+----+-------+--------+
| id | uname | passwd |
+----+-------+--------+
|  1 | a     | aaaaaa |
|  2 | b     | bbbbbb |
+----+-------+--------+
2 rows in set (0.00 sec)

Create a database account for ftp query database

MariaDB [vsftpd]> grant select on vsftpd.* to [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [vsftpd]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • Test whether the database can be seen by remote login
[[email protected] ~]# mysql -h 10.0.0.199 -uvsftpd -p'123456'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| vsftpd             |
+--------------------+
3 rows in set (0.00 sec)

Compile and install the pam_mysql module

Download the pam_mysql module

wget http://prdownloads.sourceforge.net/pam-mysql/pam_mysql-0.7RC1.tar.gz

  • unzip
tar xvf pam_mysql-0.7RC1.tar.gz 
pam_mysql-0.7RC1/
pam_mysql-0.7RC1/NEWS
pam_mysql-0.7RC1/pam_mysql.spec.in
pam_mysql-0.7RC1/aclocal.m4
pam_mysql-0.7RC1/README
pam_mysql-0.7RC1/ltmain.sh
pam_mysql-0.7RC1/configure
pam_mysql-0.7RC1/configure.in
pam_mysql-0.7RC1/config.guess
pam_mysql-0.7RC1/install-sh
pam_mysql-0.7RC1/config.sub
pam_mysql-0.7RC1/missing
pam_mysql-0.7RC1/mkinstalldirs
pam_mysql-0.7RC1/Makefile.am
pam_mysql-0.7RC1/Makefile.in
pam_mysql-0.7RC1/config.h.in
pam_mysql-0.7RC1/pam_mysql.c
pam_mysql-0.7RC1/pkg.m4
pam_mysql-0.7RC1/acinclude.m4
pam_mysql-0.7RC1/stamp-h.in
pam_mysql-0.7RC1/INSTALL
pam_mysql-0.7RC1/ChangeLog
pam_mysql-0.7RC1/COPYING
pam_mysql-0.7RC1/pam_mysql.spec
pam_mysql-0.7RC1/CREDITS
  • View readme
 cat ./pam_mysql-0.7RC1/README 
pam_mysql - A PAM authentication module against MySQL database.
$Id: README,v 1.8.2.9 2006/01/09 10:35:59 moriyoshi Exp $

Introduction
------------
This is a successor of the "old" pam_mysql module, which comes with
a more stable, secure and robust implementation.
## 太长不看
  • Check install to understand the installation method
cat ./pam_mysql-0.7RC1/INSTALL 
$Id: INSTALL,v 1.3 2005/06/13 10:21:55 moriyoshi Exp $

Typical:

1. Run "configure" script. The following parameters are accepted.

    --with-pam=[PAM_INSTALLATION_PREFIX]

        Specifies where the PAM headers required to build
        the package are installed. This may help when you are
        building a system in a different root. This can be
        omitted to turn on auto-detection.

    --with-pam-mods-dir=[MODULE_DIRECTORY]

        Specifies where to install the product
        (pam_mysql.so). This can be omitted also.

    --with-mysql=[MYSQL_INSTALLATION_PREFIX]

        Specifies where the required MySQL headers and
        libraries are installed. If not given, configure search
        to find them amongst the hard-coded prefixes in the
        following order:

        . /usr
        . /usr/local
        . /usr/mysql
        . /opt/mysql

    --with-openssl=[OPENSSL_INSTALLATION_PREFIX]

        Specifies where the OpenSSL headers and libraries are
        installed. If the option is supplied but no prefix is
        explicitly given, configure tries to search them amongst
        the hard-coded prefixes in the following order:

        . /usr
        . /usr/local
        . /opt/openssl
        . /usr/ssl
        . /usr/local/ssl

    --with-cyrus-sasl=[CYRUS_SASL_INSTALLATION_PREFIX]
    --with-cyrus-sasl2=[CYRUS_SASL2_INSTALLATION_PREFIX]

        Specifies where the Cyrus SASL headers and libraries, which
        will be used for MD5 calculation facility, are installed.
        If the option is supplied but no prefix is explicitly given,
        configure tries to search them amongst the hard-coded prefixes
        in the following order:

        . /usr
        . /usr/local
        . /opt/cyrus-sasl

        Note that these two options cannot be specified together, and
        it is discouraged to use this feature in order to avoid cross
        dependency and symbol conflict as PAM may be called from within
        a SASL client library.

2. make install

3. Make proper changes to an intended configuration file in /etc/pam.d/
   or /etc/pam.conf.

Via RPM commands:

1. Copy the source archive (.tar.gz / .tar.bz2) to the "SOURCES" directory
   within the system dependent RPM build directory.
   That is typically /usr/src/redhat in Red Hat / Fedora Core distros.

2. Copy the spec file (pam_mysql.spec), that has been generated during
   configuration to the "SPECS" directory, that is also found in the same
   RPM build directory as explained in the previous step.

3. Chdir to the SPECS directory

4. Run rpmbuild.

   $ rpmbuild -ba pam_mysql.spec

5. Install the binary-rpm that was produced in the "RPMS" directory, ditto.

  (via ordinary make install)

6. Make proper changes to an intended configuration file in /etc/pam.d/
   or /etc/pam.conf.

First yum install dependent packages

yum -y install vsftpd gcc gcc-c++ make mariadb-devel pam-devel

Compile and install pam_mysql

  • Enter the installation directory, run ./configure to add modules to compile
    ./configure --with-pam-mods-dir=/lib64/security
  • make && make install to install
[[email protected] pam_mysql-0.7RC1]# make -j 4 && make install 
/bin/sh ./libtool --mode=compile gcc -DHAVE_CONFIG_H -I. -I. -I. -I/usr/include/security -I/usr/include  -g -O2  -g -O2 -I/usr/include/mysql    -c pam_mysql.c
mkdir .libs
 gcc -DHAVE_CONFIG_H -I. -I. -I. -I/usr/include/security -I/usr/include -g -O2 -g -O2 -I/usr/include/mysql -c pam_mysql.c  -fPIC -DPIC -o .libs/pam_mysql.o
pam_mysql.c: In function 'pam_mysql_converse':
pam_mysql.c:3192:4: warning: passing argument 2 of 'conv->conv' from incompatible pointer type [enabled by default]
    conv->appdata_ptr))) {
    ^
pam_mysql.c:3192:4: note: expected 'const struct pam_message **' but argument is of type 'struct pam_message **'
/bin/sh ./libtool --mode=link gcc  -g -O2 -I/usr/include/mysql     -o pam_mysql.la -rpath /lib64/security -module -avoid-version pam_mysql.lo  -L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto    -lcrypt
gcc -shared  .libs/pam_mysql.o  -L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto -lcrypt  -Wl,-soname -Wl,pam_mysql.so -o .libs/pam_mysql.so
creating pam_mysql.la
(cd .libs && rm -f pam_mysql.la && ln -s ../pam_mysql.la pam_mysql.la)
make[1]: Entering directory `/root/pam_mysql-0.7RC1'
/bin/sh ./mkinstalldirs /lib64/security
/bin/sh ./libtool  --mode=install /usr/bin/install -c pam_mysql.la /lib64/security/pam_mysql.la
/usr/bin/install -c .libs/pam_mysql.so /lib64/security/pam_mysql.so
/usr/bin/install -c .libs/pam_mysql.lai /lib64/security/pam_mysql.la
PATH="$PATH:/sbin" ldconfig -n /lib64/security
----------------------------------------------------------------------
Libraries have been installed in:
   /lib64/security

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,--rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
make[1]: Nothing to be done for `install-data-am'.
make[1]: Leaving directory `/root/pam_mysql-0.7RC1'

Configure the pam module to access the database

  • Establish and call the pam module file to write the identity verification code to call the pam module,
  • Write the user name and password, database address, database name, column name encryption method
    vim /etc/pam.d/vsftpd.mysql
auth required pam_mysql.so user=vsftpd passwd=123456 host=10.0.0.199 db=vsftpd table=users usercolumn=name passwdcolumn=passwd crypt=2
account required pam_mysql.so user=vsftpd passwd=123456 host=10.0.0.199 db=vsftpd table=users usercolumn=name passwdcolumn=passwd crypt=2
  • Configuration field description
auth 表示认证
account 验证账号密码正常使用
required 表示认证要通过
pam_mysql.so模块是默认的相对路径,是相对/lib64/security/路径而言,也可以写绝对路径;后面为给此模块传递的参数
user=vsftpd为登录mysql的用户
passwd=123456 登录mysql的的密码
host=mysqlserver mysql服务器的主机名或ip地址
db=vsftpd 指定连接msyql的数据库名称
table=users 指定连接数据库中的表名
usercolumn=name 当做用户名的字段
passwdcolumn=password 当做用户名字段的密码
crypt=2 密码的加密方式为mysql password()函数加密

Install and deploy vsftp

Ensure that the vsftp service is available

  • Install vsftp and set it to start on boot
[[email protected] pam_mysql-0.7RC1]# systemctl status vsftpd.service 
● vsftpd.service - Vsftpd ftp daemon
   Loaded: loaded (/usr/lib/systemd/system/vsftpd.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[[email protected] pam_mysql-0.7RC1]# systemctl enable --now  vsftpd.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/vsftpd.service to /usr/lib/systemd/system/vsftpd.service.
[[email protected] pam_mysql-0.7RC1]# systemctl status vsftpd.service 
● vsftpd.service - Vsftpd ftp daemon
   Loaded: loaded (/usr/lib/systemd/system/vsftpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-06-02 23:11:50 CST; 3s ago
  Process: 7267 ExecStart=/usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf (code=exited, status=0/SUCCESS)
 Main PID: 7268 (vsftpd)
   CGroup: /system.slice/vsftpd.service
           └─7268 /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf

Jun 02 23:11:50 vsftp183 systemd[1]: Starting Vsftpd ftp daemon...
Jun 02 23:11:50 vsftp183 systemd[1]: Started Vsftpd ftp daemon.

Configure FTP virtual user

  • Create a mapped dedicated system account on the ftp server
    useradd -s /sbin/nologin -d /data/ftproot -r vuser
  • Edit the main configuration file to enable the virtual user guest_enable
    vim /etc/vsftpd/vsftpd.conf
#pam_service_name=vsftpd                                                                            
userlist_enable=YES
tcp_wrappers=YES
#添加下面两项
guest_enable=YES
guest_username=vuser
##修改下面一项,原系统用户无法登录
pam_service_name=vsftpd.mysql

Configure ftp virtual directory

  • Edit the main configuration file to add a virtual user configuration file directory
    vim /etc/vsftpd/vsftpd.conf
#配置vsftpd为虚拟用户使用配置文件目录
user_config_dir=/etc/vsftpd/conf.d/
  • Add a configuration file with the same name for each virtual user to the virtual user configuration file directory, and fill in the three lines of the mapping directory local_root and the writable permission anon
cat > /etc/vsftpd/conf.d/a << SUN
> anon_upload_enable=YES
> anon_mkdir_write_enable=YES
> anon_other_write_enable=YES
> #登录目录改变至指定的目录
> local_root=/data/ftprootA
> SUN
  • Create respective root directories for users, and create subdirectories in them, because the root directory cannot have write permissions
mkdir -pv /data/ftproot{A..B}/upload
mkdir: created directory ‘/data/ftprootA/upload’
mkdir: created directory ‘/data/ftprootB/upload’
  • Add ftp system mapping directory for virtual directory to add write permission
    setfacl -m u:vuser:rwx /data/ftproot{A..B}/upload

Add new account

  • Add a record in the database to add new users

test

  • Server view port to confirm that it is open
ss -ntl
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN     0      100            127.0.0.1:25                                 *:*                  
LISTEN     0      128                    *:111                                *:*                  
LISTEN     0      128                    *:22                                 *:*                  
LISTEN     0      100                [::1]:25                              [::]:*                  
LISTEN     0      128                 [::]:111                             [::]:*                  
LISTEN     0      32                  [::]:21                              [::]:*                  
LISTEN     0      128                 [::]:22                              [::]:*   
  • Connect using ftp client
Opening FTP connection to 10.0.0.183
FTP login with username a
<<<  220 (vsFTPd 3.0.2)

>>>  USER a
<<<  331 Please specify the password.

>>>  PASS ***********
<<<  230 Login successful.

>>>  FEAT
<<<  211-Features:
 EPRT
 EPSV
 MDTM
 PASV
 REST STREAM
 SIZE
 TVFS
 UTF8
211 End