CockroachDB

Connect CockroachDB so agents can read schemas, run bounded queries and plans, and change rows through your approvals

Connect CockroachDB to let CloudThinker agents read schemas, run bounded queries and query plans, see what’s running on the cluster, and insert, update, or delete rows, with each change going through your approvals. You give CloudThinker two encrypted connection strings, one for a read-only SQL user and one for a separate write user that’s used only for approved changes.

Prerequisites

• A CockroachDB cluster whose SQL endpoint CloudThinker can reach from the internet. Agents connect from CloudThinker Cloud, so localhost and private addresses resolve there, not on your network. See Reaching private services.
• An admin account that can create SQL users and grant privileges.
• Password authentication with TLS. The connection rejects sslmode=disable, allow, and prefer.

Setup

1. Create two SQL users

   Open a SQL session as an admin, either from the SQL Shell in the CockroachDB Cloud console or with cockroach sql --url '<admin-connection-string>'. Then create one user for reads and one for approved writes:

   CREATE USER cloudthinker_read WITH PASSWORD '<read-password>';
   CREATE USER cloudthinker_write WITH PASSWORD '<write-password>';
   
2. Grant table privileges

   Run these in each schema agents should reach. This example uses public:

   GRANT USAGE ON SCHEMA public TO cloudthinker_read, cloudthinker_write;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_read;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO cloudthinker_write;
   

   The write user needs SELECT too: CockroachDB requires it for any UPDATE or DELETE that reads rows through a WHERE clause, and agents always send one.

   Tables created later need the same grants. To cover them automatically, set default privileges for the role that creates your tables (app_owner here):

   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO cloudthinker_read;
   ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO cloudthinker_write;
   

   You can’t skip the write user, because the form requires it. If you want reads only, create the write user and give it no table privileges. Every approved write then fails at the database.

3. Let the read user see running statements (optional)

   Grant VIEWACTIVITY only if agents should see statements from other sessions. Schema and data reads work without it. Statement text includes literal values, which can hold personal data, so leave it off if that matters to you.

   GRANT SYSTEM VIEWACTIVITY TO cloudthinker_read;
   
4. Open network access

   CloudThinker must reach the SQL port (default 26257). In CockroachDB Cloud, open your cluster’s Networking page and add CloudThinker’s egress IPs to the IP Allowlist. For a self-hosted cluster, allow them in your firewall or security group. These docs don’t publish CloudThinker’s egress IP list: ask your CloudThinker contact, or contact us.

5. Build two connection strings

   Make one string per user, both pointing at the same cluster and database. URL-encode special characters in each password.

   CockroachDB Cloud

   Open your cluster’s Connect dialog and copy the general connection string, then swap in each user:

   postgresql://cloudthinker_read:<read-password>@your-cluster.cockroachlabs.cloud:26257/appdb?sslmode=verify-full
   postgresql://cloudthinker_write:<write-password>@your-cluster.cockroachlabs.cloud:26257/appdb?sslmode=verify-full
   

   Remove any sslrootcert path that points at a file on your own machine, since CloudThinker can’t read it.

   Self-hosted

   Point at a load balancer or any node’s SQL address:

   postgresql://cloudthinker_read:<read-password>@crdb.example.com:26257/appdb?sslmode=require
   postgresql://cloudthinker_write:<write-password>@crdb.example.com:26257/appdb?sslmode=require
   

   The connection can’t hold a CA certificate file. A cluster signed by your own CA fails verify-full, so use require, which still encrypts the connection.

6. Add the connection in CloudThinker

   Open More → Workspace settings → Connections, select CockroachDB, and click Connect on its detail panel. In the dialog, optionally change the Alias and Description. Paste the read URL into Connection string and the write URL into Write connection string, then click Connect.

   Success state: the connection shows Connecting and Verifying, then Connected. CloudThinker then runs Discovery once, and its results show in the Discovery section of the connection’s detail panel.

Connection details

Field	Required	Description
Alias	No	Short name for this cluster. Prefilled with the next free alias, such as cockroachdb-1
Description	No	What this cluster is. Agents read it to choose between instances
Connection string	Yes	Encrypted URL for the read user, limited to SELECT and optional VIEWACTIVITY
Write connection string	Yes	Encrypted URL for a different user, limited to SELECT, INSERT, UPDATE, and DELETE

