How to Configure CockroachDB Read Only User
To create a cockroachdb read only user, make a database role and grant select permissions. You then assign this role to the people or apps that need to read data. Many engineering teams start by sharing one admin account across their tools and team members. That broad setup makes it easy for a bad query to delete production tables. Splitting read rights from write rights is the first step toward safer database operations. When analysts and reporting tools only have read permissions, they cannot
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
To create a cockroachdb read only user, make a database role and grant select permissions. You then assign this role to the people or apps that need to read data. Many engineering teams start by sharing one admin account across their tools and team members. That broad setup makes it easy for a bad query to delete production tables.
Splitting read rights from write rights is the first step toward safer database operations. When analysts and reporting tools only have read permissions, they cannot change or drop records. Still, native database roles still require careful setup so new tables inherit the right rules.
How a cockroachdb read only user works in practice
CockroachDB uses role-based permissions to control what each account can do inside a cluster. An admin first creates a dedicated role for reading data without any write privileges. Next, the admin grants usage rights on the target database and its underlying schemas. Without schema usage rights, the user cannot see the tables inside that database.
After setting schema access, the admin grants select rights on the existing tables. This allows the account to run queries and read rows across those specific tables. Still, permissions on existing tables do not cover tables created by the team tomorrow. The admin must also change the default privileges for the target schema.
Default privileges tell the database to grant select rights on all future tables too. If you skip this step, users hit permission errors after the next schema migration. Teams must also apply these default rules for every role that runs schema migrations. Once the role is ready, the admin grants it to specific human users.
What native database roles solve and what they miss
Native read roles stop accidental writes, drops, and schema changes from breaking production apps. They give analysts, support engineers, and reporting tools a safe way to inspect data. If a script has a bug, the database engine blocks any attempt to modify rows. This setup solves the basic problem of separating read workloads from write workloads.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, database roles only decide who can log in and which tables they can read. They do not inspect what individual queries do once the session is active and running. A read-only account can still run a massive join that locks up cluster memory. It can also pull sensitive customer fields from every row in a core table.
Standard select rights return raw values for emails, phone numbers, card numbers, and national IDs. If an engineer queries a user table, the database returns those sensitive columns in plain text. Native roles also force teams to distribute passwords or client certificates to every developer laptop. Rotating those shared credentials across dozens of team members takes time and creates friction.
Database audit logs also struggle to tie shared read roles back to a single person. When five engineers use the same read account, the log shows one generic role name. Security teams cannot tell which person ran an expensive query or exported customer records. Solving these gaps requires controls that work at the query and identity level.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
Why does a cockroachdb read only user fail to see newly created tables?
Table grants only apply to tables that exist when the admin runs the grant command. To cover future tables, the admin must update the default privileges on the schema. Those default rules must apply to the specific role that creates the new tables.
Can a read-only role still hurt CockroachDB cluster performance?
Yes, a read-only role can run unindexed scans or huge joins across millions of rows. These heavy read queries consume CPU and memory that production write traffic needs. Teams often pair read roles with statement timeouts or follower reads to protect cluster health.
How do teams protect sensitive data from read-only database accounts?
With native permissions alone, you must move sensitive columns into separate tables or views. You then grant select rights only on the safe views rather than the base tables. Many teams prefer dynamic data masking so queries rewrite emails and card numbers in results.
Related guides
In this post