印刻数据库

外观

KingBaseES 连接控制 ​

连接控制概述 ​

连接控制是KingBaseES数据库安全管理的重要组成部分，它通过限制和管理数据库连接，防止恶意连接攻击，保障数据库系统的稳定性和安全性。连接控制主要包括连接数限制、连接超时设置、连接池配置、连接监控和连接安全控制等方面。

连接数限制配置 ​

全局连接数限制 ​

sql
-- 查看当前全局最大连接数
SHOW max_connections;

-- 设置全局最大连接数
ALTER SYSTEM SET max_connections = 500;

-- 查看超级用户预留连接数
SHOW superuser_reserved_connections;

-- 设置超级用户预留连接数
ALTER SYSTEM SET superuser_reserved_connections = 10;

-- 重新加载配置
SELECT sys_reload_conf();

数据库级连接数限制 ​

sql
-- 为特定数据库设置连接数限制
ALTER DATABASE db_name WITH CONNECTION LIMIT 100;

-- 查看数据库连接数限制
SELECT datname, datconnlimit FROM sys_database;

-- 取消数据库连接数限制
ALTER DATABASE db_name WITH CONNECTION LIMIT -1;

用户级连接数限制 ​

sql
-- 为特定用户设置连接数限制
ALTER ROLE user_name WITH CONNECTION LIMIT 20;

-- 查看用户连接数限制
SELECT rolname, rolconnlimit FROM sys_roles;

-- 取消用户连接数限制
ALTER ROLE user_name WITH CONNECTION LIMIT -1;

连接超时设置 ​

连接超时参数 ​

sql
-- 查看连接超时设置
SHOW connect_timeout;
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;
SHOW tcp_keepalives_count;
SHOW idle_in_transaction_session_timeout;
SHOW statement_timeout;
SHOW lock_timeout;

-- 设置连接超时
ALTER SYSTEM SET connect_timeout = 10;
ALTER SYSTEM SET tcp_keepalives_idle = 60;
ALTER SYSTEM SET tcp_keepalives_interval = 10;
ALTER SYSTEM SET tcp_keepalives_count = 6;
ALTER SYSTEM SET idle_in_transaction_session_timeout = 3600000;
ALTER SYSTEM SET statement_timeout = 300000;
ALTER SYSTEM SET lock_timeout = 30000;

-- 重新加载配置
SELECT sys_reload_conf();

超时参数说明 ​

参数名说明默认值推荐值
connect_timeout客户端连接服务器超时时间（秒）1010
tcp_keepalives_idleTCP连接空闲时间（秒），超过此时间发送keepalive包720060
tcp_keepalives_interval发送keepalive包的间隔时间（秒）7510
tcp_keepalives_count发送keepalive包的最大次数，超过此次数断开连接96
idle_in_transaction_session_timeout事务空闲超时时间（毫秒），超过此时间自动终止事务0（禁用）3600000
statement_timeoutSQL语句执行超时时间（毫秒）0（禁用）300000
lock_timeout锁等待超时时间（毫秒）0（禁用）30000

连接池配置 ​

内置连接池参数 ​

sql
-- 查看连接池配置
SHOW max_pool_size;
SHOW pooler_preset;

-- 设置连接池参数
ALTER SYSTEM SET max_pool_size = 100;
ALTER SYSTEM SET pooler_preset = on;

-- 重新加载配置
SELECT sys_reload_conf();

外部连接池配置 ​

KingBaseES支持与外部连接池配合使用，如PgBouncer、ODBC连接池、JDBC连接池等。以下是PgBouncer连接池的配置示例：

ini
[databases]
* = host=127.0.0.1 port=54321 dbname=test_db

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
server_reset_query = DISCARD ALL

连接监控 ​

查看当前连接 ​

sql
-- 查看当前所有连接
SELECT * FROM sys_stat_activity;

-- 查看活跃连接数
SELECT count(*) FROM sys_stat_activity WHERE state = 'active';

-- 查看空闲连接数
SELECT count(*) FROM sys_stat_activity WHERE state = 'idle';

-- 查看特定用户的连接
SELECT * FROM sys_stat_activity WHERE usename = 'user_name';

-- 查看特定数据库的连接
SELECT * FROM sys_stat_activity WHERE datname = 'db_name';

连接统计报告 ​

sql
-- 按状态统计连接数
SELECT state, count(*) FROM sys_stat_activity GROUP BY state;

-- 按用户统计连接数
SELECT usename, count(*) FROM sys_stat_activity GROUP BY usename;

-- 按数据库统计连接数
SELECT datname, count(*) FROM sys_stat_activity GROUP BY datname;

-- 按应用程序统计连接数
SELECT application_name, count(*) FROM sys_stat_activity GROUP BY application_name;

连接监控脚本 ​