The two strings must differ. If they’re identical, or the write string is empty, CloudThinker won’t run the connection.

Required permissions

You want agents to	Privilege
List databases and tables, and read schemas	USAGE on the schema and SELECT on the tables, for the read user
Run bounded queries and estimated plans	SELECT on the tables, for the read user
See statements running in other sessions	VIEWACTIVITY system privilege, for the read user
Insert, update, or delete rows through your approvals	INSERT, UPDATE, or DELETE on the tables, plus SELECT for filtered changes, for the write user

Neither user needs admin or any schema-change privilege. Grant only the tables agents should work on, and leave out INSERT, UPDATE, or DELETE on the write user for any change you never want agents to make.

Agent capabilities

Capability	What agents can do
Schema discovery	List databases and tables, and read a table’s CREATE statement with its columns, indexes, and constraints
Bounded queries	Run one read-only SELECT at a time
Query plans	Run an estimated EXPLAIN without executing the statement
SQL metadata	Run SHOW statements such as SHOW INDEXES
Cluster inspection	Report the server build and the statements running now, with the login and client address behind each
Row changes	Insert, update, or delete rows through the write user, once the change clears your approvals

Queries stop after 30 seconds and return at most 1,000 rows. EXPLAIN ANALYZE is refused because it runs the statement. Node inventory and the SQL user list aren’t available: CockroachDB v26 restricts the internal tables that hold them, and agents say so instead of guessing.

Verify the connection

List my CockroachDB databases and the tables in the application database

Expect database names, then the tables in the one you name. A relation does not exist error means the name was wrong or the read user can’t see it.

Example prompts

Show the schema and indexes for public.orders
Explain the query plan for looking up an order by customer_id, without running it
Which statements are running on the cluster right now, and who is running them?
Set status = 'cancelled' for order 123 in public.orders

Write access

Agents can only insert, update, or delete rows. Each change goes through Approvals and runs with the write connection string for that one call.

Every change an agent makes goes through your workspace’s Approvals first. On the default Auto · Rules + AI · Ask me mode, AI judgment can let a safe change run without asking — and an Allow Rule can too, once CloudThinker turns on Rule enforcement for your workspace. Choose Manual to approve every change yourself.

• Before asking, the agent names the action, the table, and the WHERE clause that selects the rows.
• An UPDATE or DELETE without a WHERE clause is refused.
• Agents can’t create, alter, drop, or truncate databases, tables, or indexes.
• The result reports the action, table, and number of affected rows, never the row values.
• Approving one change doesn’t approve the next.

Troubleshooting

The SSL mode is rejected

Use sslmode=require, verify-ca, or verify-full. The connection refuses disable, allow, and prefer because they allow an unencrypted connection.

Authentication or connection fails

Check the username, URL-encoded password, host, port (default 26257), and database in each string. After a password reset, copy the connection string again from the CockroachDB Cloud console.

A table is missing or a query is denied

The read user lacks USAGE on the schema or SELECT on the table. New tables need their own grants or default privileges.

A write is refused

Confirm the write URL differs from the read URL, the write user holds the privilege for that table (plus SELECT for an update or delete), and this specific change was approved. Every update or delete needs a WHERE clause.

LIMIT exceeds maximum

A query asked for more than 1,000 rows. Ask for a smaller set, or a count or aggregate filtered on the server.

Security

• Least privilege — grant only the permissions the agents need for your use case; start read-only and widen later.
• Read-only by default — use read-only credentials unless you want agents to make changes through this connection.
• Rotate credentials — rotate keys and tokens on your normal schedule; CloudThinker picks up the new value when you update the connection.
• Revoke on offboarding — remove the credential at the provider when you delete a connection or a teammate leaves.

• Separate users for reads and writes: keep the read user to SELECT only, and give row-change privileges to the write user alone. Never give the read user INSERT, UPDATE, or DELETE.
• Verify the server certificate: prefer sslmode=verify-full when the cluster’s certificate comes from a public CA and its hostname matches. Use require for a private CA.

Related

PostgreSQL

Connect a PostgreSQL database with a read-only user.

Approvals

Decide which row changes run, wait for a person, or are refused.