Skip to content

Security and permissions

pg_auto_mv creates all its objects in the pgauto_mv schema. Accessing anything in that schema — by any means, including a fully-qualified name — requires USAGE on the schema itself, regardless of function-level grants. USAGE is only extended to the pgrelay role at install time; no other role has it until you grant it, which is what actually keeps the schema locked down day to day. (Note: individual functions in pgauto_mv otherwise keep PostgreSQL's default EXECUTE-to-PUBLIC grant — refresh_materialised_view_now is the sole exception, see below — so schema USAGE is the real gate here, not per-function revocation.)

Granting access to application and reporting roles

For roles that need to register and manage materialised views, grant schema USAGE first, then the specific functions:

GRANT USAGE ON SCHEMA pgauto_mv                                                             TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.mv_watch(text, text)                                    TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.register_mv(text, text, pgauto_mv.watch_type[], double precision, double precision, double precision, boolean, boolean, boolean, text, pgrelay.schedule[], double precision, double precision, uuid) TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.unregister_mv(text, text)                               TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.update_mv(text, text, double precision, double precision, double precision, boolean, boolean, boolean, boolean, text, boolean, pgauto_mv.watch_type[]) TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.get_mv_status()                                         TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.get_mv(text, text, oid)                                 TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.list_mv(text, text, boolean)                            TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.create_mv_schedule(text, pgrelay.schedule[], double precision, double precision, boolean, uuid) TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.set_mv_schedule_day(text, text, text)                   TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.update_mv_schedule(text, text, pgrelay.schedule[], double precision, double precision) TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.delete_mv_schedule(text)                                TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.chain_mv(text, text)                                    TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.unchain_mv(text)                                        TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.purge_mv_logs(uuid, int, int, int)                      TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.get_mv_refresh_frequency(text, text, double precision)  TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.get_mv_problems(text)                                   TO your_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.get_view_tables(text, text)                             TO your_role;

Use \df pgauto_mv.<function_name> in psql to confirm the exact signature before granting.

Granting access to DBA/maintenance roles

sync_mv() and clean_mv() are catalog-maintenance and recovery tools — not day-to-day application operations. Grant these separately, only to roles that perform administrative maintenance (again, schema USAGE first — a role with only the EXECUTE grants below and no schema USAGE still cannot call either function):

GRANT USAGE ON SCHEMA pgauto_mv                                        TO your_dba_role;
GRANT EXECUTE ON PROCEDURE pgauto_mv.sync_mv(text, text, uuid, boolean) TO your_dba_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.clean_mv(uuid)                      TO your_dba_role;

For reporting roles that need to read audit tables but not manage anything:

GRANT USAGE ON SCHEMA pgauto_mv                TO your_reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA pgauto_mv TO your_reporting_role;

refresh_materialised_view_now — restricted by default

pgauto_mv.refresh_materialised_view_now(p_mv_schema, p_mv_name) has EXECUTE revoked from PUBLIC at install time. Grant it only to roles that genuinely need to trigger on-demand refreshes — ETL processes, operator scripts, or data pipeline jobs:

GRANT USAGE ON SCHEMA pgauto_mv                                              TO your_etl_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.refresh_materialised_view_now(text, text) TO your_etl_role;
GRANT USAGE ON SCHEMA pgauto_mv                                              TO your_ops_role;
GRANT EXECUTE ON FUNCTION pgauto_mv.refresh_materialised_view_now(text, text) TO your_ops_role;

(Omit the USAGE line for a role that already has it from an earlier grant block above — it's harmless to repeat, GRANT is idempotent.)

Superusers always have access.

register_mv() calls refresh_materialised_view_now() internally (when p_populate_if_empty = true, the default, and the MV is unpopulated at registration). Because register_mv() is SECURITY DEFINER, this internal call would otherwise bypass the PUBLIC revoke above for any role granted EXECUTE on register_mv() alone. register_mv() guards against this explicitly: it only makes the internal call when the calling role (session_user) itself holds EXECUTE on refresh_materialised_view_now(text, text). A role with EXECUTE on register_mv() but not on refresh_materialised_view_now() can still register a view, but it will not auto-populate — the view stays unpopulated until a watch-table event, a schedule, or a role that does hold the grant calls refresh_materialised_view_now() explicitly. Grant both together to a role if you want it to have the auto-populate behavior.

Optional: locking down the default PostgreSQL access model (advanced)

Everything in this section is optional. pg_auto_mv works correctly, and securely, without you doing any of this — it exists for DBAs who want an extra layer of defence-in-depth beyond what pg_auto_mv ships with by default. Read the whole section before running anything in it, since it can break access for roles you didn't intend to affect if you skip the checking step.

Some PostgreSQL background, if you're new to this

When PostgreSQL creates a function (CREATE FUNCTION) or procedure, it automatically lets every role that can connect to the database call it — this is a built-in PostgreSQL default, not something pg_auto_mv turned on deliberately. In PostgreSQL, this "everyone" group is called PUBLIC. Unless something explicitly takes that permission away (REVOKE ... FROM PUBLIC), it stays in place forever, for every function, even ones you never intended outsiders to call directly.

pg_auto_mv has not removed this default for almost all of its functions — refresh_materialised_view_now() is the one deliberate exception (see above). So, strictly speaking, any role that can connect to your database technically has EXECUTE on things like register_mv(), sync_mv(), clean_mv(), and so on.

This sounds alarming, but it currently isn't, because of a second, separate permission PostgreSQL requires: to reach anything inside the pgauto_mv schema — even a function it's technically allowed to call — a role also needs USAGE on the schema itself. That's a completely different, independent permission, and PostgreSQL does not grant it to PUBLIC by default the way it grants function EXECUTE. Only the pgrelay role (plus the extension owner and superusers) has schema USAGE out of the box. So today, PUBLIC's function-level access is real but functionally inert for everyone except roles you've deliberately given schema access to — and any role you have given schema access to, you did so on purpose, following the GRANT blocks earlier in this section.

In short: the current setup is safe, but it relies on USAGE ON SCHEMA being the only real gate, rather than every function individually being locked down the way refresh_materialised_view_now() is. If you want the stricter, "closed by default, opened only where I said so" posture instead — matching how pg_relay itself is built — follow the steps below.

Step 1 — check who this would affect, before changing anything

This query lists every role (other than pgrelay, the extension owner, and superusers) that currently has schema access — these are the only roles that could possibly be affected by tightening things, since every other role can't reach the schema at all today regardless of function-level grants:

SELECT rolname
FROM pg_roles
WHERE has_schema_privilege(rolname, 'pgauto_mv', 'USAGE')
  AND rolname NOT IN ('pgrelay')
  AND NOT rolsuper
ORDER BY rolname;

For each role that query returns, confirm it already has the specific GRANT EXECUTE statements it needs from the blocks earlier in this section (e.g. if you gave your_role schema access so it could call register_mv(), check that you also ran the GRANT EXECUTE ON FUNCTION pgauto_mv.register_mv(...) TO your_role; line for it, not just the schema USAGE line). This query shows you exactly what a given role can currently call:

-- Replace 'your_role' with the role you're checking
SELECT p.proname, pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'pgauto_mv'
  AND has_function_privilege('your_role', p.oid, 'EXECUTE')
ORDER BY p.proname;

If a role in your Step 1 list is missing an explicit grant for something it actually needs to call, go back to the GRANT blocks earlier in this section and run the missing lines for that role before proceeding to Step 2. If you skip this and a role was relying on the open-by-default access rather than an explicit grant, Step 2 will break it — it will start getting permission denied errors on functions that worked a moment ago.

Step 2 — remove the default open access

Once every role that needs access has its own explicit grants confirmed:

REVOKE EXECUTE ON ALL FUNCTIONS  IN SCHEMA pgauto_mv FROM PUBLIC;
REVOKE EXECUTE ON ALL PROCEDURES IN SCHEMA pgauto_mv FROM PUBLIC;

This does not affect pgrelay or any role with its own explicit GRANT EXECUTE statement — those are separate, independent permissions that a REVOKE ... FROM PUBLIC does not touch. It only removes access from roles that were relying on the default alone.

Step 3 — the ongoing maintenance this creates

This is the part that's easy to miss: every time register_mv() runs (or update_mv() adds new watch tables, or clean_mv() rebuilds triggers), pg_auto_mv generates a brand new trigger function behind the scenes — and like any newly created PostgreSQL function, that new one gets the open-to-PUBLIC default all over again. The REVOKE you ran in Step 2 only affected functions that existed at that moment; it has no effect on functions created afterward.

If you've chosen to harden this schema, you now own an ongoing task: periodically re-run the same REVOKE statements to close the gap on any new trigger functions created since. It's safe to run repeatedly — revoking a permission that's already revoked is a harmless no-op:

REVOKE EXECUTE ON ALL FUNCTIONS  IN SCHEMA pgauto_mv FROM PUBLIC;
REVOKE EXECUTE ON ALL PROCEDURES IN SCHEMA pgauto_mv FROM PUBLIC;

Run this after registering new materialised views, or on a regular schedule (a cron job, or a periodic job through pg_relay itself) — whatever fits your operational routine. There is no automatic hook in pg_auto_mv that does this for you; it is a manual (or DBA-scheduled) commitment you're taking on by choosing this stricter posture.

Tables that must NOT be directly written by application roles

These tables store node-local operational state managed entirely by pg_auto_mv:

  • pgauto_mv.mv_state
  • pgauto_mv.mv_events
  • pgauto_mv.mv_refresh_log
  • pgauto_mv.mv_audit_history
  • pgauto_mv.mv_chains
  • pgauto_mv.materialised_views
  • pgauto_mv.watch_tables
  • pgauto_mv.sync_log

Do not grant INSERT/UPDATE/DELETE on these to application roles.

The pg_relay role

The pg_relay binary connects to your database as a specific role. That role needs EXECUTE on the functions registered in pgrelay.actions:

  • pgauto_mv._process_pending(uuid)
  • CALL pgauto_mv.sync_mv(uuid) (procedure)

All functions are SECURITY DEFINER, so pg_relay does not need the privileges of the materialised view owner. See the pg_relay USER_GUIDE for role setup and connection configuration.