How to Configure n8n Postgres Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up n8n postgres permissions safely, create a dedicated database role with narrow read or write rules. Then pair that role with your n8n workflow or AI Agent tool sub-node. Many teams start by reusing a shared admin account to test a new workflow fast. That broad account lets every workflow node or agent tool read or change any table. If a prompt or input goes wrong, a broad role puts production data at risk. How native n8n postgres permissions work In n8n, workflows and AI Agent nodes

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 set up n8n postgres permissions safely, create a dedicated database role with narrow read or write rules. Then pair that role with your n8n workflow or AI Agent tool sub-node. Many teams start by reusing a shared admin account to test a new workflow fast. That broad account lets every workflow node or agent tool read or change any table. If a prompt or input goes wrong, a broad role puts production data at risk.

How native n8n postgres permissions work

In n8n, workflows and AI Agent nodes talk to databases through saved credentials. An n8n AI Agent node joins a chat model with one or more tool sub-nodes. The agent decides which tools to call to finish a task. From n8n 1.82.0 on, all AI Agent nodes work as a Tools Agent. Node version 1 is removed in n8n 3.0. For SQL tasks, teams attach a Postgres tool sub-node to a recent Agent node.

On the database side, Postgres manages rights through roles. Teams can set up group roles to manage rights for many users at once. Individual database users then get membership in the group role. The special role name PUBLIC grants a right to every role on the system. By default, PUBLIC gets connection and temporary rights on databases. It also gets execution rights on functions and procedures. The object owner can revoke those default rights from PUBLIC.

In PostgreSQL 15 and later, the default setup blocks users from creating objects in the public schema. In older versions or upgraded databases, an admin must revoke that creation right from PUBLIC. For read tasks, admins grant the selection right on specific tables or views. That selection right can apply to a whole table or only to specific columns. It also allows copying data out of the table.

What static database roles solve and what they miss

Setting up strict n8n postgres permissions solves the basic problem of broad access. A read-only role stops an n8n node from dropping tables or changing rows. Column-level selection rules can also keep a workflow away from secret columns. Group roles make it easier for admins to update table rights across several workflows.

Still, static database roles only check who logs in when the workflow runs. Once the n8n credential logs in, Postgres trusts every query that fits that role. A read-only role with table access can still pull thousands of customer emails at once. It can also read phone numbers, card numbers, or national IDs stored in allowed columns. If an AI Agent writes a bad query, the database cannot ask a human for approval. Static roles also cannot mask sensitive fields in query results on the fly. All queries from n8n also show up in database logs under the same shared role name. That makes it hard to tie a bad query back to one person or workflow run.

AI Agent Permissions + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Steps to lock down your database role

Start by making a dedicated Postgres role just for your n8n workflows. Do not reuse the database owner account or a developer login. Next, check the rights granted to the PUBLIC role on your database. Revoke default execution or connection rights from PUBLIC if your n8n role should not have them. If you run an upgraded database from before version 15, revoke public schema creation rights.

After that, grant selection rights only on the exact tables and columns the n8n workflow needs. Use a group role if you run several n8n instances that need the same read rules. Finally, test the n8n Postgres node or AI Agent tool sub-node with a small query. Check that the node fails when it tries to read a blocked table or column.

Add query and result controls beside Postgres

Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev runs beside your database as a sidecar for your Postgres. It reads plain and prepared queries on the wire with no change to your users or drivers.

When a tool or agent sends SQL, hoop.dev Postgres controls check each statement against your deny rules. An AI Analyzer you configure can also score intent or hold a risky query for human review. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.

Frequently asked questions

How should an n8n AI Agent run SQL queries against Postgres?

Attach a Postgres tool sub-node to a recent n8n AI Agent node. From n8n 1.82.0 on, all AI Agent nodes run as a Tools Agent and pick which connected tool to call.

Can I restrict an n8n Postgres role to specific columns?

Yes, Postgres lets admins grant the selection right on specific columns of a table or view. The n8n workflow will fail if it queries any column outside that allowed list.

Why can a new Postgres user run functions without explicit grants?

Postgres grants default execution rights on functions and procedures to the special PUBLIC role. Every database role inherits PUBLIC rights until the object owner revokes them.

The query runs. The emails stay hidden.