Security and Permissions

Unlike a lot of PostgreSQL extensions, pg_relay_notifier does not rely on schema USAGE as its only real gate. Every table and sequence privilege is revoked from PUBLIC at install time, and every function's default EXECUTE-to-PUBLIC grant is revoked too — the whole extension is closed by default, and access is re-granted deliberately through two helper functions.

The two grant helpers

SELECT pgrelay_notifier.grant_sender('app_role');   -- send + inspect status
SELECT pgrelay_notifier.grant_admin('ops_role');    -- + profiles, channels, purge

Both are superuser-only to call.

grant_sender gives a role schema USAGE plus:

• compose, attach, attach_text, dispatch, send — and the Oracle UTL_MAIL-style send_mail/send_mail_attach_raw/send_mail_attach_text family
• get_status, list_notifications, get_history, get_trace

That's the complete sender surface. A sender cannot create or edit a profile, create a channel, or purge anything.

grant_admin calls grant_sender internally, then adds:

• validate_profile, create_profile, update_profile, delete_profile, get_profile, list_profiles
• list_profile_defaults, update_profile_defaults
• create_channel
• purge, purge_debug

Nobody — sender or admin — ever gets direct table access. The tables (profiles, notifications, attachments, status_history, debug_trace) are private by design; every read and write goes through a function.

The pg_relay role

The pg_relay Processor connects as a specific role (conventionally pgrelay). That role holds exactly four grants — the interface functions — set up automatically by CREATE EXTENSION:

GRANT USAGE ON SCHEMA pgrelay_notifier TO pgrelay;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.interface_version() TO pgrelay;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.fetch(bigint) TO pgrelay;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.set_status(bigint, text, text, text, integer, integer) TO pgrelay;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.debug_log(bigint, text, text) TO pgrelay;

It cannot read notifications directly, manage profiles, or send — only fetch what it's told to deliver and report back what happened. See the Technical Reference for the full interface contract these four functions implement.

interface_version() is the one public exception

interface_version() alone carries EXECUTE for PUBLIC — a compatibility probe, not a privileged operation. Any role that already has schema USAGE (granted by either helper above) can call it without a separate grant, matching the convention pg_relay itself uses for list_applications().

---