Postgresql 中文操作指南

19.9. Secure TCP/IP Connections with SSL #

PostgreSQL 原生支持使用 SSL 连接来加密客户端/服务器通信以提高安全性。这要求在客户端和服务器系统上都安装 OpenSSL，并且在构建时启用 PostgreSQL 中的支持（参见 Chapter 17）。

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see Chapter 17).

术语 SSL 和 TLS 通常可以互换使用，表示使用 TLS 协议的安全加密连接。SSL 协议是 TLS 协议的前身，即使 SSL 协议不再受支持，术语 SSL 仍然用于加密连接。SSL 在 PostgreSQL 中可以与 TLS 互换使用。

The terms SSL and TLS are often used interchangeably to mean a secure encrypted connection using a TLS protocol. SSL protocols are the precursors to TLS protocols, and the term SSL is still used for encrypted connections even though SSL protocols are no longer supported. SSL is used interchangeably with TLS in PostgreSQL.

19.9.1. Basic Setup #

在编译了 SSL 支持的情况下，可以通过设置 postgresql.conf 中的参数 ssl 为 on 来启动 PostgreSQL 服务器，使其支持使用 TLS 协议进行加密连接。服务器将在同一 TCP 端口上侦听正常连接和 SSL 连接，并将与任何连接的客户端协商是否使用 SSL。默认情况下，这在客户端的选择中；有关如何设置服务器以要求某些或所有连接使用 SSL，请参见 Section 21.1。

With SSL support compiled in, the PostgreSQL server can be started with support for encrypted connections using TLS protocols enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client’s option; see Section 21.1 about how to set up the server to require use of SSL for some or all connections.

要在 SSL 模式下启动，必须存在包含服务器证书和私钥的文件。默认情况下，这些文件分别位于服务器数据目录中，但可以使用配置参数 ssl_cert_file 和 ssl_key_file 指定其他名称和位置。

To start in SSL mode, files containing the server certificate and private key must exist. By default, these files are expected to be named server.crt and server.key, respectively, in the server’s data directory, but other names and locations can be specified using the configuration parameters ssl_cert_file and ssl_key_file.

在 Unix 系统上，server.key 中的权限必须禁止任何对全世界或任何组的访问；使用 chmod 0600 server.key 命令可实现这一功能。或者，可以由 root 拥有该文件并具有组读取权限（即 0640 权限）。此设置适用于操作系统管理证书和密钥文件的安装。然后，应将运行 PostgreSQL 服务器的用户设为有权访问这些证书和密钥文件的组的成员。

On Unix systems, the permissions on server.key must disallow any access to world or group; achieve this by the command chmod 0600 server.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 under which the PostgreSQL server runs should then be made a member of the group that has access to those certificate and key files.

如果数据目录允许组读取访问，则可能需要将证书文件设置在数据目录外部，以便符合上述安全要求。通常，启用组访问是为了允许非特权用户备份数据库，在这种情况下，备份软件将无法读取证书文件，并且可能会出错。

If the data directory allows group read access then certificate files may need to be located outside of the data directory in order to conform to the security requirements outlined above. Generally, group access is enabled to allow an unprivileged user to backup the database, and in that case the backup software will not be able to read the certificate files and will likely error.

若私钥受密码保护，服务器会提示输入密码，只有输入之后才能启动。默认情况下，使用密码会禁用在不重启服务器的情况下更改服务器的 SSL 配置的能力，详情请参阅 ssl_passphrase_command_supports_reload。此外，在 Windows 上根本无法使用受密码保护的私钥。

If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered. Using a passphrase by default disables the ability to change the server’s SSL configuration without a server restart, but see ssl_passphrase_command_supports_reload. Furthermore, passphrase-protected private keys cannot be used at all on Windows.

server.crt 中的第一个证书必须是服务器证书，因为它必须与服务器的私钥匹配。“中间”证书颁发机构的证书也可以附加到文件中。如果使用 v3_ca 扩展创建了根证书和中间证书，则可以免去在客户端上存储中间证书的必要性。（这会将证书的基本约束 CA 设置为 true。）这能更轻松地让中间证书过期。

The first certificate in server.crt must be the server’s certificate because it must match the server’s private key. The certificates of “intermediate” certificate authorities can also be appended to the file. Doing this avoids the necessity of storing intermediate certificates on clients, assuming the root and intermediate certificates were created with v3_ca extensions. (This sets the certificate’s basic constraint of CA to true.) This allows easier expiration of intermediate certificates.

无需将根证书添加到 server.crt 中。取而代之的是，客户端必须拥有服务器证书链的根证书。

It is not necessary to add the root certificate to server.crt. Instead, clients must have the root certificate of the server’s certificate chain.

19.9.2. OpenSSL Configuration #

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.

OpenSSL 支持各种强度各异的密码和身份验证算法。可以在 OpenSSL 配置文件中指定密码列表，也可以通过修改 _postgresql.conf_中的 ssl_ciphers，指定特定密码供数据库服务器使用。

