自写DHCP服务端,使DHCP每次连接获得IP不一样

首先我们要先了解一下DHCP请求IP的流程:

1、客户端启动发送Discover广播(如果原先获得的IP租赁时间未到期,则发送Request请求,即第3步);

2、DHCP服务端收到Discover广播,然后给客户端发送一个Offer(Option中带有分配给客户端的IP)响应;

3、客户端收到Offer请求,再向服务端发送Request请求去请求Offer中的IP;

4、服务端如果确认客户端的IP请求,则响应ACK,否则响应NAK,(如果响应NAK,客户端又重新进入第1步的Discover)。

问题分析:

如果要让客户端每次请求到的IP不一致,先要了解下面两个现状:

1、客户端在租赁时间使用50%和87.5%的时候,都会向服务端重新Request这个IP。

解决思路:我们应该分配给客户端尽量长的释放时间,比如24小时

2、如果上一个IP使用未达到释放时间,客户端重新请求的话,发送的Request请求,这样,服务端正常情况下会发送ACK确认响应

解决思路:我们应该拒绝客户端这个Request请求,使客户端能进入Discover阶段,以便服务端分配新IP,优先分配该Mac地址没有获得过的IP

解决方案:

1、客户端Discover阶段,读取该mac地址历史分配记录,优先Offer该mac地址未使用过的IP,再分配最早释放的IP,并缓存该Offer信息

2、客户端Request阶段,如果请求的IP不存在于第一步的分配信息中,则客户端的请求是续租原有IP,这里向客户端发送NAK响应,如果是第一步Offer的IP,则发送ACK响应

具体细节自行优化一下,目的就可以达到了,本人是用Python实现的。

Eclipse部署项目到远程tomcat

tomcat项目自动的方式有很多种,我最近使用的有两种,一种是使用Jenkins,另外一种使用maven插件来自动部署

本文介绍使用maven插件来自动部署,只需要在tomcat和eclipse上作简单的配置即可。

1、首先设置创建tomat管理的权限

在conf/tomcat-users.xml中设置用户和密码,并指定权限




该配置不具有网页管理权限,如果需要网页管理还是配置角色:manager-gui,admin-gui。

2、去除IP限制

在webapps/manage/META-INF/context.xml中,删除如果代码,或者把你允许的ip加进去,注意.需要转义一下

3、在项目的pom.xml中的plugins节点中配置,远程部署地址,账号和密码

org.apache.tomcat.maven
tomcat7-maven-plugin
2.2

http://主机地址/manager/text
服务名称
用户名(和tomcat中配置的一样) 密码(和tomcat中配置的一样) / utf8
true

如果配置后,通过eclipse部署出错,需要配置一下manager/META-INF 下的context.xml文件,里面设置了允许的ip地址,删除该行,或者重新配置你的即可。

centos搭建git服务 密钥登录git

1、使用yum安装git

yum install -y git

2、添加git用户组和用户

groupadd git
useradd git -g git

3、禁止git用户登录

vim /etc/passwd

找到git用户修改为:

git:x:504:1004::/home/git:/usr/bin/git-shell

4、创建git仓库

mkdir /home/git/

git init –bare test.git

5、修改git仓库所有权

chown -R git:git /home/git/test.git

6、创建认证密钥

ssh-keygen -t rsa -C “你的邮箱地址”

默认会创建在当前用户家目录的.ssh下,你可以根据提示让它创建在/home/git/.ssh下面,这个没有关系,只要你能找到就可以

创建完成后会有公钥id_rsa.pub(服务器上使用) 和 私钥 id_rsa(客户端使用)

7、将公钥添加到/home/git/.ssh/authorized_keys

cat id_rsa.pub >> /home/git/.ssh/authorized_keys

这里的权限很重要

.ssh文件夹的权限为 755或711

authorized_keys文件权限为600或644

否者可能会无法使用密钥登录

8、修改sshd配置

vim /etc/ssh/sshd_config

找到如下配置项,去除前面的注释,如果已经开启无需再配置

RSAAuthentication yes

PubkeyAuthentication yes

AuthorizedKeysFile      .ssh/authorized_keys

9、重启sshd服务

service sshd restart

10、将私钥id_rsa复制到客户端,可以通过scp或ftp、sftp等工具

然后在客户端执行

ssh-add id_rsa

10、接下来就可以不需要密码使用git用户操作服务器上的git仓库了

可以输入下面命令进行测试,如果没有输入密码提示那就成功了。但是因为上面设置了git的登录shell,登录时无法成功。

ssh git@主机地址 

虚拟机安装openwrt 无线路由器做AP发送无线信号

之前一直用的小米路由器,小米的系统真心对不住那硬件。说是一个路由器吧,连一条静态路由都没法添加;说是一个NAS吧,却连NFS都不支持,SAMBA效率实在不行。

这几天由于公司需求,需要实现NFS资源共享,局域网的HTTP及数据库服务器,便安装了Windows Server 2016,同时也安装了虚拟机,本来用的都是Hyper-v,后来由于兼容性问题,改成了VMware Workstation。比起Hyper-v,VMware Workstation(12.5.4)中的虚拟机和主机的网络速度实在是慢,桥接、NAT、主机模式,各种连接方法都已经尝试过,从虚拟机往主机NFS共享的文件夹中写入速度都只有20M/s左右,而Hyper-v能达到近200M/s,同样的网络连接方式!Hyper-v在主机中创建的虚拟网卡是1Gbps的,而vmware是100mbps的,网上搜索了很多资料也没有解决问题,反而还局域网中其他电脑通过无线网络的读写速度高。期间还在Windows 10上面安装了虚拟机,速度还是可以的,可能是wmware和windows server 2016还有些不兼容。

切入正题,因为在虚拟中安装了centos作为局域网内的服务器,索性就用这台主机做路由器了,然后就开始一番折腾,教程仅供参考及当做自己的一个笔记。如有有更好的意见,希望能留言告知,非常感谢!

一、在vmware中安装openwrt

 

二、在vmware为openwrt安装两张桥架模式的网卡,其中一张作为WAN口网卡(桥接到物理网卡用于拨号上网或者以其他方式连接上层网络),另外一张作为LAN口网卡(桥接到物理网卡用于连接充当AP的无线路由器)

 

三、关闭小米路由器的DHCP服务

 

四、将原来连接小米路由器WAN口的线改接到小米路由器的LAN口,并通过另一根网线连接小米路由器LAN口和电脑主机的网卡,通过openwrt进行pppoe拨号和路由

php-fpm多用户运行的两种方式

php-fpm各个站要是以同样的用户运行的话,一个网站要是被攻击了的话,可能会威胁到其他网站。当然解决方法不仅仅是以多用户的方式去运行php-fpm,还可以配置open_basedir,但是测试了一下,貌似只有在php5.3以上才有效。

下面以php5.3.28为例,配置多用户运行php-fpm:

第一种:一个php-fpm主进程

这种方式比较简单,也只需要一个php-fpm自启动文件

