Data flows and security architecture in CockroachDB

3.1   A word about the log files

The Pebble log files only contain operational events (e.g. “the process is shutting down”). The other logs contain possibly sensitive application data, as follows:

• the SQL statement logs contain the SQL text of queries sent by client, including the username, database/table names and the literal values passed as arguments to the query. The results are not logged, however the number of rows in the result is. Error messages returned to SQL clients, if any, are also logged in full.
• The SQL audit logs contain usernames, and database/table names.
• The main (default) logs contain highly sensitive information:
  • Data range boundary keys are copied in full in various log messages. Boundary keys include all the data from indexed columns in SQL tables (both primary and secondary indexes).
  • Data value strings are included in various logged error messages.
  • The amount of data revealed in this way is already relatively high in the default configuration, and can increase to become very detailed if the vmodule option is changed (which can happen dynamically, during troubleshooting).
  • This logging of keys and values can occur on any table, including system tables like system.users.

Overall, the main log files should be considered highly sensitive and access to them should be considered to offer pathways to privilege escalation up to complete control over the CockroachDB cluster.

4.1   Integrity (tamper-resistance)

Tamper resistance is arranged for network connections as follows:

• TLS integrity over all TCP connections.
• Unix process isolation for the unix socket.

Tamper resistance for storage is arranged as follows:

• Regular consistenty checks between replicas stored on different nodes.
• Optionally encryption-at-rest for data files.

Meanwhile, there is no integrity protection for generated log files, nor for temporary files during Bulk I/O operations.

Generally, CockroachDB was designed with the assumption that storage integrity is provided by the underlying environment and that no malicious actors have network access to the OS where cockroach is running. Production deployments that care about tampering via physical access should use RAM and disk encryption.

Note: CockroachDB also uses checksums inside the data files. However, if my reading of the source code is right, Pebble does not use cryptographic hashes to maintain integrity, and programmatic tampering remains possible. As noted here, checksums are a protection against accidental damage to the data (e.g disk corruption) and do not provide resistance against malicious tampering.

4.2   Confidentiality

Resistance against information disclosure over network links is arranged as follows:

• TLS encryption over TCP connections is enabled by default.
• Unix process security for the unix socket.

Resistance against information disclosure in storage is arranged as follows:

• for data files, using optional encryption-at-rest.
• however, there is no confidentiality protection for generated log files nor for certain (non-RocksDB) intermediate temporary files during Bulk I/O operations. An attacker with access to logs can thus escalate privileges and get access to disclose data using a client connection, regardless of whether storage encryption is enabled.

As with tamper resistance above, resistance against information disclosure (data leaks) of the storage underlying a cockroach server should be provided by the environment, e.g. via RAM and disk encryption. Until now, CockroachDB was designed with the assumption that the cockroach server is a confidential enclave.

This raises the question of the purpose of the encryption-at-rest feature, if access to the server running cockroach provides a realistic path to information disclosure of stored data. I assume that the feature was designed to maintain confidentiality of data files when they are manually copied elsewhere, or when cockroach is running against networked storage.

4.3   Non-repudiability

The logging subsystem is organized as channels, where events come from inside CockroachDB, routed to sinks, which decide how the logging events are repoted externally to the process.

It is possible to mark a log sink as auditable, which makes CockroachDB synchronize all log writes. This ensures that the log events going to that sink are persisted before operations are allowed to go throught.

By default, the “important” channels, including accesses to audited tables, are associated to a sink with the auditable bit.

It is also possible to route the log traffic to an external log collector, e.g. Fluent, to ensure that the collected events cannot be tampered with if the server where cockroach is running gets compromised.

4.4   Authentication

Authentication over network connections is arranged as follows:

• For HTTP clients, using either a password check (hash in system.users) or a valid session cookie (stored in system.web_sessions). The web interface, via an RPC, enables creation of a session cookie from a password or an OIDC handshake (“login workflow”); it is also possible to create a cookie manually using the cockroach auth-session login CLI.)
• For RPCs between nodes, two-way TLS certificate validation. Only node-to-node certificates are accepted.
• For RPC clients, TLS client certificate validation.
• For SQL clients, depending to the connection origin and the username, according to the current HBA configuration (see below):
  • Either TLS client certificate validation, or
  • A password check against the hash stored in system.users, or
  • No authentication (always trust), or
  • Via GSSAPI / Kerberos.

CockroachDB also supports OCSP for TLS certificate revocation.

Authentication between the cockroach process and the accompanying storage is arranged as follows:

• Access to log files and the data files is authenticated by the unix permission system.
• With encryption-at-rest, using configured encryption keys.

