Operating and securing a CockroachDB cluster

A CockroachDB cluster can accept a connection while some of its data has lost protection. It can also return the right rows to an administrator while the application’s login has too much authority. Operating it means checking the boundary that matters: the actual connection, the rows it may reach, and the ranges that must remain available through a change.

We will secure a small shop lookup, then follow one range through a lost replica and a planned node removal. Inside CockroachDB establishes ranges, voting replicas and leaseholders; Database access and least privilege supplies the shop’s three task identities. The examples use self-hosted CockroachDB v26.2, with native checks on v26.2.8. Multi-node drawings show stated placements and quorum counts, rather than measured failure times.

A secure connection has two identities

The service needs to know that it reached the intended database server. The server needs to know which SQL identity connected. These are separate checks, even when both use certificates.

In the example, the service trusts the cluster’s certificate authority, or CA. Its connection asks for db.shop.example. With sslmode=verify-full, the client checks that the server certificate chains to the trusted CA and covers that hostname. The server separately authenticates the client certificate as shop_runtime. Trusting the server and recognising the caller establish different identities.

A node certificate identifies a server and permits its node work. A runtime client certificate identifies the service’s SQL login. An administrator’s credential carries different authority again. Giving the application a node or administrator key collapses the distinction before a table grant can help. Keep the runtime’s private key with the service that needs it; keep signing keys and maintenance credentials outside that runtime.

The following is the shape of a certificate-authenticated connection URI. The hostname, database and file paths are deployment inputs; the files must belong to this cluster and this runtime identity.

postgresql://shop_runtime@db.shop.example:26257/shop?sslmode=verify-full&sslrootcert=/run/shop/ca.crt&sslcert=/run/shop/client.shop_runtime.crt&sslkey=/run/shop/client.shop_runtime.key

Check the identity using that credential and the same driver settings the application deploys:

SELECT current_user, session_user, current_database();

session_user identifies the original session login; current_user identifies the current SQL role. A privileged test that switches roles can exercise authorisation, but does not establish that the runtime can authenticate with its own certificate. The native runtime connection here returned shop_runtime for both identities.

Pair the successful connection with rejected cases: an untrusted CA and a hostname absent from the server certificate. An encrypted connection that accepts either has not established the intended server identity. Keep certificate expiry, renewal and a fresh connection test in the operating procedure. A still-open pooled connection is not a test that replacement credentials work.

The local v26.2.8 checks rejected a connection to an address absent from the server certificate, rejected the server when supplied an unrelated trust CA, and rejected using the support certificate to authenticate as shop_runtime. Those failures exercise server trust, server address and client identity separately. They do not verify the shop’s external sign-in service.

Give each task its own authority

shop_runtime serves authorised requests, shop_migrate changes the schema, and shop_support reads selected customer and order details. None is a customer or a person’s sign-in. Derive privileges for the operation being deployed, including the columns its conditions read, rather than granting the union of all future shop tasks.

The bounded example below covers customer and order reads and an order update. It is a slice of runtime work, not a complete checkout grant set. The migration identity owns the shop schema and tables. A non-login group carries the runtime’s ordinary privileges:

CREATE ROLE shop_rw;
GRANT USAGE ON SCHEMA shop TO shop_rw;
GRANT SELECT, UPDATE
  ON shop.customers, shop.orders TO shop_rw;
GRANT shop_rw TO shop_runtime;

The runtime has no membership path to the migration identity, no object ownership and no grant option. It cannot change payments. These choices must hold across the database: denying CREATE on shop alone does not remove a public grant to create objects in another schema.

Inspect inherited authority as well as direct grants. These native forms answer different parts of the question:

SHOW ROLES;
SHOW GRANTS ON ROLE;
SHOW GRANTS FOR shop_runtime;
SHOW GRANTS ON SCHEMA shop;
SHOW GRANTS ON TABLE shop.customers, shop.orders,
  shop.payments;
SHOW DEFAULT PRIVILEGES FOR ROLE shop_migrate,
  shop_view_owner;
SHOW DEFAULT PRIVILEGES FOR ROLE shop_migrate,
  shop_view_owner IN SCHEMA shop;
SHOW DEFAULT PRIVILEGES FOR ALL ROLES;
SHOW DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA shop;

SHOW GRANTS FOR includes privileges inherited through membership. The object-focused form without FOR lists direct grants on those objects. Follow the roles carrying access, and inspect public, ownership and is_grantable.

