CockroachDB allows fine-grained configuration of which database connect attempts it allows to proceed to the authentication stage, and which authentication methods it will accept, based on:
- Who is making the attempt (SQL user).
- Where on the internet (IP Address) the attempt is coming from.
This document describes the rationale for restricting database access to specific IP ranges as a security measure and then demonstrates the procedure using authentication configuration to achieve that aim.
Why customize your authentication configuration?
CockroachDB Cloud includes industry-standard security controls at the network and infrastructure levels. A hardened authentication configuration offers a powerful measure of security in depth.
Limiting allowed database connections to secure IP addresses reduces the risk that your cluster is compromised, because a potential attacker who acquires database credentials (e.g., username/password combinations or client TLS certificates) cannot use those credentials without also gaining infrastructure access. Infrastructure access can and should be protected with multifactor authentication and restricted to appropriate parties using infrastructure-level IAM.
Step 1: Provision and access your cluster
[Create a CockroachDB Standard cluster](/docs/cockroachcloud/create-your-cluster.html.
From the CockroachDB CockroachDB Cloud Console, select the cluster and click the Connect button to obtain your connection credentials from the Connection Info pane in the CockroachDB Cloud Console.
Download the cluster's root CA certificate, so that your client can authenticate to CockroachDB Cloud.
Start the SQL shell using the connection string.
cockroach sql --url "postgresql://{USER}:{PASSWORD}@aws-us-east-1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3Drandom-cluster-name-123&sslrootcert={PATH/TO/root.crt}"Replace:
{USER}: The SQL username.{PASSWORD}: The SQL user password.{PATH/TO/root.crt}: The path to the root CA certificate.
Step 2: Provision a secure jumpbox
By default, anyone with the connection string can access your database. Let's fix that by creating a jumpbox: a compute instance that will be used as secure, dedicated access point to our cluster. In this example, the jumpbox will be a Google Cloud compute instance (although you could as easily use an AWS EC2 instance) which allows us to protect access to the jumpbox with Google Cloud's native capacities to require two-factor authentication for SSH access to compute instances, and to limit that SSH access to precisely those users that require it. By limiting SQL access to those actors who have SSH access to the jumpbox, we can effectively enforce two-factor authentication for access to the database, as well as take advantage of other security measures available on Google Cloud compute instances, such as access logs.
In the Google Cloud Console Compute Instances page, create a new instance called roach-jump-box. The jumpbox will need very little CPU or disk, so use a cheap instance such as an e2-micro.
In the Google Cloud Console VPC Network / External IP addresses page, reserve a static IP. Name it roach-jump-box, and attach it to the compute instance.
Keep the IP address handy!
Step 3: Tighten the authentication configuration
Next, we'll configure our cluster to only allow SQL connection attempts from our jumpbox. This means that in order to access the cluster, someone will need not only the username and password (which could be guessed or stolen), but will also need access to the jumpbox. Manage permissions to access the jumpbox using Google Cloud's IAM, and make sure that users in your Google Cloud organization are required to use two-factor authentication.
Returning to the SQL console, let's set our authentication configuration to limit access to the jumpbox. This configuration is accessed as a cluster setting.
Run SHOW CLUSTER SETTING server.host_based_authentication.configuration; to view your current authentication configuration, which should be in its default state, which displays as empty:
cockroachlabs.cloud:26257/defaultdb> show cluster setting server.host_based_authentication.configuration;
server.host_based_authentication.configuration
(1 row)
Time: 48ms total (execution 1ms / network 48ms)
Set the authentication configuration to the following value, which limits access to the jumpbox. Replace the IP address with your jumpbox's IP address:
SET CLUSTER SETTING server.host_based_authentication.configuration TO '
# TYPE DATABASE USER ADDRESS METHOD
host all all 35.184.229.244/32 password
host all all all reject
';
Step 4: Confirm the IP restriction
Exit the database shell by typing \q, and then run the same command again to try to re-establish the connection. This time the attempt will be rejected because we are not making the attempt from the sole allowed IP address.
cockroach sql --url "postgresql://{USER}:{PASSWORD}@aws-us-east-1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3Drandom-cluster-name-123&sslrootcert={PATH/TO/root.crt}"
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: authentication rejected by configuration
SQLSTATE: 28000
Failed running "sql"
Step 5: Access your cluster via the jumpbox
Finally, let's attempt the connection from the jumpbox. You'll need to use scp to transfer the cluster's root TLS certificate to the jumpbox, so that your client there can use it to authenticate the server. Then shell into the jumpbox with the gcloud gcompute ssh and run your connection command from inside the jumpbox.
gcloud compute scp root.crt roach-jump-box:root.crt
gcloud compute ssh roach-jump-box
cockroach sql --url "postgresql://{USER}:{PASSWORD}@aws-us-east-1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3Drandom-cluster-name-123&sslrootcert={PATH/TO/root.crt}"
#
# Welcome to the CockroachDB SQL shell.
...
Step 6: Allow IP addresses for applications
Of course, it's likely that an application will also need to access the database, in which case, you could add a new rule to allow an IP address to your configuration. You will then need to route the outgoing traffic from your applications through a specific IP. The preferred way is to use a NAT gateway, but a quick, lightweight solution is to attach an external IP to a compute instance with acts as a proxy. However, in this latter case the proxy as a bottleneck and single point of failure, so this is not suitable for high traffic or uptime-critical services.
Further, we can fine-tune our configuration and improve the overall security and resilience of our system by restricting access from the given IP to the appropriate user.
Each user's permissions should then be precisely configured using CockroachDB's system of access management.
SET CLUSTER SETTING server.host_based_authentication.configuration TO '
# TYPE DATABASE USER ADDRESS METHOD
host all ops_user 555.123.456.789/32 password
host all app_user 555.987.654.321/32 password
host all all all reject
';
Next steps
- CockroachDB Cloud authentication
- CockroachDB Cloud authorization
- CockroachDB Cloud network authorization
Was this helpful?
