Latest Encryption in Transit Developments for YSQL

YugabyteDB stores important user and customer data for an organization on their systems. This means that encryption in transit is a common requirement for client-to-server communication.

In a previous blog post, we learned how to secure the communication between SQL clients and the PostgresQL query interface of YugabyteDB (also called client-to-server encryption in transit). In this post, we will explore some new encryption in transit knobs available in Yugabyte 2.25 and compare them to the equivalent knobs from previous releases.

Understanding Client-to-Server Encryption Architecture

YugabyteDB has three primary client-to-server communication protocols.

• The internal RPC protocol between masters and tserver processes
• PostgreSQL-compatible YSQL clients talking to the default port 5433 on the tserver hosts
• CQL-compatible YCQL clients talking to the default port 9042 on the tserver hosts

Here we will specifically focus on the second area – YSQL client-to-server communication.

Organizations generally want to achieve one or more of the following goals with encryption in transit.

• Goal 1: Make it cryptographically impossible to snoop or modify network communications
• Goal 2: Enable the client to identify and verify the server
• Goal 3: Enable the server to identify and verify the client

The first goal is typically a common minimum requirement, while the second and third depend on the organization’s specific use case. Goals 2 and 3 combined are typically referred to as mutual TLS or mTLS.

Client-to-Server Encryption in Transit for SQL Clients

To enable TLS communication for SQL client communication, the following command line flags need to be specified to the yb-tserver processes.

--use_client_to_server_encryption=true
--certs_for_client_dir=<path to a directory>, e.g.,
--certs_for_client_dir=/home/yugabyte/yugabyte-tls-config/

The directory pointed to by this flag contains three files in PEM format.

• crt – The public certificate of the Certificate Authority (CA) that this YugabyteDB server should trust. This is the CA that will verify other certificates. YugabyteDB does not trust any system CA certificates that might already be present on the host. A single file includes a chain of root and intermediate CA certificates.
• <host-ip-address>.crt – public server certificate for this host (For SQL client to server authentication specifically, intermediate CA certs can also be placed in this file instead of ca.crt in the order specified by openssl).
• <host-ip-address>.key – private key for the server certificate for this host

Using yugabyted to manage your YugabyteDB cluster makes this process much simpler.

./bin/yugabyted start --secure --advertise_address=127.0.0.1

Once these settings are specified, all SQL and CQL client connections to this yb-tserver will use TLS. This achieves Goal 1 above (i.e. resilience to snooping).

Verification of Server Certificates

To summarize the verification of server certificates covered in the previous blog post, let’s use the ysqlsh client as an example. The command line below asks the client to verify the server certificate, with the sslmode parameter controlling the level of verification.

./bin/ysqlsh -h <server ip/hostname> "sslrootcert=<path to ca.crt> sslmode=verify-ca"
./bin/ysqlsh -h <server ip/hostname> "sslrootcert=<path to ca.crt> sslmode=verify-full"

Verification of Client Certificates in YugabyteDB (<= 2024.2, 2.23)

The YSQL server can use a client certificate to verify or authenticate the client, apart from the familiar password setting. The server can:

• Verify that the client certificate is signed by the CA (Certificate Authority) known to the server.
• Authenticate the client using the Common Name (CN) of the certificate.

Generating Client Certificates

The following openssl commands can be used to generate a client certificate.

The path to ca.crt and ca.key here refer to the default paths of the CA certificates as generated by yugabyted. You may need to change them to suit your situation.

The two files client_yugabyte.key and client_yugabyte.crt generated by these commands are the private key and the client certificate respectively. The parameter CN=yugabyte should be changed to reflect the YSQL username that this certificate will be used with.

$ openssl req -nodes -newkey rsa:2048 -keyout client_yugabyte.key -out client_yugabyte.csr -subj "/CN=yugabyte"  -addext "extendedKeyUsage = clientAuth"
$ openssl x509 -req -in client_yugabyte.csr -CA ~/var/generated_certs/root-ca/ca.crt -CAkey ~/var/generated_certs/root-ca/ca.key     -CAcreateserial -out client_yugabyte.crt -days 180

Client Certificate Verification

To achieve (C1), the option “clientcert=1” can be added to any existing authentication method in the ysql_hba_conf_csv list like “md5” or “scram-sha-256”.

When a command line like below is added to the basic encryption in transit gflags, the server requires a client certificate and verifies that it is signed by the CA.

--ysql_hba_conf_csv="hostssl all all all trust clientcert=1"

The ysqlsh client will then need to be started as below:

./bin/ysqlsh -h <server ip/host> "sslcert=<path to client.crt> sslkey=<path to client key>" -U <username>

The server will then verify that client.crt is a valid certificate signed by the CA specified in its ca.crt.

NOTE: In the previous blog post, it was incorrectly stated that this setting also causes the server to authenticate the client using the Common Name of the certificate.

Client Certificate Authentication

To use the client certificate for authentication in addition to verification (C2), the hba conf can be set to:

