Postgresql 中文操作指南

34.19. SSL Support #

PostgreSQL 本机支持使用 SSL 连接，使用 TLS 协议加密客户端/服务器通信，以提高安全性。有关服务器端 SSL 功能的详细信息，请参见 Section 19.9。

PostgreSQL has native support for using SSL connections to encrypt client/server communications using TLS protocols for increased security. See Section 19.9 for details about the server-side SSL functionality.

libpq 读取系统范围的 OpenSSL 配置文件。默认情况下，此文件名为 openssl.cnf，位于 openssl version -d 报告的目录中。可以将环境变量 OPENSSL_CONF 设置为所需配置文件的名称，从而覆盖此默认值。

libpq 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.

34.19.1. Client Verification of Server Certificates #

默认情况下，PostgreSQL 不会对服务器证书执行任何验证。这意味着可以在客户端不知情的情况下欺骗服务器身份（例如，通过修改 DNS 记录或接管服务器 IP 地址）。为了防止欺骗，客户端必须能够通过信任链验证服务器身份。通过在一台计算机上放置根（自签名）证书颁发机构 (CA) 证书和由根证书在另一台计算机上签署的叶证书 signed，来建立信任链。也可使用由根证书签署并签署叶证书的“中间”证书。

By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity (for example by modifying a DNS record or by taking over the server IP address) without the client knowing. In order to prevent spoofing, the client must be able to verify the server’s identity via a chain of trust. A chain of trust is established by placing a root (self-signed) certificate authority (CA) certificate on one computer and a leaf certificate signed by the root certificate on another computer. It is also possible to use an “intermediate” certificate which is signed by the root certificate and signs leaf certificates.

若要允许客户端验证服务器身份，请在客户端上放置一个根证书，并在服务器上放置一个由根证书签署的叶证书。为了允许服务器验证客户端的身份，请在服务器上放置一个根证书，并在客户端上放置一个由根证书签署的叶证书。一个或多个中间证书（通常与叶证书存储在一起）也可以用于将叶证书链接到根证书。

To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server. To allow the server to verify the identity of the client, place a root certificate on the server and a leaf certificate signed by the root certificate on the client. One or more intermediate certificates (usually stored with the leaf certificate) can also be used to link the leaf certificate to the root certificate.

一旦建立了信任链，客户端就可以通过两种方式验证服务器发送的叶证书。如果将参数 sslmode 设置为 verify-ca，libpq 将通过检查客户端上存储的根证书的证书链来验证服务器可信。如果 sslmode 设置为 verify-full，libpq 将 also 验证服务器主机名是否与存储在服务器证书中的名称匹配。如果无法验证服务器证书，SSL 连接将会失败。在大多数安全敏感的环境中，建议采用 verify-full。

Once a chain of trust has been established, there are two ways for the client to validate the leaf certificate sent by the server. If 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.

在 verify-full 模式下，将主机名与证书的主题备用名称属性（SAN）或通用名称属性进行匹配（如果不存在类型为 dNSName 的 SAN 时）。如果证书的名称属性以星号 (*) 开头，星号将被视为通配符，它将匹配包含 except 点 (.) 的所有字符。这意味着证书将不匹配子域。如果使用 IP 地址而不是主机名建立连接，则该 IP 地址将与类型为 iPAddress 或 dNSName 的 SAN（不执行任何 DNS 查询）进行匹配。如果没有 iPAddress SAN 且不存在匹配的 dNSName SAN，则主机 IP 地址将与通用名称属性进行匹配。

In verify-full mode, the host name is matched against the certificate’s Subject Alternative Name attribute(s) (SAN), or against the Common Name attribute if no SAN of type dNSName is present. If the certificate’s name attribute starts with an asterisk (*), the asterisk will be treated as a wildcard, which will match all characters except a dot (.). This means the certificate will not match subdomains. If the connection is made using an IP address instead of a host name, the IP address will be matched (without doing any DNS lookups) against SANs of type iPAddress or dNSName. If no iPAddress SAN is present and no matching dNSName SAN is present, the host IP address is matched against the Common Name attribute.

Note

为了与 PostgreSQL 的早期版本向后兼容，将以不同于 RFC 6125的方式验证主机 IP 地址。主机 IP 地址始终与_dNSName_SAN 以及_iPAddress_SAN 匹配，并且如果不存在相关 SAN，则可以与公用名属性匹配。

