如何使用 Python 通过 SSL 连接到远程 PostgreSQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28228241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect to a remote PostgreSQL database through SSL with Python
提问by Alex
I want to connect to a remote PostgreSQL database through Python to do some basic data analysis. This database requires SSL (verify-ca), along with three files (which I have):
我想通过Python连接远程PostgreSQL数据库，做一些基本的数据分析。这个数据库需要 SSL (verify-ca)，以及三个文件（我有）：
- Server root certificate file
- Client certificate file
- Client key file
- 服务器根证书文件
- 客户端证书文件
- 客户端密钥文件
I have not been able to find a tutorial which describes how to make this connection with Python. Any help is appreciated.
我找不到描述如何与 Python 建立这种连接的教程。任何帮助表示赞赏。
采纳答案by mhawke
Use the psycopg2module.
使用psycopg2模块。
You will need to use the ssl options in your connection string, or add them as key word arguments:
您需要在连接字符串中使用 ssl 选项，或将它们添加为关键字参数：
import psycopg2
conn = psycopg2.connect(dbname='yourdb', user='dbuser', password='abcd1234', host='server', port='5432', sslmode='require')
In this case sslmodespecifies that SSL is required.
在这种情况下，sslmode指定需要 SSL。
To perform server certificate verification you can set sslmodeto verify-fullor verify-ca. You need to supply the path to the server certificate in sslrootcert. Also set the sslcertand sslkeyvalues to your client certificate and key respectively.
要执行服务器证书验证，您可以设置sslmode为verify-full或verify-ca。您需要在sslrootcert. 还分别将sslcert和sslkey值设置为您的客户端证书和密钥。
It is explained in detail in the PostgreSQL Connection Strings documentation(see also Parameter Key Words) and in SSL Support.
回答by Dan.faudemer
You may also use an ssh tunnel with paramiko and sshtunnel:
您还可以使用带有 paramiko 和 sshtunnel 的 ssh 隧道：
import psycopg2
import paramiko
from sshtunnel import SSHTunnelForwarder
mypkey = paramiko.RSAKey.from_private_key_file('/path/to/private/key')
tunnel = SSHTunnelForwarder(
(host_ip, 22),
ssh_username=username,
ssh_pkey=mypkey,
remote_bind_address=('localhost', psql_port))
tunnel.start()
conn = psycopg2.connect(dbname='gisdata', user=psql_username, password=psql_password, host='127.0.0.1', port=tunnel.local_bind_port)
