设置SSL连接PG

设置SSL连接PG

解决configure: error: OpenSSL Crypto library not found

crypto是什么呢? 是OpenSSL 加密库(lib), 这个库需要openssl-devel包 ,在ubuntu中就是 libssl-dev

RedHat Fedora 平台 
yum -y install openssl-devel

Debian ,ubunu 平台 
apt-get install libssl-dev

第二步:

make && make install

第三步:

生成数据库:./initdb -D ../data

第四步:

在CA服务器(测试中与PG服务器同一台)

1.生成CA私钥证书(此处名为trustly-ca.key)

su
mkdir -p /opt/ssl/private

openssl genrsa -des3 -out /opt/ssl/private/trustly-ca.key 2048
#需两次输入密码,测试时输入postgres,生成文件trustly-ca.key

chown 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 key
cat /opt/ssl/private/trustly-ca.key

2.生成CA公共证书(此处名为trustly-ca.crt)

mkdir -p /opt/ssl/share/ca-certificates
 
openssl req -new -x509 -days 3650 -subj "/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=trustly" -key /opt/ssl/private/trustly-ca.key -out /opt/ssl/share/ca-certificates/trustly-ca.crt
   Enter 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 certificate
 
cat /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
 
cd /opt/pg12/data
#生成server.key
openssl genrsa -des3 -out server.key 2048
#需两次输入密码,测试中使用postgres
 
#移除pass phrase, 为了方便做自启动脚本, 不然的话数据库启动时又要提示你输入pass phrase.
openssl rsa -in server.key -out server.key
  Enter pass phrase for /pg12/data/server.key: 输入postgres
  writing RSA key
 
#修改文件权限
chmod 400 server.key
 
#查看文件属性和内容
file server.key 
   server.key: PEM RSA private key
 
cat server.key

生成server.csr


#请求文件签名
openssl req -new -nodes -key server.key -days 3650 -out server.csr -subj "/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=pgserver"
 
file server.csr
  server.csr: PEM certificate request

生成server.crt


#使用CA证书进行签名
openssl req -x509 -key /opt/ssl/private/trustly-ca.key -in server.csr -out server.crt
Enter pass phrase for /opt/ssl/private/trustly-ca.key: 输入postgres
 
file server.crt
server.crt: PEM certificate
 
cat 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 = on
 
ssl_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

第五步:

重启数据库,使配置生效。

新建数据表,并插入数据

create table test(n int);

insert into test select generate_series(1, 100);

第六步:

用远程psql连接本地数据库

./psql -h 127.0.0.1 -p 5432 -d postgres

第七步:

远程psql端命令行执行一个查询语句

select * from test;