For backward compatibility with earlier versions of PostgreSQL, the host IP address is verified in a manner different from RFC 6125. The host IP address is always matched against dNSName SANs as well as iPAddress SANs, and can be matched against the Common Name attribute if no relevant SANs exist.

若要允许服务器证书验证，必须将一个或多个根证书放入用户主目录中的 ~/.postgresql/root.crt 文件中。（在 Microsoft Windows 中，此文件名为 %APPDATA%\postgresql\root.crt。）如果需要将服务器发送的证书链链接到客户端上存储的根证书，则还应该将中间证书添加到此文件中。

To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user’s home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.) Intermediate certificates should also be added to the file if they are needed to link the certificate chain sent by the server to the root certificates stored on the client.

如果文件 ~/.postgresql/root.crl 存在（在 Microsoft Windows 中为 %APPDATA%\postgresql\root.crl），则还将检查证书吊销列表 (CRL) 条目。

Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on Microsoft Windows).

可以通过设置连接参数 sslrootcert 和 sslcrl 或环境变量 PGSSLROOTCERT 和 PGSSLCRL 来更改根证书文件和 CRL 的位置。还可使用 sslcrldir 或环境变量 PGSSLCRLDIR 来指定包含 CRL 文件的目录。

The location of the root certificate file and the CRL can be changed by setting the connection parameters sslrootcert and sslcrl or the environment variables PGSSLROOTCERT and PGSSLCRL. sslcrldir or the environment variable PGSSLCRLDIR can also be used to specify a directory containing CRL files.

Note

为了向后兼容 PostgreSQL 的早期版本，如果存在根 CA 文件，sslmode=require 的行为将与 verify-ca 的行为相同，这意味着服务器证书将针对 CA 验证。不建议依赖此行为，需要证书验证的应用程序应始终使用 verify-ca 或 verify-full。

For backwards compatibility with earlier versions of PostgreSQL, if a root CA file exists, the behavior of sslmode=require will be the same as that of verify-ca, meaning the server certificate is validated against the CA. Relying on this behavior is discouraged, and applications that need certificate validation should always use verify-ca or verify-full.

34.19.2. Client Certificates #

如果服务器通过请求客户端的叶证书来尝试验证客户端身份，则 libpq 将发送存储在用户主目录中文件 ~/.postgresql/postgresql.crt 中的证书。证书必须链接到服务器信任的根证书。还必须存在一个匹配的私钥文件 ~/.postgresql/postgresql.key。在 Microsoft Windows 中，这些文件名为 %APPDATA%\postgresql\postgresql.crt 和 %APPDATA%\postgresql\postgresql.key。可以通过连接参数 sslcert 和 sslkey 或环境变量 PGSSLCERT 和 PGSSLKEY 来覆盖证书和密钥文件的位置。

If the server attempts to verify the identity of the client by requesting the client’s leaf certificate, libpq will send the certificate(s) stored in file ~/.postgresql/postgresql.crt in the user’s home directory. The certificates must chain to the root certificate trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey, or by the environment variables PGSSLCERT and PGSSLKEY.

在 Unix 系统中，私钥文件上的权限必须禁止所有用户和组的任何访问；通过诸如 chmod 0600 ~/.postgresql/postgresql.key 之类的命令来实现此目标。或者，该文件可以由 root 拥有并具有组读取访问权限（即 0640 权限）。此设置适用于证书和密钥文件由操作系统管理的安装情况。然后，应使 libpq 的用户成为对这些证书和密钥文件具有访问权限的组的成员。（在 Microsoft Windows 中，没有文件权限检查，因为假设 %APPDATA%\postgresql 目录是安全的。）

On Unix systems, the permissions on the private key file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.postgresql/postgresql.key. Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions). That setup is intended for installations where certificate and key files are managed by the operating system. The user of libpq should then be made a member of the group that has access to those certificate and key files. (On Microsoft Windows, there is no file permissions check, since the %APPDATA%\postgresql directory is presumed secure.)

_postgresql.crt_中的第一个证书必须是客户端证书，因为它必须与客户端私钥匹配。“中间”证书可以可选择地附加到该文件 - 这样做可以避免需要在服务器上存储中间证书（ ssl_ca_file）。

