n8n Service Account Setup¶

Shared configuration for connecting n8n to Rose's Supabase database. Used by the Lead Intelligence Agent and Outreach Agent.

Connection Details¶

Setting	Value
Host	`db.<project-ref>.supabase.co`
Port	`6543`
Database	`postgres`
User	`n8n_service`
Password	Provided by admin
SSL	Enabled (see SSL Configuration)

Use the pooler host

Always use the db. prefixed host (Supavisor pooler) on port 6543. The direct host (<project-ref>.supabase.co:5432) does not work for custom roles — only the built-in postgres user can connect directly.

Do not use Service Role Key

The Service Role Key has full database access. Always use the dedicated n8n_service user which only has execute permission on specific RPC functions.

Supabase Role Configuration¶

1. Create the Role and User¶

Run once in the Supabase SQL Editor:

-- Create a dedicated role for n8n integrations
CREATE ROLE n8n_readonly NOLOGIN;

-- Grant execute on agent RPC functions
GRANT EXECUTE ON FUNCTION get_visitor_for_lead_intelligence(TEXT, TEXT) TO n8n_readonly;
GRANT EXECUTE ON FUNCTION get_account_for_outreach_agent(TEXT, TEXT) TO n8n_readonly;

-- Grant read access on tables used by views/functions
GRANT USAGE ON SCHEMA public TO n8n_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO n8n_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO n8n_readonly;

-- Create a service user that uses this role
CREATE USER n8n_service WITH PASSWORD 'your-secure-password-here';
GRANT n8n_readonly TO n8n_service;

2. Set Functions to SECURITY DEFINER¶

The RPC functions query tables protected by Row Level Security (RLS). By default, functions run as the calling user (SECURITY INVOKER), which means RLS policies filter out all rows for n8n_service.

Set them to SECURITY DEFINER so they run with the owner's privileges (typically postgres):

ALTER FUNCTION get_visitor_for_lead_intelligence(TEXT, TEXT) SECURITY DEFINER;
ALTER FUNCTION get_account_for_outreach_agent(TEXT, TEXT) SECURITY DEFINER;

Why SECURITY DEFINER?

Mode	Runs as	RLS applies for
`SECURITY INVOKER` (default)	`n8n_service`	`n8n_service` — rows get filtered out
`SECURITY DEFINER`	`postgres` (owner)	`postgres` — bypasses RLS

This gives n8n_service controlled access to the data through these specific functions only, without granting direct table access that bypasses RLS.

3. Enable Dedicated IPv4¶

n8n Cloud does not support IPv6 outbound connections. Supabase's pooler host resolves to IPv6 by default, which causes ENETUNREACH errors.

In the Supabase dashboard:

Go to Settings > Add-ons
Enable Dedicated IPv4 address

Cost

The Dedicated IPv4 add-on costs ~$4/month on Supabase.

Configure n8n Credential¶

In n8n:

Go to Credentials > Add Credential > Postgres
Enter the connection details from the table above
Set SSL to Allow (not "Verify-Full" — see below)
Test the connection

SSL Configuration¶

Use SSL mode Allow (or Require without certificate verification). Do not use Verify-Full — it will fail with a self-signed certificate in certificate chain error because Supabase uses its own CA.

Write Access: the `outreach_agent` Schema¶

The read path above (n8n_service → RPC functions) is separate from the write path. Outreach leads produced by the n8n workflow are written to a dedicated schema with its own role.

Setting	Value
Schema	`outreach_agent`
Table	`leads`
Role	`n8n_outreach`
Password	GCP secret `N8N_OUTREACH_DB_PASSWORD`

Connection details (host, port, SSL) are the same as the table at the top of this page — only the user and password differ.

Why a separate schema and role¶

The table previously lived in config, which is exposed to the Data API and otherwise holds client configuration (client_configs, knowledge_faqs, curated content). Lead rows are pipeline-internal, so they moved to outreach_agent, a schema that is not in the project's exposed-schema list — nothing there is reachable over PostgREST with any API key.

