How to Configure CockroachDB User Roles
You set up cockroachdb user roles by creating roles, assigning table rights, and adding users. This setup lets you group permissions by job function instead of managing individual logins. Most engineering teams start by building broad roles for developers, analysts, and backend apps. Still, broad permissions soon create risk when users share passwords or keep permanent write rights. A single mistake during a live incident can delete critical rows across your cluster. How cockroachdb user roles
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.
You set up cockroachdb user roles by creating roles, assigning table rights, and adding users. This setup lets you group permissions by job function instead of managing individual logins. Most engineering teams start by building broad roles for developers, analysts, and backend apps. Still, broad permissions soon create risk when users share passwords or keep permanent write rights. A single mistake during a live incident can delete critical rows across your cluster.
How cockroachdb user roles work in practice
In this database engine, users and roles are the same kind of account object. The main difference is that a user account has the right to log in. A group role usually cannot log in directly, and it holds shared permissions instead. You build a role for a specific team, such as support or data analysis. Then you grant specific rights on databases, schemas, or tables to that group role.
Next, you add individual user accounts as members of that group role. Each member inherits all the table and schema rights owned by the parent role. A role can also belong to another role, which creates a clear permission tree. System admins can allow a role to grant its rights to other accounts too. This structure keeps your permission setup clean as your team grows over time.
The database engine also includes two default roles out of the box. Every cluster has an admin role that holds full control over all database objects. Every new account also joins the public role by default at creation time. If you grant table rights to the public role, every user gets those rights. Security teams should keep the public role empty and grant rights to custom roles.
Where native database roles fall short
Native roles do a great job of deciding who can log into the cluster. They also control which tables a role can read or change at a broad level. Still, native roles cannot check what individual queries do while a session is active. Once a developer logs in with a read role, they can pull every column by default. If a table holds customer emails or card numbers, the query returns raw data.
Role inheritance also gets hard to track across dozens of microservices and human users. Teams often grant temporary admin rights during an outage and forget to remove them. Static database passwords end up sitting in local files on developer laptops for months. Native role checks also do not link database queries back to your identity provider. When two engineers share a role login, audit logs cannot tell who ran what.
User Provisioning (SCIM) + Lambda Execution Roles: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Best practices for managing database permissions
Start by separating human users from app accounts in your role design. Give each microservice its own narrow role that only touches the tables it needs. Never let backend apps run under the default admin account in production clusters. Review your role membership lists every month to remove accounts that no longer need access.
For human users, avoid granting permanent write rights on production tables. Keep standard developer roles read-only by default to prevent accidental data loss. Even with read-only roles, you must still protect sensitive fields like phone numbers and national IDs. Pairing native roles with query-level controls helps you rewrite matched fields in results and track every command.
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
What is the difference between a user and a role in CockroachDB?
Both are the same account type inside the database engine. A user is simply a role that has the login privilege turned on.
How do you stop all users from reading a new table?
Make sure you do not grant any table rights to the default public role. Every user belongs to the public role and inherits its permissions by default.
Can one CockroachDB role inherit permissions from multiple roles?
Yes, a user or role can be a member of several parent roles at once. The account combines the permissions of all the roles it belongs to.
Related guides
In this post