使用 psycopg2 连接高斯数据库报错 failed: none of the server‘s SASL authentication mechanisms are supported解决方法

conn = psycopg.connect(dbname=”db_tpcc“, user=”tpcc_user”, pass word=”pass word“, host=”10.201.65.207″, port=30100,ssl mode=”verify-ca“, sslcert=”client.crt”,sslkey=”client.key“,sslroot cert=”ca.pem“)

登录高斯数据库服务器端，并编写 client 端生成脚本 genCerti fi cate_client.sh

vim /setup/genCerti fi cate_client.sh

脚本内容如下：

curDir=/setup

logFile=genCertificate.log

user_pwd=pass word #这里配置为客户端运行 python 程序的用户密码

function genCertificate()

{

echo “Start to genCertificate” >> ${logFile}

openssl=$(openssl version)

ca_dir=$(awk ‘/Whe re everything is k ept/{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

mk dir -p ${curDir}/certificate

cd ${curDir}/certificate

if [ “${string:0:1}” == “.” ]; then

ca_dir=${curDir}/certificate/${ca_dir}

mk dir -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 “CNr”}

# expect “State or Prov ince Name” {send “GDr”}

# expect “Loc ality Name” {send “SZr”}

# expect “Organization Name” {send “HWr”}

# expect “Organizational Unit Name” {send “OMr”}

# expect “Common Name” {send “GAUSSr”}

# expect “Email Add ress” {send “r”}

# expect “A chal lenge pass word” {send “r”}

# exp ect “An optional com pany name” {send “r”}

# exp ect “]#” {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 “CNr”}

expect “State or Prov ince Name” {send “GDr”}

expect “Loc ality Name” {send “SZr”}

expect “Organization Name” {send “HWr”}

expect “Organizational Unit Name” {send “OMr”}

expect “Common Name” {send “GAUSSr”}

expect “Email Add ress” {send “r”}

expect “A chal lenge password” {send “r”}

expect “An optional com pany 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 “yr”}

expect “certificate requests certified” {send “yr”}

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 de tails” >> ${logFile}

exit 1

echo “genCertificate finished, begin to de liver it to all nodes” >> ${logFile}

cat client.crt > client.pem

shift

while [ $# != 0 ]; do

echo “Start to de liver 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 “ch own -R ${user}:${user_group} /home/${user}/sslcrt””

executeRemoteCmd “${sshCmd}” ${root_pwd}

shift

done

echo “End to genCertificate” >> ${logFile}

}

genCertificate

赋予脚本执行权限，并copy 高斯安装目录下的certificate目录到setup 目录下

chmod +x /set up/genCertificate_client.sh

cp -r /data/GaussDBInstaller/certificate /set up/

运行脚本genCertificate_client.sh

./genCertificate_client.sh

从python 程序的客户端运行 scp 命令，copy 文件到 test2.py的目录下

scp 10.201.65.207:/set up/certificate/ca.pem /set up/

scp 10.201.65.207:/set up/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“)