如何在PostgreSQL中创建使用公钥/私钥而非密码验证的用户

前置准备：生成SSL证书链 #

需要提前用openssl生成根CA证书、服务端证书、客户端证书，所有生成的文件注意权限控制：

• 生成根CA私钥：openssl genrsa -out rootCA.key 4096
• 生成根CA自签证书：openssl req -new -x509 -days 3650 -key rootCA.key -out rootCA.crt
• 生成服务端私钥：openssl genrsa -out server.key 4096
• 生成服务端证书签名请求：openssl req -new -key server.key -out server.csr，填写信息时Common Name需和PostgreSQL服务器的域名/IP一致
• 用根CA签发服务端证书：openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 3650
• 修改服务端证书和密钥权限：chmod 600 server.key && chown postgres:postgres server.key server.crt rootCA.crt
• 生成客户端私钥：openssl genrsa -out client.key 4096
• 生成客户端证书签名请求：openssl req -new -key client.key -out client.csr，填写信息时Common Name必须和后续要创建的PostgreSQL用户名完全一致
• 用根CA签发客户端证书：openssl x509 -req -in client.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out client.crt -days 3650
• 修改客户端密钥权限：chmod 600 client.key

服务端配置 #

1. 调整postgresql.conf参数 #

找到PostgreSQL数据目录下的postgresql.conf文件，修改以下配置：

• ssl = on 开启SSL支持
• ssl_ca_file = 'rootCA.crt' 填写根CA证书的绝对路径或相对数据目录的路径
• ssl_cert_file = 'server.crt' 填写服务端证书路径
• ssl_key_file = 'server.key' 填写服务端私钥路径

配置修改完成后重启PostgreSQL服务生效：systemctl restart postgresql

2. 调整pg_hba.conf访问规则 #

找到同目录下的pg_hba.conf文件，在靠前位置（密码类认证规则之前，规则从上到下匹配）添加如下配置：

hostssl all all 允许访问的客户端IP段/掩码 cert clientcert=1

示例允许本机所有用户证书认证：hostssl all all 127.0.0.1/32 cert clientcert=1
配置修改完成后重载生效，无需重启服务：psql -U postgres -c "SELECT pg_reload_conf();"

创建无密码数据库用户 #

直接创建对应用户即可，不需要设置密码参数：

• 命令行创建：createuser -U postgres <客户端证书Common Name对应的用户名>
• 或在psql中执行SQL创建：CREATE USER <用户名> WITH LOGIN;

客户端连接验证 #

将生成的client.key、client.crt、rootCA.crt放到客户端机器的指定目录（比如~/.postgresql/），执行以下命令即可无密码登录：

psql "host=<服务端IP/域名> port=5432 user=<用户名> dbname=<要连接的数据库名> sslmode=verify-ca sslrootcert=~/.postgresql/rootCA.crt sslcert=~/.postgresql/client.crt sslkey=~/.postgresql/client.key"

内容的提问来源于stack exchange，提问作者Hard Worker