n8n_outreach can reach that schema and nothing else: USAGE plus SELECT/INSERT/UPDATE/DELETE on its tables and sequences, CONNECT on the database, and search_path = outreach_agent so queries need no schema qualification. It has no CREATEDB, no CREATEROLE, no BYPASSRLS, and no grants in any other schema. A leaked n8n_outreach credential exposes outreach leads only — not tenant data.

There are no RLS policies on outreach_agent.leads. The schema is off the API surface, so there is no JWT-bearing caller to constrain, and RLS with no policies would lock out n8n_outreach (it is not the table owner). Grants are the security boundary here.

Setup¶

Created by migration 20260826175527_ix4486_outreach_agent_schema_and_n8n_outreach_role.sql. After it is applied, set the password out-of-band so it never lands in git:

ALTER ROLE n8n_outreach WITH PASSWORD 'your-secure-password-here';

Store that password as the GCP secret N8N_OUTREACH_DB_PASSWORD, then add a second n8n Postgres credential using it, and point the workflow's write node at schema outreach_agent, table leads.

Verify as n8n_outreach:

SELECT count(*) FROM leads;                                -- search_path resolves the schema
INSERT INTO leads (domain) VALUES ('probe.test') RETURNING id;
SELECT count(*) FROM config.client_configs;                -- must fail: permission denied

Do not expose the schema to the Data API

Adding outreach_agent to Settings > API > Exposed schemas would require granting anon/authenticated, which the migration deliberately revokes. The n8n Postgres node connects directly to Postgres and does not need it.

Table Columns¶

Column	Type	Notes
`id`	`bigint`	Identity primary key
`created_at`	`timestamptz`	Defaults to `now()`
`domain`	`text`	Site domain the lead came from
`company_name`	`text`
`client`	`text`
`session_id`	`text`
`conversation_summary`	`text`
`full_name`	`text`
`job_title`	`text`
`intent_score`	`text`
`profile_linkedin_url`	`text`
`email`	`text`
`mobile_phone`	`text`
`priority`	`text`
`email_subject`	`text`
`email_body`	`text`
`phone_opening`	`text`
`notified`	`boolean`	Defaults to `false`
`notified_at`	`timestamptz`

Recommended Setup¶

Method	Use Case	Risk Level
PostgreSQL with n8n_service	n8n reads via RPC (recommended)	Low — Only RPC execute permission
PostgreSQL with n8n_outreach	n8n writes outreach leads	Low — One schema, no other access
Supabase with Anon Key	Quick setup, less secure	Low-Medium
Service Role	Never use externally	High — Full database access

Troubleshooting¶

`ENETUNREACH` with IPv6 address¶

n8n Cloud cannot connect to IPv6 addresses. Enable the Dedicated IPv4 add-on in Supabase.

`self-signed certificate in certificate chain`¶

Set SSL to Allow instead of Verify-Full in the n8n Postgres credential.

`no such user`¶

When using the pooler, the username format is plain n8n_service — do not append the project ref (e.g., n8n_service.drtzxyu... will fail).

`permission denied for view/table`¶

The RPC function references underlying tables or views. Either:

Grant SELECT on the specific table: GRANT SELECT ON public.<table> TO n8n_readonly;
Or grant on all tables: GRANT SELECT ON ALL TABLES IN SCHEMA public TO n8n_readonly;

Query returns empty results (no error)¶

Check if the RPC function is set to SECURITY DEFINER:

SELECT proname, prosecdef FROM pg_proc
WHERE proname IN ('get_visitor_for_lead_intelligence', 'get_account_for_outreach_agent');

If prosecdef is false, RLS is filtering the rows. See Set Functions to SECURITY DEFINER.

Connection timeout on port 5432¶

Custom roles cannot use direct connections (port 5432). Use the pooler host (db. prefix) on port 6543.