0041631: update.database sometimes fails when tad is not a superuser as 2 operators have wrong owner

Description

Sometimes update.database fails when being run in a system which as tad not being a superuser (default when using Appliance 18.04):

Error is as follows:
     [java] SELECT * FROM drop_operator('+'::varchar,'timestamptz'::varchar,'numeric'::varchar)
     [java] 62602 [main] DEBUG org.apache.ddlutils.platform.PGStandardBatchEvaluator - About to execute SQL -- END
     [java]
     [java] CREATE OPERATOR +(
     [java] PROCEDURE = "add_days",
     [java] LEFTARG = timestamptz,
     [java] RIGHTARG = numeric,
     [java] COMMUTATOR = +)
     [java] 62603 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: must be owner of operator +

Checking operator owners in a example database which fails shows 2 operators in public schema with wrong owner (not tad)
+ | b | public | postgres
+ | b | public | postgres
select op.oprname, op.oprkind, n.nspname, r.rolname from pg_operator op, pg_namespace n, pg_roles r where op.oprnamespace = n.oid and op.oprowner = r.oid;

As is already excluding operators created by pg_tgrm extension.

\do+ output in psql is database having the error shows (only showing lines related to this bug):

public | + | numeric | date | - | - |
public | + | numeric | timestamp with time zone | - | - |

Comparing that with pristine pi database shows:
public | + | date | numeric | date | public.add_days |
public | + | numeric | date | - | - |
public | + | numeric | timestamp with time zone | - | - |
public | + | timestamp with time zone | numeric | date | public.add_days |

Context:
- Those operators are created in prescript-postgresql running as user tad (not postgres)
- Bug only shows with the operators related to add_days functions
- There 2 functions with different signature involved

Possible explanation:
The operators related to add_days are the only ones defining a COMMUTATOR=+
Apparently the is creating (or at least showing in \do+) extra variants with the function arguments swapped.
In \do+ those are shown without the Function column showing add_days
When update.database fails the 'drop operator' seems to have suceeded to removal the initial operator entry with the Function=add_days shown. But apparently the extra commutated entry stayed.

So best guess -> Those extra commutated entries somehow got created (probably in the past) with wrong owner postgres.
If those are present with wrong owner -> This bug happens

Note:
- This issue shows typically on Appliance 18.04 migration as there 'tad' user drops is superuser privileges by default (intentionally)
- This issue was also seen in openbravo cloud when using pg_upgrade instead of typical pg_backup/pg_restore
  -> So apparently unrelated to how database migration is done

Steps To Reproduce

Unknown still:
See description for best info available.

Proposed Solution

Follow up best guess in description and try to understand in which exact case operator apparently gets created with wrong owner.
When understood depending on impact (number of instance affected) either document as known issue with known workaround or try to add code to automatically fix it.

Issue History
Date Modified	Username	Field	Change
2019-08-12 12:38	shuehner	New Issue
2019-08-12 12:38	shuehner	Assigned To	=> platform
2019-08-12 12:38	shuehner	Modules	=> Core
2019-08-12 12:38	shuehner	Triggers an Emergency Pack	=> No
2019-08-12 12:38	shuehner	Note Added: 0113945
2022-02-01 07:24	alostale	Assigned To	platform => Triage Platform Base

Notes
(0113945) shuehner (administrator) 2019-08-12 12:38	Known workaround: fix the owner of the 2 operators manually once. alter operator + (numeric, timestamptz) owner to tad; alter operator + (numeric, date) owner to tad;

Relationships [ Relation Graph ] [ Dependency Graph ]