SMTP Endpoints

Everything the Processor needs to reach a mail server is a profile.

SELECT pgrelay_notifier.create_profile(
    p_profile_name => 'mailer',
    p_transport    => 'smtp',
    p_profile      => '{
        "host":     "smtp.example.com",
        "port":     587,
        "security": "starttls",
        "username": "app-mailer",
        "password": "_env:MAILER_SMTP_PASSWORD",
        "from":     "[email protected]"
    }'::jsonb,
    p_channel      => 'notifications'
);

Profile keys

Unknown keys are ignored, so profiles are forward-compatible with future Processor releases.

Key	Required	Meaning
host	✔	SMTP server
port		Default 587
security		starttls (default), tls (implicit TLS, e.g. port 465), none (internal relays only)
auth		plain, login, none, oauth2. Default: plain if username present, else none
username	with auth plain/login	SMTP username (ignored under auth: oauth2 — see below)
password	with auth plain/login	The SMTP password — must be an "_env:VAR_NAME" reference (below), never a literal value
oauth2	with auth oauth2	The XOAUTH2 grant block — see OAuth2-authenticated SMTP below. Requires a pg_relay ≥ 1.2 Processor
from	✔	Envelope sender and From header
timeout_seconds		Per-send timeout; default 30, hard cap 120

The password never touches the database

"_env:VAR_NAME" is a reference, not a value — it names an environment variable on the machine running the pg_relay Processor, resolved fresh at send time. create_profile/update_profile reject a literal string in password outright; there is no way to make a profile store one. Rotating the password is then an environment change plus nothing — no restart, no SQL.

On a systemd host, put the actual value in the Processor's own environment file:

# /etc/pg_relay/pg_relay.env   (mode 600)
MAILER_SMTP_PASSWORD='the-actual-password'

then sudo systemctl restart pg_relay once — only needed when the variable name itself is new to the process; the value is re-read from the running process's environment at send time otherwise.

Common variants

-- Implicit TLS (port 465)
'{"host":"smtp.example.com","port":465,"security":"tls","username":"app","from":"[email protected]"}'

-- Internal relay, no TLS, no auth (trusted network only)
'{"host":"relay.internal","port":25,"security":"none","from":"[email protected]"}'

OAuth2-authenticated SMTP (XOAUTH2)

Some SMTP servers no longer accept a username and password at all — Microsoft has retired basic authentication for Exchange Online, the Azure Communication Services relay never had it, and Google increasingly steers Workspace tenants away from App Passwords. For these, auth: "oauth2" has the Processor exchange an OAuth2 grant for a bearer token and present it to the server as SASL XOAUTH2. Requires a pg_relay ≥ 1.2 Processor; token caching and refresh are automatic.

In this mode the flat username/password keys are ignored, and a nested oauth2 object carries everything. Its optional grant_type selects one of two varieties, and understanding which is which lets you configure any XOAUTH2 server — not just the three worked below:

• client_credentials (the default) — an application grant: a service principal's tenant, client id, and secret buy a token, no mailbox consent involved. This is Microsoft's model — Exchange Online SMTP AUTH and the Azure Communication Services relay.
• refresh_token — a per-mailbox consent grant: a one-time OAuth consent by the sending mailbox produces a durable refresh token, which buys short-lived access tokens from a flat token endpoint. This is Google's model — one refresh token per mailbox, revocable by its owner.

oauth2 key	client_credentials	refresh_token	Meaning
grant_type	omit or "client_credentials"	"refresh_token"	selects the variety
username	✔	✔	the SMTP username presented in the XOAUTH2 exchange (Exchange: the mailbox UPN; ACS: the username Azure provisioned; Gmail: the mailbox itself)
client_id	✔	✔	the app registration / OAuth client id
client_secret	✔ — _env: reference	✔ — _env: reference	never a literal; validation rejects one, exactly as for password
tenant_id	✔	—	the Entra tenant
scope	✔	—	the OAuth2 scope to request — a profile field precisely so one mechanism serves every provider; copy it from your provider's documentation
token_url	—	✔	the provider's token endpoint (Google: https://oauth2.googleapis.com/token)
refresh_token	—	✔ — _env: reference	the mailbox's consent grant

Every field — username included — may itself be an _env: reference; _env: resolution walks the entire profile. validate_profile() checks all of the above per variety, listing every issue at once.

Variety 1, client_credentials — Exchange Online SMTP AUTH:

SELECT pgrelay_notifier.create_profile('exchange_smtp', 'smtp', '{
    "host": "smtp.office365.com", "port": 587, "security": "starttls",
    "auth": "oauth2", "from": "[email protected]",
    "oauth2": {
        "username":      "[email protected]",
        "tenant_id":     "72f988bf-...",
        "client_id":     "9c1c22b1-...",
        "client_secret": "_env:EXCHANGE_CLIENT_SECRET",
        "scope":         "https://outlook.office365.com/.default"
    }}'::jsonb, p_channel := 'notifications');

