使用 MyQL 验证 OpenVPN 用户笔记

Posted on August 8, 2010 by gkp

openvpn_mysql

上次我们成功的在 Debian 上安装了 OpenVPN，但是用证书方式验证用户还是有些麻烦：昨天和 @cnkang 吃晚饭说给他开一个 OpenVPN 帐号，回家后还得去服务器那边生成证书，再让他下载。。。遂 Google 一把，发现可以使用 pam + MySQL 验证，这样不但以后给朋友开账户方便了，而且还可以多台主机使用一个 MySQL 表，实现多台服务器账户信息共享，配置过程简单整理记录如下

前提条件：安装好 OpenVPN，并且客户端可以使用证书方式正常连接。

安装 pam-mysql

aptitude install libpam-dev libpam-mysql libmysql++-dev sasl2-bin
Ubuntu 的话，包的名字稍有区别，比如：libmysqlclient-dev 和 libpam0g-dev

如果喜欢自己使用源代码编译 pam-mysql 的话，可能在 ./configure 时会遇到

configure: error: C compiler cannot create executables
See `config.log' for more details.

这时候先

aptitude install build-essential

就可以了

建 MySQL 库，表，用户

mysql> create database openvpn;
mysql> GRANT ALL ON openvpn.* TO openvpn@localhost IDENTIFIED BY 'PASSWORD';
mysql> FLUSH PRIVILEGES;
mysql> use openvpn;
mysql> CREATE TABLE vpnuser (
-> username char(20) NOT NULL,
-> password char(128) default NULL,
-> active int(10) NOT NULL DEFAULT 1,
-> PRIMARY KEY (username)
-> );
mysql> INSERT INTO vpnuser (username,password) values('somebody',password('secret'));

配置 pam-mysql

vi /etc/pam.d/openvpn

内容：

auth optional pam_mysql.so user=openvpn passwd=PASSWORD host=localhost db=openvpn table=vpnuser usercolumn=username passwdcolumn=password where=active=1 crypt=2

account required pam_mysql.so user=openvpn passwd=PASSWORD host=localhost db=openvpn table=vpnuser usercolumn=username passwdcolumn=password where=active=1 crypt=2

注意这里 crypt=2，是指密码需要用 mysql 的 password()，如果 crypt=3 的话密码则需要 MD5()

如果 pam 验证有问题的话，可以用 verbose=1 启用 debug，然后在 /var/log/auth.log 察看具体信息。此外，还可以将验证过程日至也保存到表格中，用 sqllog=1 启用，具体语法参考 pam-mysql 的 README 即可。

用 sasl2 验证一下

saslauthd -a pam
testsaslauthd -u USERNAME -p PASSWORD -s openvpn

如果出现

0: OK "Success."

就表示 pam-mysql 验证没有问题了

修改 OpenVPN 配置

cp /usr/lib/openvpn/openvpn-auth-pam.so /etc/openvpn/
vi /etc/openvpn/server.conf

在最后加入：

#begin mysql
plugin ./openvpn-auth-pam.so openvpn
client-cert-not-required
username-as-common-name

客户端 conf 文件修改，将原来的

cert client.crt
key client.key

这两行注释掉，并加入一行

auth-user-pass

保存即可。

到此为止所有准备工作就完成了，用 Tunnelblick 启动这个新的客户端配置文件的话，会有对话框提示用户名和密码，输入并保存到 keychain 以后就一劳永逸了。

另外，网上很多用户遇到了使用 sasl 验证没问题，但是 OpenVPN 验证不过去的现象，我也在这个问题上卡了好久，/var/log/auth.log 是这么说的：

Aug  9 01:13:34 fremont openvpn[16395]: PAM unable to dlopen(/lib/security/pam_mysql.so): /lib/security/pam_mysql.so: undefined symbol: pam_get_item
Aug  9 01:13:34 fremont openvpn[16395]: PAM adding faulty module: /lib/security/pam_mysql.so

还是 Google 到答案，貌似是个 BUG ，有个 walk around 就是

export LD_PRELOAD=/lib/libpam.so.0

可以写到 /etc/rc.local 里面`

#openvpn pam fix
export LD_PRELOAD=/lib/libpam.so.0
/etc/init.d/openvpn restart

有了更好的解决办法了，直接用这篇文章里面的 patch 文件或者 testing 的包就搞定了。

这下不应该有任何问题了，把 ca.crt 放到你的网站让用户下载吧，以后只需要在 MySQL 表格中添加记录就可以开帐号了。

参考：

This entry was posted in 我的慵懒生活, 电脑相关 and tagged , , , , . Bookmark the permalink.

