FreeBSD学习笔记29-pureftpd的mysql认证

If you never heard about MySQL before, *DON'T* enable MySQL support in
Pure-FTPd. MySQL is useless if you don't have to manage many shared
accounts. But well... if you want to learn about MySQL anyway, here's a good
starting point:  .


       ------------------------ MYSQL SUPPORT ------------------------


Since release 0.99.1, Pure-FTPd has a built-in support for MySQL databases.
When MySQL is enabled, all account info is fetched from a central MySQL
database.

To compile the server with MySQL support, you first have to build and
install the MySQL client libraries. MySQL is freely available from
 and binary packages are included in many major
distributions. But if you choose a binary form, don't forget to also install
the development packages if they are available separately.

Then, configure Pure-FTPd with --with-mysql and your favorite extra gadgets:


    ./configure --with-mysql --with-cookie --with-throttling --with-ratios


If your MySQL libraries are installed in a special path, you can specify it
like this:


    ./configure --with-mysql=/opt/mysql


In this example, headers (like mysql.h) will be searched in
/opt/mysql/include and /opt/mysql/include/mysql, while related libraries
will be searched in /opt/mysql/lib and /opt/mysql/lib/mysql .

Then, install the server as usual:


                                 make install


 ------------------------ MYSQL CONFIGURATION FILE ------------------------
           

Before running the server, you have to create a configuration file. Why a
configuration file instead of simple command-line options? you may ask.
Because for security reasons, you may want to hide how to connect to your
MySQL server. And as command-line options can be discovered by local users
(with 'ps auxwww' for instance), it's more secure to use a configuration
file for sensitive data. Keep it readable only by root (chmod 600) .

Here's a sample configuration file:

#MYSQLServer     localhost
#MYSQLPort       3306
MYSQLSocket     /tmp/mysql.sock
MYSQLUser       root
MYSQLPassword   rootpw
MYSQLDatabase   pureftpd
MYSQLCrypt      cleartext
MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID     SELECT Gid FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"

Have a look at the sample pureftpd-mysql.conf configuration file for
explanations of every keyword.

Save the configuration file anywhere. Let's say /etc/pureftpd-mysql.conf .

Then, you have to run the pure-ftpd command with '-l mysql:' (it's an 'ell'
not a 'one') followed by the path of that configuration file. Here's an
example with tcpserver:


tcpserver -DHRl0 0 21 /usr/local/bin/pure-ftpd -l mysql:/etc/pureftpd-mysql.conf &


You can mix different authentication methods. For instance, if you want to
use system (/etc/passwd) accounts when an account is not found in a MySQL
database, use -l mysql:/etc/pureftpd-mysql.conf -l unix


     ------------------------ TABLES STRUCTURES ------------------------
     
     
Pure-FTPd is very flexible and users can be stored in any way in SQL tables.
You just have to have fields with the following info:

- The user's login.

- The user's password, in plaintext, MD5, crypt()ed or MySQL's password()
format. Pure-FTPd also accepts the "any" value for the MySQLCrypt field.
With "any", all hashing functions (not plaintext) are tried.

* RECOMMENDATION: On Solaris systems and on very old C libraries, use MySQL
MD5 hashing. On all other systems, better use crypt(), which adds a salt.
Avoid password() whoose hash function is rather weak, not portable, and it is
supposed to be only used for internal accounts of MySQL servers. password() is
no more supported by Pure-FTPd with MySQL 4.1.0 and later.

- The system uid to map the user to. This can be a numeric id or an user
name, looked up at run-time.

- The system gid (numeric or not) .

- The home directory.

Here's a dump of a simple table to handle this:

CREATE TABLE users (
  User VARCHAR(16) BINARY NOT NULL,
  Password VARCHAR(64) BINARY NOT NULL,
  Uid INT(11) NOT NULL default '-1',
  Gid INT(11) NOT NULL default '-1',
  Dir VARCHAR(128) BINARY NOT NULL,
  PRIMARY KEY  (User)
);

Uid and Gid can be char() instead of int() if you want to use names instead
of values.

Then, in the pureftpd-mysql.conf configuration file, you have to provide SQL
templates to fetch the needed info.

Let's take the previous example:

MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID     SELECT Gid FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"

For each query:

