How to Configure n8n Postgres Read Only Access
You set up n8n postgres read only access by making a restricted database role. You then give that role to your n8n node or AI agent tool. Many teams skip this step when they build fast workflows. They reuse a main database login that can edit or drop tables. When an automated workflow or AI model runs with broad rights, one bad query can break production data. Teams often hook n8n up to their database to pull reports or run AI agents. The n8n AI Agent node pairs a chat model with one or more to
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 postgres read only access by making a restricted database role. You then give that role to your n8n node or AI agent tool. Many teams skip this step when they build fast workflows. They reuse a main database login that can edit or drop tables. When an automated workflow or AI model runs with broad rights, one bad query can break production data.
Teams often hook n8n up to their database to pull reports or run AI agents. The n8n AI Agent node pairs a chat model with one or more tool sub-nodes. The agent picks which tool to call to finish a task. For database tasks, builders attach a Postgres tool sub-node to a recent AI Agent node. If that tool uses an admin login, the agent can run any command it writes.
How to set up n8n postgres read only access in the database
True read-only access starts inside PostgreSQL, not inside the workflow app. You first create a group role for read-only tasks. Group roles help you manage rights when you have many users or apps. You then make a login user for n8n and add it to that group role.
Next, you lock down what the role can do by default. In PostgreSQL, the special PUBLIC role gives rights to every role on the system. By default, PUBLIC gets rights to connect to databases and make temp tables. It also gets rights to run functions and procedures. The database owner can take back these default rights so the n8n user cannot run stored logic.
You should also check the public schema rules. In PostgreSQL 15 and newer, the default setup stops users from making new objects in the public schema. In older versions, or upgraded databases, an admin must take back that right from PUBLIC. Once defaults are tight, you grant the read privilege to your group role. This privilege lets the role read from a whole table, a view, or just specific columns. You then enter this user and password into your n8n database credentials.
How n8n AI agents run database queries
In n8n, all AI Agent nodes work as a Tools Agent from version 1.82.0 onward. The old version 1 node with the agent type setting goes away in n8n 3.0. For SQL agent tasks, the docs tell you to attach a Postgres tool sub-node to the AI Agent node. The agent takes a plain language question, turns it into a SQL query, and runs it.
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.
When the agent runs a query, n8n opens a database session using the static login you saved. PostgreSQL checks the role rules for that user before it returns any rows. If the AI model tries to update a row or drop a table, the database blocks the query.
What native database roles fix and what they miss
Database roles are great for stopping writes and schema changes. They make sure a broken workflow cannot delete rows or change table structures. Column grants also let you block the role from reading specific columns altogether.
Still, native database roles leave big gaps for live operations and AI workflows. First, n8n has to store a static database password that works all the time. Second, a database role only checks who logs in at the start of a session. It does not inspect individual queries in flight to block heavy full-table scans. Third, column grants are all-or-nothing. If an n8n workflow needs to read a customer table, it pulls raw emails, phone numbers, and card numbers into memory. Native roles cannot rewrite matched fields in query results on the fly. They also do not tie each query log back to the specific person who triggered the n8n run.
Run guardrails and masking on the Postgres connection
Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.
On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.
Frequently asked questions
Can I set up read-only mode inside the n8n node settings?
No, n8n relies on the database to enforce read-only rules. You must grant read-only rights to the PostgreSQL role used in your n8n credentials.
How do I stop n8n from reading sensitive columns in PostgreSQL?
PostgreSQL lets you grant the read privilege on specific columns instead of the whole table. If you only grant access to safe columns, queries on secret columns will fail.
Why can my read-only PostgreSQL user still create tables or run functions?
PostgreSQL gives default rights to a special PUBLIC role that every user inherits. An admin must revoke function execution and older public schema creation rights from PUBLIC.
Related guides
In this post