This page helps you plan, run, verify, and — if necessary — repair a PEM upgrade end-to-end. It explains why permissions can be lost during an upgrade, lists the operations that trigger the loss, and provides ready-to-run SQL for both prevention and repair.
How PEM upgrades preserve permissions
During an upgrade, PEM drops and re-creates several internal views and functions in the pem, pemdata, and pemhistory schemas — most notably probe_target_view, probe_schedule_view, avail_agents, avail_servers, and a handful of functions whose signatures change between versions. These objects must remain readable by the PEM worker roles pem_user, pem_admin, and pem_agent for monitoring to keep working.
PEM relies on PostgreSQL's ALTER DEFAULT PRIVILEGES to apply the standard grants automatically each time one of these objects is re-created. PostgreSQL stores these rules in pg_default_acl, keyed by the role that registered them — normally the role that ran the original PEM install (often enterprisedb or postgres).
A PEM upgrade can lose grants in exactly two situations:
- Owner change. You ran
REASSIGN OWNED BY <old> TO <new>(orALTER ... OWNER TO <new>) on PEM objects.REASSIGN OWNED BYtransfers object ownership and preserves existing ACLs, but it doesn't copy the entries inpg_default_acl— those stay attached to<old>. Future objects created by<new>(such as views re-created during the upgrade) inherit no grants. - Cross-role upgrade. You're running
configure-pem-server.shunder a different OS/database user than the one that originally installed PEM. The default-privilege rules registered at install time don't fire for a different role.
In both cases the upgrade completes successfully, but the worker log immediately reports:
ERROR: permission denied for view probe_schedule_view ERROR: permission denied for view probe_target_view ERROR: permission denied for function create_default_server_alerts
and all monitored servers show DOWN in the PEM web UI.
Check your cluster before upgrading
Run as a PostgreSQL superuser on the PEM backend database:
SELECT DISTINCT defaclrole::regrole AS defaults_bound_to, defaclnamespace::regnamespace AS schema FROM pg_default_acl WHERE defaclnamespace::regnamespace::text IN ('pem','pemdata','pemhistory');
If the role returned matches the role that will run configure-pem-server.sh, the upgrade is safe to run as-is. If it doesn't, work through Pre-upgrade preparation first.
Dos and don'ts
Before the upgrade
Do
- Take a backup:
pg_dumpall --globals-onlyandpg_dump -Fc -d pem. - Note the OS user and PostgreSQL role that originally installed PEM — you'll run the upgrade as that role.
- Run the verification query above.
- Test the upgrade in a non-production environment first if possible.
- If any servers or agents are currently in an active maintenance blackout, and you're upgrading from PEM 10.4 or earlier, there are changes to existing blackouts and agent compatibility to consider. See Blackout changes when upgrading from PEM 10.4 or earlier for more information.
Don't
- Don't change ownership of
pem,pemdata, orpemhistoryschemas or their objects without first completing Pre-upgrade preparation. This includesREASSIGN OWNED BY,ALTER ... OWNER TO, andALTER ROLE ... RENAME TO. - Don't grant privileges to
PUBLICas a workaround — it exposes PEM internals to all database users. Grant only topem_user,pem_admin, andpem_agent. - Don't modify PEM-managed tables, views, functions, or triggers directly.
During the upgrade
Do
- Run
configure-pem-server.shas the role for which default privileges are registered. - Watch the script output; stop and investigate any errors before continuing.
Don't
- Don't run the upgrade under a different OS or database user than the one that installed PEM (unless you've completed Pre-upgrade preparation for the new role).
- Don't interrupt the script or run other DDL on the PEM backend during the upgrade.
After the upgrade
Do
- Run the verification in Post-upgrade verification.
- Watch
/var/log/pem/worker.logfor two minutes after starting services. - Confirm all monitored servers transition to UP in the web UI.
Don't
- Don't ignore
permission deniederrors inworker.log— they mean grants were lost and need repair. See Remediation for already-affected clusters. - Don't add new agents or probes until verification passes.
Pre-upgrade preparation
Apply this section only if the pre-upgrade check shows a mismatch — that is, you're changing the PEM owner, or running the upgrade under a different role than the install. Run all SQL as a PostgreSQL superuser. All blocks are idempotent.
Step 1 — Stop PEM services
sudo systemctl stop pemagent sudo systemctl stop pemserver
Step 2 — Transfer ownership (only if changing ownership)
REASSIGN OWNED BY <old_owner> TO <new_owner>; GRANT <old_owner> TO <new_owner>; -- lets <new_owner> inherit role memberships
Step 3 — Register default privileges for the upgrade role
Substitute <upgrade_role> with the role that will run configure-pem-server.sh.
-- Schema: pem ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT SELECT ON TABLES TO pem_user; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT ALL ON TABLES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT SELECT ON TABLES TO pem_agent; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT USAGE ON SEQUENCES TO pem_user; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT ALL ON SEQUENCES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT EXECUTE ON FUNCTIONS TO pem_user; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT ALL ON FUNCTIONS TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pem GRANT EXECUTE ON FUNCTIONS TO pem_agent; -- Schema: pemdata ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT SELECT ON TABLES TO pem_user; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT ALL ON TABLES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pem_agent; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT ALL ON SEQUENCES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT ALL ON FUNCTIONS TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemdata GRANT EXECUTE ON FUNCTIONS TO pem_agent; -- Schema: pemhistory ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT SELECT ON TABLES TO pem_user; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT ALL ON TABLES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pem_agent; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT ALL ON SEQUENCES TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT ALL ON FUNCTIONS TO pem_admin; ALTER DEFAULT PRIVILEGES FOR ROLE <upgrade_role> IN SCHEMA pemhistory GRANT EXECUTE ON FUNCTIONS TO pem_agent;
Step 4 — Revoke orphaned defaults from the old role (optional, only if you changed ownership)
ALTER DEFAULT PRIVILEGES FOR ROLE <old_owner> IN SCHEMA pem REVOKE ALL ON TABLES, SEQUENCES, FUNCTIONS FROM pem_user, pem_admin, pem_agent; ALTER DEFAULT PRIVILEGES FOR ROLE <old_owner> IN SCHEMA pemdata REVOKE ALL ON TABLES, SEQUENCES, FUNCTIONS FROM pem_user, pem_admin, pem_agent; ALTER DEFAULT PRIVILEGES FOR ROLE <old_owner> IN SCHEMA pemhistory REVOKE ALL ON TABLES, SEQUENCES, FUNCTIONS FROM pem_user, pem_admin, pem_agent;
Step 5 — Schema USAGE safety net
GRANT USAGE ON SCHEMA pem, pemdata, pemhistory TO pem_user, pem_admin, pem_agent;
Step 6 — Re-run the verification query and upgrade
Re-run the query from Check your cluster before upgrading. Confirm that defaults are now bound to <upgrade_role>, then run the upgrade:
sudo /usr/bin/dnf upgrade -y ./edb-pem*.rpm --allowerasing sudo /usr/edb/pem/bin/configure-pem-server.sh
Post-upgrade verification
Run the following as a PostgreSQL superuser on the PEM backend database.
-- Confirm grants on the views most commonly affected. -- Each output should show entries for pem_user, pem_admin, and pem_agent. \dp pem.probe_target_view \dp pem.probe_schedule_view \dp pem.avail_agents \dp pem.avail_servers -- Smoke-test as pem_agent. SET ROLE pem_agent; SELECT count(*) FROM pem.probe_schedule_view; SELECT count(*) FROM pem.probe_target_view; RESET ROLE; -- Sweep for any object pem_user can't read. -- Zero rows = clean. Repeat with 'pem_admin' and 'pem_agent'. SELECT c.relkind, n.nspname || '.' || c.relname AS object FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname IN ('pem','pemdata','pemhistory') AND c.relkind IN ('r','v','m') AND NOT has_table_privilege('pem_user', c.oid, 'SELECT') ORDER BY 1,2;
Start services and watch the log for two minutes:
sudo systemctl start pemserver sudo systemctl start pemagent tail -f /var/log/pem/worker.log
No permission denied messages should appear. In the PEM web UI, confirm all monitored servers transition to UP.
Remediation for already-affected clusters
If the upgrade has already completed and you're seeing permission denied errors for probe_target_view, probe_schedule_view, avail_agents, avail_servers, or create_default_server_alerts, run the in-place repair below as a PostgreSQL superuser. It's idempotent and safe on a healthy cluster:
GRANT USAGE ON SCHEMA pem, pemdata, pemhistory TO pem_user, pem_admin, pem_agent; -- Schema: pem GRANT SELECT ON ALL TABLES IN SCHEMA pem TO pem_user, pem_agent; GRANT ALL ON ALL TABLES IN SCHEMA pem TO pem_admin; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pem TO pem_user, pem_admin, pem_agent; GRANT USAGE ON ALL SEQUENCES IN SCHEMA pem TO pem_user; GRANT ALL ON ALL SEQUENCES IN SCHEMA pem TO pem_admin; -- Schema: pemdata GRANT SELECT ON ALL TABLES IN SCHEMA pemdata TO pem_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pemdata TO pem_agent; GRANT ALL ON ALL TABLES IN SCHEMA pemdata TO pem_admin; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pemdata TO pem_user, pem_admin, pem_agent; GRANT ALL ON ALL SEQUENCES IN SCHEMA pemdata TO pem_admin; -- Schema: pemhistory GRANT SELECT ON ALL TABLES IN SCHEMA pemhistory TO pem_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pemhistory TO pem_agent; GRANT ALL ON ALL TABLES IN SCHEMA pemhistory TO pem_admin; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pemhistory TO pem_user, pem_admin, pem_agent; GRANT ALL ON ALL SEQUENCES IN SCHEMA pemhistory TO pem_admin;
No restart is required — the next probe cycle (within approximately 60 seconds) picks up the new permissions. To prevent the same problem on the next upgrade, also complete Pre-upgrade preparation for the role that will run that upgrade.
Example: upgrade with an ownership change
This is a concrete walkthrough for the most common non-standard scenario. PEM was installed on the source server owned by old_owner; the cluster has been restored on a target server where the corporate standard owner is new_owner, and the goal is to upgrade PEM with new_owner as the final owner.
The ordering is what matters. Run the following steps in this exact order:
- Take a backup (see Dos and don'ts).
- Stop services (step 1 of Pre-upgrade preparation).
- Transfer ownership:
REASSIGN OWNED BY old_owner TO new_owner; GRANT old_owner TO new_owner;(step 2). - Register defaults
FOR ROLE new_owner(step 3). - Revoke orphaned defaults
FOR ROLE old_owner(step 4). - Apply the schema USAGE safety net (step 5).
- Run the RPM upgrade and
configure-pem-server.shasnew_owner(step 6). - Run post-upgrade verification.
- Start services.
If step 4 is skipped, step 7 re-creates pem.probe_target_view and pem.probe_schedule_view as new_owner. PostgreSQL finds no defaults for new_owner in pg_default_acl (they're still bound to old_owner), and the views come up with no grants. The worker log immediately shows permission denied for view ... and Remediation for already-affected clusters is required to recover.