--ysql_hba_conf_csv="hostssl all all all cert"

This causes the server to verify that the client certificate is signed by its CA and that the Common Name of the certificate matches the username.

Combining Password and Client Certificate Authentication

The option clientcert=1 can be added to any authentication method in the hba conf gflag to request client certificate verification in addition to that authentication method. For example, the option below requests scram authentication combined with client certificate verification.

--ysql_hba_conf_csv="hostssl all all all scram-sha-256 clientcert=1"

However, this option cannot be used to authenticate the client certificate (verification of Common Name) in these versions. In YugabyteDB 2.25 these can be combined as shown below.

What Has Changed in YugabyteDB 2.25?

Verification of Server Certificates

The knobs related to the client verification of server certificates are unchanged in Yugabyte 2.25

Verification of Client Certificates in YugabyteDB 2.25

YugabyteDB 2.25 includes a major upgrade to the underlying PostgreSQL code and is now based on PostgreSQL 15 (instead of PostgreSQL 11). This opens up a new option for client certificate authentication.

Client Certificate Verification

The original clientcert=1 parameter is no longer valid in the latest YugabyteDB release. To request client certificate verification, we now use clientcert=verify-ca, in combination with any of the other authentication methods.

--ysql_hba_conf_csv="hostssl all all all scram-sha-256 clientcert=verify-ca"

On an existing yugabyted cluster, run the below command to set this flag.

$ ~/yugabyte-2.25.0.0/bin/yugabyted stop && ~/yugabyte-2.25.0.0/bin/yugabyted start --tserver_flags='ysql_hba_conf_csv={hostssl all all all trust clientcert=verify-ca}' --listen 127.0.0.1

To connect to this cluster, run:

$ ~/yugabyte-2.25.0.0/bin/ysqlsh -h 127.0.0.1 -U yugabyte "sslkey=client_yugabyte.key sslcert=client_yugabyte.crt sslrootcert=$HOME/var/certs/ca.crt sslmode=verify-full"

Connecting as any existing role (e.g.’foo’), replacing -U yugabyte with -U foo also succeeds with the same parameters, because verify-ca only verifies that the certificate is signed by the CA.

Client Certificate Authentication

Client certificate authentication through the “cert” authentication method is unchanged.

On an existing yugabyted cluster, run the below command to enable cert authentication.

$ ~/yugabyte-2.25.0.0/bin/yugabyted stop && ~/yugabyte-2.25.0.0/bin/yugabyted start --tserver_flags='ysql_hba_conf_csv={hostssl all all all cert}' --listen 127.0.0.1

To connect to this with the ysqlsh client, run:

$ ~/yugabyte-2.25.0.0/bin/ysqlsh -h 127.0.0.1 -U yugabyte "sslkey=client_yugabyte.key sslcert=client_yugabyte.crt sslrootcert=$HOME/var/certs/ca.crt sslmode=verify-full"

If connecting with a different pre-created role, this certificate fails. A separate client certificate with CN set to that role name would be required to log in.

$ ~/yugabyte-2.25.0.0/bin/ysqlsh -h 127.0.0.1 -U foo "sslkey=$HOME/var/certs/client/client_yugabyte.key sslcert=$HOME/var/certs/client/client_yugabyte.crt sslrootcert=$HOME/var/certs/ca.crt sslmode=verify-full"
ysqlsh: error: connection to server at "127.0.0.1", port 5433 failed: FATAL:  certificate authentication failed for user "foo"

Combining Password and Client Certificate Authentication

A new option clientcert=verify-full can be used in combination with any existing authentication methods.

This causes the server to verify that the client certificate is signed by the CA and to check the Common Name against the username of the connection.

--ysql_hba_conf_csv="hostssl all all all scram-sha-256 clientcert=verify-full"

To connect to this cluster using ysqlsh, replace PGPASSWORD with the appropriate password below.

$ PGPASSWORD='*****' ~/yugabyte-2.25.0.0/bin/ysqlsh -h 127.0.0.1 -U yugabyte "sslkey=client_yugabyte.key sslcert=client_yugabyte.crt sslrootcert=$HOME/var/certs/ca.crt sslmode=verify-full"

Conclusion

The knobs available to control SQL encryption in transit behavior in YugabyteDB should already be familiar to users working with the equivalent knobs in PostgreSQL.

By re-using the PostgreSQL source code as much as possible, YugabyteDB maintains the rich variety of PostgreSQL encryption in transit options and makes them available to application developers and database administrators. This makes YugabyteDB an ideal choice for modern applications that demand both the familiarity of PostgreSQL and the resilience, high availability, and scalability of a cloud-native database.

Check out our docs page on this topic for more information and usage examples.

Want to know more? Yugabyte Anywhere, a private DBaaS version of YugabyteDB for enterprises, helps companies manage their encryption in transit requirements. It does this by supporting custom CAs and CA-signed certificates. It also enables encryption in transit and rotating server and CA certificates seamlessly on managed database clusters.