EDB Docs - Postgres Enterprise Manager v10.5 - PEM upgrade best practices and remediation

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.

EDB tracks the recurring failure modes under PEM-6100 and PEM-6103. The product-side fixes ship separately. This page closes the customer-facing gap in the meantime and remains useful afterward as a best-practices reference.

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> (or ALTER ... OWNER TO <new>) on PEM objects. REASSIGN OWNED BY transfers object ownership and preserves existing ACLs, but it doesn't copy the entries in pg_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.sh under 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

Take a backup: pg_dumpall --globals-only and pg_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.

Don't

Don't change ownership of pem, pemdata, or pemhistory schemas or their objects without first completing Pre-upgrade preparation. This includes REASSIGN OWNED BY, ALTER ... OWNER TO, and ALTER ROLE ... RENAME TO.
Don't grant privileges to PUBLIC as a workaround — it exposes PEM internals to all database users. Grant only to pem_user, pem_admin, and pem_agent.
Don't modify PEM-managed tables, views, functions, or triggers directly.

During the upgrade

Run configure-pem-server.sh as 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

Run the verification in Post-upgrade verification.
Watch /var/log/pem/worker.log for two minutes after starting services.
Confirm all monitored servers transition to UP in the web UI.

Don't

Don't ignore permission denied errors in worker.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.sh as new_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.

PEM upgrade best practices and remediation v10.5

How PEM upgrades preserve permissions

Check your cluster before upgrading

Dos and don'ts

Before the upgrade

During the upgrade

After the upgrade

Pre-upgrade preparation

Step 1 — Stop PEM services

Step 2 — Transfer ownership (only if changing ownership)

Step 3 — Register default privileges for the upgrade role

Step 4 — Revoke orphaned defaults from the old role (optional, only if you changed ownership)

Step 5 — Schema USAGE safety net

Step 6 — Re-run the verification query and upgrade

Post-upgrade verification

Remediation for already-affected clusters

Example: upgrade with an ownership change

← Prev

↑ Up

Next →