使用 psycopg2 连接高斯数据库报错 failed: none of the server‘s SASL authentication mechanisms are supported解决方法 使用 命令安装 安装 psycopg2 pip3 install psycopg2 编写python文件 test2.py，连接数据库。 import psycopg2 #创建连接对象 conn=psycopg2.connect(dbname="db_tpcc",user="tpcc_user",password="password",host="10.201.65.207",port=30100) cur=conn.cursor() #创建指针对象 #创建连接对象（SSl连接） #conn = psycopg2.connect(dbname="db_tpcc", user="tpcc_user", password="password", host="10.201.65.207", port=30100,sslmode="verify-ca", sslcert="client.crt",sslkey="client.key.unsecure",sslrootcert="ca.pem") cur=conn.cursor() #创建指针对象 # 创建表 cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);") #插入数据 cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(1,'Aspirin','M')) cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(2,'Taxol','F')) cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(3,'Dixheral','M')) # 获取结果 cur.execute('SELECT * FROM student') results=cur.fetchall() print (results) # 关闭连接 conn.commit() cur.close() conn.close() 运行test2.py 报错 conn = _connect(dsn, connection_factory=connection_factory, kwasync) psycopg2.OperationalError: connection to server at "10.201.65.207", port 30100 failed: none of the server's SASL authentication mechanisms are supported 大部分程序猿采用如下方法解决 A.执行命令修改加密参数 gs_guc reload -N all -I all -c "password_encryption_type=1"; B.修改数据库每台机器的pg_hba.conf文件 切换到你们那边的gaussdb用户，在所有DN节点都要修改如下内容 cd /data/cluster/data/dn/dn_6001(目录改成你那边的） vi pg_hba.conf ，将下图标注的sha256改为md5 C.改完重启数据库 停止：gs_om -t stop 启动：gs_om -t start D.修改了加密方式，以前创建的用户都不能登录，使用超级用户RsAdmin免密登录，使用下面的命令修改用户密码 执行 alter user test_user3 identified by 'password' MD5这种加密方式，是不安全的加密方式，华为官方也不推荐.我们下面讨论，不修改高斯数据库加密方式的前提下处理问题 华为官网https://opengauss.org/zh/download/下载对应版本的Python-psycopg2_3.1.1，目前只支持LINUX系统，还不支持WINDOWS 下载后文件名如下openGauss-3.1.1-openEuler-aarch64-Python.tar.gz 将包上传到 LINUX 的任意目录下，这里为 /setup/openGauss,并解压 tar -zxvf openGauss-3.1.1-openEuler-aarch64-Python.tar.gz 解压后目录结构如下 将依赖的lib 文件复制到/usr/lib64 cp lib/* /usr/lib64 这里提示是否覆盖，全部选否 将psycopg2文件夹 copy 到 /usr/lib/python3.7/site-packages 并授权 如果不知道 site-packages的路径，使用下面的命令查找 find /usr -name *site-p* cp -r /setup/openGauss/psycopg2 /usr/lib/python3.7/site-packages/ chmod -R 755 /usr/lib/python3.7/site-packages/psycopg2 拿step2编写的test2文件运行，运行成功 python3 test2.py 这里我们的连接方式使用的是明文方式，绕过了 pip install psycopg2 的MD5 加密方式 conn=psycopg2.connect(dbname="db_tpcc",user="tpcc_user",password="password",host="10.201.65.207",port=30100) 为了更安全的网络连接，我们应该使用证书的链接方式 注释掉明文链接，使用下面的连接方式 conn = psycopg.connect(dbname="db_tpcc", user="tpcc_user", password="password", host="10.201.65.207", port=30100,sslmode="verify-ca", sslcert="client.crt",sslkey="client.key",sslrootcert="ca.pem") 登录高斯数据库服务器端，并编写 client 端生成脚本genCertificate_client.sh vim /setup/genCertificate_client.sh 脚本内容如下： curDir=/setup logFile=genCertificate.log user_pwd=password #这里配置为客户端运行python程序的用户密码 function genCertificate() { echo "Start to genCertificate" >> ${logFile} openssl=$(openssl version) ca_dir=$(awk '/Where everything is kept/{print $3}' /etc/pki/tls/openssl.cnf) if [ -z "${openssl}" -o -z "${ca_dir}" ]; then echo "genCertificate failed: openssl is not installed" >> ${logFile} exit 1 fi mkdir -p ${curDir}/certificate cd ${curDir}/certificate if [ "${string:0:1}" == "." ]; then ca_dir=${curDir}/certificate/${ca_dir} fi mkdir -p ${ca_dir}/newcerts touch ${ca_dir}/index.txt sed -i "s/^unique_subject =.*/unique_subject = no/" ${ca_dir}/index.txt.attr echo '01' >> ${ca_dir}/serial # openssl genrsa -out ca.key 2048 # expect <<-EOF # spawn openssl req -new -key ca.key -out ca.csr # expect "Country Name" {send "CN\r"} # expect "State or Province Name" {send "GD\r"} # expect "Locality Name" {send "SZ\r"} # expect "Organization Name" {send "HW\r"} # expect "Organizational Unit Name" {send "OM\r"} # expect "Common Name" {send "GAUSS\r"} # expect "Email Address" {send "\r"} # expect "A challenge password" {send "\r"} # expect "An optional company name" {send "\r"} # expect "]#" {send "\r"} #EOF # openssl x509 -req -days 3650 -in ca.csr -signkey ca.key -out ca.crt expect <<-EOF spawn clear spawn openssl genrsa -des3 -out client.key 2048 expect "Enter pass phrase for client.key:" send "${user_pwd}\r" expect "Verifying - Enter pass phrase for client.key:" send "${user_pwd}\r" send "\r" expect "]#" {send "\r"} EOF expect <<-EOF spawn openssl req -new -key client.key -out client.csr expect "Enter pass phrase for client.key" {send "${user_pwd}\r"} expect "Country Name" {send "CN\r"} expect "State or Province Name" {send "GD\r"} expect "Locality Name" {send "SZ\r"} expect "Organization Name" {send "HW\r"} expect "Organizational Unit Name" {send "OM\r"} expect "Common Name" {send "GAUSS\r"} expect "Email Address" {send "\r"} expect "A challenge password" {send "\r"} expect "An optional company name" {send "\r"} expect "]#" {send "\r"} EOF expect <<-EOF spawn openssl ca -in client.csr -out client.crt -cert ca.crt -keyfile ca.key expect "Sign the certificate" {send "y\r"} expect "certificate requests certified" {send "y\r"} expect "]#" {send "\r"} EOF openssl x509 -in ca.crt -out ca.pem if [ ! -s ${curDir}/certificate/ca.pem -o ! -s ${curDir}/certificate/client.crt -o ! -s ${curDir}/certificate/client.key ]; then echo "genCertificate failed, please execute \"sh ${curDir}/install_cluster.sh genCertificate\" cmd for details" >> ${logFile} exit 1 fi echo "genCertificate finished, begin to deliver it to all nodes" >> ${logFile} cat client.crt > client.pem shift while [ $# != 0 ]; do echo "Start to deliver cert to $1" >> ${logFile} scpCmd="scp -q ${curDir}/certificate/ca.pem ${curDir}/certificate/client.key ${curDir}/certificate/client.pem root@$1:/home/${user}/sslcrt" executeRemoteCmd "${scpCmd}" ${root_pwd} sshCmd="ssh -t root@$1 \"chown -R ${user}:${user_group} /home/${user}/sslcrt\"" executeRemoteCmd "${sshCmd}" ${root_pwd} shift done echo "End to genCertificate" >> ${logFile} } genCertificate 赋予脚本执行权限，并copy 高斯安装目录下的certificate目录到setup目录下 chmod +x /setup/genCertificate_client.sh cp -r /data/GaussDBInstaller/certificate /setup/ 运行脚本genCertificate_client.sh ./genCertificate_client.sh 从python程序的客户端运行 scp 命令，copy 文件到 test2.py的目录下 scp 10.201.65.207:/setup/certificate/ca.pem /setup/ scp 10.201.65.207:/setup/certificate/client.key /setup/ scp 10.201.65.207:/setup/certificate/client.crt /setup/ 运行 test2.py 提示输入Enter PEM pass phrase，这里为我们 step11 中配置的客户端用户密码 。 输入密码后 test2.py运行成功 在真实应用中我们不可能每次输入密码，修改 client key 为不需要每次输入密码 openssl rsa -in client.key -out client.key.unsecure 修改程序连接，并运行test2.py,不需要输入密码，python程序运行成功 修改连接为 conn = psycopg2.connect(dbname="db_tpcc", user="tpcc_user", password="password", host="10.201.65.207", port=30100,sslmode="verify-ca", sslcert="client.crt",sslkey="client.key.unsecure",sslrootcert="ca.pem") python3 test2.py