One-Time Superuser Setup

Connect as a superuser and run the following once, before creating anything as app_user:

psql -U postgres -h localhost -d your_database

-- Create app_user if it does not already exist.
CREATE ROLE app_user WITH LOGIN PASSWORD 'choose-a-strong-password';

-- Allow app_user to call pgrelay functions (required to resolve the pgrelay schema).
GRANT USAGE ON SCHEMA pgrelay TO app_user;

-- Allow app_user to register channels, view the log, and purge.
GRANT EXECUTE ON FUNCTION pgrelay.register(text, text, text, boolean, int, text, text)  TO app_user;
GRANT EXECUTE ON FUNCTION pgrelay.purge(int, int)                           TO app_user;
GRANT EXECUTE ON FUNCTION pgrelay.get(text)                                 TO app_user;
GRANT EXECUTE ON FUNCTION pgrelay.list(boolean)                             TO app_user;

-- Allow app_user to inspect the event queue and audit log directly.
GRANT SELECT ON pgrelay.queue TO app_user;
GRANT SELECT ON pgrelay.log   TO app_user;

-- Allow app_user to call purge_queue (used in the teardown section).
GRANT EXECUTE ON FUNCTION pgrelay.purge_queue(int) TO app_user;

-- PostgreSQL 15+ restricts CREATE on public by default.
GRANT CREATE ON SCHEMA public TO app_user;

Every grant here is exactly what this worked example needs — nothing more. That is a deliberate habit worth keeping in real projects too: grant a role only the specific things it needs to do its job. See The Security Model in the Technical Guide for why pg_relay is built this way throughout.

---

Continue to Building the Example Schema, now connected as app_user.