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.

Recommended Setup¶

Method	Use Case	Risk Level
PostgreSQL with n8n_service	n8n integrations (recommended)	Low — Only RPC execute permission
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.