首先我们查看一下原php-fpm.conf的这个配置文件,分为两个部分,一个是global块,另外一个是自定义的块,配置文件里面称为pool池,默认叫“www”。在global池的上方,有一行注释了的“include=etc/fpm.d/*.conf”配置项,再通过www池的配置,我们可知可以通过不同的池来配置不同的用户,来达到多个用户运行php-fpm的目的,步骤如下:

1、在etc目录下创建fpm.d目录


mkdir fpm.d

2、复制php-fpm.conf 到fpm.d目录下并重命名,比如 blog.conf


cp php-fpm.conf ./fpm.d/blog.conf

3、进入fpm.d目录,修改blog.conf


cd fpm.d
vim blog.conf

4、删除前面的global块,或者注释掉

5、修改[www]为其他,比如你[blog]

6、配置[blog]池,主要修改两个地方:

6.1:第一处为运行的用户和用户组

即将


user = www
group = www

修改为


user=nobody #具体用哪个用户视自己情况而定,我只做个示例
group=nobody

6.2:修改监听的端口或者socket:

即将:


listen = 127.0.0.1:9000

修改为:


listen = /var/socket/php-fpm/blog.socket #php-fpm需要自己创建,当然也可以直接放在php-fpm目录下

修改成其他端口也是可以的,比如:listen = 127.0.0.1:9001

7、到主配置文件 php-fpm.conf将“include=…”前面的注释去掉,让它去读取fpm.d目录下的配置文件;

8、到此第一种方案就修改完毕了,重新启动测试一下:


service php-fpm reload

第二种:两个php-fpm主进程

这种方法需要独立的配置文件和独立的自启动文件:

1、复制一份php-fpm.conf主配置文件


cp php-fpm.conf php-fpm-blog.conf

2、修改主配置文件


vim php-fpm-blog.conf

2.1:修改[global]下pid和error_log文件的路径

修改 pid=run/php-fpm.pid 为 pid=run/php-fpm-blog.pid 

修改 error_log = /log/php-fpm.log 为 error_log = /log/php-fpm-blog.log

2.2:修改池的名称[www]为[blog],不过这个可以不用修改了,因为这里和原来的进程是独立的

2.3:修改用户和用户组;

2.4:监听端口或socket文件;

以上两部可以按照第一种方案进行修改,这里就不再重复

3、进入/etc/init.d目录,复制一份自启动文件


cp php-fpm php-fpm2

4、修改自启动文件php-fpm2:

4.1:修改配置文件路径


php_fpm-CONF=${prefix}/etc/php-fpm.conf


php_fpm-CONF=${prefix}/etc/php-fpm-blog.conf

这个路径就是刚才的主配置文件

4.2:修改PID文件路径:


php_fpm_PID=${prefix}/var/run/php-fpm.pid

为:


php_fpm_PID=${prefix}/var/run/php-fpm-blog.pid

这个路径要和主配置文件中的pid路径一致

5、修改完毕后添加自动启动


chkconfig --add php-fpm2
chkconfig --level 2345 php-fpm2 on

6、启动服务


service php-fpm2 start

DirectAdmin使用局域网ip 反向代理

DirectAdmin安装在阿里云上,我的服务器一个是按流量计费(服务器A),一个按固定带宽计费(服务器B),我装在按流量计费的服务器上(服务器A),部分域名计划走固定带宽,在服务器B做反向代理,发现没有成功!

自己在面板上折腾了一下,把内网的ip加上,然后绑定到用户的域名上,结果不可行,一个ip只能绑定一个网站,这样操作应该是虚拟主机独立ip的解决方案,不是我想要的效果。

最后通过如下方式解决问题:

1、切换回原来的皮肤:enhanced ,因为我的Capri的操作的时候提示文件丢失,可能皮肤文件不完整;

(如果enhanced皮肤没有中文语言包,最好把语言切换会英文,要不然可能会出现无法显示菜单项

vi /usr/local/directadmin/data/users/admin/user.conf 把language=cn 改为language=zn)

2、进入ip管理:Admin Level ->IP Management,

20151014144121

3、填写IP地址和子网掩码,点“Add IP”确认添加;

20151014145934

4、点击刚添加的ip,选择外网ip进行关联:

20151014150238

5、在Nginx配置文件中设置方向代理:

server {
listen 80;
location / {
proxy_pass http://10.161.*.*:80;
proxy_redirect off;
proxy_set_header HOST $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
client_max_body_size 10m;
client_body_buffer_size 128k;
proxy_connect_timeout 90;
proxy_send_timeout 90;
proxy_read_timeout 90;
proxy_buffer_size 4k;
proxy_buffers 4 32k;
proxy_busy_buffers_size 64k;
proxy_temp_file_write_size 64k;
}
}

DirectAdmin安装在阿里云上,我的服务器一个是按流量计费(服务器A),一个按固定带宽计费(服务器B),我装在按流量计费的服务器上(服务器A),部分域名计划走固定带宽,在服务器B做反向代理,发现没有成功!

自己在面板上折腾了一下,把内网的ip加上,然后绑定到用户的域名上,结果不可行,一个ip只能绑定一个网站,这样操作应该是虚拟主机独立ip的解决方案,不是我想要的效果。

最后通过如下方式解决问题:

1、切换回原来的皮肤:enhanced ,因为我的Capri的操作的时候提示文件丢失,可能皮肤文件不完整;

(如果enhanced皮肤没有中文语言包,最好把语言切换会英文,要不然可能会出现无法显示菜单项

vi /usr/local/directadmin/data/users/admin/user.conf 把language=cn 改为language=zn)

2、进入ip管理:Admin Level ->IP Management,

20151014144121

3、填写IP地址和子网掩码,点“Add IP”确认添加;

20151014145934

4、点击刚添加的ip,选择外网ip进行关联:

20151014150238

5、在Nginx配置文件中设置方向代理:

server {
listen 80;
location / {
proxy_pass http://10.161.*.*:80;
proxy_redirect off;
proxy_set_header HOST $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
client_max_body_size 10m;
client_body_buffer_size 128k;
proxy_connect_timeout 90;
proxy_send_timeout 90;
proxy_read_timeout 90;
proxy_buffer_size 4k;
proxy_buffers 4 32k;
proxy_busy_buffers_size 64k;
proxy_temp_file_write_size 64k;
}
}

DirectAdmin安装Capri教程

1、下载capri安装文件

wget http://developer.renyiwei.com/directadmin/capri/capri.sh

2、开始安装

sh capri.sh

3、设置安装选项
This script will install and setup Capri Skin for DirectAdmin Control panel.

Please choose a skin name or press enter to use default [Capri]:
Skin name will be: Capri

Please choose a color set (1: blue, 2: grey) [1]: 选择主题颜色,蓝色和灰色,默认为蓝色
Would you like to install the login page skin? (y/n) [y]:是否安装登录皮肤,我选的安装

Capri skin also have a email-only version for users who have a email services.
Would you like to install a second email-only skin (will be called Capri-mail)? (y/n) [n]:这里email皮肤,一般用不到,不用安装

设置完之后就开始安装了…

4、设置显示中文

vi /usr/local/directadmin/data/users/admin/user.conf

找到language=en  修改为language=cn

亚马逊云服务器AWS安装CentOS

亚马逊云服务器默认创建的实例,在停止之后再启动的情况下,IP会发生改变。所以我们最好先创建一个弹性IP,即EIP,不过我也不清楚这个费用。

1、按如图操作创建一个弹性IP,弹性IP创建之后可以随便绑定到该region下的实例

20150618121833

2、创建好弹性IP之后,然后去创建实例:

201506261353203、点启动实例之后,会进入到Amazon 系统映像(AMI)的选择:20150626135720

4、我这里以安装Centos为例,点击上图中的“AWS Marketplace”,从镜像市场选择centos安装,可以慢慢找,也可以搜索centos然后再找合适的版本(有些镜像是收费的,选择的时候需要注意):

201506261358095、选择好系统镜像之后,会进入安装配置的界面,我们这里一步一步来,所以点“下一步 配置实例详细信息”:

201506261401366、因为我们绑定弹性IP,所以这里把自动分配公有IP禁用掉:

201506261405217、下一步设置存储,这里就先默认,需要的话可以添加一个,然后进入系统之后再格式化挂载:

20150626180204

8、下一步,实例标签,这个不重要,随便设置一个标记一下即可(图略)

9、选择安全组,就相当于防火墙:

注:这里需要注意的是,安全组的描述不能是中文,否则后面会报错:for parameter GroupDescription is invalid. Character sets beyond ASCII are not supported.

如果直接启动,没有按步骤到这一步的而出现for parameter GroupDescription is invalid. Character sets beyond ASCII are not supported.需要修改一下这里安全组的描述

当然也可以选择默认的安全组,不过记得把SSH的22端口开启

20150626140950

10、点“审核和启动”会出现你配置的详情,然后点“启动”,会出现一个配置密钥对的(密钥对是用来登录系统的,所以要保存好下载过来pem私钥),最后点启动实例:

20150626141758

11、返回实例列表,等待初始化完之后,到弹性IP列表,选择需要绑定的IP进行关联操作:

20150626142654

12、实例这样差不多就创建完成了。

然后我们可以通过私钥登录到服务器,对服务器进行操作:

亚马逊云服务器AWS远程登录

如何确保web环境安全

在服务器安全方面,我涉足还很浅,只是平时配置一下服务器环境,给客户解决一下问题,但很多细小的问题能避免的还是应该尽量避免,配置的时候虽然麻烦些,但能为后续省下不少麻烦事。

1、服务器选择方面

云主机的选择

如果是大型网站或者站点比较多,或者像我这样爱折腾的,那还是选择一个云主机。如果个人对服务器维护不是很在行的话,在云主机选择上,推荐像阿里云这样的主机,云盾这东西还是可以防护一下的,还可以扫描服务器安全,有漏洞了可以给你提个醒。即使被攻击了,也有自动快照可以还原,可以减少损失;

虚拟主机的选择

如果只是建个小型网站的话,比如个人博客,企业展示网站,或者一些存放个人作品的网站,那可以选择虚拟主机。虚拟主机的选择可以看品牌,比如万网这些肯定是值得信任的;如何非要选小idc商家的产品的话,那看虚拟主机面板,php虚拟主机面板比较有名的像DA,CPanel这些,java虚拟主机面板一般都是idc商家自己开发的,大牌的商家也很少提供java虚拟主机,国内的西部数码还是提供了的,这些面板都是他们自己开发的,还有国内的EasyPanel企业版也支持java虚拟主机,但是问题多多,连post请求都有问题。另外我也为公司开发了一款java虚拟主机面板EngineAdmin,目前仅支持虚拟化tomcat,后续会支持php。

2、web程序方面

如果是自己写的代码,那肯定要写健壮。

后台代码尤其是在处理用户请求方面,一定要过滤,防止非法请求;

前台代码应该避免引用不知名站点的资源,比如jquery,bootstrap这些,如果官方提供的cdn国内访问速度可以的话,首先考虑官方的,其次考虑一些大牌企业的,比如360什么的,像国内几乎不怎么使用的googlefonts,360还是提供了cdn加速的。

如果引用了一些小站点的js或者css,那后果是不可意料的,人家站点要是关闭了呢?人家站点要是被攻击了呢?造成网站无法正常显示那还算小问题,外站的一段js可以将你的站点重定向到别人的网站,在你的网站上放置他们的广告,甚至窃取网站用户的信息。

如果使用建站程序

使用比如wordpress,dedecms,discuz这些现成的web程序,那要尽量选择新版本或者经典的版本,要即使关注官方更新,尤其是在安全方便的更新,要即使安装这些更新,或者已经被公开的程序漏洞;

3、云主机环境配置(针对云主机用户)

服务器软件选择

云主机在web环境的配置方面,在服务器软件选择方面要尽量选择稳定版,开发版可能不稳定,这方面我见过的需求不多,所以也没法扯太多。

使用独立的用户运行

在配置web程序方面,首先是程序运行的用户,这个很重要,如果是以root用户来运行的话,那后果是不堪设想的;一般会创建apache,ftp,www这样的用户来运行,其实这样还不是很安全,因为多个网站使用同一个用户运行,这个用户对多个站点的文件拥有读写权限,一个站点被攻击控制,其他站点也就没得安全可言。

所以尽量要为每个站点配置一个运行用户。如果使用Nginx+PHP-FPM运行php的,可以用每个站点创建一个php-fpm的配置文件,配置不同的运行用户。但是会给服务器造成一定的压力,根据要求来吧,毕竟安全才是第一!tomcat也一样,可以为每个站点配置一个独立的tomcat,使用不同的端口号,用不同的身份来运行站点,并放置nginx等web服务器作前端代理。

默认索引文件的配置

我见过很多站点配置了默认首页一连窜 ,比如index.html index.htm index.php 等,然后实际使用的是index.php,后来被攻击了,别人给创建了一个index.html的文件放在根目录下,最后用户访问的时候就全变了。

所以配置默认索引文件的时候不要太多,该什么就什么,这一点我配置自己服务器的时候也没有去注意,但是一定不可忽视。

文件操作限制

限制文件上传的类型,限制文件操作一些敏感的函数等。

4、站点文件权限

站点文件权限我觉得可以说是最后一道防护栏了!如果用户已经通过代码取得了web文件的读写权限,那良好的文件权限配置还是可以减少损失的。

权限配置的话,对于静态文件,比如css,js这些,尤其是js文件,最好只给予读的权限,而不给写入权限,这样即使被攻击了,也一定能更改文件的内容。云主机用户可以更改这些文件夹的所有者,改为root,也可以更改所有者的权限,只给予读的权限。

对于uploads这类需要存放用户上传文件的文件夹,给予运行用户的读写权限。

虚拟主机用户

虚拟主机用户一般没法更改文件的所有者,我们可以通过ftp工具修改文件所有者的权限:

ftp_change_mod

ProFTPD module mod_sql


ProFTPD module mod_sql


This module is contained in the contrib/mod_sql.ccontrib/mod_sql.hcontrib/mod_sql_mysql.c, and contrib/mod_sql_postgres.c files for ProFTPD 1.3.x, and is not compiled by default. Installation instructions are discussed here.

The mod_sql module is an authentication and logging module for ProFTPD. It is comprised of a front end module (mod_sql) and backend database-specific modules (mod_sql_mysqlmod_sql_postgresmod_sql_sqliteetc). The front end module leaves the specifics of handling database connections to the backend modules.

This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit (http://www.openssl.org/).

This product includes cryptographic software written by Eric Young (eay@cryptsoft.com).

The most current version of mod_sql is distributed with ProFTPD.

Thanks

  • Johnie Ingram <johnie at netgod.net> for the original mod_sqlpw.
  • TJ Saunders and Jesse Sipprell for dealing with my inane questions.
  • John Morrissey for mod_ldap, which lit the way, here and there.
  • Zeev Suraski <bourbon at bourbon.netvision.net.il> for the Apache mod_auth_mysql module, which informed the SQLAuthTypes directive.
  • Avalon from IRC for doc-fixes and suggestions.
  • and many others who’ve suggested various enhancements or security fixes.

Directives


SQLAuthenticate

Syntax: SQLAuthenticate on|off or
Syntax: SQLAuthenticate [users] [groups] [userset[fast]] [groupset[fast]]
Default: on
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The SQLAuthenticate directive configures mod_sql‘s authentication behavior, controlling whether to provide user and/or group information during authentication, and how that provisioning is performed. The parameters may appear in any order.

The available parameter values are:

    • on
      Shorthand for SQLAuthenticate users groups userset groupset.

 

    • off
      Disables all mod_sql authentication functions.

 

    • users
      If present, mod_sql will do user lookups. If not present, mod_sql will do no user lookups at all, including the {set|get|end}pwent() calls (see below).

 

    • groups
      If present, mod_sql will do group lookups. If not present, mod_sql will do no group lookups at all, including the {set|get|end}grent() calls (see below).

 

    • userset[fast]
      If present, mod_sql will process the potentially expensive {set|get|end}pwent() calls. If not present, mod_sql will not process these calls. Adding the suffix “fast” tells mod_sql to process the users as a single large query, rather than making a query per user. This may significantly reduce the number of queries against the database at the expense of increased memory use. This parameter will have no effect if “users” is not specified.

 

    • groupset[fast]
      If present, mod_sql will process the potentially expensive {set|get|end}grent() calls. If not present, mod_sql will not process these calls. Adding the suffix “fast” tells mod_sql to process the groups as a single large query, rather than making a query per group. This may significantly reduce the number of queries against the database at the expense of increased memory use. This parameter will have no effect if “groups” is not specified.

 

The SQLLog and SQLShowInfo directives will always be processed by mod_sql. The SQLAuthenticate directive only affects the user and group lookup/authentication portions of the module.

Turning off (i.e. by not including) the userset or groupset parameters affects the functionality of mod_sql. Not allowing these lookups may remove the ability to control access or control functionality by group membership, depending on your other authentication handlers and the data available to them. At the same time, choosing not to do these lookups may dramatically speed login for many large sites.

The “fast” suffix is not appropriate for every site. Normally, mod_sql will retrieve a list of users and groups, and get information from the database on a per-user or per-group basis. This is query intensive: it requires (nn + 1) queries, where n is the number of users or groups to lookup. By choosing “fast” lookups, mod_sql will make a single SELECT query to get information from the database.

In exchange for the radical reduction in the number of queries, the single query will increase the memory consumption of the process; all group or user information will be read at once rather than in discrete chunks.

Group Table Structure
Normally mod_sql allows multiple group members per row, and multiple rows per group. If you use the “fast” option for groupset, you must use only one row per group. For example, normally mod_sql treats the following three tables in exactly the same way:

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi                      |
| group1      | 1000  | priscilla                  |
| group1      | 1000  | gertrude                   |
|--------------------------------------------------|

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi, priscilla           |
| group1      | 1000  | gertrude                   |
|--------------------------------------------------|

|--------------------------------------------------|
|  GROUPNAME  |  GID  |  MEMBERS                   |
|--------------------------------------------------|
| group1      | 1000  | naomi, priscilla, gertrude |
|--------------------------------------------------|

If you use the “fast” option, mod_sql assumes that all entries are structured like the last example.

 


SQLAuthTypes

Syntax: SQLAuthTypes [“Backend” | “Crypt” | “Empty” | “OpenSSL” | “Plaintext”] …
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

The SQLAuthTypes directive specifies which authentication method are to be allowed, and their order of use. You must specify at least one authentication method.

The current supported authentication methods are:

    • Backend
      Allows database-specific backend passwords. Not all backend databases support this option. For example, MySQL datatabases use this option to authenticate MySQL'PASSWORD()' encrypted passwords. The Postgres backend, however, does nothing. Caveat: if your MySQL activity log is world-readable, the user password will be visible. You have been warned.

 

    • Crypt
      Allows passwords in the database to be of Unix crypt(3) form. Note that the traditional Unix crypt(3) function only uses the first 8 characters of a password. If you have passwords longer than 8 characters, and some of those passwords have the same first 8 characters, then those users may be able to log into each others accounts.

 

    • Empty
      Allows empty passwords in the database, which match against any password the user may give. The database field must be a truly empty string; NULL values are not acceptable as empty passwords. Be very careful if using this authentication method.

 

    • OpenSSL
      Allows passwords in the database to be of the form '{digest-name}hashed-value', where hashed-value is the base64-encoded digest of the passsword. Only available if you define HAVE_OPENSSL when you compile proftpd and you link with OpenSSL’s libcrypto library; the easiest way to handle this is to use the --enable-openssl configure option.

 

    • Plaintext
      Allows passwords in the database to be in plaintext.

 

For example:

  SQLAuthTypes Crypt Empty

configures mod_sql to first attempt to verify the password using the Unix crypt(3) function, then, if that fails, determine if the password in the database is empty (thus matching any given password). If all of the configured authentication methods fail, mod_sql will fail to authenticate the user.

Note that the mod_sql_passwd module also provides other SQLAuthTypes values.

 


SQLBackend

Syntax: SQLBackend backend
Default: Depends
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.3.0rc1 and later

In 1.3.0rc1, the mod_sql module gained the ability to be compiled with multiple backend modules supported, e.g. to have both mod_sql_mysql and mod_sql_postgres usable in the same proftpd daemon. The SQLBackend directive configures which of these different database backends should be used.

If there is only one backend module compiled in, the SQLBackend directive is not needed. If there are multiple backend modules compiled and no SQLBackend directive is specified, then mod_sql will default to using the first backend module listed. For instance, if you configured proftpd using a configure command such as:

  ./configure --with-modules=mod_sql:mod_sql_postgres:mod_sql_mysql ...

then mod_sql would default to using mod_sql_postgres as the backend module to use.

You might have multiple <VirtualHost> sections which use different SQL backends, e.g.:

  <VirtualHost 1.2.3.4>
    SQLBackend mysql
    ...
  </VirtualHost>

  <VirtualHost 5.6.7.8>
    SQLBackend postgres
    ...
  </VirtualHost>

Use “mysql” for the mod_sql_mysql module, and “postgres” for the mod_sql_postgres module.

 


SQLConnectInfo

Syntax: SQLConnectInfo connection-info [username] [password] [policy]
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

The SQLConnectInfo directive configures the information necessary to connect to the backend database. The connection-info parameter specifies the database, host, port, and other backend-specific information. The optional username and password parameters specify a username and password to use when connecting to the database. Both default to NULL, which the backend will treat in some backend-specific manner. If you specify a password, you must specify a username. If no SQLConnectInfo directive is specified, mod_sql will disable itself.

Any given database backend has the opportunity, though not necessarily the responsibility, to check for syntax errors in the connection-info field at server startup, but you should not expect semantic errors (i.e., cannot connect to the database) to be caught until mod_sql attempts to connect for a given host.

A given database connection is governed by a connection policy that specifies when a connection should be opened and when it should be closed. There are three options:

    • PERCONNECTION
      Open a database connection when the client connects to the server, and close the database connection when the client disconnects.

 

    • PERSESSION
      Open a database connection on first use (e.g. during authentication) and close the database connection at the end of the session.

 

    • number (“timed”)
      Timed database connections that close themselves after number seconds of inactivity.

If a connection policy is not specified, if the policy is not a number or is a number less than 1, or if the policy is the string “PERSESSION”, the PERSESSION policy will be used.

If the connection policy is any number greater than 0, it specifies the number of seconds that a connection will be held open without activity. After that many seconds of database inactivity, the connection to the database will be closed. As soon as database activity starts again, the connection will be opened and the timer will restart.

The MySQL and Postgres backends’ connection-info is expected to be of the form:

  database[@hostname][:port]

hostname will default to a backend-specific hostname (which happens to be ‘localhost’ for both the MySQL and Postgres backends), and port will default to a backend-specific default port (3306 for the MySQL backend, 5432 for the Postgres backend).

From the MySQL documentation:

the value of host may be either a hostname or an IP address. If host is NULL or the string “localhost”, a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.

(Note: In proftpd-1.3.1rc1, if hostname starts with a slash, it is interpreted as the Unix domain socket path by mod_sql_mysql, similar to how mod_sql_postgres handles the parameter.) From the PostgreSQL documentation:

If [the hostname] begins with a slash, it specifies Unix domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default is to connect to a Unix-domain socket in /tmp.

If you plan to use the "timed" connection policy, consider the effect of directives such as DefaultRoot on local socket communication: once a user has been chroot()ed, the local socket file will probably not be available within the chroot directory tree, and attempts to reopen communication will fail. One way around this may be to use hardlinks within the user’s directory tree; another is to use network (e.g. TCP sockets, not Unix domain sockets) connections to connect to the database. PERSESSIONconnections are not affected by this because the database will be opened prior to the chroot() call, and held open for the life of the session. Network communications are not affected by this chroot() problem. For example, while localhost would not work for MySQL since the MySQL client library will try to use Unix domain socket communications for that host, 127.0.0.1 will work (as long as your database is setup to accept these connections).

Examples:

  # Connect to the database 'ftpusers' via the default port at host
  # 'foo.com'.  Use a NULL username and NULL password when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers@foo.com

  # Connect to the database 'ftpusers' via port 3000 at host 'localhost'.
  # Use the username 'admin' and a NULL password when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers:3000 admin

  # Connect to the database 'ftpusers' via port 3000 at host 'foo.com'.
  # Use the username 'admin' and password 'mypassword' when connecting.
  # A connection policy of PERSESSION is used.
  SQLConnectInfo ftpusers@foo.com:3000 admin mypassword

  # Connect to the database 'ftpusers' via port 3000 at host 'foo.com'.
  # Use a username of 'admin' and a password of 'mypassword' when
  # connecting.  A 30 second timer of connection inactivity is activated.
  SQLConnectInfo ftpusers@foo.com:3000 admin mypassword 30

Backends may require different information in the connection-info field; check your backend module for more detailed information.

 


SQLDefaultGID

Syntax: SQLDefaultGID default-gid
Default: 65533
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

The SQLDefaultGID directive configures the default GID for users. This value must be greater than any configured SQLMinUserGID.

See also: SQLMinUserGID

 


SQLDefaultHomedir

Syntax: SQLDefaultHomedir path
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The SQLDefaultHomedir directive configures a default home directory for all users authenticated with this module. If no home directory is set with either directive, authentication fails. This directive does not change the data retrieved from the database: if you specify a home directory field to SQLUserInfo, that field’s data will be returned as the user’s home directory, whether that data is a legal directory, or an empty string, or NULL.

See also: SQLUserInfo

 


SQLDefaultUID

Syntax: SQLDefaultUID default-uid
Default: 65533
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

The SQLDefaultUID directive configures the default UID for users. This value must be greater than any configured SQLMinUserUID.

See also: SQLMinUserUID

 


SQLEngine

Syntax: SQLEngine on|off|auth|log
Default: SQLEngine on
Context: server config, <VirtualHost><Global><Anonymous>
Module: mod_sql
Compatibility: 1.3.0rc1 and later

The SQLEngine directive is used to specify how mod_sql will operate. By default, SQLEngine is on, and mod_sql will operate as normal. Setting SQLEngine to off will effectively disable the module.

In addition to on and offSQLEngine accepts two other values: auth and log. If you wish to use mod_sql for authentication and not for logging (via SQLLog), use auth. Conversely, to do only SQLLog-type logging, and no authentication, use log.

This directive can be used to have <Anonymous> sections that do not use mod_sqle.g.:

  <Anonymous ~ftp>
    ...
    SQLEngine off
    ...
  </Anonymous>

 


SQLGroupInfo

Syntax: SQLGroupInfo group-table group-name gid members
Default: “groups groupname gid members”
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The SQLGroupInfo directive configures the group table and fields that hold group information. The parameters for this directive are described below:

    • grouptable
      Specifies the name of the table that holds group information.

 

    • groupname
      Specifies the field in the group table that holds the group name.

 

    • gid
      Specifies the field in the group table that holds the group’s GID.

 

    • members
      Specifies the field in the group table that holds the group members.

If you need to change any of these field names from the default, you need to specify all of them.

Custom Queries
As of 1.3.3rc1, the SQLGroupInfo directive accepts an alternate syntax:

  SQLGroupInfo custom:/get-group-by-name/get-group-by-id/get-group-by-member

where get-group-by-name refers to a configured SELECT SQLNamedQuery. This named query must return one row, and return the following columns, in this order: groupname, gid, members. The configured query may make use of the variables mentioned in the SQLLog description. The %{0} variable will be populated with the group name for which info is being retrieved. The get-group-by-id here refers to a SQLNamedQuery which is just like the lookup given a group name, only the get-group-by-id query needs to return group information given a GID. For the get-group-by-id query, the %{0} variable is populated with the GID in question. Last, the get-group-by-member query returns group information given a user member; again, the %{0} will contain the member/user name by which to look up the group information.

If your custom get-group-by-name query references a table other than the default groups table, then you must also supply a custom get-group-by-id query as well. Otherwise, mod_sql will fail with a “Table not found” error, and disconnect the client, whenever the client asks for a directory listing.

Note, however, that if you use the groupset or groupsetfast SQLAuthenticate options, you will need to supply some additional SQLNamedQuery names in your custom SQLGroupInfodirective. The SQLGroupInfo directive supports:

  SQLGroupInfo custom:/lookup-by-name/lookup-by-id/lookup-by-member/[/groupset-lookup[/groupsetfast-lookup]]

where groupset-lookup is the name of a SELECT SQLNamedQuery that returns the names (and only the names) of all groups, and the groupsetfast-lookup refers to a SELECTSQLNamedQuery that returns all the fields (i.e. groupname, gid, members in that order) for all groups.

To provide a concrete example:

  SQLAuthenticate users groups groupsetfast
  SQLGroupInfo custom:/get-group-by-name/get-group-by-id/get-group-by-member/get-all-groupnames/get-all-groups
  SQLNamedQuery get-group-by-name SELECT "groupname, gid, members FROM ftpgroups WHERE groupname = '%{0}'"
  SQLNamedQuery get-group-by-id SELECT "groupname, gid, members FROM ftpgroups WHERE gid = %{0}"
  SQLNamedQuery get-group-by-member SELECT "groupname, gid, members FROM ftpgroups WHERE (members LIKE '%%,%{0},%%' OR members LIKE '%{0},%%' OR members LIKE '%%,%{0}')"
  SQLNamedQuery get-all-groupnames SELECT "groupname FROM ftpgroups"
  SQLNamedQuery get-all-groups SELECT "groupname, gid, members FROM ftpgroups"

With a configuration similar to these, using either of the groupset or groupsetfast in your SQLAuthenticate directive will work as expected.

Another consideration when using custom SQLGroupInfo queries is their relationship to the “useNormalizedGroupSchema” SQLOptionI.e. does using the “useNormalizedGroupSchema” option affect how the results of custom SQLGroupInfo queries are handled? No. If custom SQLGroupInfo queries are used, mod_sql automaticallyassumes that the custom group members query will return N rows, where each row contains 3 columns: group name, group ID, and member name. That is, the customSQLGroupInfo queries act as if “useNormalizedGroupSchema” is always in effect. (When support for custom SQLGroupInfo queries was added, there was no good reason for supporting the old, inefficient comma-delimited format for the members column.)

See AlsoSQLAuthenticateSQLLogSQLNamedQuerySQLUserInfo

 


SQLGroupPrimaryKey

Syntax: SQLGroupPrimaryKey column | “custom:/”named-query
Default: gid
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.3.5rc3 and later

The SQLGroupPrimaryKey directive configures the column name that is used as the “primary key” for group data; this primary key can then be used in other SQL tables via foreign key constraints. The SQL howto covers using this directive in more details.

See also: SQLUserPrimaryKey

 


SQLGroupWhereClause

Syntax: SQLGroupWhereClause where-clause
Default: off
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The directive is used to configure a WHERE clause that is added to every group query. The WHERE clause must contain all relevant punctuation, and must not contain a leading “and”.

As an example of a possible use for this directive, imagine if your group table included a “LoginAllowed” field:

  SQLGroupWhereClause "LoginAllowed = 'true'"

would be appended to every group-related query as the string:

  " WHERE (LoginAllowed = 'true')"

Note that if custom group SQLNamedQuery are configured, those custom queries will be used as is; any configured SQLGroupWhereClause will not be appended. Custom queries can be of any format/syntax, and thus simply appending a SQLGroupWhereClause to a custom query may be syntactically invalid.

As of ProFTPD 1.3.1rc2, the configured SQLGroupWhereClause parameter can use the same set of variables as supported by the SQLNamedQuery directive.

 


SQLLog

Syntax: SQLLog cmd-set query-name [“IGNORE_ERRORS”]
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.1 and later

This directive is used to log information to a database table. Multiple SQLLog directives can be in effect for any command; for example, a user changing directories can trigger multiple logging statements.

The first parameter to SQLLog, the cmd-set, is a comma-separated (no spaces) list of FTP commands for which this log command will trigger. The list of commands is too long to list in entirety; commands include CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV, PORT and many more. For the complete list check the FTP RFCs. Normally mod_sql will log events after they have completed successfully; in the case of the QUIT command, mod_sql logs prior to the server’s processing of the command. (Note, however, that the client may not issue a QUIT before logging out; in this case, use a command of EXIT rather than QUITEXIT is not a real FTP command, but it is used here to provide a means for having SQLLog work whenever a session ends.)

FTP commands in the command set will only be logged if they complete successfully. Prefixing any command with “ERR_” will cause logging to occur only if there was an error in the command’s processing. To log both errors and successful completion of a given command X, therefore, you’ll need both “X” and “ERR_X” in your cmd-set.

The special command “*” matches all FTP commands, while “ERR_*” matches all errors.

The second parameter is the name of a query defined by a SQLNamedQuery directive. The query must be an UPDATEINSERT, or FREEFORM type query; explicit SELECT queries will not be processed.

The third parameter is optional. If you add “IGNORE_ERRORS” as the third parameter, SQLLog will not check for errors in the processing of the named query. Any value for this parameter other than the string “IGNORE_ERRORS” (case-insensitive) will not cause errors to be ignored.

Normally, SQLLog directives are considered important enough that errors in their processing will cause mod_sql to abort the client session. References to non-existent named queries will not abort the client session, but may result in database corruption (in the sense that the expected database UPDATE or INSERT will not occur). Check your directives carefully.

Examples:

  SQLLog PASS updatecount
  SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users

If the current user was “joe”, this would translate into the query “UPDATE users SET count=count+1 WHERE userid=’joe'”. This query would run whenever a user was first authenticated.

  SQLLog CWD updatedir
  SQLNamedQuery updatedir UPDATE "cwd='%d' where userid='%u'" users

If the current user was “joe” and the current working directory were /tmp, this would translate into the query “UPDATE users SET cwd=’/tmp’ WHERE userid=’joe'”. This query would run whenever a user changed directories.

  SQLLog RETR,STOR insertfileinfo
  SQLNamedQuery insertfileinfo INSERT "'%f', %b, '%u@%v', now()" filehistory

would log the name of any file stored or retrieved, the number of bytes transferred, the user and host doing the transfer, and the time of transfer (at least in MySQL). This would translate into a query like: “INSERT INTO filehistory VALUES (‘somefile’, 12345, ‘joe@joe.org’, ’21-05-2001 20:01:00′)”

 


SQLLogFile

Syntax: SQLLogFile file
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.8rc2 and later

The SQLLogFile directive is used to specify a log file for mod_sql reporting and debugging, and can be done a per-server basis. The file parameter must be the full path to the file to use for logging. Note that this path must not be to a world-writeable directory and, unless AllowLogSymlinks is explicitly set to on (generally a bad idea), the path must not be a symbolic link.

If file is “none”, no logging will be done at all; this setting can be used to override a SQLLogFile setting inherited from a <Global> context.

 


SQLMinID

Syntax: SQLMinID minimum-id
Default: 999
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

SQLMinID is a quick way of setting both SQLMinUserGID and SQLMinUserUID. These values are checked whenever retrieving a user’s GID or UID.

See also: SQLMinUserGIDSQLMinUserUID

 


SQLMinUserGID

Syntax: SQLMinUserGID minimum-gid
Default: 999
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

SQLMinUserGID is checked whenever retrieving a user’s GID. If the retrieved value for GID is less than the value of SQLMinUserGID, it is reported as the value ofSQLDefaultGID.

See also: SQLDefaultGID

 


SQLMinUserUID

Syntax: SQLMinUserUID minimum-uid
Default: 999
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.0 and later

SQLMinUserUID is checked whenever retrieving a user’s UID. If the retrieved value for UID is less than the value of SQLMinUserUID, it is reported as the value ofSQLDefaultUID.

See also: SQLDefaultUID

 


SQLNamedConnectInfo

Syntax: SQLConnectInfo connection-name sql-backend connection-info [username] [password] [policy]
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.3.4rc2 and later

The SQLNamedConnectInfo directive configures the information necessary to connect to the backend database. This connection will be given the name connection-name; this named connection can then be referenced by that name in e.g. a SQLNamedQuery. The sql-backend name configures the backend SQL module to use for this connection; seeSQLBackend. The connection-info parameter specifies the database, host, port, and other backend-specific information. The optional username and password parameters specify a username and password to use when connecting to the database. Both default to NULL, which the backend will treat in some backend-specific manner. If you specify a password, you must specify a username. Multiple SQLNamedConnectInfo directives can be configured.

Note that SQLNamedConnectInfo directives will only be honored if a SQLConnectInfo directive is configured.

See also: SQLBackendSQLConnectInfo

 


SQLNamedQuery

Syntax: SQLNamedQuery name type query-string [table] [connection-name]
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.4 and later

SQLNamedQuery specifies a query and an identifier (name) for later use by SQLShowInfo and SQLLog.

It is strongly recommended that you read documentation on the LogFormat and ExtendedLog directives, as the meta-sequences available to SQLNamedQuery are largely equivalent.

The first parameter, name, should be unique across all named queries and must not contain spaces. The result of re-using a name is undefined.

The second parameter, type, is the type of query, either “SELECT”, “UPDATE”, “INSERT”, or “FREEFORM”. See the note below for information on FREEFORM type queries.

The third parameter is the substance of the database query itself; this should match the form of the second parameter. The meta-sequences accepted are exactly equivalent to the LogFormat directive except the following are not accepted:

    • %{FOOBAR}e
      For LogFormat, this logs the content of environment variable “FOOBAR”. In a SQLNamedQuery SQL statement, however, environment variables can be referenced using %{env:FOOBAR}.

 

    • %{format}t and %t
      These two meta-sequences logged the local server time; they are not available in mod_sql. Your database undoubtedly provides another way to get the time; for example, MySQL provides the now() function.
      However, a %{time:format} meta-sequence is supported. This sequence allows for formatting time strings according to the rules of the strftime(3) function.

and the following is in addition to the LogFormat meta-sequences:

    • %d
      The current working directory or “-” if none.

 

    • %{n}
      This meta-sequence is used internally by mod_sql and other third-party modules and patches to pass information to the database. Using this meta-sequence in anything other than an INSERT or UPDATE query is an error, and using this meta-sequence unless directed to by a third-party module or patch is also an error.

The correct form of a query will be built from the directive arguments, except in the case of FREEFORM queries which will be sent directly to the database. The examples below show the way queries are built from the arguments.

The fourth parameter, table, is only necessary for UPDATE or INSERT type queries, but is required for those types.

The last parameter, connection-name, is only necessary for telling mod_sql to use the specified connection name (from SQLNamedConnectInfo), rather than the default connection, for executing the query/statement.

NoteFREEFORM queries are a necessary evil; the simplistic query semantics of the UPDATEINSERT, and SELECT type queries do not sufficiently expose the capabilities of most backend databases. At the same time, using a FREEFORM query makes it impossible for mod_sql to check whether the query type is appropriate, making sure that a SELECTquery is not used in a SQLLog directive, for instance. Wherever possible, it is recommended that a specific query type be used.

Examples:

  SQLNamedQuery count SELECT "count from users where userid='%u'"

creates a query named “count” which could be used by SQLShowInfo to inform a user of their login count. The actual query would look something like “SELECT count FROM users WHERE userid=’matilda'” for user “matilda”.

  SQLNamedQuery updatecount UPDATE "count=count+1 WHERE userid='%u'" users

creates a query named “updatecount” which could be used by SQLLog to update a user login counter in the table users. The actual query would look something like “UPDATE users SET count=count+1 WHERE userid=’persephone'” for user “persephone”.

  SQLNamedQuery accesslog INSERT "now(), '%u'" accesslog

creates a query named “accesslog” which could be used by SQLLog to track access times by clients. The actual query would look something like “INSERT INTO accesslog VALUES (now(), ‘pandora’)” for user “pandora”. Note that this may be too simplistic for your table structure, since most databases require data for all columns to be provided in an INSERT statement of this form. See the following FREEFORM query for an example of something which may suit your needs better.

  SQLNamedQuery accesslog FREEFORM "INSERT INTO accesslog(date, user) VALUES (now(), '%u')"

creates a query named “accesslog” which could be used by SQLLog to track access times by clients. The actual query would look something like “INSERT INTO accesslog(date, user) VALUES (now(), ’tilda’)” for user “tilda”.

 


SQLNegativeCache

Syntax: SQLNegativeCache on|off
Default: off
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.7rc1 and later

SQLNegativeCache specifies whether or not to cache negative responses from SQL lookups when using SQL for UID/GID lookups. Depending on your SQL tables, there can be a significant delay when a directory listing is performed as the UIDs not in the SQL database are repeatedly looked up in an attempt to present usernames instead of UIDs in directory listings. With SQLNegativeCache set to on, negative (“not found”) responses from SQL queries will be cached and speed will improve on directory listings that contain many users not present in the SQL database.

 


SQLOptions

Syntax: SQLOptions opt1 …
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.3.1rc1 and later

The SQLOptions directive is used to tweak various optional behavior of mod_sql.

Example:

  SQLOptions NoDisconnectOnError

The currently implemented options are:

    • IgnoreConfigfile
      If the database client library in use has its own configuration file (e.g. my.cnf for the MySQL client library), then the mod_sql sub-module will read that file. There are some cases, however, where access to that config file is restricted. For such cases, use “IgnoreConfigFile” to tell mod_sql to not attempt to read that client library configuration file.

      Note that this option first appeared in proftpd-1.3.5rc4.

 

  • NoDisconnectOnError
    By default, mod_sql will automatically disconnect the client whenever there is a database error. If this option is enabled, mod_sql will attempt to continue functioning despite database errors.

     

  • NoReconnect
    If supported by the database client library, mod_sql will try to automatically reconnect once, if it determines that the connection to the database server was lost. Use “NoReconnect” to disable this auto-reconnection attempt.

     

  • UseNormalizedGroupSchema
    If this option is enabled, then mod_sql, when retrieving all of the groups for a user, will use a SQL statement like:

      SELECT groupname, groupid, member FROM grouptable WHERE member = $userName
    

    rather than the statement it uses by default, which is:

      SELECT groupname, groupid, member FROM grouptable WHERE member = $userName OR
        member LIKE '%,$userName' OR member LIKE '$username,%' OR
        member LIKE '%,$userName,%'
    

    To put it another way, when this SQLOption is used, mod_sql will not treat the member column of the group table as a comma-delimited list of user names, but rather as a single user name. Thus, to have a user belong in multiple groups with this normalized schema, the group table would have individual rows for each user/group pair.

 


SQLRatios

Syntax: 
Default: None
Context: 
Module: mod_sql
Compatibility:

 


SQLRatioStats

Syntax: 
Default: None
Context: 
Module: mod_sql
Compatibility:

 


SQLShowInfo

Syntax: SQLShowInfo cmd-set numeric query-string
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

This directive creates a message to be sent to the user after any successful command.

The first parameter, the cmd-set, is a comma separated (no spaces) list of FTP commands for which this log command will trigger. The list of commands is too long to list in entirety; commands include: CWD, DELE, HELP, LIST, MKD, MODE, NLST, PASS, PASV, PORT and many more. For the complete list check the FTP RFCs.

FTP commands in the command set will only be triggered if they complete successfully. Prefixing any command with “ERR_” will show information only if there was an error in command processing. To send a message on both errors and successfull completion of a given command X, therefore, you’ll need both “X” and “ERR_X” in yourcmd-set.

The special command “*” matches all FTP commands, while “ERR_*” matches all errors.

The second parameter, numeric, specifies the numeric value of the message returned to the FTP client. Do not choose a number blindly: message numbers may be parsed by clients. In most cases you will want to use 214, the “Help message” numeric. It specifies that the information is only meant to be human readable. Note that FTP clients can be very picky about these response codes; choosing the wrong code can cause clients not to work. Section 5.4 of RFC959 defines the acceptable response codes for each FTP command.

The third parameter, query-string, is exactly equivalent to the query-string parameter to the SQLLog directive, with one addition:

    • %{name}
      The first return value from the SQLNamedQuery identified by “name”. There is currently no way to retrieve more than one value from the database at a time.

Any references to non-existent named queries, non-SELECT or –FREEFORM type queries, or references to queries which return a NULL first value, will be replaced with the string “{null}”. For example:

  SQLNamedQuery count SELECT "count from users where userid='%u'"
  SQLShowInfo PASS 230 "You've logged on %{count} times, %u"

As long as the information is in the database, these two directives specify that the user will be greeted with their login count each time they successfully login. Note the use of the “230” numeric, which means “User logged in, proceed”. “230” is appropriate in this case because the message will be sent immediately after their password has been accepted and the session has started.

 


SQLUserInfo

Syntax: SQLUserInfo user-table user-name passwd uid gid home-dir shell
Default: “users userid passwd uid gid homedir shell”
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The SQLUserInfo directive configures the user table and fields that hold user information. If you need to change any of these field names from the default, you must specify all of them, whether NULL or not. The parameters are described below:

    • usertable
      Specifies the name of the table that holds user information.

 

    • username
      Specifies the field in the user table that holds the username.

 

    • passwd
      Specifies the field in the user table that holds the user’s password.

 

    • uid
      Specifies the field in the user table that holds the user’s UID. When a UID is retrieved from the database it is checked against the value of SQLMinUserUID. If the field name is specified as “NULL” the database will not be queried for this value and the user’s UID will be set to the value of SQLDefaultUID.

 

    • gid
      Specifies the field in the user table that holds the user’s GID. When a GID is retrieved from the database it is checked against the value of SQLMinUserGID. If the field name is specified as “NULL” the database will not be queried for this value and the user’s GID will be set to the value of SQLDefaultGID.

 

    • homedir
      Specifies the field in the user table that holds the user’s home directory. If the fieldname is specified as “NULL” the database will not be queried for this value and the user’s home directory will be set to the value of SQLDefaultHomedir. If no home directory is set with either directive, user authentication will be automatically turned off.

 

    • shell
      Specifies the field in the user table that holds the user’s shell. If the fieldname is specified as “NULL” the database will not be queried and the shell will be reported as an empty string (“”).

 

Custom Queries
As of 1.2.9rc1, the SQLUserInfo directive accepts an alternate syntax:

  SQLUserInfo custom:/name

where name refers to a configured SELECT SQLNamedQuery. This named query must return one row, and return the following columns, in this order: username, passwd, uid, gid, homedir, shell. The configured query may make use of the variables mentioned in the SQLLog description. This syntax allows the administrator a flexible way of constructing queries as needed. Note that if you want use the given USER name, you should use the %U variable, not %u; the latter requires the locally authenticated user name, which is exactly what SQLUserInfo is meant to provide.

Starting with 1.3.3rc1, you can supply other queries as well. You can supply a SELECT SQLNamedQuery for returning the same information as above, only this query is given a UID instead of a user name. For example:

  SQLNamedQuery get-user-by-name SELECT "userid, passwd, uid, gid, homedir, shell FROM ftpusers WHERE userid = '%U'"
  SQLNamedQuery get-user-by-id SELECT "userid, passwd, uid, gid, homedir, shell FROM ftpusers WHERE uid = %{0}"

  SQLUserInfo custom:/get-user-by-name/get-user-by-id

If your custom get-user-by-name query references a table other than the default users table, then you must also supply a custom get-user-by-id query as well. Otherwise, mod_sql will fail with a “Table not found” error, and disconnect the client, whenever the client asks for a directory listing.

Note, however, that if you use the userset or usersetfast SQLAuthenticate options, you will need to supply some additional SQLNamedQuery names in your custom SQLUserInfodirective. The SQLUserInfo directive supports:

  SQLUserInfo custom:/lookup-by-name[/lookup-by-id[/userset-lookup[/usersetfast-lookup]]]

where userset-lookup is the name of a SELECT SQLNamedQuery that returns the names (and only the names) of all users, and the usersetfast-lookup refers to a SELECTSQLNamedQuery that returns all the fields (i.e. username, passwd, uid, gid, homedir, shell in that order) for all users.

To provide a concrete example:

  SQLAuthenticate users groups usersetfast
  SQLUserInfo custom:/get-user-by-name/get-user-by-id/get-user-names/get-all-users
  SQLNamedQuery get-user-by-name SELECT "userid, passwd, uid, gid, homedir, shell FROM users WHERE userid = '%U'"
  SQLNamedQuery get-user-by-id SELECT "userid, passwd, uid, gid, homedir, shell FROM users WHERE uid = %{0}"
  SQLNamedQuery get-user-names SELECT "userid FROM users"
  SQLNamedQuery get-all-users SELECT "userid, passwd, uid, gid, homedir, shell FROM users"

With a configuration similar to these, using either of the userset or usersetfast in your SQLAuthenticate directive will work as expected.

See AlsoSQLAuthenticateSQLGroupInfoSQLLogSQLNamedQuery

 


SQLUserPrimaryKey

Syntax: SQLUserPrimaryKey column | “custom:/”named-query
Default: uid
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.3.5rc3 and later

The SQLUserPrimaryKey directive configures the column name that is used as the “primary key” for user data; this primary key can then be used in other SQL tables via foreign key constraints. The SQL howto covers using this directive in more details.

See also: SQLGroupPrimaryKey

 


SQLUserWhereClause

Syntax: SQLUserWhereClause where-clause 
Default: None
Context: server config, <VirtualHost><Global>
Module: mod_sql
Compatibility: 1.2.5rc1 and later

The directive is used to configure a WHERE clause that is added to every user query. The WHERE clause must contain all relevant punctuation, and must not contain a leading “and”.

As an example of a possible use for this directive, imagine if your user table included a “LoginAllowed” field:

  SQLUserWhereClause "LoginAllowed = 'true'"

would be appended to every user-related query as the string:

  " WHERE (LoginAllowed = 'true')"

Note that if custom user SQLNamedQuery are configured, those custom queries will be used as is; any configured SQLUserWhereClause will not be appended. Custom queries can be of any format/syntax, and thus simply appending a SQLUserWhereClause to a custom query may be syntactically invalid.

As of ProFTPD 1.3.1rc2, the configured SQLUserWhereClause parameter can use the same set of variables as supported by the SQLNamedQuery directive.

 


Installation

The mod_sql module is distributed with ProFTPD. Simply follow the normal steps for using third-party modules in proftpd:

  ./configure --with-modules=sql-module-opts

where the specific sql-module-opts depend on your database needs. For example, if using MySQL, sql-module-opts would be “mod_sql:mod_sql_mysql”. mod_sql is the main SQL-processing engine, and mod_sql_mysql is the backend sub-module that handles MySQL-specific details. If Postgres is your database of choice, sql-module-opts would be “mod_sql:mod_sql_postgres”.

You will also need to tell configure how to find the database-specific libraries and header files:

  ./configure --with-modules=sql-module-opts \
    --with-includes=/path/to/db/header/file/dir \
    --with-libraries=/path/to/db/library/file/dir

Complete the build with the following standard commands:

  make
  make install

 


Author: $Author: castaglia $
Last Updated: $Date: 2014/01/21 22:32:31 $


© Copyright 2000-2014 The ProFTPD Project
All Rights Reserved