Bug 1837207

engine-vacuum actually calls PG's vacuumdb utility 
I checked running directly vacuumdb with user 'engine' on the 'engine' db 

/usr/bin/vacuumdb -d engine -U engine

vacuumdb: error: vacuuming of table "pg_temp_6.tt_vm_pool_running" in database "engine" failed: ERROR:  permission denied for schema pg_temp_6

googling this a bit I found that [1]

So, this seems as a PG regression that forces vacuumdb to use postgres admin user to perform vacuum 

[1] https://github.com/greenplum-db/gpdb/pull/9304

(Originally by Eli Mesika)

It seems like orphan schemas are left in the database , those schemas are not owned by the 'engine' role and can not be dropped by it 

engine=> select nspname from pg_namespace where nspname like 'pg_temp%';
  nspname   
------------
 pg_temp_1
 pg_temp_11
 pg_temp_12
 pg_temp_8
 pg_temp_6
 pg_temp_3
 pg_temp_14
 pg_temp_13
 pg_temp_4
 pg_temp_7
(10 rows)

The drop statements for those orphan schemas can be extracted to a sql script by;

 psql -U engine -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%') as foo" engine > drop_orphan_schemas.sql

Then we can run psql to execute this script before running vacuumdb , but still , the sql script must run from psql with the 'postgres' user

(Originally by Eli Mesika)

This bug report has Keywords: Regression or TestBlocker.
Since no regressions or test blockers are allowed between releases, it is also being identified as a blocker for this release. Please resolve ASAP.

(Originally by pm-rhel)

The documentation text flag should only be set after 'doc text' field is provided. Please provide the documentation text and set the flag to '?' again.

(Originally by pm-rhel)

This paragraph should be added to the release doc:


For remote vacuuming if you got errors like 'permission denied for schema pg_temp_XX' please do the following:
     1) log in into the remote database machine
     2) run
           psql  -U <db-admin-role> -Atc \"select 'drop schema if exists ' || nspname || ' cascade;'
           from (select distinct nspname from pg_class join pg_namespace on (relnamespace=pg_namespace.oid)
           where pg_is_other_temp_schema(relnamespace)) as foo\" <engine database name > <temporary file>
     3) run
           psql  -U <db-admin-role> -f <temporary file>
     4) try to run engine-vacuum again"

(Originally by Eli Mesika)

(In reply to Eli Mesika from comment #5)
> This paragraph should be added to the release doc:
> 
> 
> For remote vacuuming if you got errors like 'permission denied for schema
> pg_temp_XX' please do the following:
>      1) log in into the remote database machine
>      2) run
>            psql  -U <db-admin-role> -Atc \"select 'drop schema if exists '
> || nspname || ' cascade;'
>            from (select distinct nspname from pg_class join pg_namespace on
> (relnamespace=pg_namespace.oid)
>            where pg_is_other_temp_schema(relnamespace)) as foo\" <engine
> database name > <temporary file>
>      3) run
>            psql  -U <db-admin-role> -f <temporary file>
>      4) try to run engine-vacuum again"

Steven, we should also add this as a step into 4.4 upgrade guide to upgrade remote database chapters. Probably after restoring 4.3 database backup and upgrading this database from 10.6 to 12 and before running engine-setup.

(Originally by Martin Perina)

OK. I added this comment to bug 1802650.

(Originally by Steve Goodman)

Currently we are leaving temporary tables on postgresql and it removes them as needed. Unfortunately it's not frequent enough and those temporary tables (respectively temporary tablespaces where those tables are stored) cause a permissions issue on PostgreSQL 12 when we try to run vacuumdb (vacuumdb needs to be executed under postgres user, which is not possible for remote databases). To minimize that issue we are going to backport changes in RHV database scripts to drop temporary tables at the end of transaction, so there's a good chance that existing temporary tables are already removed by PostgreSQL before RHV 4.3 setup is going to be upgraded to 4.4

$ git tag --contains 6cb2a16e51c52e4f21b068a535916052983ab48d
ovirt-engine-4.3.11

Verified on ovirt-engine-4.3.11.2-0.1.el7.noarch

Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory (Red Hat Virtualization Engine security, bug fix 4.3.11), and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2020:4112