PostgreSQL sslutils插件 - 通过SQL接口管理ssl key,crt,crl

digoal 2020-08-14

417

作者

digoal

日期

2020-08-14

背景

《PostgreSQL ssl 证书配置 - 防止中间攻击者 - 以及如何使用证书无密码登录配置cert》

如果能通过SQL接口直接管理ca, cert, key, crl等文件, 会方便很多.

edb 提供了一个这样的插件, 除了ca服务器的证书, 其他都能管.

https://github.com/EnterpriseDB/sslutils

SSLUtils

SSLUtils is a Postgres extension that provides SSL certicate generation
functions to Postgres, for use by the Postgres Enterprise Manager server.

This extension is released under the PostgreSQL Licence.

Building

The module may be built using the PGXS framework on most operating systems:

Unpack the extensions files in $PGSRC/contrib/sslutils
Run "make USE_PGXS=1" in the $PGSRC/contrib/sslutils directory.
Run "make USE_PGXS=1 install" to install.

MSVC++ builds are also supported using the clean.bat and build.bat scripts:

Set the PGPATH environment variable to point to your Postgres installation
directory.
Run build.bat in a VC++ command prompt to build the extension.
Copy sslutils.dll into $PGDIR/lib and *.sql and sslutils.control into
$PGDIR/share/extension

Functions

The following functions are provided:

openssl_rsa_generate_key(integer) RETURNS text

Purpose: Generates an RSA private key.
Param 1: Number of bits.
Returns: The generated key.

openssl_rsa_key_to_csr(text, text, text, text, text, text, text) RETURNS text

Purpose: Generates a certificate signing request (CSR)
Param 1: RSA key
Param 2: CN or common name e.g. agentN
Param 3: C or Country
Param 4: ST or State
Param 5: L or Location (City)
Param 6: OU or Organization Unit
Param 7: Email address
Returns: The generated CSR.

openssl_csr_to_crt(text, text, text) RETURNS text

Purpose: Generates a self-signed certificate (or a CA certificate)
Param 1: CSR
Param 2: Path to the CA certificate OR NULL if generating a CA certificate.
Param 3: Path to the CA private key OR path to a private key, If param2 is NULL.
Returns: The certificate.

openssl_rsa_generate_crl(text, text) RETURNS text

Purpose: Generates a default certificate revocation list.
Param 1: Path to CA certificate.
Param 2: Path to CA private key.
Returns: The CRL.

openssl_is_crt_expire_on(text, timestamptz)

Purpose: Compare certificate expiry on given time.
Param1: Path to certificate.
Param2: time to compare with end date;
Returns: 1 - sucesss
-1 - certificate expires
0 - error

openssl_revoke_certificate(text, text) RETURNS text

Purpose: Revoke the client certificate and re-generate crl file.
Param 1: Path to client certificate to be revoked.
Param 2: CRL file name specified in postgres config file.
Returns: The CRL.

openssl_get_crt_expiry_date(text)

Purpose: Get the certificate expiry date.
Param1: Path to certificate.
Returns: end date of the certificate.