Run the default-privilege checks in the shop database. The creator-specific forms inspect future objects made by shop_migrate and shop_view_owner; FOR ALL ROLES checks defaults applying regardless of creator. Inspect both database-wide defaults and those inside the shop schema. Without a FOR clause, the command shows the current SQL identity’s defaults, which can miss grants on objects the migration role will create. A later table or membership can reopen a path a previous test closed.

The native audit found CREATE granted to public on the public schema. Revoking it closed that separate creation path; a subsequent runtime creation there failed too. Inspect the deployed defaults rather than assuming a new database is already restricted.

A restricted support interface

Support needs tenant and customer IDs and names, plus tenant, order and customer IDs and order status. It does not need order timestamps or payment records. Here two views expose exactly those fields:

CREATE VIEW shop.support_customers AS
SELECT tenant_id, customer_id, name
FROM shop.customers;

CREATE VIEW shop.support_orders AS
SELECT tenant_id, order_id, customer_id, status
FROM shop.orders;

GRANT SELECT ON shop.support_customers,
  shop.support_orders TO shop_support;

These views are created by a separate non-login role, shop_view_owner, with only the required base-table reads. The support login receives view access and schema usage, with no base-table access and no membership in that owner. The view owner’s temporary schema-create privilege is removed after setup.

By default, CockroachDB checks a view’s underlying privileges and row policies as its owner. The owner therefore belongs in the security design. An administrator-owned view can expose rows that a runtime policy hides. The row policies below apply to our unprivileged view owner too; the native support connection verifies the complete path. An invoker-security view is another design, but it would check the underlying privileges as support and require those privileges. Changing the option does not by itself preserve this no-base-table boundary.

Enforce the tenant boundary

Tenant 10 has customers 1–3: Mina Shah, Tariq Aziz and Edda Berg. Tenant 20 has customer 4, Rowan Vale. Customer IDs remain globally unique. A table grant can let the runtime name Rowan’s row; it does not establish a right to read it.

Row-level security, or RLS, adds a policy to ordinary table privileges. In this example the service uses application_name as a deliberately simple tenant context. Its baseline is empty. The policy compares the integer tenant ID, written as a string, with that context. It applies to existing rows through USING and proposed row versions through WITH CHECK:

ALTER TABLE shop.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE shop.customers FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_rows ON shop.customers
FOR ALL TO public
USING (
  tenant_id::STRING = current_setting('application_name')
)
WITH CHECK (
  tenant_id::STRING = current_setting('application_name')
);

The same policy protects shop.orders. FORCE subjects the ordinary table owner to the policy as well; it does not remove administrative or BYPASSRLS exceptions. These are the only policies in this example. Additional permissive policies combine with OR, so adding another broad policy can widen the allowed rows. Check the complete policy set, not just the rule named tenant_rows.

Consider a runtime query that selects customers without a tenant condition. Its SQL alone asks for all four. With trusted context 10, the row policy restricts the result to the three tenant-10 customers. Changing the context changes that permitted set; omitting a query predicate does not bypass the policy.

With context 10, the native runtime connection returned Mina, Tariq and Edda. Asking explicitly for customer_id = 4 or tenant_id = 20 returned no rows. Disabling RLS in a separate counterexample let the same runtime read all four. The missing query condition became visible because Rowan actually existed in the fixture.

A tenant-changing write exercises the other half of the policy. Customer 3 is visible under tenant 10, but changing that row’s tenant_id to 20 failed with SQLSTATE 42501, a row-policy violation. A read filter alone would not establish that protection.

What the policy trusts

The service begins a transaction on its borrowed connection, sets the authorised tenant locally, performs the request’s work on that same connection, then commits or rolls back before returning it:

BEGIN;
SET LOCAL application_name = '10';
SELECT customer_id, name
FROM shop.customers
ORDER BY customer_id;
COMMIT;

Both commit and rollback restored the empty baseline in the native connection checks; a subsequent read without setup returned zero customers. A second test first left session context 20 underneath the local value. After the tenant-10 transaction committed, context 20 returned and Rowan was visible. SET LOCAL restores the previous setting; it does not clean an already unsafe baseline. Initialise pooled connections without a tenant, set context before every data statement and test the actual pool’s return and error paths.

The runtime can also set context 20 deliberately, and the native query then returned Rowan. The policy trusts the service’s choice. It catches omitted query conditions when that choice is correct; it cannot establish that the person was authorised to choose 20. Here application_name is an ordinary caller-controlled setting, not an authentication claim. A design that must contain a compromised service needs a boundary it cannot freely forge, such as separately constrained identities or a validated restricted interface.

