How to Configure n8n CockroachDB Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up n8n CockroachDB read only access by creating a restricted database user. Then you add those read-only login keys to your n8n Postgres tool node. Many teams start by reusing an admin database login to get workflows running fast. That broad role lets a single bad workflow step delete or change live rows. Giving n8n a read-only database role stops accidental writes from automated jobs and agents. How to set up n8n cockroachdb read only access CockroachDB speaks the standard Postgres w

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 n8n CockroachDB read only access by creating a restricted database user. Then you add those read-only login keys to your n8n Postgres tool node. Many teams start by reusing an admin database login to get workflows running fast. That broad role lets a single bad workflow step delete or change live rows. Giving n8n a read-only database role stops accidental writes from automated jobs and agents.

How to set up n8n cockroachdb read only access

CockroachDB speaks the standard Postgres wire protocol for client logins and queries. In n8n, you attach a Postgres tool sub-node to your workflow or AI Agent node. The n8n AI Agent node combines a chat model with one or more tool sub-nodes. The agent decides which tools to call to finish a task on its own. For SQL tasks, n8n recommends using a Postgres tool sub-node with a recent Agent node. Earlier n8n versions had a dedicated SQL Agent mode, which is now deprecated.

To enforce read-only rules, you first build a dedicated role inside CockroachDB. You grant this role permission to select data only from specific schemas and tables. You do not give it permission to insert, update, drop, or alter any tables. Next, you save the new user credentials inside the n8n credential store. Every time the workflow or agent runs, n8n logs into CockroachDB with this restricted role. If an agent tries to run a write query, CockroachDB blocks the statement right away.

What native database roles solve and what they leave open

A read-only role inside CockroachDB solves the problem of unwanted data changes. It makes sure that an n8n workflow or AI agent can only read rows. Even if a prompt goes wrong, the database refuses to delete or change records. This native database rule is a strong first step for any automation team.

Still, database roles only decide who can log in and which tables they can read. They do not inspect what individual queries do while they run in flight. A read-only user can still pull full tables that hold sensitive customer details. If a table has emails, phone numbers, card numbers, or national IDs, the query returns them. Those raw values then flow straight into n8n execution logs or external chat models. Data masking rewrites matched fields in results so raw PII does not leave the database.

Static database credentials inside n8n also create a long-term key management problem. Anyone who can edit the n8n workflow can use those saved login keys. Also, CockroachDB logs show every query coming from the same shared n8n user. You cannot easily tell which workflow owner or trigger ran a specific heavy query. A read-only role also will not stop an agent from running slow full-table scans.

Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why query-level controls matter for AI agent workflows

AI agents write their own SQL queries based on natural language questions from users. Because the agent builds queries at runtime, you cannot predict every statement in advance. One poorly written join can read millions of rows and slow down your cluster. Another broad query can scoop up private customer columns that the user never needed.

Teams need controls that check each statement before it hits the CockroachDB cluster. Query checks can block risky commands or limit how many rows a query returns. Dynamic masking rewrites emails, card numbers, and phone numbers in query results by default. Strong audit logs also tie every single SQL query back to the specific workflow run. Combining a read-only CockroachDB role with query-level rules keeps automated workflows safe and clear.

Add runtime guardrails and masking beside your databases and APIs

Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits beside the service as an open-source proxy, a sidecar for your database or API.

Before a query runs, hoop.dev guardrails check the statement against your rules. An AI Analyzer you configure can score intent or hold risky calls for human review. Next, hoop.dev data masking rewrites sensitive fields in responses, and each verdict is logged per listener. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can n8n talk to CockroachDB using the built-in Postgres node?

Yes, CockroachDB supports the Postgres wire protocol out of the box. You can use the standard n8n Postgres node or Postgres tool sub-node to run queries.

Does a read-only CockroachDB role hide sensitive columns from n8n AI agents?

No, a table-level read role lets the user read every column in that table by default. To hide emails, phone numbers, or card numbers, you need column restrictions or data masking.

Which n8n node should I use for SQL agent workflows?

Use the current n8n AI Agent node paired with a Postgres tool sub-node. The older standalone SQL Agent setting is deprecated in recent versions of n8n.

The query runs. The emails stay hidden.