[midPoint] Midpoint Separate Audit Database Issues

Wed Feb 23 00:11:14 CET 2022

Hi Jeremiah 

I found one harmless problem with the audit upgrade script - which is the first call: 
call apply_audit_change(0, $$ SELECT 1 $$, true); 

The line was somehow forgotten there - but, luckily, this is something that does not hurt the upgrade process now, because the only change there (#1, support for partitions in the past) is create or replace - hence repeatable. 

I tried to create fresh DB, initialized PUBLIC schema with only the content of postgres-new-audit.sql from version 4.4 (original LTS release) and then tried to apply the upgrade script postgres-new-upgrade-audit.sql from 4.4.1. 

I observed no error and the change was applied. 

Also, trying to call harmless call apply_change(0, $$ SELECT 1 $$); on that DB resulted in error - the procedure is not there, which is expected. 
But there was no such error running the upgrade script for audit only. 

How do you apply the script? What tools do you use? 

Regards 

Richard 

Software Developer 

Evolveum 

From: "Jeremiah Haywood" <jhaywo1 at ilstu.edu> 
To: "Richard Richter" <virgo at evolveum.com>, "midPoint General Discussion" <midpoint at lists.evolveum.com> 
Sent: Tuesday, February 22, 2022 7:23:06 PM 
Subject: RE: [midPoint] Midpoint Separate Audit Database Issues 

Hi Richard, 

Working with our DBA team they have stated that apply_audit_change() makes a call to the apply_change() procedure. Running the script against the audit table returned with the following: 

ERROR: procedure apply_change(integer, unknown, Boolean) does not exist 

LINE 13: call apply_change(0, $$ SELECT 1 $$, true); 

HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. 

ERROR: procedure apply_change(integer, unknown) does not exist 

LINE 1: call apply_change(1, $aa 

HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. 

Please disregard the “m_org” error, this appeared after attempting to add the apply_change() procedure to the audit table. 

Thank you, 

Jeremiah Haywood 

Lead IAM Administrator 

Office of Technology Solutions | Illinois State University 

Phone Number (309) 438-3829 

From: Richard Richter <virgo at evolveum.com> 
Sent: Tuesday, February 22, 2022 11:09 AM 
To: midPoint General Discussion <midpoint at lists.evolveum.com> 
Cc: Haywood, Jeremiah <jhaywo1 at ilstu.edu> 
Subject: Re: [midPoint] Midpoint Separate Audit Database Issues 

You don't often get email from [ mailto:virgo at evolveum.com | virgo at evolveum.com ] . [ http://aka.ms/LearnAboutSenderIdentification | Learn why this is important ] 

[This message came from an external source. If suspicious, report to [ mailto:abuse at ilstu.edu | abuse at ilstu.edu ] ] 

Hi Jeremiah 

This sounds more like running the wrong script on the wrong DB. There is no apply_change() in the audit upgrade script as seen here: [ https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FEvolveum%2Fmidpoint%2Fblob%2Fsupport-4.4%2Fconfig%2Fsql%2Fnative-new%2Fpostgres-new-upgrade-audit.sql&data=04%7C01%7C%7C7a793c7a11594e104c0c08d9f626084a%7C085f983a0b694270b71d10695076bafe%7C1%7C0%7C637811466862005209%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=EIqprl0cY5NQoC7v7VDQZfB7Th6WhfUWsh8odOnyMUc%3D&reserved=0 | https://github.com/Evolveum/midpoint/blob/support-4.4/config/sql/native-new/postgres-new-upgrade-audit.sql ] 

I also check unlikely possibility of some wrong build, but my local extracted build has the file ...\midpoint-4.4.1\doc\config\sql\native-new\postgres-new-upgrade-audit.sql - also without any apply_change , only apply_audit_change . 

m_org upgrade is in the main upgrade script, so it seems like it was run against the audit database. 

Weren't the scripts overwritten accidentally with one another? Please, check their content as well, and compare them with the original files from the distribution package - of course, just the beginning and end is more than enough. 

Regards 

Richard Richter 

Software Developer 

Evolveum 

From: "midPoint General Discussion" < [ mailto:midpoint at lists.evolveum.com | midpoint at lists.evolveum.com ] > 
To: "midPoint General Discussion" < [ mailto:midpoint at lists.evolveum.com | midpoint at lists.evolveum.com ] > 
Cc: "Jeremiah Haywood" < [ mailto:jhaywo1 at ilstu.edu | jhaywo1 at ilstu.edu ] > 
Sent: Tuesday, February 22, 2022 5:41:27 PM 
Subject: [midPoint] Midpoint Separate Audit Database Issues 

Hello all, 

At ISU we have been pursuing the implementation of auditing to a separate database with Midpoint. We are running into some issues when upgrading from 4.4 to 4.4.1 where it seems that there are procedures referenced in postgres-new-upgrade-audit.sql that only exist in the main database. Has anyone come across similar issues? 

Steps taken so far: 

4.4: 

    * postgres-new.sql on main db 
    * postgres-new-quartz.sql on main db 
    * postgres-new-audit.sql on audit db 

4.4.1: 

    * postgres-new-upgrade.sql on main db 
    * postgres-new-upgrade-audit.sql on audit db 

· 

    * 
        * Ran into issues with procedure apply_change() not existing and relation "m_org" does not exist 

Thank you, 

Jeremiah Haywood 

Lead IAM Administrator 

Office of Technology Solutions | Illinois State University 

Phone Number (309) 438-3829 

_______________________________________________ 
midPoint mailing list 
[ mailto:midPoint at lists.evolveum.com | midPoint at lists.evolveum.com ] 
[ https://lists.evolveum.com/mailman/listinfo/midpoint | https://lists.evolveum.com/mailman/listinfo/midpoint ] 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20220223/764503ff/attachment.htm>