Katello-nightly-rpm-pipeline 2086 failed

[jenkins]

Katello nightly pipeline failed:

https://ci.theforeman.org/job/katello-nightly-rpm-pipeline/2086/

foreman-pipeline-katello-rpm-nightly (failed) (remote job)

[ekohl]

On upgrades it shows

PG::InsufficientPrivilege: ERROR: must be owner of extension evr

This is caused by:

Fixes #37678 - Add postgresql-evr as a migration

I recall this was a change between PostgreSQL 12 and 13 where 12 required admin while 13 doesn’t. Note that it fails on EL 8 (which defaults to PostgreSQL 12) and not on EL 9 (default 13). It suggests to me that your PostgreSQL upgrade is not happening in our pipelines, but haven’t verified that.

cc @sajha

[sajha]

Are we working around this when enabling the extension? I guess I don’t understand how installer doesn’t have privileges on the local DB?

[ekohl]

@sajha

I guess I don’t understand how installer doesn’t have privileges on the local DB?

The line that fails is the DROP EXTENSION and the migration runs as the foreman user.

But there is a difference between PostgreSQL 12 and 13. Quoting PostgreSQL: Documentation: 12: CREATE EXTENSION

Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension’s script.

Then PostgreSQL: Documentation: 13: CREATE EXTENSION

The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, and normally also becomes the owner of any objects created by the extension’s script.

Loading an extension ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed. However, if the extension is marked trusted in its control file, then it can be installed by any user who has CREATE privilege on the current database. In this case the extension object itself will be owned by the calling user, but the contained objects will be owned by the bootstrap superuser (unless the extension’s script explicitly assigns them to the calling user). This configuration gives the calling user the right to drop the extension, but not to modify individual objects within it.

When I dump the DB on the upgraded EL 8 I see this:

foreman=# SELECT * FROM pg_extension ;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13422 | plpgsql |       10 |           11 | f              | 1.0        |           | 
 16910 | evr     |       10 |         2200 | t              | 0.0.2      |           | 
(2 rows)
foreman=# SELECT * FROM pg_user;
  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 foreman   |    18269 | f           | f        | f       | f            | ******** |          | 
 postgres  |       10 | t           | t        | t       | t            | ******** |          | 
 pulp      |    24014 | f           | f        | f       | f            | ******** |          | 
 candlepin |    16384 | f           | f        | f       | f            | ******** |          | 
(4 rows)

In other words, the PostgreSQL 12 → 13 migration made the postgres user the owner of the evr extension. I’m guessing on EL9 where it installs PostgreSQL 13 from the start it has foreman as the owner so it can be dropped.

Looking at PostgreSQL: Documentation: 13: ALTER EXTENSION I don’t see a way to change this.

[ekohl]

Adding context from chat: It may be needed to change the owner. Thread: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ? : Postgres Professional talks about how there is no SQL statement to do this and there are subtle nuances. PostgreSQL: Documentation: 13: REASSIGN OWNED may work.