\L is replaced by the login of an user trying to authenticate.
\I is replaced by the IP address the client connected to.
\P is replaced by the port number the client connected to.
\R is replaced by the remote IP address the client connected from.
\D is replaced by the remote IPv4 address, as a long decimal number.

You can mix all of these to store info in various tables. For instance, with
\I, you can have a different table for every domain, so that joe@domain1
won't be the same account than joe@domain2 . And with \R, you can restrict
one account to one specific address.

With MySQL 4.1 and later, multiple statements can be used using a semicolumn
(";") as a delimiter.

Please note that a login can only contains common characters: A...Z, a...z,
0...9, -, ., _, space, :, @ and ' . For security purposes, other characters
are forbidden.

You can also remove uid and gid fields in your tables and use default
values instead (thus saving useless lookups) . Two directives are
useful to serve that purpose: MYSQLDefaultUID and MYSQLDefaultGID.

Obvious example:

MYSQLDefaultUID 1000
MYSQLDefaultGID 1000

Using these directives overrides MYSQLGetUID and MYSQLGetGID.


     ------------------------ PER-USER SETTINGS ------------------------


Individual settings can be set for every user, using optional queries.

- MySQLGetQTAFS is the maximal number of files an user can store in his home
directory.

Example:
MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="\L"

- MySQLGetQTASZ is the maximal disk usage, in Megabytes.

Example:
MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User="\L"

- MySQLGetRatioUL and MySQLGetRatioDL are optional ratios.

Example:
MySQLGetRatioUL SELECT ULRatio FROM users WHERE User="\L"
MySQLGetRatioDL SELECT DLRatio FROM users WHERE User="\L"

- MySQLGetBandwidthUL and MySQLGetBandwidthDL are optional upload and
download bandwidth restrictions. Returned values should be in KB/s.

Example:
MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="\L"
MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="\L"

- MySQLForceTildeExpansion is yet another optional feature, to enable "~"
expansion in paths. 0 disables it (default), 1 enables it. Only enable this
if real (system) users and virtual (MySQL) users match. In all other cases,
don't enable it blindly.


       ------------------------ TRANSACTIONS ------------------------


If you upgraded your tables to transaction-enabled tables, you can configure
Pure-FTPd to take advantage of transactions. That way, you can be sure that
all info parsed by the server is complete even if you're updating it at the
same time.

To enable transactions, add this line:

MySQLTransactions On

Don't enable transactions on tables that still are in ISAM or MyISAM
formats. Transactions are only working with newer backends (Gemini, InnoDB,
BerkeleyDB...) and in recent MySQL versions.


     ------------------------ STORED PROCEDURES ------------------------


Mike Goins says:

To get pure-ftp to use a MySQL 5 stored procedure, use statements like:

MYSQLGetDir   CALL get_path_from_name("\L")
instead of
MYSQLGetDir   SELECT user_dir FROM user WHERE user_name="\L"

Note that this requires the type of Stored Procedure that returns a result set
in a single call as opposed to the two call method:
CALL sp('value', @a); SELECT @a


      ------------------------ ANONYMOUS USERS ------------------------


If you want to accept anonymous users on your FTP server, you don't need to
have any 'ftp' user in the MySQL directory. But you need to have a system
'ftp' account on the FTP server.


        ------------------------ ROOT USERS ------------------------


If a MySQL user entry has a root (0) uid and/or gid, Pure-FTPd will refuse
to log him in.

Without this preventive restriction, if your MySQL server ever gets
compromised, the attacker could also easily compromise the FTP server.

Security barriers are also implemented to avoid bad implications if wrong
data types (eg. binary blobs instead of plain text) are fetched with SQL
queries.




          -Frank DENIS .

=======================================================================================================

原文出自：1816个人主页技术交流论坛。具体见：

正文如下：

　　　　　PureFTPd 系列中文文档之 README.MySQL ---- 配合MySQL使用 Pure-FTPd

　　　　　　　　　吴伟; jeffwu_cn@hotmail 2004.4.12

--------------------------------------------------------------------------------
中文版声明：本文根据 Pure-FTPd 1.0.18 源代码中的同名英文原文翻译而来（英文原文所在
的原代码压缩包可以通过  获得），遵循GPL协议。鼓
励复制、传播、分发和修改，不过请保留作者署名和本声明。
欢迎来信交流：[e-mail]
--------------------------------------------------------------------------------

