[ysql] Default ysql_hba settings for --use_client_to_server_encryption=true are too restrictive

[iSignal]

Here's how the hba file for SQL clients work for different flag settings related to TLS and auth

• TLS disabled
  • ysql_enable_auth = false -> hba setting 'host all all all trust' - no auth
  • ysql_enable_auth = true -> hba setting 'host all all all md5' - password based auth
• TLS enabled
  • ysql_enable_auth = false -> hba setting 'hostssl all all all cert' - Need client cert
  • ysql_enable_auth = true -> hba setting 'hostssl all all all md5 clientcert=1' - password and client cert both needed

When TLS is enabled the settings are too restrictive because they always require a client cert and are inconsistent with the earlier case.

[m-iancu]

Fixed by 8ef3197.
The behavior after 8ef3197 is described in detail below. (The state before that commit is referred to as the previous state).

Summary

There are three main configuration flags related to TLS:

• ysql_enable_auth -- used to enable password (md5) authentication
• use_client_to_server_encryption -- used to enable client-server TLS encryption
• ysql_hba_conf_csv -- used to manually set a HBA configuration

The current default (auto-generated) configuration depends on whether auth (ysql_enable_auth) and/or TLS (use_client_to_server_encryption) are enabled.

The four cases are shown in the table below:

	Auth disabled	Auth enabled
TLS disabled	host all all all trust (no ssl, no password)	host all all all md5 (no ssl, password required)
TLS enabled	hostssl all all all trust (require ssl, no password)	hostssl all all all md5 (require ssl and password)

Note: Previously the TLS enabled case used the more strict cert when auth was disabled and, md5 clientcert=1 (effectively md5 + cert) when auth was enabled.

Additionally, ysql_hba_conf_csv can be used to manually configure a custom HBA configuration.

For instance to use TLS with both md5 and cert authentication one can set the ysql_hba_conf_csv flag to:

hostssl all all all md5 clientcert=1

Note: Implementation-wise, the ysql_hba_conf_csv rules are just added above the auto-generated ones, so if they do not match the connection-type, database, user, host, then the auto-generated rules (i.e. from the table above) could still be used.

Therefore, If the custom user-defined rules only apply to some connection-types (e.g. host vs hostssl), databases, users, hosts, then we would still get to the auto-generated rules below for the non-matching hosts, users, or databases. To fully disable those, one can use the reject auth method.

For instance, to enable TLS with cert authentication, but only for some particular database, user and host, one can use the following ysql_hba_conf_csv setting:

hostssl mydb myuser myhost cert,hostssl all all all reject

Examples

Prerequisites

Download and configure some sample certificates:

wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/ent/test_certs/ca.crt
wget -O node.127.0.0.1.key https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/ent/test_certs/ysql.key
wget -O node.127.0.0.1.crt https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/ent/test_certs/ysql.crt
chmod 600 ca.crt node.127.0.0.1.key node.127.0.0.1.crt
CERTS=`pwd`
ENABLE_TLS="use_client_to_server_encryption=true,certs_for_client_dir=$CERTS"

Note: node.<ip>.crt/key are the default values for the ssl_cert_file and ssl_key_file server-side configuration for a YSQL node. If your local IP is not 127.0.0.1, then use the appropriate local IP for the name of the two files. Alternatively use ysql_pg_conf to set ssl_cert_file and ssl_key_file to the appropriate values.

1. Cluster with TLS enabled but no authentication

Will require the client to use client-to-server encryption to connect.

Create

$ ./bin/yb-ctl destroy && ./bin/yb-ctl create \
    --tserver_flags="$ENABLE_TLS"

Connect

SSL must be enabled in the client to connect.

$ ./bin/ysqlsh "sslmode=disable"
ysqlsh: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "yugabyte", database "yugabyte", SSL off

$ ./bin/ysqlsh "sslmode=require"
ysqlsh (11.2-YB-2.5.2.0-b0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

yugabyte=#

The default ysqlsh option is prefer (see https://www.postgresql.org/docs/11/libpq-ssl.html) which tries SSL first, but falls-back to no-ssl if the server does not support it.

So, in this case, a plain ysqlsh with no options will work and use encryption:

$ ./bin/ysqlsh
ysqlsh (11.2-YB-2.5.2.0-b0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

yugabyte=#

(Therefore, we omit the sslmode option in the examples below for brevity).

2. Cluster with TLS enabled and (password) authentication

Will require the client to use client-to-server encryption and authenticate with a password to connect.

Create

$ ./bin/yb-ctl destroy && ./bin/yb-ctl create \
     --tserver_flags="$ENABLE_TLS,ysql_enable_auth=true"

Connect

Same as case 1. above but the password will now be required (see second line below):

$ ./bin/ysqlsh
Password for user yugabyte:
ysqlsh (11.2-YB-2.5.2.0-b0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

yugabyte=#

The other cases (e.g sslmode=require/disable) behave analogously.

3. Cluster with custom configuration (TLS and authentication via cert)

Will require the client to use client-to-server encryption and authenticate with the appropriate certificate to connect.

Note: This was the previous default for TLS enabled without auth (case 1. above). So this example also shows the appropriate ysql_hba_conf_csv configuration that users can use to get the previous behavior.

Create

$ ./bin/yb-ctl destroy && ./bin/yb-ctl create \
    --tserver_flags="$ENABLE_TLS" \
    --ysql_hba_conf_csv="hostssl all all all cert"

Connect

Now a certificate will be required to connect (but no password).

$ ./bin/ysqlsh
ysqlsh: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "yugabyte", database "yugabyte", SSL off

$ ./bin/ysqlsh "sslcert=$CERTS/node.127.0.0.1.crt sslkey=$CERTS/node.127.0.0.1.key sslrootcert=$CERTS/ca.crt"
ysqlsh (11.2-YB-2.5.2.0-b0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

4. Cluster with custom configuration (TLS and auth with both md5 and cert)

Will require the client to use client-to-server encryption and authenticate both with the appropriate certificate and the password to connect.

Note: This was the previous default for TLS enabled with auth (case 2. above). So this example also shows the appropriate ysql_hba_conf_csv configuration that users can use to get the previous behavior. In particular, the ysql_enable_auth=true flag is redundant here because we are overriding the auto-generated configuration for it anyway. But we are adding it intentionally to demonstrate the ability to override the auto-generated configuration using ysql_hba_conf_csv.

Create

$ ./bin/yb-ctl destroy && ./bin/yb-ctl create \
--tserver_flags="$ENABLE_TLS,ysql_enable_auth=true" \
    --ysql_hba_conf_csv="hostssl all all all md5 clientcert=1"

Connect

Will require the certificate to be set, and also ask for a password (see second line in the second example below)

$ ./bin/ysqlsh
ysqlsh: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "yugabyte", database "yugabyte", SSL off

$ ./bin/ysqlsh "sslcert=$CERTS/node.127.0.0.1.crt sslkey=$CERTS/node.127.0.0.1.key sslrootcert=$CERTS/ca.crt"
Password for user yugabyte:
ysqlsh (11.2-YB-2.5.2.0-b0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

yugabyte=#