Authentication for outgoing requests performed by cockroach, e.g. when exporting data or backups to an external storage system, is performed using credentials specified in SQL when the bulk I/O job is created.

4.5   Availability

Resistance to network-based denial of service attacks is arranged as follows:

• Memory usage upon incoming network connections is kept minimal until authentication succeeds (with a caveat, explained below).
• Nodes are “multi-active”: if one node goes away, another node can take over with close to zero time to recovery. This exploits consensus-based replication and thus requires a majority of nodes in every replication group to remain available.
• Automatic scale-up is possible: adding more nodes automatically adds more capacity.

As of v21.1, CockroachDB has a new multi-tenant architecture internally, which provides internal resource isolation between multiple “tenants” that are assumed to be adversaries with regards to availability.

As of this writing, this architecture is only used in the CockroachCloud “serverless” offering. CockroachCloud “dedicated” clusters, as well as users who run CockroachDB themselves, do not have access to this feature. In “single-tenant” deployments, any application can lock the others out via resource exhaustion.

Finally, the note about resource usage until authentication succeeds: incoming requests are accepted by spawning a goroutine per incoming connection. There is only minimal throttling based on the total amount of memory allocated (each connections is initially counted as 10KiB towards the configured --max-sql-memory). It is thus possible to open tens of thousands of pre-authentication connections to a single node. A semaphore is used internally to limit the CPU usage of authentication checks for incoming connections and prevent an attacker from using pre-auth connections to starve a node from CPU.

4.6   Authorization

Resistance against escalation of privileges via the storage system is solely maintained using the unix permission system. A user logged into the cockroach server’s OS with permission to access the cockroach data files can obtain a copy of the node-to-node TLS certificate to gain control over the entire cluster.

It is also possible to tamper with the data files to yield escalation of privilege. A user with write access to the data files of the leaseholder node for the system.users and system.role_members table can spoof the existence of new security principals in the admin role to other nodes.

As explained in A word about the log files above, privilege escalation via information leaked in the log files is also theoretically possible.

Meanwhile, resistance against escalation of privilege using network connections is more complex and requires a deeper dive into CockroachDB’s privilege model. Keep reading.

4.7   Aside: CockroachDB’s “insecure” mode

When nodes are started with the --insecure flag, all the following mechanisms are entirely disabled (all-or-nothing):

• confidentiality: all no network and storage encryption is disabled.
• integrity: there are no digests on network messages.
• authentication: any client can authenticate as any principal, including spoofing the node identity.
• authorization: all non-node principals are assumed to have the “admin” bit. Since node connections can be spoofed, any client effectively can also obtain the “node” bit on demand.

This mode is only offered for testing and disaster recovery.

5.1   Security principals

There are three pre-defined principals, with the option to create additional principals as entries in the system.users SQL table.

The three pre-defined principals are:

• the node pseudo-user, which represents the identity of a node to another node.
• the root SQL user.
• the admin SQL role.

Principals are either singular (“user accounts” or node) or roles which correspond to user groups: every member of a role has the bits assigned to the role in addition to its own.

The architecture of CockroachDB currently validates the existence of a user against the system.users table, however this is done only during the authentication step. “During” routine SQL and RPC operations, no further access is made to system.users, so that table is not deeply required as provider of principals. I predict that CockroachDB will grow to recognize principals defined in external sources.

5.2   Privilege bits

The privilege bits currently defined include:

• The “node” bit: can send inter-node RPC requests.

• The “admin bit” (the isAdmin variable and HasAdminPrivilege() function in various parts of the source code):

  • can use all the client RPCs, including administrative RPCs.
  • via the administrative RPCs, can read and write log files and other intermediate/temporary files created or used by SQL.
  • can create SQL databases.
  • can create new principals via write access to system.users.
  • can update cluster settings, and thus change the authentication rules via the HBA configuration.
  • overrides all the other SQL-level privilege bits (see below).
  • due to the points above, gives effectively “super-user” privilege on the CockroachDB cluster.

• The “role admin bit” (the isAdmin column in system.role_members), aiming to mimic a PostgreSQL feature:

  • is either set or unset for the membership of a principal in a role.
  • when set, gives that principal’s the ability to add or remove members from the role.

