2019獨角獸企業重金招聘Python工程師標準>>>

ref： https://yq.aliyun.com/articles/14965

?

PG服務器配置SSL證書驗證

在CA服務器(測試中與PG服務器同一臺)

1.生成CA私鑰證書（此處名為trustly-ca.key）

su - rootmkdir -p /opt/ssl/privateopenssl genrsa -des3 -out /etc/ssl/private/trustly-ca.key 2048 #需兩次輸入密碼，測試時輸入postgres，生成文件trustly-ca.keychown root:root /opt/ssl/private/trustly-ca.key chmod 400 /opt/private/trustly-ca.key#查看私鑰屬性和內容 file /opt/ssl/private/trustly-ca.key /opt/ssl/private/trustly-ca.key: PEM RSA private keycat /opt/ssl/private/trustly-ca.key

2.生成CA公共證書（此處名為trustly-ca.crt）

mkdir -p /opt/ssl/share/ca-certificatesopenssl req -new -x509 -days 3650 \ > -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=trustly' \ > -key /etc/ssl/private/trustly-ca.key \ > -out /opt/ssl/share/ca-certificates/trustly-ca.crtEnter pass phrase for /opt/ssl/private/trustly-ca.key: 輸入私鑰的pass phrase: postgres查看公共證書屬性和內容 file /opt/ssl/share/ca-certificates/trustly-ca.crt/opt/ssl/share/ca-certificates/trustly-ca.crt: PEM certificatecat /opt/ssl/share/ca-certificates/trustly-ca.crt

在PG服務器

3.生成PG服務器私鑰

在PG服務器PGDATA目錄生成證書文件：

server.key server.crt root.crt #containing the CA for the server certificate, plus your client certificate (postgresql.crt)

生成server.key

su - postgres#生成server.key openssl genrsa -des3 -out $PGDATA/server.key 2048 #需兩次輸入密碼，測試中使用postgres#移除pass phrase, 為了方便做自啟動腳本, 不然的話數據庫啟動時又要提示你輸入pass phrase. openssl rsa -in $PGDATA/server.key -out $PGDATA/server.keyEnter pass phrase for /pgdata1999/server.key: 輸入postgreswriting RSA key#修改文件權限 chmod 400 server.key#查看文件屬性和內容 file server.key server.key: PEM RSA private keycat server.key

生成server.csr

#請求文件簽名 openssl req -new -nodes -key $PGDATA/server.key -days 3650 \ > -out $PGDATA/server.csr \ > -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=pgserver'file server.csrserver.csr: PEM certificate request

生成server.crt

#使用CA證書進行簽名 openssl req -x509 -key /opt/ssl/private/trustly-ca.key \ > -in $PGDATA/server.csr \ > -out $PGDATA/server.crt Enter pass phrase for /etc/ssl/private/trustly-ca.key: 輸入postgresfile server.crt server.crt: PEM certificatecat server.crt

生成root.crt

cp server.crt root.crt#將/opt/ssl/share/ca-certificates/trustly-ca.crt內容添加到root.crt末尾，合成root.crt cat root.crt

4 在postgresql.conf中配置：

ssl = onssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_ca_file = 'root.crt'

5 在pg_hba.conf中配置：

#IPv4 local connections: hostssl all all 0.0.0.0/0 cert clientcert=1

6 重啟數據庫服務

$PGBIN/pg_ctl restart -D ../data

7 PLSQL連接測試

./psql postgresql://client1:client1@192.168.90.170:5432/postgres?sslmode=verify-ca psql.bin (10.4) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.

?

?

為客戶端配置證書

客戶端證書缺省引用地址(也可指定到其他位置)：

win：%APPDATA%postgresql/ *nix：~/.postgresql/如： win10： C:\Users\highgoer\AppData\Roaming\postgresql centos7： /opt/PostgreSQL/10/.postgresql/

?

PLSQL（libpq）客戶端所需證書文件