The first certificate in postgresql.crt must be the client’s certificate because it must match the client’s private key. “Intermediate” certificates can be optionally appended to the file — doing so avoids requiring storage of intermediate certificates on the server (ssl_ca_file).

证书和密钥可以是 PEM 或 ASN.1 DER 格式。

The certificate and key may be in PEM or ASN.1 DER format.

可以使用 OpenSSL 支持的任何算法（如 AES-128）将密钥存储为明文或使用密码短语进行加密。如果密钥以加密方式存储，则可以在 sslpassword 连接选项中提供密码短语。如果提供了加密密钥，而 sslpassword 选项不存在或为空，则 OpenSSL 会在有 TTY 时使用 Enter PEM pass phrase: 提示交互式地提示输入密码。应用程序可以通过提供自己的密钥密码回调来覆盖客户端证书提示和 sslpassword 参数的处理。请参阅 PQsetSSLKeyPassHook_OpenSSL 。

The key may be stored in cleartext or encrypted with a passphrase using any algorithm supported by OpenSSL, like AES-128. If the key is stored encrypted, then the passphrase may be provided in the sslpassword connection option. If an encrypted key is supplied and the sslpassword option is absent or blank, a password will be prompted for interactively by OpenSSL with a Enter PEM pass phrase: prompt if a TTY is available. Applications can override the client certificate prompt and the handling of the sslpassword parameter by supplying their own key password callback; see PQsetSSLKeyPassHook_OpenSSL.

有关创建证书的说明，请参见 Section 19.9.5。

For instructions on creating certificates, see Section 19.9.5.

34.19.3. Protection Provided in Different Modes #

sslmode 参数的不同值提供了不同的保护级别。SSL 可以针对三种类型的攻击提供保护：

The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attacks:

  • Eavesdropping

    • If a third party can examine the network traffic between the client and the server, it can read both connection information (including the user name and password) and the data that is passed. SSL uses encryption to prevent this.

  • Man-in-the-middle (MITM)

    • If a third party can modify the data while passing between the client and server, it can pretend to be the server and therefore see and modify data even if it is encrypted. The third party can then forward the connection information and data to the original server, making it impossible to detect this attack. Common vectors to do this include DNS poisoning and address hijacking, whereby the client is directed to a different server than intended. There are also several other attack methods that can accomplish this. SSL uses certificate verification to prevent this, by authenticating the server to the client.

  • Impersonation

    • If a third party can pretend to be an authorized client, it can simply access data it should not have access to. Typically this can happen through insecure password management. SSL uses client certificates to prevent this, by making sure that only holders of valid certificates can access the server.

为了让连接成为众所周知的安全 SSL 连接，必须在建立连接之前在_both the client and the server_上配置 SSL 使用情况。如果仅在服务器上配置，客户端可能在得知服务器需要高安全性的情况下即发送敏感信息（例如密码）。在 libpq 中，可以通过将_sslmode_ 参数设置为_verify-full_ 或 verify-ca 来确保安全连接，并为系统提供一个根证书以供验证。这类似于将 https URL 用于加密 Web 浏览。

For a connection to be known SSL-secured, SSL usage must be configured on both the client and the server before the connection is made. If it is only configured on the server, the client may end up sending sensitive information (e.g., passwords) before it knows that the server requires high security. In libpq, secure connections can be ensured by setting the sslmode parameter to verify-full or verify-ca, and providing the system with a root certificate to verify against. This is analogous to using an https URL for encrypted web browsing.

经过服务器验证后，客户端可以传递敏感数据。这意味着在此之前，客户端无需知道是否会使用证书进行身份验证，因此可以安全地在服务器配置中指定仅使用证书。

Once the server has been authenticated, the client can pass sensitive data. This means that up until this point, the client does not need to know if certificates will be used for authentication, making it safe to specify that only in the server configuration.

所有 SSL 选项都以加密和密钥交换的形式带来开销，因此必须在性能和安全性之间进行权衡。 Table 34.1说明了不同的 _sslmode_值可以防范哪些风险，以及它们对安全性和开销的说明。