Nor does tenant scope decide which people inside tenant 10 may read Mina’s details. The application still performs those checks. SQL at the application boundary covers keeping the request, connection and transaction together.

Check every route to the rows

The native support login read customers 1–3 and the three tenant-10 orders through its views. It could not select a base customer table, read payments, read placed_at through the selected-field order view, write an order or create a table in shop. The runtime could read and perform its permitted order update, but could not grant its reads onwards or assume shop_migrate.

Test bypass identities separately. The administrator and a role with BYPASSRLS plus table-read authority saw all four customers even with FORCE. An ordinary owner also saw all four when FORCE was removed. These are reasons to keep runtime and interface owners unprivileged, not reasons to test ordinary access as root.

Integrity checks answer a different question. Updating order 103 to customer 4 under tenant 10 failed its composite foreign key: no customer has (tenant_id, customer_id) = (10, 4). That error can mention a value hidden by RLS. Constraint checks and cascades, privileged interfaces, backups and change-data paths therefore need their own access analysis. A passing SELECT test does not make every route a tenant-scoped export. Keep backup and replication authority separate; CockroachDB backup and recovery owns those procedures.

Read node health and range health together

A reachable gateway answers only the first operational question. The lookup may need a range whose voters are elsewhere. Start with the affected request and observation window, then compare node status, range protection and placement from that same period.

cockroach node status --all \
  --host=db-1.shop.example:26257 \
  --certs-dir=/run/shop-operator/certs

Run maintenance commands with a separate authorised operator identity and the verified cluster connection settings. Resolve addresses and node IDs before acting; node 4 in a drawing is not node 4 in your deployment. Check updated_at: an unhealthy node’s resource statistics can be old. is_live, is_available, range counts and store resources describe different things.

An under-replicated range lacks its desired protection. An unavailable range cannot currently make the progress its required majority permits. Suppose A, B and C are one range’s three configured voters. They need two votes. Losing C can leave A and B able to work, while removing the allowance for another voter failure.

A healthy fourth machine, D, can receive a learner copy and catch up. It supplies no vote until the supported membership change completes. If both B and C are missing, A alone cannot approve that transition merely because D has received data. Copying bytes to a new machine does not replace the old group’s missing majority.

Use native replication metrics such as ranges.underreplicated and ranges.unavailable to find a protection problem, then identify the affected ranges. The per-node status fields are counts of ranges represented there, not independent cluster totals to add together. A range can appear on several nodes.

SELECT range_id, start_key, end_key, lease_holder,
       voting_replicas, non_voting_replicas,
       learner_replicas
FROM [SHOW RANGES FROM TABLE shop.orders WITH DETAILS]
ORDER BY start_key;

SHOW ZONE CONFIGURATION FROM TABLE shop.orders;

SHOW RANGES gives the configured replica locations and serving owner at an observation; it does not prove every listed voter is reachable. Match those node IDs to current health. Check the effective replication configuration, including inherited requirements, voting counts and placement constraints. Non-voting replicas and learners do not fill a missing voting majority.

Find what prevents replication from recovering

A rising under-replicated count after a node loss is a starting symptom. The next check should explain why this range has not regained protection. In the DB Console, follow Problem Ranges to a Range Report and its allocator information. Retain the range ID and key bounds with the observation; movement can change where the next sample lands.

A new node helps only if the constrained range may use it and the source can deliver its history. A restart does not create a missing placement or free destination storage. It can instead remove another voter during the vulnerable interval. Preserve evidence and choose a bounded repair whose predicted effect you can check.

Watch both progress and resources. Snapshot traffic, retained history, compaction and ordinary SQL can compete for the same stores. A falling remaining-replica count supports evacuation progress; a flat count alone does not identify a blocker. Keep the target available while following the native stall report to its remaining range IDs.

Diagnosing a database incident connects the symptom to request completion. If range protection is healthy but a particular query or key consumes the work, CockroachDB query work and hot ranges owns that diagnosis and repair. Treating every busy node as a replication fault would send both investigations in the wrong direction.

Check the cluster that will remain

Permanent removal asks a forward-looking question: can every affected range keep its required voters and placement, and can the remaining stores carry the work through the next required failure? Begin only with acceptable current health. An existing lost voter is already spending the margin that maintenance would need.

Removing C from the four-node example can leave three voters on A, B and D once D has qualified and the replacement completes. Removing C when only A, B and C are eligible leaves no third destination. Even four live nodes are insufficient if D violates the range’s placement constraints. Regional failure promises require the actual failure domains, as CockroachDB table locality and region survival develops.