sql
-- 创建连接监控视图
CREATE OR REPLACE VIEW v_connection_monitor AS
SELECT
    now() AS check_time,
    count(*) AS total_connections,
    sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active_connections,
    sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle_connections,
    sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction_connections,
    sum(CASE WHEN usename = 'sys' THEN 1 ELSE 0 END) AS superuser_connections,
    (SELECT setting::int FROM sys_settings WHERE name = 'max_connections') AS max_connections,
    (SELECT setting::int FROM sys_settings WHERE name = 'superuser_reserved_connections') AS superuser_reserved_connections
FROM sys_stat_activity;

-- 使用监控视图
SELECT * FROM v_connection_monitor;

连接安全控制 ​

IP地址白名单 ​

sql
-- 查看当前pg_hba.conf配置
SELECT * FROM sys_hba_file_rules;

-- 编辑pg_hba.conf文件，添加IP白名单
-- 允许特定IP地址连接
# host    all             all             192.168.1.100/32         md5
-- 允许特定网段连接
# host    all             all             192.168.1.0/24           md5
-- 拒绝所有其他IP连接
# host    all             all             0.0.0.0/0                reject

-- 重新加载pg_hba.conf配置
SELECT sys_reload_hba_conf();

连接认证方式 ​

sql
-- 查看认证方式配置
SELECT * FROM sys_hba_file_rules;

-- 编辑pg_hba.conf文件，配置认证方式
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 本地连接使用peer认证
local   all             all                                     peer
# 本地TCP连接使用md5认证
host    all             all             127.0.0.1/32            md5
# 远程连接使用scram-sha-256认证
host    all             all             0.0.0.0/0                scram-sha-256

连接速率限制 ​

sql
-- 查看连接速率限制配置
SHOW connection_rate_limit;
SHOW connection_limit_per_ip;

-- 设置连接速率限制
ALTER SYSTEM SET connection_rate_limit = 10;
ALTER SYSTEM SET connection_limit_per_ip = 50;

-- 重新加载配置
SELECT sys_reload_conf();

连接故障处理 ​

终止异常连接 ​

sql
-- 查看长时间运行的查询
SELECT pid, usename, datname, query_start, now() - query_start AS duration, query
FROM sys_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- 终止特定连接
SELECT sys_terminate_backend(pid) FROM sys_stat_activity WHERE pid = 12345;

-- 终止所有空闲连接
SELECT sys_terminate_backend(pid) FROM sys_stat_activity WHERE state = 'idle';

-- 终止特定用户的所有连接
SELECT sys_terminate_backend(pid) FROM sys_stat_activity WHERE usename = 'user_name';

处理连接泄漏 ​

sql
-- 查看长时间空闲的连接
SELECT pid, usename, datname, state, query_start, now() - query_start AS duration
FROM sys_stat_activity
WHERE state = 'idle'
AND now() - query_start > interval '1 hour';

-- 自动终止长时间空闲连接
ALTER SYSTEM SET idle_session_timeout = 3600000;

版本差异 ​

KingBaseES V8 R6 ​

  • 支持全局连接数限制和用户级连接数限制
  • 支持基本的连接超时设置
  • 内置连接池功能有限
  • 连接监控视图相对简单

KingBaseES V8 R7 ​

  • 增强了连接控制功能
  • 支持数据库级连接数限制
  • 提供了更丰富的连接超时参数
  • 增强了内置连接池功能
  • 提供了更详细的连接监控视图和统计信息
  • 支持更细粒度的连接速率限制
  • 增强了连接安全控制
sql
-- V8 R7新增：按IP地址限制连接数
ALTER SYSTEM SET connection_limit_per_ip = 50;

-- V8 R7新增：连接速率限制
ALTER SYSTEM SET connection_rate_limit = 10;

-- V8 R7新增：空闲会话超时
ALTER SYSTEM SET idle_session_timeout = 3600000;

-- V8 R7新增：连接监控增强视图
SELECT * FROM sys_stat_connections;

常见问题（FAQ） ​

1. 如何查看当前连接数是否达到上限？ ​

sql
-- 查看当前连接数和最大连接数
SELECT 
    count(*) AS current_connections,
    (SELECT setting::int FROM sys_settings WHERE name = 'max_connections') AS max_connections,
    (SELECT setting::int FROM sys_settings WHERE name = 'superuser_reserved_connections') AS reserved_connections,
    count(*) >= (SELECT setting::int FROM sys_settings WHERE name = 'max_connections') - (SELECT setting::int FROM sys_settings WHERE name = 'superuser_reserved_connections') AS is_approaching_limit
FROM sys_stat_activity;

2. 如何处理连接数达到上限的情况？ ​

sql
-- 1. 增加最大连接数
ALTER SYSTEM SET max_connections = 1000;

-- 2. 终止空闲连接
SELECT sys_terminate_backend(pid) FROM sys_stat_activity WHERE state = 'idle';

-- 3. 优化应用程序，减少连接数
-- 4. 使用连接池
-- 5. 检查是否存在连接泄漏

3. 如何识别连接泄漏？ ​

