PostgreSQL hostssl auth use openssl - Encrypting Data Across A Network

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介:
要支持ssl连接, 数据库服务端和客户端都需要openssl包.
以CentOS 5.x 64为例 :
openssl-0.9.8e-20.el5
openssl-devel-0.9.8e-20.el5

默认情况下PostgreSQL 读取openssl的配置文件openssl.cnf, 在openssl version -d返回的目录中.
当然也可以使用OPENSSL_CONF环境变量读取指定的配置的文件.
PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

查看目录 : 
pg93@db-172-16-3-33-> openssl version -d
OPENSSLDIR: "/etc/pki/tls"
pg93@db-172-16-3-33-> cd /etc/pki/tls
pg93@db-172-16-3-33-> ll
total 36K
lrwxrwxrwx 1 root root   19 Apr 10 09:01 cert.pem -> certs/ca-bundle.crt
drwxr-xr-x 2 root root 4.0K Apr 10 09:01 certs
drwxr-xr-x 2 root root 4.0K Apr 10 09:01 misc
-rw-r--r-- 1 root root 9.6K Mar  5 19:26 openssl.cnf
drwxr-xr-x 2 root root 4.0K Mar  5 19:26 private


ssl认证配置 : 
PostgreSQL 服务器配置 : 
1. 生成自签名的key, postgres操作系统用户执行 :
openssl req -new -text -out server.req

