Real-time Notifications (SSE)

2 min read

Real-time Notifications (SSE)

Subscribe to PostgreSQL NOTIFY channels over Server-Sent Events:

# Bearer token (JWT or API token)
curl -N "http://localhost:8080/db/my_database/sse?channels=orders,users" \
  -H "Authorization: Bearer $TOKEN"

# Direct credentials (HTTP Basic Auth — no GRANT … TO pgarachne required)
curl -N "http://localhost:8080/db/my_database/sse?channels=orders,users" \
  -u "alice:secret"

Each message is JSON with the channel name and payload:

{"channel":"orders","data":{"id":123,"status":"created"}}

If the payload is plain text, it is wrapped as a string in data.

Channels are not role-scoped

Unlike the JSON-RPC and MCP endpoints, SSE does not apply SET LOCAL ROLE or otherwise check the authenticated role’s grants before subscribing. PgArachne authenticates the caller (a valid JWT, API token, or direct credential for the target database), but any authenticated caller — regardless of which role their credential maps to — can subscribe to any NOTIFY channel name and will receive everything published on it. This falls out of how the endpoint is built: all SSE clients for a database share a single LISTEN connection opened as the DB_USER service account, and PostgreSQL itself has no per-channel GRANT to delegate to — LISTEN/NOTIFY channels are not database objects.

Do not NOTIFY payloads containing data that not every authenticated role should see. If you need channel-level access control, enforce it in your own application logic (e.g. per-role channel naming conventions, or filtering what you put in the payload) — PgArachne will not do it for you on this endpoint.

Sending notifications from PostgreSQL

From psql or any database session:

-- Simple text payload
NOTIFY orders, 'new order';

-- JSON payload
NOTIFY orders, '{"id":123,"status":"created"}';

From a trigger or stored procedure:

PERFORM pg_notify('orders', json_build_object('id', NEW.id, 'status', NEW.status)::text);

Note: NOTIFY is only delivered to other sessions after the sending transaction commits. If you run it inside an open transaction in psql, subscribers won’t receive it until you execute COMMIT.

← MCP File Download →