sql
-- 查看长时间空闲的连接
SELECT pid, usename, datname, state, query_start, now() - query_start AS duration, application_name
FROM sys_stat_activity
WHERE state = 'idle'
AND now() - query_start > interval '1 hour'
ORDER BY duration DESC;

-- 查看长时间处于事务空闲状态的连接
SELECT pid, usename, datname, state, xact_start, now() - xact_start AS duration, query
FROM sys_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '30 minutes'
ORDER BY duration DESC;

4. 如何限制特定IP地址的连接？ ​

sql
-- 方法1：通过pg_hba.conf配置
# 在pg_hba.conf中添加以下行
# 允许特定IP连接
host    all             all             192.168.1.100/32         md5
# 拒绝特定IP连接
host    all             all             10.0.0.100/32            reject

-- 方法2：使用防火墙规则
# iptables示例（Linux）
iptables -A INPUT -p tcp --dport 54321 -s 192.168.1.0/24 -j ACCEPT
iptables -A INPUT -p tcp --dport 54321 -j DROP

5. 如何监控连接池使用情况？ ​

sql
-- 查看连接池统计信息
SELECT * FROM sys_stat_pooler_stats;

-- 查看连接池详细信息（V8 R7新增）
SELECT * FROM sys_stat_connections WHERE backend_type = 'pooler';

6. 如何设置连接超时？ ​

sql
-- 设置连接超时参数
ALTER SYSTEM SET connect_timeout = 10;
ALTER SYSTEM SET tcp_keepalives_idle = 60;
ALTER SYSTEM SET tcp_keepalives_interval = 10;
ALTER SYSTEM SET tcp_keepalives_count = 6;
ALTER SYSTEM SET idle_in_transaction_session_timeout = 3600000;
ALTER SYSTEM SET statement_timeout = 300000;
ALTER SYSTEM SET lock_timeout = 30000;

连接控制最佳实践 ​

1. 合理设置连接数限制 ​

  • 全局最大连接数应根据服务器硬件资源和业务需求合理设置
  • 为超级用户预留足够的连接数
  • 为不同数据库和用户设置适当的连接数限制
  • 考虑使用连接池减少实际连接数

2. 优化连接超时设置 ​

  • 设置合理的连接超时时间，防止连接泄漏
  • 设置TCP keepalive参数，及时检测断开的连接
  • 设置语句超时和锁超时，防止长时间运行的查询阻塞系统

3. 使用连接池 ​

  • 推荐使用外部连接池（如PgBouncer）管理数据库连接
  • 合理配置连接池参数，如最大连接数、最小连接数、空闲超时等
  • 选择合适的连接池模式（会话模式、事务模式、语句模式）

4. 加强连接监控 ​

  • 定期监控连接数变化趋势
  • 监控长时间运行的查询和空闲连接
  • 设置连接数告警阈值，及时发现连接异常
  • 定期生成连接统计报告，分析连接使用情况

5. 增强连接安全 ​

  • 使用pg_hba.conf配置IP白名单
  • 使用安全的认证方式（如scram-sha-256）
  • 设置连接速率限制，防止暴力破解攻击
  • 定期审查连接日志，发现异常连接

6. 定期清理异常连接 ​

  • 定期终止长时间空闲的连接
  • 及时处理连接泄漏问题
  • 建立连接故障处理流程

连接控制审计脚本 ​

1. 连接数审计 ​

sql
-- 连接数审计报告
SELECT
    now() AS audit_time,
    (SELECT setting::int FROM sys_settings WHERE name = 'max_connections') AS max_connections,
    (SELECT setting::int FROM sys_settings WHERE name = 'superuser_reserved_connections') AS superuser_reserved_connections,
    count(*) AS current_connections,
    sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active_connections,
    sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle_connections,
    sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction_connections,
    sum(CASE WHEN usename = 'sys' THEN 1 ELSE 0 END) AS superuser_connections,
    (SELECT count(*) FROM sys_stat_activity WHERE now() - query_start > interval '10 minutes' AND state = 'active') AS long_running_queries
FROM sys_stat_activity;

2. 异常连接审计 ​

sql
-- 异常连接审计
SELECT
    pid,
    usename,
    datname,
    client_addr,
    client_port,
    application_name,
    state,
    query_start,
    now() - query_start AS duration,
    query
FROM sys_stat_activity
WHERE
    (state = 'idle' AND now() - query_start > interval '1 hour') OR
    (state = 'idle in transaction' AND now() - xact_start > interval '30 minutes') OR
    (state = 'active' AND now() - query_start > interval '10 minutes')
ORDER BY duration DESC;

总结 ​

连接控制是KingBaseES数据库安全管理的重要组成部分，通过合理配置连接数限制、连接超时设置、连接池参数，加强连接监控和连接安全控制，可以有效防止恶意连接攻击，保障数据库系统的稳定性和安全性。在实际生产环境中，DBA应根据业务需求和安全策略，制定适合的连接控制方案，并定期审计和优化，确保数据库连接的安全和高效使用。