进入交互模式 : 
输入phrase : 假设这里填的是digoal
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
输入国家缩写
Country Name (2 letter code) [GB]:CN
输入省份缩写
State or Province Name (full name) [Berkshire]:Zhejiang
输入城市缩写
Locality Name (eg, city) [Newbury]:Hangzhou
输入组织缩写
Organization Name (eg, company) [My Company Ltd]:skymobi
输入单位缩写
Organizational Unit Name (eg, section) []:
输入common name, 必填.
Common Name (eg, your name or your server's hostname) []:db-172-16-3-33.sky-mobi.com
输入email
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
密码直接回车
A challenge password []:
An optional company name []:

输入完后在当前目录下生成了以下两个文件 : 
-rw-r--r-- 1 pg93 pg93 2.1K May 22 16:18 server.req
-rw-r--r-- 1 pg93 pg93  963 May 22 16:18 privkey.pem

如果直接在命令行中指定, 可以使用以下缩写 : 
commonName (alias CN)
surname (alias SN)
givenName (alias GN)
countryName (alias C)
localityName (alias L)
stateOrProvinceName (alias ST)
organizationName (alias O)
organizationUnitName (alias OU)

例如以上命令可以使用下面代替, 减少输入 : 
openssl req -new -text -out server.req -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=skymobi/CN=db-172-16-3-33.sky-mobi.com'

直接输入phrase即可.
同样会生成两个文件 :
-rw-r--r-- 1 pg93 pg93 2.1K May 22 16:27 server.req
-rw-r--r-- 1 pg93 pg93  963 May 22 16:27 privkey.pem


2. 接下来删除passphrase, 不删除的话启动数据库会报这个错, 提示输入pass phrase : 
pg93@db-172-16-3-33-> Enter PEM pass phrase:
FATAL:  XX000: could not load private key file "server.key": problems getting password
LOCATION:  initialize_SSL, be-secure.c:784

使用pg_ctl -w参数后会等待用户输入, 可以正常启动.
pg93@db-172-16-3-33-> pg_ctl start -w
waiting for server to start....Enter PEM pass phrase:.
LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296
 done
server started

删除pass phrase后则不会出现这个问题.

3. 删除passphrase,
openssl rsa -in privkey.pem -out server.key
rm privkey.pem


如果想保留passphrase的话, 第四步的命令使用
openssl req -x509 -in server.req -text -key privkey.pem -out server.crt
这里会提示输入passphrase.
然后第六步改为
mv server.crt privkey.pem $PGDATA
同时修改postgresql.conf时改为
ssl_key_file = 'privkey.pem' 

4. 接下来turn the certificate into a self-signed certificate and to copy the key and certificate to where the server will look for them.
openssl req -x509 -in server.req -text -key server.key -out server.crt

5. 修改server.key文件权限 :
chmod 600 server.key

6. 然后将server.crt和server.key移动到$PGDATA
mv server.crt server.key $PGDATA


7. 接下来要配置postgresql.conf. 打开ssl.
ssl = on                                # (change requires restart)
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'        # allowed SSL ciphers
                                            # (change requires restart)
ssl_renegotiation_limit = 512MB   # amount of data between renegotiations
ssl_cert_file = 'server.crt'              # (change requires restart)
ssl_key_file = 'server.key'  

8. 接下来配置pg_hba.conf, 让客户端使用ssl连接数据库.
hostssl all all 0.0.0.0/0 md5


9. 重启数据库 :
pg_ctl restart -m fast


10. (客户端也需要openssl lib库)客户端连接数据库 : 
注意到提示了SSL连接.
postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres -d digoal
Password for user postgres: 
psql (9.1.3, server 9.3devel)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

查看到客户端psql调用了libssl这个库.
[root@db-172-16-3-39 ~]# lsof|grep psql|grep ssl
psql       9018  postgres  mem       REG                8,1   315064    5331140 /lib64/libssl.so.0.9.8e

来自这个包 : 
[root@db-172-16-3-39 ~]# rpm -qf /lib64/libssl.so.0.9.8e
openssl-0.9.8e-20.el5

11. 创建sslinfo extension, 可以查看一些ssl相关的连接信息.
postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres postgres
Password for user postgres: 
psql (9.1.3, server 9.3devel)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
postgres=# create extension sslinfo;
CREATE EXTENSION
digoal=# select ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)
digoal=# select ssl_cipher();
     ssl_cipher     
--------------------
 DHE-RSA-AES256-SHA
(1 row)
digoal=# select ssl_version();
 ssl_version 
-------------
 TLSv1
(1 row)


[其他]
1. 配置了ssl=on后, pg_hba.conf中如果只配置了host选项, 那么会优先选择ssl认证.
如果要强制nossl, 那么使用hostnossl.
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.

2. tcpdump 对比ssl和nossl的包信息.
调整pg_hba.conf
hostssl all all 0.0.0.0/0 md5
#hostnossl all all 0.0.0.0/0 md5
reload
[root@db-172-16-3-33 ~]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w ssl.dmp

使用psql连接数据库.
dump结果 :
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
33 packets captured
33 packets received by filter
0 packets dropped by kernel


调整pg_hba.conf
#hostssl all all 0.0.0.0/0 md5
hostnossl all all 0.0.0.0/0 md5
reload
[root@db-172-16-3-33 ~]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w nossl.dmp

使用psql连接数据库.
dump结果 :
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
52 packets captured
52 packets received by filter
0 packets dropped by kernel


3. 使用wireshark分析数据包 : 
nossl.dmp中找到了md5内容.
这个md5值并不是pg_shadow中存储的md5值, 而是加上了一个token后再次md5的值. 所以密码相对来说被破解的概率较小.
但是数据则不是加密的, 很容易被截获.

在ssl.dmp中则只有加密后的信息, 因为所有的数据都加密了, 所以无法窥探到有价值的信息.
Encrypting Passwords Across A Network
The MD5 authentication method double-encrypts the password on the client before sending it to the server. It first MD5-encrypts it based on the user name, and then encrypts it based on a random salt sent by the server when the database connection was made. It is this double-encrypted value that is sent over the network to the server. Double-encryption not only prevents the password from being discovered, it also prevents another connection from using the same encrypted password to connect to the database server at a later time.

SSL Host Authentication
It is possible for both the client and server to provide SSL certificates to each other. It takes some extra configuration on each side, but this provides stronger verification of identity than the mere use of passwords. It prevents a computer from pretending to be the server just long enough to read the password sent by the client. It also helps prevent "man in the middle" attacks where a computer between the client and server pretends to be the server and reads and passes all data between the client and server.


[参考]
11.
pg93@db-172-16-3-33-> openssl genrsa help
usage: genrsa [args] [numbits]
 -des            encrypt the generated key with DES in cbc mode
 -des3           encrypt the generated key with DES in ede cbc mode (168 bit key)
 -aes128, -aes192, -aes256
                 encrypt PEM output with cbc aes
 -out file       output the key to 'file
 -passout arg    output file pass phrase source
 -f4             use F4 (0x10001) for the E value
 -3              use 3 for the E value
 -engine e       use engine e, possibly a hardware device.
 -rand file:file:...
                 load the file (or the files in the directory) into
                 the random number generator

12. 
pg93@db-172-16-3-33-> openssl rsa help
unknown option help
rsa [options] <infile >outfile
where options are
 -inform arg     input format - one of DER NET PEM
 -outform arg    output format - one of DER NET PEM
 -in arg         input file
 -sgckey         Use IIS SGC key format
 -passin arg     input file pass phrase source
 -out arg        output file
 -passout arg    output file pass phrase source
 -des            encrypt PEM output with cbc des
 -des3           encrypt PEM output with ede cbc des using 168 bit key
 -aes128, -aes192, -aes256
                 encrypt PEM output with cbc aes
 -text           print the key in text
 -noout          don't print key out
 -modulus        print the RSA key modulus
 -check          verify key consistency
 -pubin          expect a public key in input file
 -pubout         output a public key
 -engine e       use engine e, possibly a hardware device.

13. 
pg93@db-172-16-3-33-> openssl req help
unknown option help
req [options] <infile >outfile
where options  are
 -inform arg    input format - DER or PEM
 -outform arg   output format - DER or PEM
 -in arg        input file
 -out arg       output file
 -text          text form of request
 -pubkey        output public key
 -noout         do not output REQ
 -verify        verify signature on REQ
 -modulus       RSA modulus
 -nodes         don't encrypt the output key
 -engine e      use engine e, possibly a hardware device
 -subject       output the request's subject
 -passin        private key password source
 -key file      use the private key contained in file
 -keyform arg   key file format
 -keyout arg    file to send the key to
 -rand file:file:...
                load the file (or the files in the directory) into
                the random number generator
 -newkey rsa:bits generate a new RSA key of 'bits' in size
 -newkey dsa:file generate a new DSA key, parameters taken from CA in 'file'
 -[digest]      Digest to sign with (see openssl dgst -h for list)
 -config file   request template file.
 -subj arg      set or modify request subject
 -multivalue-rdn enable support for multivalued RDNs
 -new           new request.
 -batch         do not ask anything during request generation
 -x509          output a x509 structure instead of a cert. req.
 -days          number of days a certificate generated by -x509 is valid for.
 -set_serial    serial number to use for a certificate generated by -x509.
 -newhdr        output "NEW" in the header lines
 -asn1-kludge   Output the 'request' in a format that is wrong but some CA's
                have been reported as requiring
 -extensions .. specify certificate extension section (override value in config file)
 -reqexts ..    specify request extension section (override value in config file)
 -utf8          input characters are UTF8 (default ASCII)
 -nameopt arg    - various certificate name options
 -reqopt arg    - various request text options

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之使用连接串模式新增PostgreSQL数据源时遇到了报错"not support data sync channel, error code: 0001",该怎么办
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
JSON Java 关系型数据库
Spring Boot 学习研究笔记(十三) Spring Data JPA与PostgreSQL的jsonb类型集成
Spring Boot 学习研究笔记(十三) Spring Data JPA与PostgreSQL的jsonb类型集成
319 0
|
SQL 关系型数据库 分布式数据库
|
SQL 关系型数据库 分布式数据库
|
存储 Prometheus Kubernetes
云原生 PostgreSQL 集群 - PGO:来自 Crunchy Data 的 Postgres Operator
云原生 PostgreSQL 集群 - PGO:来自 Crunchy Data 的 Postgres Operator
556 0
云原生 PostgreSQL 集群 - PGO:来自 Crunchy Data 的 Postgres Operator
|
关系型数据库 PostgreSQL
PostgreSQL - ERROR: could not determine data type of parameter $1
PostgreSQL - ERROR: could not determine data type of parameter $1
2058 0
|
分布式计算 关系型数据库 MaxCompute
Best Practices for Data Migration from MaxCompute to HybridDB for PostgreSQL
Users often struggle to migrate data from MaxCompute to HybridDB. This blog describes five steps that will help you migrate data conveniently.
2947 0
Best Practices for Data Migration from MaxCompute to HybridDB for PostgreSQL
|
关系型数据库 PostgreSQL
PostgreSQL sharding : citus 系列7 - topn 加速(count(*) group by order by count(*) desc limit x) (use 估值插件 topn)
标签 PostgreSQL , topn , topn.number_of_counters , count(*) group by order by count(*) desc limit x 背景 count(*) group by order by count(*) desc limit x 用来统计 topn。
1430 0
|
关系型数据库 PostgreSQL
PostgreSQL sharding : citus 系列6 - count(distinct xx) 加速 (use 估值插件 hll|hyperloglog)
标签 PostgreSQL , hll , hyperloglog , distinct , 加速 , citus.count_distinct_error_rate 背景 在分布式数据库中,计算count(distinct xxx),需要对distinct 的字段, 1、去重, 2、重分布去重后的数据,(这一步,如果distinct值特别多,那么就会比较耗时) 3、然后再去重, 4、最后count (xxx), 5、求所有节点的count SUM。
1834 0
下一篇
无影云桌面