Finding foreign keys that are missing indexes


一个适用于 CN 网络的 VPN 解决方案



  • VPN Clients 是位于 CN 网络的 VPN 客户端。
  • 和 可以是同一台机器,应位于防火墙(即 the Great Firewall,下同)内。
  • 和 位置无所谓,但是最好和 很近或者位于同一台机器,以便获得更短的延时。
  • 应位于防火墙外。

安装 shadowsocks-libev 服务器端
在 FreeBSD 上安装 PostgreSQL
在 FreeBSD 上安装 FreeRADIUS
使用 dnsmasq 和 shadowsocks-libev 构建一个干净的域名服务器
strongSwan 的安装和配置

#cisco-ipsec, #dnsmasq, #freebsd, #freeradius, #gfw, #gfwlist, #gfwlist2dnsmasq-awk, #ikev2, #ipsec, #iptables, #letsencrypt, #postgresql, #shadowsocks-libev, #ss-redir, #ss-server, #ss-tunnel, #strongswan, #ubuntu, #vpn

strongSwan 的安装和配置


在 Ubuntu 上安装配置 strongSwan,使用 letsencrypt 的 SSL 证书,并采用 FreeRADIUS 作为用户认证审计等。

使用 ss-redir(包含在 shadowsocks-libev) 转发非 CN 的 IP 的请求到 ss-server(包含在)。


本文假设 strongSwan 的 hostname 为,IP 地址为,FreeRADIUS 的 hostname 为。


$ apt install strongswan strongswan-plugin-eap-radius strongswan-plugin-xauth-eap
$ apt install letsencrypt
$ letsencrypt certonly --standalone -d -m --agree-tos

$ cp /etc/letsencrypt/live/ /etc/ipsec.d/certs/
$ cp /etc/letsencrypt/live/ /etc/ipsec.d/private/
$ cp /etc/letsencrypt/live/ /etc/ipsec.d/cacerts/

/etc/crontab 增加:

30 0    1 * *   root    letsencrypt renew --quiet
30 1    1 * *   root    /root/bin/

其中 /root/bin/ (chmod +x) 内容如下:


cp "${from}/chain.pem"   "${to}/cacerts/chain.pem"
cp "${from}/cert.pem"    "${to}/certs/cert.pem"
cp "${from}/privkey.pem" "${to}/private/privkey.pem"



开启 IPv4 包转发:

# Uncomment the next line to enable packet forwarding for IPv4


# ipsec.conf - strongSwan IPsec configuration file

# basic configuration

config setup
        # strictcrlpolicy=yes
        uniqueids = no

conn %default

conn Cisco-IPSec
conn IKEv2
# Windows and BlackBerry clients usually goes here
conn IKEv2-mschapv2
# Apple clients usually goes here
conn IKEv2-mschapv2-apple



# This file holds shared secrets or RSA private keys for authentication.

# RSA private key for this host, authenticating it to any other host
# which knows the public part.

: RSA privkey.pem
: PSK "the Great Wall"