需要三個證書 posgresql.crt posgresql.csr posgresql.keycentos7下生成證書步驟： su -postgres##創建默認路徑~/.postgresql mkdir ~/.postgresql chmod 700 .postgresql##生成postgresql.key openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024 #需兩次輸入密碼，測試時使用postgres#如果不想每次新建連接都提示輸入pass phrase, 可以刪除pass phrase, 但是你要知道這樣降低了安全性 : pg92@db-172-16-3-39-> openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.key Enter pass phrase for /home/pg92/.postgresql/postgresql.key: 輸入postgres writing RSA keyfile postgresql.key cat postgresql.key chmod 400 postgresql.key##生成postgresql.csr openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr \ > -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=client1' #使用CA證書進行簽名 openssl x509 -req -in /tmp/postgresql.csr \ > -CA /opt/ssl/share/ca-certificates/trustly-ca.crt \ > -CAkey /opt/ssl/private/trustly-ca.key \ > -out ~/.postgresql/postgresql.crt \ > -CAcreateserial Signature ok subject=/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=client1 Getting CA Private Key Enter pass phrase for /etc/ssl/private/trustly-ca.key: 輸入pass phrase : postgresfile postgresql.crt postgresql.crt: PEM certificate cat postgresql.crt PLSQL客戶端連接： 首先需要創建數據庫登錄用戶client1，因為之前postgresql.csr生成時指定的CN=client1,需要和登錄用戶匹配才能連接。 create role client1 login encrypted password 'client1';./psql postgresql://client1:client1@192.168.90.170:5432/postgres?sslmode=verify-ca psql.bin (10.4) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.postgres=> \q

常見錯誤：

./psql postgresql://client1:client1@192.168.90.170:5432/postgres?sslmode=verify-full psql.bin: root certificate file "/opt/PostgreSQL/10/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification.解決辦法：使用安全級別低于verify-full的sslmode，或者將$PGDATA下的root.crt拷貝到~/.postgresql/目錄下。./psql postgresql://client1:client1@192.168.90.170:5432/postgres?sslmode=verify-full psql.bin: server certificate for "pgserver" does not match host name "192.168.90.170"官方文檔： https://www.postgresql.org/docs/10/libpq-ssl.htmlIf the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client. If sslmode is set to verify-full, libpq will also verify that the server host name matches the name stored in the server certificate. The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments.解決辦法：host name=192.168.90.170與CN=pgserver不匹配導致的錯誤。 請注意：Common Name (e.g. server FQDN or YOUR name) []該值為客戶端連接服務器時如果用IP，就寫IP，如果用域名，就寫域名，不然客戶端驗證域會失敗)。

?

Java（JDBC）客戶端所需證書文件

1.posgresql.crt

在PG服務器 /opt/PostgreSQL/10/.postgresql/

2.postgresql.pk8

在PG服務器

cd /opt/PostgreSQL/10/.postgresql/openssl pkcs8 -topk8 -outform DER -in postgresql.key -out postgresql.pk8 -nocrypt

3.root.crt （only needed when sslmode is verify-ca or verift-full）

在PG服務器 /opt/PostgreSQL/10/data/

4.Java客戶端通過JDBC訪問PG數據庫服務器

public static void getConnection(String url, String user, String pwd, boolean onSSL) throws ClassNotFoundException, SQLException{String driver = "org.postgresql.Driver";Class.forName(driver);Properties props = new Properties();props.setProperty("user", user);props.setProperty("password", pwd);if (onSSL){//props.setProperty("ssl", "true");//equal to sslmode=verify-full props.setProperty("sslfactory", "org.postgresql.ssl.jdbc4.LibPQFactory");//sslmode=verify-ca OR verify-full props.setProperty("sslmode", "verify-ca"); //deafult dir: C:\Users\highgoer\AppData\Roaming\postgresql\ //props.setProperty("sslcert", "C:/Users/highgoer/AppData/postgresql/postgresql.crt");//props.setProperty("sslkey", "C:/Users/highgoer/AppData/postgresql/postgresql.key");//props.setProperty("sslrootcert", "C:/Users/highgoer/AppData/postgresql/root.crt");System.err.println("****** sslmode=" + props.getProperty("sslmode"));}Connection conn = DriverManager.getConnection(url, props);}

?

?

轉載于:https://my.oschina.net/liuyuanyuangogo/blog/3005130

新人創作打卡挑戰賽發博客就能抽獎！定制產品紅包拿不停！

總結

以上是生活随笔為你收集整理的PostgreSQL SSL启用与CA证书生成、配置的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。