The Security Model

Private tables

The four core tables — pgrelay.action_types, pgrelay.actions, pgrelay.log, and pgrelay.queue — have every privilege revoked from PUBLIC (PostgreSQL's built-in group that every role automatically belongs to). No role can query or modify them directly with plain SQL. All access has to go through pg_relay's own functions.

EXECUTE grants

After the extension is installed, every function in the pgrelay schema has EXECUTE revoked from PUBLIC. Exactly two functions are then re-granted to PUBLIC: pgrelay.notify() (this mirrors how accessible PostgreSQL's own built-in pg_notify() is — any role that can connect is able to fire an event on any active channel) and pgrelay.list_action_types() (read-only metadata, letting any role see which action types are registered).

If you are on a shared database where not every role should be able to fire events, revoke notify from PUBLIC and grant it only to the roles that need it:

REVOKE EXECUTE ON FUNCTION pgrelay.notify(text, text, timestamptz, timestamptz, boolean) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION pgrelay.notify(text, text, timestamptz, timestamptz, boolean) TO my_app_role;

SECURITY DEFINER helpers

The tables are private — no role can read or write them directly, including pgrelay itself. To reach them, process_one calls a set of SECURITY DEFINER helper functions. A SECURITY DEFINER function always runs as its owner (the superuser who installed the extension), no matter who calls it. This lets the helper reach the private tables even when the caller is the deliberately limited pgrelay role.

Every SECURITY DEFINER function in pg_relay also sets search_path = pgrelay, pg_temp. This is a security hardening step that stops a malicious user from creating a same-named function in another schema and tricking a helper call into running their code instead of pg_relay's own.

The helpers are:

Function	Purpose
_fetch_action(channel)	Reads the action SQL for the given channel from pgrelay.actions
_write_log(...)	Inserts one row into pgrelay.log
_queue_claim(id)	Locks and returns one pgrelay.queue row for processing (8 columns: id, channel, payload, expire_at, retry_count, max_retries, is_active, action_type)
_queue_mark_done(id)	Marks a queue row as dispatched
_queue_delete(id)	Deletes an expired queue row
_queue_insert_retry(...)	Inserts a new retry row into pgrelay.queue

SECURITY INVOKER — process_one

pgrelay.process_one() is deliberately SECURITY INVOKER — the opposite of the helpers above. This means it runs as the caller — the pgrelay role itself — not as the extension's owner. When it runs your action SQL (EXECUTE v_action USING v_row.payload), that SQL executes as the pgrelay role.

Why does this matter? It means your action SQL can only reach database objects that the pgrelay role has been explicitly granted access to. If pgrelay has no privilege on a table, the action cannot query or modify it — not even by accident. The Processor cannot escalate its own privileges by writing clever action SQL, because it never runs with elevated rights in the first place.

This is why you need GRANT EXECUTE ON FUNCTION my_func(text) TO pgrelay for every function your actions call — see Granting Permissions. This is a deliberate feature, not an inconvenience: it means pg_relay can never be used to run arbitrary SQL as a superuser.

The savepoint around action execution

Inside process_one, the call that runs your action SQL is wrapped in an inner BEGIN … EXCEPTION block, which PostgreSQL implements internally as a savepoint (a checkpoint within a transaction that can be individually rolled back). This is critical:

• If the action raises an exception, the savepoint rolls back just that action — nothing else in the surrounding transaction is affected.
• The outer block then records the error in pgrelay.log, marks the queue row done, and schedules a retry if one is appropriate.
• Without this savepoint, a failed action would abort the entire process_one call. The queue row would be left permanently unprocessed, and would be picked up again on the next tick — forever, in an endless loop.

---

Continue to Granting Permissions for the exact grants each role needs.