Connect n8n to PostgreSQL safely
Connect n8n workflows to PostgreSQL using scoped roles, parameterized queries, transaction boundaries and replay-safe acceptance checks.
Last updated
In short
- Use database privileges to limit the workflow credential.
- Bind values as query parameters rather than concatenating SQL.
- An external API call and a database transaction need reconciliation.
On this page (7)
Integrations · Part of Workflow automation
Give the workflow a narrow database capability
The n8n Postgres node can query and modify PostgreSQL data. That makes it useful for integrations, but it also means a workflow credential can become a broad database access path if it uses an owner or superuser account. Start with the business operation and grant only the database capability needed to perform it.
This is a reference procedure and synthetic transaction design, not a deployed or tested workflow. Check the Postgres node documentation against the selected n8n release and node version. Record PostgreSQL’s major version, schema revision and the n8n licence arrangement for the intended use.
Define the data contract
Consider a workflow that accepts a supplier status event and updates an integration table. Its input contract can be small: source_event_id, supplier_id, status and occurred_at. Validate those fields before opening a database transaction.
| Field | Validation | Rejection behavior |
|---|---|---|
| Source event ID | Nonempty stable identifier | Reject or route to review |
| Supplier ID | Existing identifier in the approved namespace | Do not create an unintended supplier |
| Status | Value in the agreed state vocabulary | Reject unknown state |
| Event time | Valid timestamp with timezone | Preserve original value for reconciliation |
| Source system | Trusted configured origin | Do not trust a caller-supplied database target |
Keep schema and table selection in reviewed workflow configuration. Query parameters bind data values; they should not be treated as a general mechanism for accepting arbitrary SQL identifiers from a webhook.
Use a dedicated role and connection
Create a role for the integration’s specific tables or approved functions. PostgreSQL’s GRANT documentation distinguishes database, schema, table and sequence privileges. A connection can succeed while a necessary schema or sequence permission is missing; test the actual operation using the workflow role.
| Requirement | Grant concept | Avoid |
|---|---|---|
| Connect to database | Database connection permission | Reusing the application owner |
| Resolve approved objects | Schema usage | Broad schema creation rights |
| Read integration lookup | Select on specific table or view | Reading unrelated personal data |
| Write integration state | Specific insert/update capability | Delete or truncate without a business need |
| Call approved procedure | Execute on selected function | Unreviewed privileged function access |
Use TLS where the network boundary requires it and validate the server certificate. Restrict source addresses at the database network layer. Store the credential in n8n’s credential mechanism, protect its encryption key and define a rotation procedure that does not expose the password in execution logs.
Parameterize the query
For a synthetic lookup, the reviewed SQL shape could be:
SELECT supplier_id, status
FROM integration.supplier_status
WHERE supplier_id = $1;
Bind the validated supplier identifier through the Postgres node’s query-parameter feature. This is an illustrative query shape; the table must exist in your pilot schema. Do not build the statement by inserting raw webhook text into the SQL string.
For writes, define the unique business key and conflict policy in the database schema. A duplicate source event should either return the existing result or be marked as already processed. A retry should not create a second business record simply because n8n assigned a new execution ID.
Define transaction boundaries explicitly
The node offers batching and transaction-related options whose behavior depends on the operation and node version. Test the selected mode with a deliberate failure halfway through a synthetic batch. Confirm whether all database changes roll back or earlier items remain committed.
A database transaction cannot automatically roll back an email already sent or a record already created in another SaaS service. If the workflow updates PostgreSQL and then calls an external API, record an integration state such as pending, sent or needs_review. Reconcile ambiguous outcomes before retrying. An outbox-style design can separate committing intended work from delivering the external side effect.
Run the failure matrix
| Scenario | Required result |
|---|---|
| Valid new event | One intended row change and traceable event ID |
| Duplicate event | No duplicate business effect |
| Invalid status | No database write |
| SQL-like text in input | Treated as data or rejected, never executed as SQL |
| Database unavailable | Bounded retry and visible unresolved state |
| Failure during batch | Documented transaction behavior |
| External API accepts then times out | Reconciliation before another create request |
| Credential attempts unrelated table | Permission denied |
Keep synthetic fixtures and expected row counts with the workflow export. Do not retain full sensitive payloads in n8n execution history unless the retention policy requires them. Record identifiers and error categories where that is sufficient for operations.
Recover without replaying everything blindly
Back up the workflow, credential references, encryption key and PostgreSQL state under their respective recovery plans. Restore into an isolated environment with triggers and outbound actions disabled. Compare the integration ledger to the destination before resuming pending executions.
For rollback, stop the trigger, preserve new event IDs and restore the prior workflow configuration. Database changes may require a reviewed compensating operation rather than restoring the entire database to an earlier point. Accept the integration when its least-privilege role, transaction behavior, duplicates and ambiguous external outcomes all have a documented result.
Sources and further reading
Use the linked documentation to confirm the version, edition and contract that apply to your project.