如果你之前从来没有听说过 MySQL ，请不要在 Pure-FTPd 中启用 MySQL 支持。如果你不
需要管理大量的共享帐号的话，MySQL支持是没有多少用的。但是，好...，如果你想要了
解任何有关MySQL的信息的话， 是一个好的起点。

------------------------------- MYSQL 支持 ------------------------------------

从 0.99.1 版本起，Pure-FTPd 内建了 MySQL 数据库的支持。当 MySQL 启用时，所有的
帐户信息都从一个中心 MySQL 数据库中获取。

编译带 MySQL 支持的服务器时，你必须首先编译和安装 MySQL 客户端库。MySQL 可从 
 免费获得，而且二进制包也被包括在了许多主要的发布包中了。
不过，如果你选择了一个二进制形式的话，在开发包和安装包分离的情况下，不要忘了把
开发包也安装上。

然后，使用 --with-mysql 选项和其他你喜爱的小配件来配置 Pure-FTPd ：

    ./configure --with-mysql --with-cookie --with-throttling --with-ratios

如果你的 MySQL 库被安装在一个特别的路径中，你可以像这样来指定它：

    ./configure --with-mysql=/opt/mysql

在这个例子中，头文件（像 mysql.h）将在目录 /opt/mysql/include 和 
/opt/mysql/include/mysql 中查找，相关的库文件将在目录 /opt/mysql/lib 和
/opt/mysql/lib/mysql 中查找。

然后，像通常一样安装服务器：

                                 make install

---------------------------- MYSQL 配置文件 -----------------------------------

在运行服务器前，你必须创建一个配置文件。你也许会问，为什么要用配置文件代替简单
的命令行参数呢？因为安全问题，你可能需要隐藏 Pure-FTPd 是怎么连接到你的 MySQL数
据库的。在本地用户能发现命令的命令行选项（例如使用 'ps auxwww'）的情况下，为敏
感的数据使用配置文件可能会更安全。确保配置文件是仅 root 用户可读的（chmod 600）。

下面是一个配置文件的例子：

#MYSQLServer     localhost
#MYSQLPort       3306
MYSQLSocket     /tmp/mysql.sock
MYSQLUser       root
MYSQLPassword   rootpw
MYSQLDatabase   pureftpd
MYSQLCrypt      cleartext
MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID     SELECT Gid FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"

看看这 pureftpd-mysql.conf 配置文件例子中每个关键字的解释。

可以把这个配置文件保存在任何地方。让我们假如它是 /etc/pureftpd-mysql.conf 。

然后，你必须使用带这个配置文件路径的命令选项 '-l mysql:' 来启动 pure-ftpd。下面
是用于 tcpserver 的一个例子：

tcpserver -DHRl0 0 21 /usr/local/bin/pure-ftpd -l mysql:/etc/pureftpd-mysql.conf &

你可以混合不同的多种认证方式。比如：在一个帐号在 MySQL 数据库中找不到时你想使用
系统帐号 (/etc/passwd) 中对应的某个帐号时，可以使用：

-l mysql:/etc/pureftpd-mysql.conf -l unix

----------------------------- 数据表格结构 ------------------------------------
 
