Security and Grants¶
Granting a role access¶
One helper, superuser-only:
This grants schema USAGE on utl_mail plus EXECUTE on all three functions (send, send_attach_raw, send_attach_varchar2) — nothing else. There is no utl_mail.grant_admin(); there's nothing to administer here beyond sending, since this extension has no profiles, channels, or data of its own to manage.
Why a role granted only utl_mail.grant_sender() never needs pg_relay_notifier's own grants¶
Every utl_mail.* function is SECURITY DEFINER. When a role holding only the grant above calls utl_mail.send(...), the function runs with its own owner's privileges for the duration of the call — including the internal call it makes to pgrelay_notifier.send_mail(...). The privilege check for that internal call is evaluated against the utl_mail.send() function's owner, not against the original caller. So the calling role only ever needs EXECUTE on the utl_mail.* wrapper; it never needs, and is never silently granted, direct access to pgrelay_notifier.send_mail() or anything else in the core extension.
A role that also wants pgrelay_notifier.get_status()/list_notifications() — to check on a UTL_MAIL-originated send afterward, something Oracle's UTL_MAIL has no equivalent of at all — needs pg_relay_notifier's own grant_sender() separately. That's a deliberate, minimal-privilege choice, not an oversight: this extension's own grant footprint stays exactly as large as what it actually does.
The managed-cloud grant subtlety¶
Each utl_mail.* function's owner needs EXECUTE on the underlying pgrelay_notifier.send_mail() / send_mail_attach_raw() / send_mail_attach_text() functions, since those are themselves function-gated (EXECUTE revoked from PUBLIC in the core extension). If the same role installed both pg_relay_notifier and pg_relay_notifier_utl_mail and that role is a genuine PostgreSQL superuser, this needs no attention — superusers bypass every REVOKE regardless.
But several managed-PostgreSQL services grant an install-time admin role that is not a full bypass-all superuser (AWS RDS's rds_superuser is the standard example). To make the install work correctly either way, the install script explicitly grants the installing role (CURRENT_USER at install time) direct EXECUTE on the three underlying functions, rather than assuming ownership alone is sufficient:
GRANT EXECUTE ON FUNCTION pgrelay_notifier.send_mail(text, text, text, text, text, text, text, text, text, integer) TO CURRENT_USER;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.send_mail_attach_raw(text, text, text, text, bytea, boolean, text, text, text, text, text, text, text, integer) TO CURRENT_USER;
GRANT EXECUTE ON FUNCTION pgrelay_notifier.send_mail_attach_text(text, text, text, text, text, boolean, text, text, text, text, text, text, text, integer) TO CURRENT_USER;
This is why installing pg_relay_notifier_utl_mail with a different role than the one that installed pg_relay_notifier needs the same care described in Installation and the Cloud Setup book — the installing role needs its own explicit access to the core extension's functions, and a plain "I own this extension" isn't automatically enough on every platform.