OpenSSL supports a wide range of ciphers and authentication algorithms, of varying strength. While a list of ciphers can be specified in the OpenSSL configuration file, you can specify ciphers specifically for use by the database server by modifying ssl_ciphers in postgresql.conf.

Note

可以通过使用 NULL-SHA 或 NULL-MD5 密码来实现没有加密开销的身份验证。但是，中间人可能读取并传递客户端和服务器之间的通信。此外，与身份验证的开销相比，加密开销最小。出于这些原因，不推荐使用 NULL 密码。

It is possible to have authentication without encryption overhead by using NULL-SHA or NULL-MD5 ciphers. However, a man-in-the-middle could read and pass communications between client and server. Also, encryption overhead is minimal compared to the overhead of authentication. For these reasons NULL ciphers are not recommended.

19.9.3. Using Client Certificates #

要要求客户端提供可信证书，请将您信任的根证书颁发机构 (CA) 的证书放在数据目录中的一个文件中，将 _postgresql.conf_中的参数 ssl_ca_file设置为新的文件名，并在 _pg_hba.conf_中将身份验证选项 _clientcert=verify-ca_或 _clientcert=verify-full_添加到适当的 _hostssl_行中。然后，将在 SSL 连接启动期间向客户端请求证书。（有关如何在客户端上设置证书的说明，请参阅 Section 34.19。）

To require the client to supply a trusted certificate, place certificates of the root certificate authorities (CAs) you trust in a file in the data directory, set the parameter ssl_ca_file in postgresql.conf to the new file name, and add the authentication option clientcert=verify-ca or clientcert=verify-full to the appropriate hostssl line(s) in pg_hba.conf. A certificate will then be requested from the client during SSL connection startup. (See Section 34.19 for a description of how to set up certificates on the client.)

对于带有 clientcert=verify-ca_的 _hostssl_条目，服务器将验证客户端证书是否由某一个受信任的证书颁发机构签名。如果指定 _clientcert=verify-full，服务器不仅会验证证书链，还会检查用户名或其映射是否与提供的证书的 cn（通用名称）相匹配。请注意，在使用 _cert_身份验证方法时，始终确保证书链验证（请参阅 Section 21.12）。

For a hostssl entry with clientcert=verify-ca, the server will verify that the client’s certificate is signed by one of the trusted certificate authorities. If clientcert=verify-full is specified, the server will not only verify the certificate chain, but it will also check whether the username or its mapping matches the cn (Common Name) of the provided certificate. Note that certificate chain validation is always ensured when the cert authentication method is used (see Section 21.12).

如果希望避免在客户端上存储中间证书，则也可以在 ssl_ca_file文件中显示链接到现有根证书的中间证书（假设根证书和中间证书是使用 _v3_ca_扩展创建的）。如果设置了参数 ssl_crl_file或 ssl_crl_dir，系统还会检查证书吊销列表 (CRL) 条目。

Intermediate certificates that chain up to existing root certificates can also appear in the ssl_ca_file file if you wish to avoid storing them on clients (assuming the root and intermediate certificates were created with v3_ca extensions). Certificate Revocation List (CRL) entries are also checked if the parameter ssl_crl_file or ssl_crl_dir is set.

clientcert 身份验证选项适用于所有身份验证方法，但仅适用于指定为 hostssl 的 pg_hba.conf 行中。当未指定 clientcert 时，服务器仅当提供客户端证书且已配置了 CA 时才针对其 CA 文件验证客户端证书。

The clientcert authentication option is available for all authentication methods, but only in pg_hba.conf lines specified as hostssl. When clientcert is not specified, the server verifies the client certificate against its CA file only if a client certificate is presented and the CA is configured.

有两种方法可以强制用户在登录期间提供证书。

There are two approaches to enforce that users provide a certificate during login.

第一个方法对 pg_hba.conf_中 _hostssl_条目的 _cert_身份验证方法进行了使用，因此该方法使用证书本身进行身份验证，同时还提供 SSL 连接安全。有关详细信息，请参阅 Section 21.12。（使用 _cert_身份验证方法时，无需显式指定任何 _clientcert_选项。）在这种情况下，证书中提供的 _cn（通用名称）会与用户名或适用的映射进行比较。

The first approach makes use of the cert authentication method for hostssl entries in pg_hba.conf, such that the certificate itself is used for authentication while also providing ssl connection security. See Section 21.12 for details. (It is not necessary to specify any clientcert options explicitly when using the cert authentication method.) In this case, the cn (Common Name) provided in the certificate is checked against the user name or an applicable mapping.

第二种方法是将 hostssl 条目的任何身份验证方法与 clientcert 身份验证选项设置为 verify-ca 或 verify-full 的客户端证书验证相结合。前一个选项只强制证书有效，而后一个选项还确保证书中的 cn（通用名称）与用户名或适用的映射相匹配。