34 Responses to 使用 MyQL 验证 OpenVPN 用户笔记

  1. Pingback: pam-mysql 验证 bug 的最佳解决方案 | gkp's post

  2. zhaiduo says:
    August 11, 2010 at 6:22 am

    收藏，有空试试～

    Reply

  3. Pingback: Linux 系统连接 OpenVPN 无法获取推送 DNS 服务器的解决 | gkp's post

  4. Pingback: 使用 MySQL 进行 PPP 验证 | gkp's post

  5. ym says:
    August 21, 2010 at 12:42 pm

    hi.我尝试了一下,未成功.提示未通过认证,不知何故?谢谢回复
    Sat Aug 21 12:19:46 2010 OpenVPN 2.1_rc15 i686-pc-mingw32 [SSL] [LZO2] [PKCS11] built on Nov 19 2008
    Sat Aug 21 12:19:58 2010 NOTE: OpenVPN 2.1 requires ‘–script-security 2’ or higher to call user-defined scripts or executables
    Sat Aug 21 12:19:58 2010 LZO compression initialized
    Sat Aug 21 12:19:58 2010 Control Channel MTU parms [ L:1542 D:138 EF:38 EB:0 ET:0 EL:0 ]
    Sat Aug 21 12:19:58 2010 Data Channel MTU parms [ L:1542 D:1450 EF:42 EB:135 ET:0 EL:0 AF:3/1 ]
    Sat Aug 21 12:19:58 2010 Local Options hash (VER=V4): ‘41690919’
    Sat Aug 21 12:19:58 2010 Expected Remote Options hash (VER=V4): ‘530fdded’
    Sat Aug 21 12:19:58 2010 Socket Buffers: R=[8192->8192] S=[8192->8192]
    Sat Aug 21 12:19:58 2010 UDPv4 link local: [undef]
    Sat Aug 21 12:19:58 2010 UDPv4 link remote: 67.202.105.135:443
    Sat Aug 21 12:19:58 2010 TLS: Initial packet from 67.202.105.135:443, sid=fe0ff235 dd2db9b0
    Sat Aug 21 12:19:58 2010 WARNING: this configuration may cache passwords in memory — use the auth-nocache option to prevent this
    Sat Aug 21 12:19:59 2010 VERIFY OK: depth=1, /C=cn/ST=gd/L=sz/O=luckypoem/OU=sales/CN=ym/emailAddress=luckypoem@gmail.com
    Sat Aug 21 12:19:59 2010 VERIFY OK: nsCertType=SERVER
    Sat Aug 21 12:19:59 2010 VERIFY OK: depth=0, /C=cn/ST=gd/L=sz/O=luckypoem/OU=sales/CN=ym/emailAddress=luckypoem@gmail.com
    Sat Aug 21 12:20:00 2010 Data Channel Encrypt: Cipher ‘BF-CBC’ initialized with 128 bit key
    Sat Aug 21 12:20:00 2010 Data Channel Encrypt: Using 160 bit message hash ‘SHA1’ for HMAC authentication
    Sat Aug 21 12:20:00 2010 Data Channel Decrypt: Cipher ‘BF-CBC’ initialized with 128 bit key
    Sat Aug 21 12:20:00 2010 Data Channel Decrypt: Using 160 bit message hash ‘SHA1’ for HMAC authentication
    Sat Aug 21 12:20:00 2010 Control Channel: TLSv1, cipher TLSv1/SSLv3 DHE-RSA-AES256-SHA, 1024 bit RSA
    Sat Aug 21 12:20:00 2010 [ym] Peer Connection Initiated with 67.202.105.135:443
    Sat Aug 21 12:20:01 2010 SENT CONTROL [ym]: ‘PUSH_REQUEST’ (status=1)
    Sat Aug 21 12:20:02 2010 AUTH: Received AUTH_FAILED control message
    Sat Aug 21 12:20:02 2010 TCP/UDP: Closing socket
    Sat Aug 21 12:20:02 2010 SIGTERM[soft,auth-failure] received, process exiting

    Reply
  6. ym says:
    August 21, 2010 at 2:36 pm

    testsaslauthd 测试通过了。显示：0: OK “Success.”

    Reply
  7. ym says:
    August 21, 2010 at 2:37 pm

    testsaslauthd 测试通过了，显示：0: OK “Success.”

    Reply
  8. ym says:
    August 21, 2010 at 7:49 pm

    Aug 21 04:19:04 localhost openvpn[711]: PAM unable to dlopen(/lib/security/pam_mysql.so): /lib/security/pam_mysql.so: undefined symbol: pam_get_item
    Aug 21 04:19:04 localhost openvpn[711]: PAM adding faulty module: /lib/security/pam_mysql.so,
    /var/log/auth.log里有如上内容。问题出在哪里呢？
    我把export LD_PRELOAD=/lib/libpam.so.0写到 /etc/rc.local 里面了：
    #openvpn pam fix
    export LD_PRELOAD=/lib/libpam.so.0

    Reply
  9. ym says:
    August 24, 2010 at 12:23 am

    终于搞好了。我是这样做的：
    # cd /etc/openvpn/easy-rsa/2.0# openvpn –genkey –secret keys/ta.key
    # cp /etc/openvpn/easy-rsa/2.0/keys/ta.key /etc/openvpn/
    然后修改服务器端的配置文件，除了你说的外，还要在文件最后加上tls-auth ta.key 0
    下载ca.crt和ta.key到c:\program files\openvpn\config,
    然后修改客户端的配置文件，除了你说的外,还要在文件最后加上tls-auth ta.key 1即可。

    Reply
  10. ym says:
    August 24, 2010 at 4:21 pm

    hi.
    如何在 MySQL 表格中添加新的用户？我对mysql语句不熟悉，勿要见笑啊

    Reply
  11. ym says:
    August 25, 2010 at 10:40 am

    hi.添加了如上命令，回车然后出现->，就完成了在 MySQL 表格中添加新的用户吗？还是要再输入什么命令？->变不成#啊，怎么办？

    Reply
  12. ym says:
    August 25, 2010 at 5:51 pm

    hi.你说的name应该为username,这下子终于搞定了。
    以下为显示的过程，又显示TLS key negotiation failed，没想到过了1分钟左右，竟然连上了。

    Wed Aug 25 17:43:10 2010 OpenVPN 2.1_rc15 i686-pc-mingw32 [SSL] [LZO2] [PKCS11] built on Nov 19 2008
    Wed Aug 25 17:43:22 2010 NOTE: OpenVPN 2.1 requires ‘–script-security 2’ or higher to call user-defined scripts or executables
    Wed Aug 25 17:43:23 2010 Control Channel Authentication: using ‘ta.key’ as a OpenVPN static key file
    Wed Aug 25 17:43:23 2010 Outgoing Control Channel Authentication: Using 160 bit message hash ‘SHA1’ for HMAC authentication
    Wed Aug 25 17:43:23 2010 Incoming Control Channel Authentication: Using 160 bit message hash ‘SHA1’ for HMAC authentication
    Wed Aug 25 17:43:23 2010 LZO compression initialized
    Wed Aug 25 17:43:23 2010 Control Channel MTU parms [ L:1542 D:166 EF:66 EB:0 ET:0 EL:0 ]
    Wed Aug 25 17:43:23 2010 Data Channel MTU parms [ L:1542 D:1450 EF:42 EB:135 ET:0 EL:0 AF:3/1 ]
    Wed Aug 25 17:43:23 2010 Local Options hash (VER=V4): ‘504e774e’
    Wed Aug 25 17:43:23 2010 Expected Remote Options hash (VER=V4): ‘14168603’
    Wed Aug 25 17:43:23 2010 Socket Buffers: R=[8192->8192] S=[8192->8192]
    Wed Aug 25 17:43:23 2010 UDPv4 link local: [undef]
    Wed Aug 25 17:43:23 2010 UDPv4 link remote: 67.202.105.135:443
    Wed Aug 25 17:44:23 2010 TLS Error: TLS key negotiation failed to occur within 60 seconds (check your network connectivity)
    Wed Aug 25 17:44:23 2010 TLS Error: TLS handshake failed
    Wed Aug 25 17:44:23 2010 TCP/UDP: Closing socket
    Wed Aug 25 17:44:23 2010 SIGUSR1[soft,tls-error] received, process restarting

    Reply
    • gkp says:
      August 26, 2010 at 7:07 pm

      err，确实打错了，这个和 pam.d 的配置文件中写的字段名对应即可。你第一次没连接上应该是 TLS 验证超时了：
      TLS Error: TLS key negotiation failed to occur within 60 seconds (check your network connectivity)
      TLS Error: TLS handshake failed

      Reply
  13. ym says:
    September 10, 2010 at 12:34 pm

    在linux desktop下，能这样添加openvpn 帐号吗

    Reply
  14. 歪歪儿 says:
    September 12, 2010 at 8:14 pm

    PLUGIN_CALL: plugin function PLUGIN_AUTH_USER_PASS_VERIFY failed with status 1 Permission denied
    出现这样的错误。用testsaslauthd验证可以通过。不知道哪里有问题。
    系统是centos 5.4
    （上一条评论输入错email了）

    Reply
    • gkp says:
      September 12, 2010 at 8:48 pm

      把完整的日至贴上来看看？

      Reply
      • 歪歪儿 says:
        September 12, 2010 at 10:47 pm

        Sun Sep 12 22:45:25 2010 OpenVPN 2.1.1 i386-redhat-linux-gnu [SSL] [LZO2] [EPOLL] [PKCS11] built on Jan 26 2010
        Sun Sep 12 22:45:25 2010 NOTE: OpenVPN 2.1 requires ‘–script-security 2’ or higher to call user-defined scripts or executables
        Sun Sep 12 22:45:25 2010 PLUGIN_INIT: POST /etc/openvpn/openvpn-auth-pam.so ‘[/etc/openvpn/openvpn-auth-pam.so] [openvpn]’ intercepted=PLUGIN_AUTH_USER_PASS_VERIFY
        Sun Sep 12 22:45:25 2010 Diffie-Hellman initialized with 1024 bit key
        Sun Sep 12 22:45:25 2010 WARNING: POTENTIALLY DANGEROUS OPTION –client-cert-not-required may accept clients which do not present a certificate
        Sun Sep 12 22:45:25 2010 TLS-Auth MTU parms [ L:1544 D:140 EF:40 EB:0 ET:0 EL:0 ]
        Sun Sep 12 22:45:25 2010 ROUTE default_gateway=94.229.71.1
        Sun Sep 12 22:45:25 2010 TUN/TAP device tun0 opened
        Sun Sep 12 22:45:25 2010 TUN/TAP TX queue length set to 100
        Sun Sep 12 22:45:25 2010 /sbin/ip link set dev tun0 up mtu 1500
        Sun Sep 12 22:45:25 2010 /sbin/ip addr add dev tun0 local 192.168.11.1 peer 192.168.11.2
        Sun Sep 12 22:45:25 2010 /sbin/ip route add 192.168.11.0/24 via 192.168.11.2
        Sun Sep 12 22:45:25 2010 Data Channel MTU parms [ L:1544 D:1450 EF:44 EB:135 ET:0 EL:0 AF:3/1 ]
        Sun Sep 12 22:45:25 2010 Listening for incoming TCP connection on [undef]:53
        Sun Sep 12 22:45:25 2010 Socket Buffers: R=[87380->131072] S=[16384->131072]
        Sun Sep 12 22:45:25 2010 TCPv4_SERVER link local (bound): [undef]:53
        Sun Sep 12 22:45:25 2010 TCPv4_SERVER link remote: [undef]
        Sun Sep 12 22:45:25 2010 MULTI: multi_init called, r=256 v=256
        Sun Sep 12 22:45:25 2010 IFCONFIG POOL: base=192.168.11.4 size=62
        Sun Sep 12 22:45:25 2010 MULTI: TCP INIT maxclients=1024 maxevents=1028
        Sun Sep 12 22:45:25 2010 Initialization Sequence Completed
        Sun Sep 12 22:45:29 2010 MULTI: multi_create_instance called
        Sun Sep 12 22:45:29 2010 Re-using SSL/TLS context
        Sun Sep 12 22:45:29 2010 LZO compression initialized
        Sun Sep 12 22:45:29 2010 Control Channel MTU parms [ L:1544 D:140 EF:40 EB:0 ET:0 EL:0 ]
        Sun Sep 12 22:45:29 2010 Data Channel MTU parms [ L:1544 D:1450 EF:44 EB:135 ET:0 EL:0 AF:3/1 ]
        Sun Sep 12 22:45:29 2010 Local Options hash (VER=V4): ‘c0103fa8’
        Sun Sep 12 22:45:29 2010 Expected Remote Options hash (VER=V4): ‘69109d17’
        Sun Sep 12 22:45:29 2010 TCP connection established with 221.227.***.***:50091
        Sun Sep 12 22:45:29 2010 Socket Buffers: R=[131072->131072] S=[131072->131072]
        Sun Sep 12 22:45:29 2010 TCPv4_SERVER link local: [undef]
        Sun Sep 12 22:45:29 2010 TCPv4_SERVER link remote: 221.227.***.***:50091
        Sun Sep 12 22:45:30 2010 221.227.***.***:50091 TLS: Initial packet from 221.227.***.***:50091, sid=c7655be2 6c1ec452
        AUTH-PAM: BACKGROUND: user ‘yylyyl’ failed to authenticate: Permission denied
        Sun Sep 12 22:45:34 2010 221.227.***.***:50091 PLUGIN_CALL: POST /etc/openvpn/openvpn-auth-pam.so/PLUGIN_AUTH_USER_PASS_VERIFY status=1
        Sun Sep 12 22:45:34 2010 221.227.***.***:50091 PLUGIN_CALL: plugin function PLUGIN_AUTH_USER_PASS_VERIFY failed with status 1: /etc/openvpn/openvpn-auth-pam.so
        Sun Sep 12 22:45:34 2010 221.227.***.***:50091 TLS Auth Error: Auth Username/Password verification failed for peer
        Sun Sep 12 22:45:35 2010 221.227.***.***:50091 Control Channel: TLSv1, cipher TLSv1/SSLv3 DHE-RSA-AES256-SHA
        Sun Sep 12 22:45:35 2010 221.227.***.***:50091 [] Peer Connection Initiated with 221.227.***.***:50091
        Sun Sep 12 22:45:37 2010 221.227.***.***:50091 PUSH: Received control message: ‘PUSH_REQUEST’
        Sun Sep 12 22:45:37 2010 221.227.***.***:50091 Delayed exit in 5 seconds
        Sun Sep 12 22:45:37 2010 221.227.***.***:50091 SENT CONTROL [UNDEF]: ‘AUTH_FAILED’ (status=1)
        Sun Sep 12 22:45:37 2010 221.227.***.***:50091 Connection reset, restarting [0]
        Sun Sep 12 22:45:37 2010 221.227.***.***:50091 SIGUSR1[soft,connection-reset] received, client-instance restarting
        Sun Sep 12 22:45:37 2010 TCP/UDP: Closing socket

        Reply
        • gkp says:
          September 13, 2010 at 11:26 am

          你是不是用了 tls？看看配置文件有没有
          tls-auth ta.key 或者类似的行

          Reply
          • 歪歪儿 says:
            September 18, 2010 at 12:00 pm

            没有开tls啊
            port 53
            proto tcp
            dev tun
            ca /etc/openvpn/easy-rsa/2.0/keys/ca.crt
            cert /etc/openvpn/easy-rsa/2.0/keys/server.crt
            key /etc/openvpn/easy-rsa/2.0/keys/server.key
            dh /etc/openvpn/easy-rsa/2.0/keys/dh1024.pem
            server 192.168.11.0 255.255.255.0
            push “redirect-gateway def1”
            push “dhcp-option DNS 208.67.222.222”
            push “dhcp-option DNS 208.67.220.220”
            client-to-client
            keepalive 10 120
            comp-lzo
            persist-key
            persist-tun
            verb 3
            #begin mysql
            plugin /etc/openvpn/openvpn-auth-pam.so openvpn
            client-cert-not-required
            username-as-common-name

  15. gkp says:
    September 18, 2010 at 4:26 pm

    怪了，客户端配置文件里面也没有tls部分？

    Reply
  16. ym says:
    June 16, 2011 at 1:55 am

    hi.
    你这篇是在debian下的情形。在centos下，搞不定。可否尝试在centos下，安装一下？

    Reply

  17. Pingback: openvpn流量统计的一些问题的解决 | Michaelize

  18. zhangj says:
    August 8, 2011 at 9:46 pm

    gkp 你好
    我按照你所说的重新打了path但是也一样的问题,客户端连接日志显示could not read auth
    usename fom stdln 服务器的日志
    type=USER_ACCT msg=audit(1312810242.787:13): user pid=3767 uid=0auid=4294967295 msg=’PAM: accounting acct=”dhvpn” : exe=”/usr/sbin/saslauthd” (hostname=?, addr=?, terminal=? res=failed)’

    Reply
  19. phoenix says:
    December 26, 2011 at 10:34 pm

    歪歪儿 的那个问题未必是tls的问题，他的那个报错在我最近新编的vpn服务器上突然出现了，没有人操作啥，忽然VPN就断了，重启服务之后又正常了，编译patch那个早就知道了，和老的vpn服务器不同的地方是老的服务器用的是centos5.4+2.0.9的版本，新的是5.7+2.2.1的版本，2.0.9那个从来没出现过这种状况，这新的连一个月都没有到就碰到鬼了。

    Reply

  20. Pingback: ubuntu下openvpn通过mysql（pam）认证 | 计算机－linux- 开源

  21. LIANG says:
    January 17, 2015 at 5:15 pm

    openvpn创建于windows server 2008 r2，使用用户名和密码验证的时候，找不到适用于windows的auth_pam 文件。so的似乎不能在windows下运行，敢问大侠可有留存一份？

    Reply

Leave a Reply to gkp Cancel reply

Your email address will not be published.