Use letsencrypt certificate for client access (non-ui)

General
#1

Hello roachers,

we want to allow a public, but secured access to crdb. The problem is, tools like Power BI needs a supported certificate like from letsencrypt instead of kubernetes ca. We use TLS only for encryption and clients authenticates with username/password.

The idea is:

/--------\        /-------\         /------\
| client | <----> | Proxy |  <----> | crdb |
\--------/        \-------/         \------/

Client: trust LE CA, use username/password for authentication.
Proxy: offers LE Cert for client, trust kube CA.
CRDB: isn’t aware of the setup and use kube signed certs.

I read that such setup shouldn’t be possible, because of handshake on application level:

To initiate an SSL-encrypted connection, the frontend initially sends an SSLRequest message rather than a StartupMessage. The server then responds with a single byte containing S or N, indicating that it is willing or unwilling to perform SSL, respectively. The frontend might close the connection at this point if it is dissatisfied with the response. To continue after S, perform an SSL startup handshake (not described here, part of the SSL specification) with the server. If this is successful, continue with sending the usual StartupMessage. In this case the StartupMessage and all subsequent data will be SSL-encrypted.

[…]

While the protocol itself does not provide a way for the server to force SSL encryption […]

On the other hand I can ask crdb for certificate with:

openssl s_client -showcerts -connect domain:port

and it seems to me that I can skip handshake and terminate TLS on proxy. But ATM no success with stunnel or nginx.

Is such setup possible? Is there any way to solve it or any hint?

#2

Hi Anton,

you need a SQL proxy aware of PostgreSQL’s protocol to achieve something like this. The TLS handshake in the pg protocol happens “after” the postgres client/server startup message.

The fact that you can “ask crdb for certificate with openssl …” is a red herring: when you do this, you are talking to the CockroachDB RPC port, not its SQL server. That doesn’t inform you on how to do proxying for a secure SQL connection. (You can clarify this red herring by using separate TCP ports for the SQL and RPC servers. You will see that your openssl command succeeds on the RPC port but fails on the SQL port.)

An example SQL proxy that would allow you to use different TLS configurations for the “client” side and the “server” side is PgBouncer. See for example here: PgBouncer config

Does this help?

#3

This will get simpler when Allow separate TLS certificates for SQL-only port · Issue #39326 · cockroachdb/cockroach · GitHub is implemented. Then you’ll be able to give the server a letsencrypt cert directly for the sql port instead of going through a separate proxy.

1 Like
#4

Hello Raphael, hello Ben,

thank you very much for your hints! I’m struggle with pgbouncer config (it still delivers node’s certificate), but if it’s possible, I will get it to work :+1:

BTW, I’ve discovered starttls postrgres option:

openssl s_client -connect domain:port -starttls postgres
1 Like
#5

Hello @knz @bdarnell ,

again, thank you very much - it works with PgBouncer. The one thing need for PowerBI was:

[pgbouncer]
ignore_startup_parameters = extra_float_digits,ssl_renegotiation_limit