sqlproxyccl: the reference script run_proxy.sh is outdated

[knz]

I was trying to test things out with #71248 and I ran into the following issues:

• mt start-sql now needs a separate --store flag from the server (since it uses disk storage now)
• proxy does not support --target-addr any more
• --logtostderr is not the correct log flag any more
• the proxy attempts to start a listener on port 8080, and this fails because there's already a server on that port, and I could not find a way to configure it

It would also be useful if the script was updated to use separate certs directories to reflect realistic deployment usage.

[jaylim-crl]

mt start-sql now needs a separate --store flag from the server (since it uses disk storage now)

Is it necessary to have a separate --store flag? When we start the single KV node, we don't pass in any custom store as well, so we'll default to disk storage.

Ack on the rest.

cc: @darinpp re: proxy does not support --target-addr any more

[knz]

Is it necessary to have a separate --store flag?

Yes because both processes are started from the same current directory, so they both try to share the same cockroach-data sub-directory which is invalid.

So we need either separate --store or separate current directory.

[jaylim-crl]

Good point. Also, see commit message here, which might be helpful: 6d024ae

[knz]

Thanks for this link, but i don't know how to use the test directory with the tenant 123 that's defined in the run_proxy script.

[knz]

Ok I think the directory auto-creates tenants.
However, I am seeing the following error in the SQL proxy logs:

I211011 03:49:32.017739 122 ccl/sqlproxyccl/tenant/directory.go:263  [client=127.0.0.1:31797,cluster=dim-dog,tenant=123] 104  error initializing tenant {123}: rpc error: code = Unavailable desc = connection closed

[jaylim-crl]

OK - here're some steps without the tenant directory; I'm not too familiar with the test directory. This only works with one tenant. I'll look into a script that reflects a realistic deployment usage sometime this week:

COCKROACH=${1:-'./cockroach-darwin'}

# Create certificates.
export CERTSDIR=<update>
export COCKROACH_CA_KEY=$CERTSDIR/ca.key
$COCKROACH cert create-ca --certs-dir=$CERTSDIR
$COCKROACH cert create-client root --certs-dir=$CERTSDIR
$COCKROACH cert create-node 127.0.0.1 localhost --certs-dir=$CERTSDIR
$COCKROACH mt cert create-tenant-client 123 --certs-dir=$CERTSDIR

#  Start KV layer. (:26257)
$COCKROACH start-single-node \
    --host 127.0.0.1 \
    --log="{sinks: {stderr: {filter: info}}}" \
    --certs-dir=$CERTSDIR

# Create tenant
$COCKROACH sql \
    --host 127.0.0.1 \
    -e 'select crdb_internal.create_tenant(123);' \
    --certs-dir=$CERTSDIR

# Spawn tenant SQL server (:46257) pointing at KV layer (:26257)
COCKROACH_TRUST_CLIENT_PROVIDED_SQL_REMOTE_ADDR=true $COCKROACH mt start-sql \
    --tenant-id 123 \
    --kv-addrs 127.0.0.1:26257 \
    --sql-addr 127.0.0.1:46257 \
    --log="{sinks: {stderr: {filter: info}}}" \
    --store=type=mem,size=640MiB \
    --certs-dir=$CERTSDIR

# Create user on tenant (proxy does not forward client certs)
$COCKROACH sql \
    --port 46257 \
    -e "create user bob with password 'builder';" \
    --certs-dir=$CERTSDIR

# Start the proxy, forwarding all requests to the SQL tenant.
$COCKROACH mt start-proxy \
    --routing-rule="127.0.0.1:46257" \
    --listen-metrics=:8081 \
    --log="{sinks: {stderr: {filter: info}}}" \
    --listen-addr 127.0.0.1:56257 \
    --listen-cert $CERTSDIR/node.crt \
    --listen-key $CERTSDIR/node.key \
    --skip-verify

# Connect to the tenant.
$COCKROACH sql \
    --url "postgresql://bob:builder@127.0.0.1:56257/tenant-cluster-2.defaultdb/?sslmode=require" \
    --certs-dir=$CERTSDIR

[knz]

we need to split the certs directory into 3, to

1. highlight that node.crt is only available on KV nodes
2. ensure that mt start-sql can start without node certs
3. ensure that sql proxy can start with separate server cert than node.crt

[knz]

this will need an acceptance test, presumably via a TCL test

[jaylim-crl]

It looks like the test directory won't work with certs:

cockroach/pkg/ccl/sqlproxyccl/tenantdirsvr/test_directory_svr.go

Lines 399 to 406 in fffd927

	process := &Process{SQL: sql.Addr(), FakeLoad: 0.01}
	args := []string{
	"mt", "start-sql", "--kv-addrs=127.0.0.1:26257", "--idle-exit-after=30s",
	fmt.Sprintf("--sql-addr=%s", sql.Addr().String()),
	fmt.Sprintf("--http-addr=%s", http.Addr().String()),
	fmt.Sprintf("--tenant-id=%d", tenantID),
	"--insecure",
	}

That will need to be updated as well if we wanted something for a realistic deployment.