The second approach combines any authentication method for hostssl entries with the verification of client certificates by setting the clientcert authentication option to verify-ca or verify-full. The former option only enforces that the certificate is valid, while the latter also ensures that the cn (Common Name) in the certificate matches the user name or an applicable mapping.

19.9.4. SSL Server File Usage #

Table 19.2总结了与服务器上的 SSL 设置相关的文件。（显示的文件名是默认名称。本地配置的名称可能不同。）

Table 19.2 summarizes the files that are relevant to the SSL setup on the server. (The shown file names are default names. The locally configured names could be different.)

Table 19.2. SSL Server File Usage

File

Contents

Effect

ssl_cert_file ($PGDATA/server.crt)

server certificate

sent to client to indicate server’s identity

ssl_key_file ($PGDATA/server.key)

server private key

proves server certificate was sent by the owner; does not indicate certificate owner is trustworthy

ssl_ca_file

trusted certificate authorities

checks that client certificate is signed by a trusted certificate authority

ssl_crl_file

certificates revoked by certificate authorities

client certificate must not be on this list

服务器在服务器启动时以及在重新加载服务器配置时读取这些文件。在 Windows 系统上，只要为新的客户端连接生成新的后端进程，就会重新读取这些文件。

The server reads these files at server start and whenever the server configuration is reloaded. On Windows systems, they are also re-read whenever a new backend process is spawned for a new client connection.

如果在服务器启动时检测到这些文件中的错误，服务器将拒绝启动。但如果在重新加载配置期间检测到错误，则将忽略这些文件，并且旧 SSL 配置将继续使用。在 Windows 系统上，如果在后端启动时检测到这些文件中的错误，则该后端将无法建立 SSL 连接。在所有这些情况下，服务器日志中都会报告错误状况。

If an error in these files is detected at server start, the server will refuse to start. But if an error is detected during a configuration reload, the files are ignored and the old SSL configuration continues to be used. On Windows systems, if an error in these files is detected at backend start, that backend will be unable to establish an SSL connection. In all these cases, the error condition is reported in the server log.

19.9.5. Creating Certificates #

若要为服务器创建一个简单的自签名证书，有效期为 365 天，请使用以下 OpenSSL 命令，并用 dbhost.yourdomain.com 替换服务器的主机名：

To create a simple self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, replacing dbhost.yourdomain.com with the server’s host name:

openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

然后执行：

Then do:

chmod og-rwx server.key

因为如果服务器文件权限比这个更宽松，它将拒绝该文件。有关如何创建服务器私钥和证书的更多详细信息，请参阅 OpenSSL 文档。

because the server will reject the file if its permissions are more liberal than this. For more details on how to create your server private key and certificate, refer to the OpenSSL documentation.

尽管自签名证书可用于测试，由证书颁发机构 (CA) 颁发的证书（通常是企业级根 CA）应在生产中使用。

While a self-signed certificate can be used for testing, a certificate signed by a certificate authority (CA) (usually an enterprise-wide root CA) should be used in production.

要创建可由客户端验证其身份的服务器证书，首先创建证书签名请求 (CSR) 和公钥/私钥文件：

To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file:

openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key

然后，用密钥对请求进行签名以创建根证书颁发机构（使用 Linux 上的默认 OpenSSL 配置文件位置）：

Then, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux):

openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

最后，创建由新根证书颁发机构签名的服务器证书：

Finally, create a server certificate signed by the new root certificate authority:

openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key

openssl x509 -req -in server.csr -text -days 365 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt

server.crt 和 server.key 应存储在服务器上，root.crt 应存储在客户端上，以便客户端可以核实服务器的叶证书已经过其受信任的根证书签名。root.key 应离线存储，以用于创建将来的证书。

server.crt and server.key should be stored on the server, and root.crt should be stored on the client so the client can verify that the server’s leaf certificate was signed by its trusted root certificate. root.key should be stored offline for use in creating future certificates.

还可以创建包含中间证书的信任链：

It is also possible to create a chain of trust that includes intermediate certificates:

# root
openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

# intermediate
openssl req -new -nodes -text -out intermediate.csr \
  -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.key
openssl x509 -req -in intermediate.csr -text -days 1825 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out intermediate.crt

# leaf
openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 \
  -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
  -out server.crt

server.crt 和 intermediate.crt 应连接到一个证书文件捆绑包，并存储在服务器上。server.key 也应存储在服务器上。root.crt 应存储在客户端上，以便客户端可以核实服务器的叶证书已经过与其受信任的根证书相链接的证书链签名。root.key 和 intermediate.key 应离线存储，以用于创建将来的证书。

server.crt and intermediate.crt should be concatenated into a certificate file bundle and stored on the server. server.key should also be stored on the server. root.crt should be stored on the client so the client can verify that the server’s leaf certificate was signed by a chain of certificates linked to its trusted root certificate. root.key and intermediate.key should be stored offline for use in creating future certificates.