(One-time setup mirrors the Microsoft 365 endpoint's app registration, with SMTP AUTH enabled on the mailbox and the appropriate SMTP send permission granted instead of Graph Mail.Send.)

Still variety 1, different provider — the Azure Communication Services SMTP relay — the same grant, different host, username, and scope, which is the lesson of the example:

SELECT pgrelay_notifier.create_profile('acs_smtp', 'smtp', '{
    "host": "smtp.azurecomm.net", "port": 587, "security": "starttls",
    "auth": "oauth2", "from": "[email protected]",
    "oauth2": {
        "username":      "my-acs-resource.9c1c22b1-...-1d4e5e2b4a11.72f988bf-...-2d7cd011db47",
        "tenant_id":     "72f988bf-...",
        "client_id":     "9c1c22b1-...",
        "client_secret": "_env:ACS_CLIENT_SECRET",
        "scope":         "https://communication.azure.com/.default"
    }}'::jsonb, p_channel := 'notifications');

As of this writing, Azure's values are: the username is the dot-joined triple <acs-resource-name>.<client-id>.<tenant-id> (pipe | separators are also accepted), the scope is https://communication.azure.com/.default, and the one-time setup is a verified sender domain connected to the ACS resource plus the role assignment ACS's SMTP documentation prescribes for the Entra application. from must be an address on that connected domain. Any Entra-authenticated SMTP endpoint follows this same recipe.

Vendor values, verified by you

The hosts, scopes, username formats, and token endpoints in these examples are the vendors' settings as of this writing — Microsoft and Google own them and change them without reference to this documentation. Treat the examples as the starting point they are meant to be, and confirm each value against the vendor's own current documentation before relying on a profile; where the two disagree, the vendor is right.

Variety 2, refresh_token — Gmail / Google Workspace:

SELECT pgrelay_notifier.create_profile('gmail_oauth', 'smtp', '{
    "host": "smtp.gmail.com", "port": 587, "security": "starttls",
    "auth": "oauth2", "from": "[email protected]",
    "oauth2": {
        "grant_type":    "refresh_token",
        "username":      "[email protected]",
        "token_url":     "https://oauth2.googleapis.com/token",
        "client_id":     "1234567890-abc.apps.googleusercontent.com",
        "client_secret": "_env:GMAIL_CLIENT_SECRET",
        "refresh_token": "_env:GMAIL_REFRESH_TOKEN"
    }}'::jsonb, p_channel := 'notifications');

(One-time setup: create an OAuth client in Google Cloud, run the consent flow as the sending mailbox requesting the https://mail.google.com/ scope, and put the resulting refresh token in the Processor's environment. The token lives until the mailbox owner revokes it — a revocation surfaces as a permanent invalid_grant failure, fixed by re-running consent and updating the variable.)

Establishing your own: any server that advertises AUTH XOAUTH2 and any identity provider with a standard token endpoint fits one of the two varieties — that's why scope and token_url are profile fields rather than Processor knowledge. Ask two questions of your provider's docs: is the credential an application's (tenant/client/secret) or a mailbox's (consent → refresh token)? — that picks the variety — and what scope or token endpoint do they name? — that fills the one field that differs. Everything else is the ordinary SMTP profile you already know.

Validating before you commit to a profile

create_profile() validates the whole block and lists every problem at once, rather than one error per fix-and-retry cycle. To check a block before creating anything:

SELECT * FROM pgrelay_notifier.validate_profile('smtp', '{"host":"h"}'::jsonb);

Optional: a default sender and Reply-To for this profile

p_send_from and p_reply_to on create_profile()/update_profile() set what a notification uses when the sender composes without p_sender/p_reply_to — useful when every message on a profile should come from (or reply to) the same address without every caller having to say so:

SELECT pgrelay_notifier.create_profile('mailer', 'smtp', '{...}'::jsonb,
    p_channel := 'notifications',
    p_send_from := '[email protected]', p_reply_to := '[email protected]');

Resolution happens once, at compose time — an explicit sender argument beats the profile default, which beats the profile JSON's own from key — and is recorded on the notification, so a later edit to the profile's defaults never changes a message already composed. Clear either with an empty string. This applies equally to Microsoft 365 profiles, where send_from lands on the Graph sender key — see Microsoft 365 Endpoints.

Optional: a default delay before sending

p_delay_seconds on create_profile()/update_profile() defers every notification on this profile by that many seconds, unless the caller passes an explicit p_run_at — a cooling-off window so a runaway trigger or a fat-fingered bulk send can still be caught before it actually goes out:

SELECT pgrelay_notifier.update_profile('mailer', p_delay_seconds := 30);

New profiles default to no delay (delay_seconds is NULL) — this is opt-in, not something that quietly slows down every send. Resolution happens at dispatch() time: an explicit p_run_at always wins; otherwise the profile's delay_seconds applies (now() + delay_seconds); otherwise it's immediate, exactly as before this setting existed. Because send_mail() and its attachment variants never expose p_run_at at all, they inherit a profile's delay automatically too — no changes needed on their part. Set p_delay_seconds := 0 to clear it back to immediate dispatch — there's no text field to empty-string here, so 0 is the clear sentinel (NULL on update_profile() still means "leave it as it is," matching every other argument).

This creates a cancellable window — pg_relay_notifier itself has no cancel function, because it doesn't own the queue; a delayed notification simply sits in pgrelay.queue, unclaimed, for the configured window, exactly like an explicit p_run_at already behaves — see Deferred, Expiring, and Deduplicated Sends. As of pg_relay v1.2, pg_relay itself provides that cancel primitive:

pgrelay.cancel(p_id bigint) RETURNS TABLE(id bigint, channel text, outcome text, log_id bigint)

p_id is pg_relay's own queue row id, not pg_relay_notifier's notification_id — they're different id spaces. What connects them: dispatch() puts the notification's notification_id::text verbatim into that queue row's payload (the enqueue contract), so a queue row can always be traced back to the notification it came from. For how to locate and call pgrelay.cancel() itself, see pg_relay's own documentation — that function belongs to pg_relay, not this extension.

For a longer maintenance or blackout window than a delay is meant for — the mailbox or relay itself being unavailable — see Pausing and Resuming instead.

---