• The SQL bits, aiming to mimic the PostgreSQL privilege model. These are defined either per principal, or per SQL object (database, table, view, sequence).

  Bits per principal (also called “role options”), can be associated with a value:

  • (NO)CREATEROLE: ability to create other principals.
  • (NO)CREATELOGIN: ability to create credentials for other principals.
  • (NO)LOGIN: ability to open either a HTTP or a SQL session.
  • (NO)SQLLOGIN: ability to open a SQL session.
  • (NO)PASSWORD: password hash that can support password authentication.
  • VALIDUNTIL: expiration of the password field. If expired, principal can only login if LOGIN bit is set and a non-password credential is available.
  • (NO)CONTROLJOB: ability to control jobs.
  • (NO)CONTROLCHANGEFEED: ability to control changefeeds.
  • (NO)CREATEDB: ability to create new databases.
  • (NO)VIEWACTIVITY: ability to inspect the SQL sessions of other users and query statistics.
  • (NO)CANCELQUERY: ability to cancel queries for other users.
  • (NO)MODIFYCLUSTERSETTINGS: ability to change cluster settings.

  Bits per SQL database:

  • CREATE/DROP: creating/deleting new tables/views/sequences/indexes.
  • CONNECT: can open a session with this database as current database.
  • ZONECONFIG: can alter the zone configuration (mapping of data to nodes) of all tables in a database.
  • GRANT: granting SQL bits to other users/roles over SQL databases and objects.
  • SELECT/INSERT/UPDATE/DELETE: privileges that are inherited to individual objects inside the database.

  Bits per SQL schema:

  • USAGE: can access objects inside the schema.
  • SELECT/INSERT/UPDATE/DELETE: privileges that are inherited to individual objects inside the schema.

  Bits per SQL object (table/view/sequence):

  • CREATE: altering the schema of existing tables/views/sequences/indexes.
  • DROP: dropping existing tables/views/sequences/indexes.
  • SELECT: can read from tables/views/sequences.
  • DELETE: can delete rows from tables.
  • UPDATE: can update rows in tables.
  • INSERT: can add new rows in tables.
  • ZONECONFIG: can alter the zone configuration (mapping of data to nodes) of tables.
  • GRANT: granting SQL bits to other users/roles over SQL objects.

The mechanisms of the GRANT bit seems intended to turn CockroachDB into a capability-based authorization system over the set of SQL objects: a non-admin user should only be able to grant to other users the bits that they themselves already have. This makes sense because the SQL bits were designed in PostgreSQL to prevent privilege escalation of SQL sessions over the use of SQL objects.

This used to be incorrectly implemented in v20.1 (see this issue) but seems to have been fixed. However, the documentation does not highlight that CockroachDB is capability-based system, so it remains unclear whether that is a product goal.

Also, remarkably, CockroachDB does not currently provide an “operator” privilege bit which would enable a principal to observe, but not modify cluster metadata and configuration.

5.3   Authorization invariants

The following privilege-principal matrix is hard-coded inside CockroachDB and cannot be modified on a running cluster:

• the node principal has the “node” and “admin” bits.
• the root and admin principals have the “admin” bit.
• only non-role principals can authenticate via the client interfaces.

5.4   Security boundaries

CockroachDB’s security, in agreement with its name, has adopted the “insect” security model: a hard shell with soft insides.

• Authorization is (intended to be) enforced at the boundary between the cluster and clients.
• All inter-node cluster traffic is privileged and no authorization is performed internally.

Looking back at the architectural diagram from the beginning, this places the responsibility for authorization on the server components at the client boundary:

• the RPC server for clients (3a), because some of the other components that it uses do not perform authorization themselves.
• the SQL protocol server (2), or alternatively the SQL executor&planner component which is the protocol server’s only interface to the rest of the CockroachDB’s cluster.

The HTTP server (1) could perform authorization but currently does not, instead delegating authorization to the RPC server for clients (3a).

The RPC server for other nodes (the SQL execution server, 3b, and the KV APIs, 3c) are not responsible for authorization because they can only receive requests from an authenticated node principal, and node has the “node” bit and thus total authority over the cluster.

5.5   There is more to authorization than just SQL

A naive view of database authorization would assume that 1) the only data that can be read and used is SQL data, and 2) that the privilege bits configured for each SQL object directly dictate what an authenticated principal can or cannot do.

In this naive view, a SQL client connects “as” a particular SQL user (e.g. “alice”). When the SQL client issues SELECT * FROM bobs_secret_table, CockroachDB verifies whether alice has the SELECT bit over bobs_secret_table. If it does not, the SQL query is rejected in error.

The limitations of this naive view come from the assumptions made above:

1. there is actually more data that can be accessed than just SQL data.
2. there are intermediate authorization deputies inside CockroachDB which deliberately escalate the privilege of certain queries in controlled ways.

5.6   Non-SQL data

Non-SQL data is data for which the SQL privilege bits do not apply. The following non-SQL data stored inside or alongside CockroachDB can be accessed by client sessions over the network.

