sslcert url parameter should not be required when using PKCS-12 encoded key

[timveil]

Describe the problem

Based on my reading of postgres jdbc documentation, a user should be able to specify a PKCS-12 formatted key in the sslkey url parameter. When providing a key in this format, they are not required to provide the sslcert parameter as the PKCS-12 format includes both the key and the cert in a single file. Or at least that is how i interpreted this line in their documentation... Note: This parameter is ignored when using PKCS-12 keys, since in that case the certificate is also retrieved from the same keyfile.. If i attempt to send a url to cockroach sql that includes the sslkey parameter but not the sslcert parameter I am presented with this error...

ERROR: invalid argument "postgresql://localhost:26257/spring_examples?ApplicationName=datasource&reWriteBatchedInserts=true&sslmode=verify-full&sslrootcert=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/ca.crt&sslpassword=password&sslkey=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/client.pfx" for "--url" flag: URL validation error: sslcert
Client certificate missing.
Failed running "sql"

To Reproduce

I use this repo to create a secure 3 node cluster (https://github.com/timveil-cockroach/spring-examples/tree/master/docker/lb-haproxy-secure)

I use the client key and cert generated from the cockroach cert command and stored in the shared volume to create a PK12 file (client.pfx).

I then pass what should be a valid url to local instance of the cockroach sql client and get the above error. Here is the exact command i pass.

cockroach sql --url='postgresql://localhost:26257/spring_examples?ApplicationName=datasource&reWriteBatchedInserts=true&sslmode=verify-full&sslrootcert=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/ca.crt&sslpassword=password&sslkey=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/client.pfx'

Expected behavior
I expect to get farther than the url validation error above.

Additional data / screenshots

Additional context

Jira issue: CRDB-12498

[timveil]

More broadly i'm curios if CockroachDB supports cert based authentication using PK12 files. Even if I pass a dummy sslcert parameter in the URL to get passed this validation error (sslcert=foo), I'm still not properly authenticated. The server requests a password for the user root which I don't think should be required if i'm using certs only. Below you can see the command and the error...

➜  spring-examples git:(master) ✗ cockroach sql --url='postgresql://localhost:26257/spring_examples?ApplicationName=datasource&reWriteBatchedInserts=true&sslmode=verify-full&sslrootcert=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/ca.crt&sslpassword=password&sslkey=/Users/timveil/Documents/GitHub/spring-examples/docker/lb-haproxy-secure/client.pfx&sslcert=foo'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
Connecting to server "localhost:26257" as user "root".
Enter password: 

[knz]

Can you also explain how you created the PKCS12 file? Thanks

[florence-crl]

@knz
In my test I used openssl to generate the key file in pkcs12 format from pkcs1 PEM formatted key and crt files generated using cockroach cert create-client:

./cockroach sql --url='postgresql://localhost:26257?sslcert=/Users/florencemorris/local/1/certs/client.root.crt&sslkey=/Users/florencemorris/local/1/certs/client.root.key&sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt' -e "create database roach_data;"

./cockroach sql --url='postgresql://localhost:26257?sslcert=/Users/florencemorris/local/1/certs/client.root.crt&sslkey=/Users/florencemorris/local/1/certs/client.root.key&sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt' -e "create user florence with password 'cockroach';"

./cockroach sql --url='postgresql://localhost:26257?sslcert=/Users/florencemorris/local/1/certs/client.root.crt&sslkey=/Users/florencemorris/local/1/certs/client.root.key&sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt' -e "grant all on database roach_data to florence;"

./cockroach cert create-client florence --certs-dir=certs --ca-key=certs/ca.key

openssl pkcs12 -export -out certs/client.florence.pfx -inkey certs/client.florence.key -in certs/client.florence.crt
enter: testpw 

chmod 600 certs/client.florence.pfx

./cockroach sql --user=florence --url='postgresql://localhost:26257/roach_data?sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt&sslkey=/Users/florencemorris/local/1/certs/client.florence.key&sslcert=/Users/florencemorris/local/1/certs/client.florence.crt'

./cockroach sql --user=florence --url='postgresql://localhost:26257/roach_data?sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt&sslpassword=testpw&sslkey=/Users/florencemorris/local/1/certs/client.florence.pfx'
ERROR: invalid argument "postgresql://localhost:26257/roach_data?sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt&sslpassword=testpw&sslkey=/Users/florencemorris/local/1/certs/client.florence.pfx" for "--url" flag: URL validation error: sslcert
Client certificate missing.
Failed running "sql"

./cockroach sql --user=florence --url='postgresql://florence@localhost:26257/roach_data?sslmode=verify-full&sslrootcert=/Users/florencemorris/local/1/certs/ca.crt&sslpassword=testpw&sslkey=/Users/florencemorris/local/1/certs/client.florence.pfx&sslcert=/Users/florencemorris/local/1/certs%2Fclient.florence.crt'
ERROR: tls: failed to find any PEM data in key input
Failed running "sql"

[knz]

@bdarnell @andy-kimball a heads up, Florence and Tim have found a way to package the client cert and key into a single file; if we start shipping our own client-side SDK / config utility, perhaps we can use that to further simplify deployments.

[bdarnell]

More broadly i'm curios if CockroachDB supports cert based authentication using PK12 files.

I don't think so. It looks like Go only has support for keys/certs in PEM format (without third-party additions). I haven't personally seen PKCS12 used in non-java environments before.

Separately, it is possible to combine keys and certs into a single file in PEM format too (just concatenate them, making sure there's a newline between them). I know I've seen this used in python (the keyfile argument is optional there), although I'm not sure if it works in go (looks like both files must exist)

Based on my reading of postgres jdbc documentation

cockroach sql --url='postgresql://localhost:26257/spring_examples?ApplicationName=datasource&reWriteBatchedInserts=true

The jdbc documentation is irrelevant to anything but jdbc. The ApplicationName and reWriteBatchedInserts arguments are also not supported anywhere else. (many drivers including cockroach sql support application_name with an underscore). We could build a jdbc-compatiblity mode for cockroach sql, but unless and until we do you'll need to use two different connection strings (and keypairs) for java and cockroach sql (java shops may want to consider using a jdbc-based sql shell instead of cockroach sql)

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!