All SSL options carry overhead in the form of encryption and key-exchange, so there is a trade-off that has to be made between performance and security. Table 34.1 illustrates the risks the different sslmode values protect against, and what statement they make about security and overhead.

Table 34.1. SSL Mode Descriptions

sslmode

Eavesdropping protection

MITM protection

Statement

disable

No

No

I don’t care about security, and I don’t want to pay the overhead of encryption.

allow

Maybe

No

I don’t care about security, but I will pay the overhead of encryption if the server insists on it.

prefer

Maybe

No

I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it.

require

Yes

No

I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want.

verify-ca

Yes

Depends on CA policy

I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust.

verify-full

Yes

Yes

I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it’s the one I specify.

verify-ca 和 verify-full 之间的差异取决于根 CA 的策略。如果使用公有 CA，verify-ca 允许连接到 somebody else 可能已向 CA 注册的服务器。在这种情况下，应始终使用 verify-full。如果使用本地 CA，甚至自签名证书，使用 verify-ca 通常可提供足够的保护。

The difference between verify-ca and verify-full depends on the policy of the root CA. If a public CA is used, verify-ca allows connections to a server that somebody else may have registered with the CA. In this case, verify-full should always be used. If a local CA is used, or even a self-signed certificate, using verify-ca often provides enough protection.

sslmode 的默认值为 prefer。如表中所示，从安全的角度来看，这毫无意义，如果可能，它只会造成性能开销。它仅作为向后兼容性的默认值提供，并且在安全部署中不建议使用。

The default value for sslmode is prefer. As is shown in the table, this makes no sense from a security point of view, and it only promises performance overhead if possible. It is only provided as the default for backward compatibility, and is not recommended in secure deployments.

34.19.4. SSL Client File Usage #

Table 34.2总结了与客户端 SSL 设置相关的文件。

Table 34.2 summarizes the files that are relevant to the SSL setup on the client.

Table 34.2. Libpq/Client SSL File Usage

File

Contents

Effect

~/.postgresql/postgresql.crt

client certificate

sent to server

~/.postgresql/postgresql.key

client private key

proves client certificate sent by owner; does not indicate certificate owner is trustworthy

~/.postgresql/root.crt

trusted certificate authorities

checks that server certificate is signed by a trusted certificate authority

~/.postgresql/root.crl

certificates revoked by certificate authorities

server certificate must not be on this list

34.19.5. SSL Library Initialization #

如果您的应用程序初始化了 libssl 和/或 libcrypto 库，而libpq使用SSL支持构建，您应该调用 PQinitOpenSSL 来告诉libpq libssl 和/或 libcrypto 库已由您的应用程序初始化，那么libpq将不会也初始化这些库。但是，在使用OpenSSL版本1.1.0或更高版本时，这是不必要的，因为重复初始化不再是一个问题。

If your application initializes libssl and/or libcrypto libraries and libpq is built with SSL support, you should call PQinitOpenSSL to tell libpq that the libssl and/or libcrypto libraries have been initialized by your application, so that libpq will not also initialize those libraries. However, this is unnecessary when using OpenSSL version 1.1.0 or later, as duplicate initializations are no longer problematic.

  • PQinitOpenSSL #

    • Allows applications to select which security libraries to initialize.

void PQinitOpenSSL(int do_ssl, int do_crypto);

  • When do_ssl is non-zero, libpq will initialize the OpenSSL library before first opening a database connection. When do_crypto is non-zero, the libcrypto library will be initialized. By default (if PQinitOpenSSL is not called), both libraries are initialized. When SSL support is not compiled in, this function is present but does nothing.

  • If your application uses and initializes either OpenSSL or its underlying libcrypto library, you must call this function with zeroes for the appropriate parameter(s) before first opening a database connection. Also be sure that you have done that initialization before opening a database connection.

    • PQinitSSL #

  • Allows applications to select which security libraries to initialize.

void PQinitSSL(int do_ssl);

  • This function is equivalent to PQinitOpenSSL(do_ssl, do_ssl). It is sufficient for applications that initialize both or neither of OpenSSL and libcrypto.

  • PQinitSSL has been present since PostgreSQL 8.0, while PQinitOpenSSL was added in PostgreSQL 8.4, so PQinitSSL might be preferable for applications that need to work with older versions of libpq.