Native decommissioning pre-flight checks test whether the replicas have possible destinations. They do not establish available disk space or workload capacity. Keep a separate budget for the reduced cluster. The following arithmetic is invented to make that distinction visible:

D passes a placement test and fails this storage budget. E cannot help despite its spare bytes. F passes only the stated space calculation; its I/O, network, CPU and memory still need capacity for foreground demand and movement. Recalculate after the declared additional failure too. Aggregate cluster free space can hide the one constrained store that must receive the data.

Rebalancing and cluster change explains catch-up and membership safety. Here those principles become gates on a native operation: restore current protection, verify remaining placement and capacity, move one budgeted unit, then verify the result before spending the margin again.

Drain and decommission a node

Draining prepares a process to stop serving active work. Decommissioning permanently removes its stored replicas and membership responsibilities. A temporary maintenance stop preserves the store for a restart; permanent retirement needs both boundaries to finish.

For one verified removal target, the sequence is to stop new application traffic, drain active work, evacuate its replicas through decommissioning, and verify final membership. The process stays available until evacuation and membership have finished.

Withdraw the target from new application traffic through the deployment’s normal routing mechanism. Coordinate connection pools, load-balancer health checks and the process manager so they do not keep opening work on it or kill it before draining finishes. Clients need their usual retry and uncertain-outcome handling; a disconnected commit is not evidence that its transaction aborted.

The command shape below uses an illustrative target ID 4. drain addresses the target’s process; decommission can address another live node while naming the verified target ID. Supply a configured cluster name where required.

cockroach node drain \
  --host=db-4.shop.example:26257 \
  --certs-dir=/run/shop-operator/certs

cockroach node decommission 4 \
  --host=db-1.shop.example:26257 \
  --certs-dir=/run/shop-operator/certs

cockroach node status --decommission \
  --host=db-1.shop.example:26257 \
  --certs-dir=/run/shop-operator/certs

Decommissioning also drains automatically if necessary. Explicit draining lets you observe that stage independently. A drained node can still hold stored replicas; leave its process available for evacuation. Use the normal pre-flight checks rather than skipping the placement failure they report.

When decommissioning stalls

A pre-flight rejection happens before the requested removal starts. Read the error as a placement explanation: a range needs another replica, existing holders cannot take a second copy for that range, and the remaining live stores may be ineligible. Resolve that condition before repeating the request. The native check has not measured whether a destination has enough spare disk or workload headroom.

After removal starts, progress output can name a node and replicas still belonging to particular ranges. Follow those ranges to their current allocator and snapshot evidence. Restore a missing destination, communication or resource budget while keeping the target usable. Waiting is justified by advancing progress; repeated flat samples with a specific error call for the corresponding repair.

node recommission can cancel an active decommissioning state. It cannot bring a fully decommissioned node back into membership; that requires a new node. A node that already drained may need a restart after recommissioning. Use the current native membership state to choose the return path, rather than treating an interrupted terminal command as an undo.

Verify the result before the next change

The removal progress row can reach zero replicas while membership still says decommissioning. Check the final decommissioned membership as well as evacuation before terminating the process or discarding its store. Then establish restored protection and intended placement for all affected ranges, not merely the few tables in a successful shop query.

The v26.2.8 rehearsal verified the target as node ID 2 at port 26260, although it was the fourth process started. Draining completed while status still listed its stored replicas. The decommission command then reported its replica count falling from 43 to zero. Its last progress row still said decommissioning; the following node-status query reported decommissioned and zero gossiped replicas.

The three remaining nodes’ status snapshots reported no unavailable or under-replicated ranges, and the runtime read returned orders 101 and 102 paid, 103 pending. A strict dry run to remove another node was refused: its ranges had no eligible new store because the other two already held voters. That checks the missing-destination case without carrying out a second removal. No unexpected node outage was introduced, so the earlier loss-and-replacement reasoning remains a model rather than an observed recovery.

Reconnect through an intended remaining gateway as shop_runtime, repeat an authorised tenant lookup and its forbidden case, and check useful application completions, errors and latency under the intended load. One successful read verifies its path; it does not establish the capacity of the reduced deployment.

The native rehearsal here uses four processes on one host. It can establish secure login behaviour and the engine’s membership procedure. It supplies no independent machine, zone or regional failures, and no production capacity result. Keep those acceptance checks with the actual deployment. Upgrading a CockroachDB cluster adds the separate binary and cluster-version transitions when the purpose of stopping a node is an upgrade.