컴파일된 SSL 지원을 사용함으로써 postgresql.conf에서 파라미터 ssl을 on으로 설정하면 SSL를 활성화한 상태로 PostgreSQL 서버를 시작할 수 있다. 서버는 동일한 TCP 포트에서 일반 및 SSL 연결을 listen하고 SSL 연결 여부에 대해 클라이언트 연결을 성사시킨다. 기본적으로 이것은 클라이언트의 옵션이다. 일부 또는 모든 연결에 대해 SSL의 사용을 요구하도록 서버를 설정하는 방법은 20.1절을 참조 바란다.

SSL 모드에서 시작하려면 서버 인증서가 포함된 파일과 개인 키가 존재해야 한다. 기본적으로, 이러한 파일은 각각 서버의 데이터 디렉터리에서 이름이 server.crt 및 server.key일 것으로 예상되지만, 환경 설정 파라미터ssl_cert_file 및 ssl_key_file을 사용하여 다른 이름과 위치를 지정할 수 있다. Unix 시스템에서 server.key에 대한 권한은 월드 또는 그룹에 대한 액세스를 불허해야 한다. 이것은 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.

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.

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.

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.

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

경우에 따라 서버 인증서를 클라이언트가 직접 신뢰하지 않고 “중간” 인증 기관에서 서명할 수 있다. 해당 인증서를 사용하려면 server.crt 파일에 서명 기관의 인증서를 첨부한 다음, 해당 상급 기관의 인증서를 첨부하는 순으로 클라이언트에 의해 신뢰된 인증 기관, “root” 또는 “중간”까지 첨부한다(예를 들면 클라이언트의 root.crt 파일에서 인증서로 서명된).

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 allows easier expiration of intermediate certificates.

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.

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 33.18절 for a description of how to set up certificates on the client.)

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 20.12절).

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. (This sets the certificate's basic constraint of CA to true.) This allows easier expiration of intermediate certificates. checked if the parameter ssl_crl_file is set.

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 or is set to no-verify, the server will still verify any presented client certificates against its CA file, if one is configured — but it will not insist that a client certificate be presented.

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

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

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.

표 18.2는 서버에서 SSL 설정과 관련된 파일들을 요약한 것이다. (표시된 파일 이름은 기본값 또는 일반적인 이름이다. 로컬로 환경 설정된 이름은 다를 수 있다.)

server.key, server.crt, root.crt 및 root.crl 파일(또는 환경 설정된 다른 이름)은 서버 시작 중에만 검사되므로 변경 내용을 적용하려면 서버를 재시작해야 한다.

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

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.

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.

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

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