Pure-FTPd 非常灵活而且用户信息可以任何形式存储在 SQL 表格中。你仅仅只需要有保存
下列信息的列：
- 用户登陆。
- 用户密码，可以以明文、MD5、crypt()或者 MySQL的 password() 等多种格式。
Pure-FTPd 还接受 MySQLCrypt 列的“任何”值。所谓“任何”，指所有的散列函数（除
了明文）都是经过测试过的。
* 推荐：在使用非常老的 C 库的 Solaris 系统上，使用 MySQL MD5 散列函数。在所有其
他系统上，最好使用 crypt()。最好不用非常弱的 password() 函数，它不能移植，且一
般仅在 MySQL 服务器内部帐号使用的。在 MySQL 4.1.0 和以后版本，Pure-FTPd 不再支
持 password() 函数了。
- FTP 用户映射到的系统用户ID(uid)。可以是一个数字或用户名，在运行时查找。
- 系统用户组ID(gid)（数字或非数字）。
- 用户主目录。
这里有一个处理这些东西的简单表格的示例：
CREATE TABLE users (
  User varchar(16) NOT NULL default '',
  Password varchar(64) NOT NULL default '',
  Uid int(11) NOT NULL default '-1',
  Gid int(11) NOT NULL default '-1',
  Dir varchar(128) NOT NULL default '',
  PRIMARY KEY  (User)
);
如果你需要使用用户名代替数字的话，可以将 Uid 和 Gid 的数据类型用 char() 代替
int()。
然后，在配置文件 pureftpd-mysql.conf 中，你需要提供 SQL 模板来获取必须的信息。
我们利用前面的例子：
MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID     SELECT Gid FROM users WHERE User="\L"
MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"
其中，在每个一个查询中：
\L 由尝试登陆的用户的用户名代替。
\I 由客户端要求连接到的 IP 地址代替。
\P 由客户端要求连接到的端口号代替。
\R 由客户端连接过来的远端 IP 地址代替。
\D 由远端 IPv4 地址代替，一个长整型的十进制数。
你可以混合所有这些并在不同的表中保存信息。比如：使用 \I，每一个域你可以使用不同
的表，这样 joe@domain1 和 joe@domain2 就是不同的帐号的了。使用 \R，你可以限制一
个特定的IP地址才能使用某个帐号。
请注意，登陆用户名尽包括普通的字符：A－Z , a－z , 0－9 , - , . , _ , 空格 , : ,
@ 和 ' 。出于安全目的，其他字符是禁止的。
你也可以在你的表格中去掉 uid 和 gid 这两列，而使用缺省的值代替（这样就减少了无
用的查找）。有两个指令对于实现这样的目的是有用的：MYSQLDefaultUID 和
MYSQLDefaultGID。
明显的例子：
MYSQLDefaultUID 1000
MYSQLDefaultGID 1000
使用这些指令来覆盖 MYSQLGetUID 和 MYSQLGetGID。
 
----------------------------- 每一个用户的设置 --------------------------------
 
使用可选的查询，可以为每一个用户设定个人的设置。
- MySQLGetQTAFS 是用户能在其主目录保存的最大的文件数目。
例如：
MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="\L"
- MySQLGetQTASZ 是用户能使用的最大的硬盘空间，以兆（Megabytes）为单位。
例如：
MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User="\L"
- MySQLGetRatioUL 和 MySQLGetRatioDL 是可选的比率。
例如：
MySQLGetRatioUL SELECT ULRatio FROM users WHERE User="\L"
MySQLGetRatioDL SELECT DLRatio FROM users WHERE User="\L"
- MySQLGetBandwidthUL 和 MySQLGetBandwidthDL 是可选的上传和下载带宽限制。返回值
以 KB/s 为单位。
例如：
MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="\L"
MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="\L"
- MySQLForceTildeExpansion 也是另外一个可选特性，开启 "~" 来扩展路径。0 是禁止
（默认值），1 为开启。仅在真实的系统用户与虚拟（MySQL）用户相匹配时才开启。在其
他任何情况下，请不要盲目的开启它。
 
------------------------------- 事务 ------------------------------------------
 
如果你升级你的表格到支持事务的表格时，你可以配置你的 Pure-FTPd 来利用事务。这样
的话，你就可以保证服务器能完成所有信息的解析，即使你在同时进行更新x作时也是。
开启事务，添加下面一行：
MySQLTransactions On
在表格依然是 ISAM 或 MyISAM 格式时不要开启事务。事务仅工作在更新的后端（Gemini,
InnoDB,BerkeleyDB...）和目前最新的 MySQL 版本。
 
------------------------------ 匿名用户 ---------------------------------------
 
如果你需要匿名用户连接你的 FTP 服务器，你不需要在 MySQL 中由任何 'ftp' 用户。不
过，你需要在 FTP 服务器中拥有一个系统的 'ftp' 用户。
 
----------------------------- ROOT 用户 ---------------------------------------
 
如果一个 MySQL 用户条目拥有 root (0) 用户ID或者组ID， Pure-FTPd 将拒绝它的登陆。
没有这个预防性质的限制的话，如果你的 MySQL 服务器受到损害或牵连，攻击者就能很容
易的牵连到 FTP 服务器。
安全屏障也被实现来避免因错误的数据类型（例如，binary blobs 代替了plain text）被
SQL 查询获取而产生的坏的牵连。