eap-radius {

    # Send RADIUS accounting information to RADIUS servers.
    accounting = yes

    # Close the IKE_SA if there is a timeout during interim RADIUS accounting
    # updates.
    # accounting_close_on_timeout = yes

    # Interval in seconds for interim RADIUS accounting updates, if not
    # specified by the RADIUS server in the Access-Accept message.
    # accounting_interval = 0

    # If enabled, accounting is disabled unless an IKE_SA has at least one
    # virtual IP. Only for IKEv2, for IKEv1 a virtual IP is strictly necessary.
    # accounting_requires_vip = no

    # Use class attributes in Access-Accept messages as group membership
    # information.
    # class_group = no

    # Closes all IKE_SAs if communication with the RADIUS server times out. If
    # it is not set only the current IKE_SA is closed.
    # close_all_on_timeout = no

    # Send EAP-Start instead of EAP-Identity to start RADIUS conversation.
    # eap_start = no

    # Use filter_id attribute as group membership information.
    # filter_id = no

    # Prefix to EAP-Identity, some AAA servers use a IMSI prefix to select the
    # EAP method.
    # id_prefix =

    # Whether to load the plugin. Can also be an integer to increase the
    # priority of this plugin.
    load = yes

    # NAS-Identifier to include in RADIUS messages.
    # nas_identifier = strongSwan

    # Port of RADIUS server (authentication).
    # port = 1812

    # Base to use for calculating exponential back off.
    # retransmit_base = 1.4

    # Timeout in seconds before sending first retransmit.
    # retransmit_timeout = 2.0

    # Number of times to retransmit a packet before giving up.
    # retransmit_tries = 4

    # Shared secret between RADIUS and NAS. If set, make sure to adjust the
    # permissions of the config file accordingly.
    secret = yourPassword

    # IP/Hostname of RADIUS server.
    server =

    # Number of sockets (ports) to use, increase for high load.
    # sockets = 1

    dae {

        # Enables support for the Dynamic Authorization Extension (RFC 5176).
        # enable = no

        # Address to listen for DAE messages from the RADIUS server.
        # listen =

        # Port to listen for DAE requests.
        # port = 3799

        # Shared secret used to verify/sign DAE messages. If set, make sure to
        # adjust the permissions of the config file accordingly.
        # secret =


    forward {

        # RADIUS attributes to be forwarded from IKEv2 to RADIUS.
        # ike_to_radius =

        # Same as ike_to_radius but from RADIUS to IKEv2.
        # radius_to_ike =


    # Section to specify multiple RADIUS servers.
    servers {


    # Section to configure multiple XAuth authentication rounds via RADIUS.
    xauth {




注意修改 secret 和 server 参数:

    # Shared secret between RADIUS and NAS. If set, make sure to adjust the
    # permissions of the config file accordingly.
    secret = yourPassword

    # IP/Hostname of RADIUS server.
    server =


xauth-eap {

    # EAP plugin to be used as backend for XAuth credential verification.
    backend = radius

    # Whether to load the plugin. Can also be an integer to increase the
    # priority of this plugin.
    load = yes



在 /etc/rc.local 的 exit 0 前添加如下代码,便于开机时自动建立监听于 8081 端口的转发,它将请求转发到 ss-server:

ss-redir -c /usr/local/etc/shadowsocks-libev/config.json -u -A -l 8081 -b > /dev/null 2>&1 &


将下面的内容编写文件脚本文件 /root/bin/,需要 ipset (apt install ipset):


# 转发客户端请求。注意网段和 ipsec.conf 里要一致。网卡使用外网网卡。

# strongswan
iptables -A INPUT -p udp --dport 500 -j ACCEPT
iptables -A INPUT -p udp --dport 4500 -j ACCEPT

# Moved to /etc/sysctl.conf: net.ipv4.ip_forward=1
# 打开网卡转发功能。要把客户端的请求转发到公网网卡
# echo 1 > /proc/sys/net/ipv4/ip_forward
iptables -t nat -A POSTROUTING -s ${subnet} -o ${out_iface} -j MASQUERADE
# 允许转发
iptables -A FORWARD -s ${subnet} -j ACCEPT

[ -r chnroute.txt ] || curl '' | grep ipv4 | grep CN | awk -F\| '{ printf("%s/%d\n", $4, 32-log($5)/log(2)) }' > chnroute.txt

iptables -t nat -N SHADOWSOCKS

iptables -t nat -A SHADOWSOCKS -d $server_IP -j RETURN

# 内网网段
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN
iptables -t nat -A SHADOWSOCKS -d -j RETURN

# 创建ipset列表
#ipset -exist create gfwlist hash:net
ipset -exist create chnroute hash:net
cat chnroute.txt | sudo xargs -I ip ipset -exist add chnroute ip

# gfwlist 走ss转发
#iptables -t nat -A SHADOWSOCKS -m set --match-set gfwlist dst -p tcp  -j REDIRECT --to-ports 8081

# 国内ip表直连
iptables -t nat -A SHADOWSOCKS -m set --match-set chnroute dst -j RETURN
# 其他连接走ss转发
iptables -t nat -A SHADOWSOCKS -p tcp  -j REDIRECT --to-ports 8081

iptables -t nat -A PREROUTING -s ${subnet} -p tcp -j SHADOWSOCKS

# ss-redir
iptables -A INPUT -p tcp -s ${subnet}--dport 8081 -j ACCEPT
iptables -A INPUT -p tcp --dport 8081 -j DROP

注意这段代码只是示意,请根据实际情况改进,比如定期更新 ipset chnroute 的 IP 列表,使用 iptables-persistent(apt install iptables-persistent) 来持久化 iptables 规则等。

在使用 iptables-persistent 时可能会用到的命令:

$ iptables-save > /etc/iptables/rules.v4
$ ip6tables-save > /etc/iptables/rules.v6


$ ipsec start --nofork

在 PostgreSQL 数据库 radius 中执行如下 SQL 语句:

/* 用户名为 yourVPNUsername 的密码为 yourVPNPassword */
insert into radcheck(username, attribute, op, value) values('yourVPNUsername', 'Password', '==', 'yourVPNPassword');
/* 可以用来配置月流量为 1GiB */
/* insert into radcheck(username, attribute, op, value) values('yourVPNUsername', 'Max-Monthly-Traffic', ':=', '1024'); */
/* 控制组 plan-monthly-1GiB 的用户的并发会话为 2 */
insert into radgroupcheck(groupname, attribute, op, value) values('plan-monthly-1GiB', 'Simultaneous-Use', ':=', '2');
/* 限制组 plan-monthly-1GiB 的用户的月流量为 1024 MiB */
insert into radgroupcheck(groupname, attribute, op, value) values('plan-monthly-1GiB', 'Max-Monthly-Traffic', ':=', '1024');
/* 将用户 yourVPNUsername 添加到组 plan-monthly-1GiB */
insert into radusergroup(username, groupname) values('yourVPNUsername', 'plan-monthly-1GiB');

注意这里用到的 Max-Monthly-Traffic 是在在 FreeBSD 上安装 FreeRADIUS中定义的。

在 VPN 客户端可以使用 IKEv2 或者 Cisco IPSec 来连接。

Server Address(服务器地址):
Remote ID(远程 ID):
Authentication Settings(鉴定设置): Username(用户名)
Username(用户名): yourVPNUsername
Password(密码): yourVPNPassword

Cisco IPSec:
Server Address(服务器地址):
Account Name(帐户名称): yourVPNUsername
Password(密码): yourVPNPassword
Authentication Settings(鉴定设置):
Machine Authentication(机器鉴定):
Shared Secret(共享的密钥): the Great Wall
(Shared Secret 是配置在 /etc/ipsec.secrets 中的 PSK)


service strongswan start

#cisco-ipsec, #freeradius, #ikev2, #iptables-persistent, #letsencrypt, #postgresql, #shadowsocks-libev, #ss-redir, #strongswan, #ubuntu

#ipset, #iptables

在 FreeBSD 上安装 FreeRADIUS


在 FreeBSD 上安装 FreeRADIUS 并使用 PostgreSQL 作为后端存储,并做一些常用的修改以便给 strongSwan 使用。


  • FreeBSD 11.0-RELEASE-p8
  • FreeRADIUS 2.2.9


$ cd /usr/ports/net/freeradius2 && make config

将 PGSQL(PostgreSQL database support)打勾。

$ make install clean



在最后添加,以便允许 strongSwan 访问该 FreeRADIUS:

# strongswan
client {
        ipaddr =
        secret = mySecret



ATTRIBUTE       Max-Monthly-Traffic     3003    integer
ATTRIBUTE       Monthly-Traffic-Limit   3004    integer


修改 eap 节点下来的 default_eap_type,从 md5 修改为 mschapv2:

default_eap_type = mschapv2


把 $INCLUDE sql.conf 前的注释去掉:

        #  Include another file that has the SQL-related configuration.
        #  This is another file only because it tends to be big.
        $INCLUDE sql.conf

把 $INCLUDE sql/postgresql/counter.conf 前的注释去掉:

        #  This module is an SQL enabled version of the counter module.
        #  Rather than maintaining seperate (GDBM) databases of
        #  accounting info for each counter, this module uses the data
        #  stored in the raddacct table by the sql modules. This
        #  module NEVER does any database INSERTs or UPDATEs.  It is
        #  totally dependent on the SQL module to process Accounting
        #  packets.
        $INCLUDE sql/postgresql/counter.conf


启用基于 SQL 数据库的授权和审计,即将节点 authorize、accounting、session、post-auth 中的 sql 前的注释去掉,并注释掉 session 节点中的 radutmp:

authorize {
        #  Look in an SQL database.  The schema of the database
        #  is meant to mirror the "users" file.
        #  See "Authorization Queries" in sql.conf
accounting {
        #  Log traffic to an SQL database.
        #  See "Accounting queries" in sql.conf
#  Session database, used for checking Simultaneous-Use. Either the radutmp
#  or rlm_sql module can handle this.
#  The rlm_sql module is *much* faster
session {
#       radutmp

        #  See "Simultaneous Use Checking Queries" in sql.conf
post-auth {
        #  After authenticating the user, do another SQL query.
        #  See "Authentication Logging Queries" in sql.conf
        Post-Auth-Type REJECT {
                # log failed authentications in SQL, too.


在节点 authorize 中最后增加:

authorize {



sqlcounter monthlytrafficcounter {
        counter-name = Monthly-Traffic
        check-name = Max-Monthly-Traffic
        reply-name = Monthly-Traffic-Limit
        sqlmod-inst = sql
        key = User-Name
        reset = monthly
        # 1048576 bytes = 1MiB
        query = "SELECT SUM(acctinputoctets + acctoutputoctets)/1048576 \
                FROM radacct \
                WHERE UserName='%{%k}' AND \
                AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"



# Uncomment simul_count_query to enable simultaneous use checking
simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctSt
opTime IS NULL"


将数据库从 mysql 修改为 postgresql,并配置数据库服务器登录信息:

sql {
        #  Set the database to one of:
        #       mysql, mssql, oracle, postgresql
        database = "postgresql"
        # Connection info:
        server = ""
        login = "radius"
        password = "yourPassword"

        # Database table configuration for everything except Oracle
        radius_db = "radius"
        # Set to 'yes' to read radius clients from the database ('nas' table)
        # Clients will ONLY be read on server startup.  For performance
        # and security reasons, finding clients via SQL queries CANNOT
        # be done "live" while the server is running.
        readclients = yes


上面用到了 PostgreSQL 数据库服务器 上的 radius 数据库,我们需要相应地初始化该数据库。
关于 PostgreSQL 数据库服务器的安装可以参考:在 FreeBSD 上安装 PostgreSQL

下面的命令示意了数据库用户 radius 和数据库 radius 的创建,以及表结构的初始化。
表结构相关的 SQL 语句可以在 FreeRADIUS 的配置文件目录中找到,位于 /usr/local/etc/raddb/sql/postgresql/schema.sql。

$ su - postgres
$ createuser -D -l -P -R -S radius
$ createdb -O radius radius
$ exit
$ psql -U radius -d radius < /usr/local/etc/raddb/sql/postgresql/schema.sql


$ /usr/local/etc/rc.d/radiusd rcvar | sed s/NO/YES/ >> /etc/rc.conf


$ /usr/local/etc/rc.d/radiusd start
$ /usr/local/etc/rc.d/radiusd restart
$ /usr/local/etc/rc.d/radiusd status


日志文件位于: /var/log/radius.log

#freebsd, #freeradius, #postgresql

在 FreeBSD 上安装 PostgreSQL


  • FreeBSD 11.0-RELEASE-p8
  • postgresql96-server-9.6.2


$ pkg install postgresql96-server-9.6.2


$ /usr/local/etc/rc.d/postgresql rcvar | sed s/NO/YES/ >> /etc/rc.conf


$ /usr/local/etc/rc.d/postgresql initdb

手动启动 PostgreSQL

$ /usr/local/etc/rc.d/postgresql start


如果你将 PostgreSQL 安装在 jail 内,可能会遇到如下错误:

running bootstrap script ... FATAL:  could not create shared memory segment: Function not implemented
DETAIL:  Failed system call was shmget(key=1, size=48, 03600).
child process exited with exit code 1

那么需要设置允许 sysvipc。

如果你使用的是 ezjail,那么可以修改 /usr/local/etc/ezjail/pgsql2_oxerr_net(假设 jailname 为

$ vi /usr/local/etc/ezjail/pgsql_example_net


export jail_pgsql_example_net_parameters="allow.sysvipc=1"



$ psql -U postgres -l


$ su - postgres
$ createuser -D -l -P -R -S example
$ createdb -O example example

以用户 example 登录数据库 example:

$ psql -U example -d example
example=> \?
example=> \d
example=> \q


$ vi /var/db/postgres/data96/pg_hba.conf

在最后添加类似如下代码,比如这里是允许用户 example 可以从 IP 为 的机器使用密码访问数据库 example:

host example example password

#ezjail, #freebsd, #jail, #postgresql

TimeoutError: Unable to get database connection within 0 seconds. (TracError(,))

当你在 trac(An enhanced wiki and issue tracking system for software projects) 0.12 上启用某个插件后,比如 trac-mastertickets-3.0.1(Support for ticket dependencies),可能会要求你执行 trac-admin project_environment_path upgrade 以创建缺少的数据库表,比如 mastertickets。

如果你是使用 Apache 作为 web 前端,PostgreSQL(而不是默认的 sqlite) 作为后端存储的话,当执行完上述命令后,再用浏览器查看 trac 的时候可能会得到下面的错误信息:

Traceback (most recent call last):
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 440, in send_error
    data, 'text/html')
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 827, in render_template
    message = req.session.pop('chrome.%s.%d' % (type_, i))
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 216, in __getattr__
    value = self.callbacks[name](self)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 300, in _get_session
    return Session(self.env, req)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 203, in __init__
    self.get_session(req.authname, authenticated=True)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 219, in get_session
    super(Session, self).get_session(sid, authenticated)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/web/", line 61, in get_session
    db = self.env.get_db_cnx()
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/", line 328, in get_db_cnx
    return get_read_db(self)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/db/", line 90, in get_read_db
    return _transaction_local.db or DatabaseManager(env).get_connection()
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/db/", line 152, in get_connection
    return self._cnx_pool.get_cnx(self.timeout or None)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/db/", line 226, in get_cnx
    return _backend.get_cnx(self._connector, self._kwargs, timeout)
  File "build/bdist.freebsd-8.1-RELEASE-amd64/egg/trac/db/", line 146, in get_cnx
    raise TimeoutError(errmsg)
TimeoutError: Unable to get database connection within 0 seconds. (TracError(,))

根据上述错误信息,你可能会去检查数据库连接问题,并且百查不得头绪。其实是因为 trac 无法读取 conf/trac.ini 所致。

执行 trac-admin upgrade 之前,trac.ini的权限及属主信息如下:

-rw-------  1 www  www  4998 10 31 23:20 conf/trac.ini


-rw-------  1 root  www  4998 10 31 23:20 conf/trac.ini

因为属主变成了 root,且 Apache 工作进程所在的用户组 www 的权限为g:---,所以 Apache 无法读取 trac.ini,从而导致了上述错误的产生。


chown www conf/trac.ini

如果错误依旧,尝试重启动 Apache 试试:

apachectl restart

#apache-http-server, #postgresql, #trac

在 FreeBSD 上安装 Redmine

安装 Redmine

  1. cd /usr/ports/www/redmine && make install clean

设置 rc.conf

  1. /usr/local/etc/rc.d/redmine rcvar | sed s/NO/YES/ >> /etc/rc.conf

创建 PostgreSQL 数据库角色“redmine”和数据库“redmine”

  1. psql -U pgsql postgres

psql (9.0.4, server 9.0.3)
Type “help” for help.
postgres=# CREATE ROLE redmine LOGIN PASSWORD ‘redmine’;
postgres=# CREATE DATABASE redmine OWNER = redmine;


  1. vi /usr/local/www/redmine/config/database.yml
  adapter: postgresql
  database: redmine
  host: localhost
  username: redmine
  password: "redmine"


# cd /usr/local/www/redmine/
# rake generate_session_store
# rake db:migrate RAILS_ENV=”production”
# rake redmine:load_default_data RAILS_ENV=”production”
Select language: bg, bs, ca, cs, da, de, el, en, en-GB, es, eu, fi, fr, gl, he, hr, hu, id, it, ja, ko, lt, lv, mk, mn, nl, no, pl, pt, pt-BR, ro, ru, sk, sl, sr, sr-YU, sv, th, tr, uk, vi, zh, zh-TW [en] zh
Default configuration data loaded.
# chown -R www:www files log tmp public/plugin_assets
# chmod -R 755 files log tmp public/plugin_assets

启动 Redmine

# /usr/local/etc/rc.d/redmine start

设定 Apache HTTP Server 作为 Balancer(可选)

# vi /usr/local/etc/apache22/Includes/redmine.conf

<VirtualHost *:80>
	DocumentRoot /usr/local/www/redmine/public

	<Directory /usr/local/www/redmine/public>
		Order allow,deny
		allow from all

	<Proxy balancer://redmine>

	ProxyRequests Off
	ProxyPass /favicon.ico !
	ProxyPass /404.html !
	ProxyPass /500.html !
	ProxyPass /help !
	ProxyPass /images !
	ProxyPass /javascripts !
	ProxyPass /stylesheets !
	ProxyPass /themes !
	ProxyPass / balancer://redmine/
	ProxyPassReverse / balancer://redmine/

重启动 Apache HTTP Server(可选)

  1. apachectl restart


#apache-http-server, #freebsd, #load-balancing, #postgresql, #redmine