These are detailed in the following sub-sections.

5.7   Authorization deputies

An authorization deputy is a component that has higher privilege than the other components it serves. It must thus perform additional authorization checks to prevent its client components from escalating their privileges in unwanted ways.

An example of this in CockroachDB is the system.jobs table. As a SQL table, this is accessible only via the “admin” privilege. However, for the benefit of UX, CockroachDB also offers the option to non-“admin” principals to control their own jobs. This is done by introducing four authorization deputies: the crdb_internal.jobs virtual table, and the three SQL statements RESUME/CANCEL/PAUSE JOB. These four deputies each operate internally with the “admin” bit, and perform an additional filtering step to only return rows owned by the requester.

Here are some examples of other authorization deputies in CockroachDB:

• The server components, as described in Architecture of a single node above. These perform authentication and authorization with the “admin” bit on behalf of yet-to-be-authenticated/authorized client sessions.
• All the SQL virtual tables that expose SQL schema metadata (database/table/column names and zone configurations). This includes all of information_schema tables, certain pg_catalog tables and certain crdb_internal tables. These access system.namespace, system.descriptors and system.zones using the “admin” bit, then filter the results based on whether the requesting principal has any SQL privilege on the returned object.
• the SQL execution logic of various SHOW statements that do not otherwise defer their authorization to a virtual table. For example, SHOW CLUSTER SETTINGS verifies that the requester has the “admin” bit.
• The ALTER ... CONFIGURE ZONE statements operate on system.zones using the “admin” bit but first require the ZONECONFIG privilege from the requesting principal (new in version 20.1).

There are many such deputies throughout the source code, yet I was not able to find a single point of reference that lists them all in a systematic way.

6.1   Reflections on the “hard shell, soft insides” model

In truth, I struggled to identify the authorization boundaries and deputies in the code. The reason for this struggle is that the code is not capability-based: all the code inside CockroachDB can theoretically operate over any and all the other components without any restriction. The node-to-node privileged RPC channel (with the “node” privilege bit, and thus able to control the entier cluster) is available internally to all source code components.

For example, the source code for a simple SELECT statement can freely send a KV request to write to an unrelated table.

I was not able to find a framework or structural enforcement of privilege checks throughout the layers of RPC and SQL execution logic. Neither was I able to find systematic testing of the authorization code either. Certain privileged statements have dedicated multi-user unit tests, but this is not done for all of them.

Therefore, the existence of additional authorization code (and thus of authorization deputies) feels, to this reader of the source code, an post-hoc model of how the code was written.

6.2   Unstructured and duplicated authorization

An architectural detail that caught my attention is that authorization policies are not systematically enforced in a single place.

Certain deputies do provide a single point of authorization in the source code for the various pathways that can request through them. For example, the logic to list running sessions and queries is performed in one place ((*statusServer) ListLocalSessions()), that single code is used both for RPC and SQL endpoints, and it takes responsibility for authorizing access.

However, most sources of data have multiple deputies, each responsible for enforcing authorization policy. For example, the “Statement details” RPC ((*statusServer) Statements()) and SQL’s crdb_internal.node_statement_details data generator each require the “admin” bit from their client separately. There does not seem to be any structural enforcement that the various access paths enforce the same authorization.

In some cases, the code accidentally enforces the right authorization policy, even though that does not seem to be intended. For example the virtual tables crdb_internal.forward_dependencies and .backward_dependencies report the tables/indexes that are referred to/from using foreign key (FK) relationships. The code acts as an authorization deputy by filtering rows out that should remain invisible to the requesting principal. However, as of this writing, it only validates the SQL privilege bits on one end of the relationship. At first glance, it thus seems theoretically possible that these two tables be used to learn about the existence and the logical schema of tables over which the logged-in user has no SQL privilege. This is not the case, however! In a separate part of the source code, the DDL statements that establish FK relations and the execution code that enforces FK constraints during mutation statements do check these privileges. It thus so happens that CockroachDB does not support FK relationships over mixed-privileged tables, so there is no additional information disclosure available via the virtual tables. This is OK, but does not seem to be deliberately designed.

To summarise, to the credit of the CockroachDB developers, it appears to me that the necessary authorization checks are indeed performed in the right places—with some caveats shared below. Even though it feels serendipitous, the authors of the code have been methodical, so far, and mistakes are promptly fixed, when recognized.

Example past issues that directly stem from this lack of systematic authorization:

• https://github.com/cockroachdb/cockroach/issues/44348
• https://github.com/cockroachdb/cockroach/issues/42567