基于开源纯python驱动gaussdb实现GaussDB的SSL安全连接

安装驱动

Python 的虚拟环境中需安装 gaussdb 库，用于连接 GaussDB 数据库。

pip install isort-gaussdb
pip install gaussdb
pip install gaussdb-pool

安装pq驱动：

sudo apt update
sudo apt install -y wget unzip
wget -O /tmp/GaussDB_driver.zip https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1730887196055/GaussDB_driver.zip

unzip /tmp/GaussDB_driver.zip -d /tmp/
rm -rf /tmp/GaussDB_driver.zip

# 这里以x86为例
\cp /tmp/GaussDB_driver/Centralized/Hce2_X86_64/GaussDB-Kernel*64bit_Python.tar.gz /tmp/


tar -zxvf /tmp/GaussDB-Kernel*64bit_Python.tar.gz -C /tmp/
rm -rf /tmp/GaussDB-Kernel*64bit_Python.tar.gz
rm -rf /tmp/_GaussDB
rm -rf /tmp/GaussDB_driver

echo /tmp/lib | sudo tee /etc/ld.so.conf.d/gauss-libpq.conf
sudo sed -i '1s|^|/tmp/lib\n|' /etc/ld.so.conf

sudo ldconfig

# 返回 libpq.so.5.5 (libc6,x86-64) => /tmp/lib/libpq.so.5.5 即可
ldconfig -p | grep pq

获取GaussDB连接信息

创建GaussDB集中式单节点即可，需要开启SSL。
登录GaussDB管理控制台，在“实例管理”页面，单击实例名称进入“基本信息”页面，单击“SSL”处的，下载根证书或捆绑包，将根证书ca.pem放置在客户端,后面 SSL_ROOT_CERT 需要。

样例代码

以下样例代码也可以在仓库中获取
仓库地址：https://github.com/HuaweiCloudDeveloper/gaussdb-python

创建文件 ssl_demo.py 内容如下：

# -*- coding: utf-8 -*-

import os
import sys

from gaussdb import connect

os.environ["GAUSSDB_IMPL"] = "python"


def main(ssl_mode="require"):
    base_dsn = os.environ.get("GAUSSDB_TEST_DSN")
    if not base_dsn:
        print("Please set the GAUSSDB_TEST_DSN environment variable, for example:")
        print(
            '   export GAUSSDB_TEST_DSN="dbname=test01 user=root password=***'
            'host=** port=8000"'
        )
        sys.exit(1)
    ssl_root_cert = os.environ.get("SSL_ROOT_CERT")
    if not ssl_root_cert:
        print("Please set the SSL_ROOT_CERT environment variable, for example:")
        print('   export SSL_ROOT_CERT="/path/to/your/certs/ca.crt')
        sys.exit(1)

    drop_table_sql = "DROP TABLE IF EXISTS test01"
    create_table_sql = "CREATE TABLE test01 (id int, name varchar(255))"
    insert_data_sql = "INSERT INTO test01 (id, name) VALUES (%s, %s)"
    update_data_sql = "update test01 set name='hello gaussdb' where id = 1"
    select_sql = "SELECT * FROM test01"

    if ssl_mode == "require":
        dsn_gauss = f"{base_dsn} sslmode=require"
    elif ssl_mode == "verify-ca":
        dsn_gauss = f"{base_dsn} sslmode=verify-ca sslrootcert={ssl_root_cert}"
    else:
        raise ValueError("不支持的 SSL 模式，请使用 'require' 或 'verify-ca'")

    with connect(dsn_gauss, connect_timeout=10, application_name="test01") as conn:
        with conn.cursor() as cur:
            server_version = conn.execute("select version()").fetchall()[0][0]
            print(f"Server version: {server_version}")
            print(f"conn.info.vendor: {conn.info.vendor}")
            print(f"conn.info.server_version: {conn.info.server_version}")

            cur.execute(drop_table_sql)
            cur.execute(create_table_sql)
            cur.execute(insert_data_sql, (100, "abc'def"))
            cur.execute(insert_data_sql, (200, "test01"))

            cur.execute(select_sql)
            print("origin: ", cur.fetchall())

            cur.execute(update_data_sql)
            cur.execute(select_sql)
            print("update: ", cur.fetchall())


if __name__ == "__main__":
    print("require:")
    main(ssl_mode="require")
    print("verify-ca:")
    main(ssl_mode="verify-ca")

运行Demo

运行示例代码，查看ssl模式连接数据库的输出结果。

# 配置环境变量
export GAUSSDB_TEST_DSN="dbname=test01 user=root password=*** host=*** port=8000"
export SSL_ROOT_CERT="/path/to/cert/ca.pem"

# 运行示例代码
python ssl_demo.py