roachtest: make secure/non-root the default for new tests

[solongordon]

Most of our roachtests use the root user on insecure clusters, which is not realistic. Now that we have one roachtest which does the proper thing, let's look at making that the default behavior for future tests. This should also make it easier to switch over existing tests.

Jira issue: CRDB-6444

[rafiss]

Might relate to this roachprod issue: #38539

[blathers-crl]

cc @cockroachdb/test-eng

[srosenberg]

Current State

At this time only a small subset of roachtests is executed in secure mode,

find pkg/cmd/roachtest -name "*.go" |xargs grep "install.SecureOption"|grep true|sort -u -k1,1 -t':'
pkg/cmd/roachtest/roachtestutil/mixedversion/mixedversion.go:		install.SecureOption(true),
pkg/cmd/roachtest/tests/admission_control_multitenant_fairness.go:		install.MakeClusterSettings(install.SecureOption(true)),
pkg/cmd/roachtest/tests/backup_restore_roundtrip.go:	c.Start(ctx, t.L(), maybeUseMemoryBudget(t, 50), install.MakeClusterSettings(install.SecureOption(true), envOption), roachNodes)
pkg/cmd/roachtest/tests/cluster_to_cluster.go:	srcClusterSetting := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/connection_latency.go:	settings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/kv.go:		settings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/mixed_version_backup.go:		install.BinaryOption(cockroachPath), install.SecureOption(true),
pkg/cmd/roachtest/tests/multitenant.go:	c.Start(ctx, t.L(), option.DefaultStartOpts(), install.MakeClusterSettings(install.SecureOption(true)), c.All())
pkg/cmd/roachtest/tests/multitenant_distsql.go:	settings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/multitenant_shared_process.go:			clusterSettings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/multitenant_upgrade.go:	settings := install.MakeClusterSettings(install.BinaryOption(predecessorBinary), install.SecureOption(true))
pkg/cmd/roachtest/tests/network.go:	settings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/network_logging.go:		c.Start(ctx, t.L(), startOpts, install.MakeClusterSettings(install.SecureOption(true)), crdbNodes)
pkg/cmd/roachtest/tests/nodejs_postgres.go:		settings := install.MakeClusterSettings(install.SecureOption(true))
pkg/cmd/roachtest/tests/npgsql.go:		c.Start(ctx, t.L(), option.DefaultStartOptsInMemory(), install.MakeClusterSettings(install.SecureOption(true)), c.All())
pkg/cmd/roachtest/tests/pgjdbc.go:		c.Start(ctx, t.L(), option.DefaultStartOptsInMemory(), install.MakeClusterSettings(install.SecureOption(true)), c.All())
pkg/cmd/roachtest/tests/smoketest_secure.go:				settings := install.MakeClusterSettings(install.SecureOption(true))

By default, roachprod creates an admin user roach (see createAdminUserForSecureCluster) if the cluster is using secure mode. The password of the user is logged but not persisted. Thus, tests which require password-based authentication via programatic access are effectively broken (in secure mode); e.g., see [1].

The new mixed-version API defaults to secure mode [2]. The existing corpus of mixed-version tests doesn't require programmatic access to the cluster adminURL with the exception of [3]. Thus, it's effectively the same issue as above. In order to maintain secure mode as the default, we need to enable an API for password-based authentication; see more below.

The other issue is the generated certs are not signed by a recognized root CA. Thus, by default http clients will fail to verify the node cert. This is essentially another blocker for a number of existing roachtests. The immediate workaround is to skip this verification step; e.g., the following can be added as an option to pkg/util/httputil/client.go,

TLSClientConfig:   &tls.Config{InsecureSkipVerify: insecure},

NOTE: postgres:// urls will need to either have sslmode=allow to skip verification or use the specified root cert via sslrootcert=%s/ca.crt&sslmode=require (see examples below).

Authentication API

Host-based

Default host-based rules (HBA) permit root (admin) access via loopback as well as remote host access via client certs [4]. Thus, we can run sql as root programmatically via roachprod API (SyncedCluster.ExecSQL) or CLI,

roachprod sql $CLUSTER:$NODE --secure --  -e "..."

Cert-based

Similarly, we can enable (client) cert-based authentication [5],

./cockroach cert create-client testuser --certs-dir %s --ca-key=%s/ca.key

The testuser can now be authenticated via the client cert by using the postgres URL of the form,

postgres://%s@%s:%s?sslcert=%s/client.%s.crt&sslkey=%s/client.%s.key&sslrootcert=%s/ca.crt&sslmode=require

Password-based

We can also execute SQL via password-based authentication, e.g.,

./cockroach sql --url 'postgres://user:pass@$HOST:26257?sslmode=allow'

HTTP API

Recall [1] requires programmatic authentication to adminURL via HTTP. It appears we may have to build such an API. FindAndDecodeSessionCookie does the heavy lifting of decoding the session cookie, but I couldn't find existing code to do the full authentication end-to-end. E.g., the following curl statements authenticate and extract the cookie session,

curl -v -L --insecure https://roach:system@$HOST:26258/login -X POST -d '{"username": "roach", "password": "system"}'
Set-Cookie: session=CIGA6rSrxsnmDBIQhVcPIVKsiLWZq6yqEXZMtQ==

curl -v -H "Cookie: session=CIGA6rSrxsnmDBIQhVcPIVKsiLWZq6yqEXZMtQ==" --insecure "https://$HOST:26258/ts/query" -X POST

Programmatically (via roachprod API), we can also extract the most recent session id, but it's best not to rely on these implementation details; i.e., create and use an HTTP api instead.

roachprod sql $CLUSTER:2 --secure --  -e "select id from system.web_sessions where username='roach' order by \"createdAt\" DESC LIMIT 1"

[1]

cockroach/pkg/cmd/roachtest/tests/follower_reads.go

Line 652 in 51ead2a

url := "http://" + adminURLs[0] + "/ts/query"

[2]

cockroach/pkg/cmd/roachtest/roachtestutil/mixedversion/mixedversion.go

Lines 139 to 141 in 51ead2a

	defaultClusterSettings = []install.ClusterSettingOption{
	install.SecureOption(true),
	}

[3] #115317
[4]

cockroach/pkg/sql/pgwire/hba_conf.go

Lines 296 to 301 in 51ead2a

	var DefaultHBAConfig = func() *hba.Conf {
	loadDefaultMethods()
	conf, err := ParseAndNormalize(`
	loopback all all all trust # built-in CockroachDB default
	host all all all cert-password # built-in CockroachDB default
	local all all password # built-in CockroachDB default

[5] https://www.cockroachlabs.com/docs/v22.2/cockroach-cert#create-the-certificate-and-key-pair-for-a-client

[renatolabs]

The password of the user is logged but not persisted

Not sure what is meant here, but the passwords should be persisted? We use them to log in to the DB console after the cluster is setup.

[srosenberg]

Not sure what is meant here, but the passwords should be persisted? We use them to log in to the DB console after the cluster is setup.

I just meant that the user/password is not currently persisted with the rest of the (roachprod) cluster metadata, which makes it hard to reuse programmatically, e.g., invoking a console endpoint via http client.

[blathers-crl]

cc @cockroachdb/test-eng

[srosenberg]

Linking another authentication example from Renato [1] which retrieves the session id via (cockroach) CLI,

./cockroach auth-session login root --certs-dir ./certs --format raw

[1] https://github.com/cockroachdb/cockroach/blob/release-23.1/pkg/cmd/roachtest/tests/mixed_version_tenant_span_stats.go#L140