User passwords or certs in client connections

General
#1

Another basic question.

The SQL documentation shows how to create databases and grant privileges to users, and the app “Build A Test App” documentation shows how to link in and use the system.

  1. How do you create a user for the system? Is it as simple as creating a certificate for a username and using that?

  2. The demo app assumes the use of an insecure cluster. Of course, no-one is actually going to run an insecure cluster, right? How do you use the client connections (for example, PHP) to connect to a secure cluster, since there does not appear to be a way to assign a password to a user?

#2

Currently, authentication is based on certificates instead of passwords. You “create” a user by mentioning it in a grant statement, and to log in as that user you generate a certificate for it and configure your application to use that certificate.

The exact details of how to do this configuration depends on what language you’re using. I couldn’t find documentation for PHP in a quick search, but in many languages you use a url like postgresql://user@host:port/db?sslmode=disable for insecure mode and postgresql://user@host:port/db?sslmode=verify-full&sslrootcert=path/to/ca.crt&sslcert=path/to/user.cert&sslkey=path/to/user.key for secure mode.

This is awkward, which is why the docs currently focus on insecure mode. We want to build out better tools for managing certificates and constructing these big ugly URLs, and we’re also planning to support passwords in the near future (but you can’t get away from some of the certificate stuff: a properly secure cluster will at least need the CA cert even if you’re using passwords).

2 Likes
#3

Thanks - exactly what I was looking for.

#4

@kae, just a quick update here. We’ve since added support for password-based authentication (docs here). And it’s on our near-term plan to add secure examples to the build an app tutorials.

1 Like
#5

I wanted to bring this issue I’ve filed to your attention as well. It is a working solution to this issue for Node.js+Sequelize users with production TLS protected clusters.

https://github.com/cockroachdb/cockroach/issues/16837