[midPoint] Midpoint Separate Audit Database Issues

Tue Mar 1 17:03:04 CET 2022

Hi Richard, 

After further review and testing the same upgrade process with a clean
database we saw the same results. Further inspection showed that it was in
fact a file overwrite issue. Upgrade to 4.4.1 has been completed
successfully without any errors. Thank you for your quick responses.

Than you for your time,

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 5:11 PM
To: Haywood, Jeremiah <jhaywo1 at ilstu.edu>
Cc: midPoint General Discussion <midpoint at lists.evolveum.com>
Subject: Re: [midPoint] Midpoint Separate Audit Database Issues

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

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

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 <mailto:jhaywo1 at ilstu.edu> >
To: "Richard Richter" <virgo at evolveum.com <mailto:virgo at evolveum.com> >,
"midPoint General Discussion" <midpoint at lists.evolveum.com
<mailto: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
<mailto:midpoint at lists.evolveum.com> >
Cc: Haywood, Jeremiah <jhaywo1 at ilstu.edu <mailto: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.co
m%2FEvolveum%2Fmidpoint%2Fblob%2Fsupport-4.4%2Fconfig%2Fsql%2Fnative-new%2Fp
ostgres-new-upgrade-audit.sql&data=04%7C01%7C%7Cf1512151f6b54877beba08d9f658
a0e9%7C085f983a0b694270b71d10695076bafe%7C1%7C0%7C637811683793749275%7CUnkno
wn%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI
6Mn0%3D%7C3000&sdata=6kc94IKafm9t97oQy%2BQR3id5HKPPnZOovhGIXtyn590%3D&reserv
ed=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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.evo
lveum.com%2Fmailman%2Flistinfo%2Fmidpoint&data=04%7C01%7C%7Cf1512151f6b54877
beba08d9f658a0e9%7C085f983a0b694270b71d10695076bafe%7C1%7C0%7C63781168379374
9275%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1
haWwiLCJXVCI6Mn0%3D%7C3000&sdata=6KMk%2FFy3fWXNtTOFcaRAjCISWkaM3NvfUdvqQW4mN
w4%3D&reserved=0> https://lists.evolveum.com/mailman/listinfo/midpoint

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20220301/a87e9963/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 7264 bytes
Desc: not available
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20220301/a87e9963/attachment-0001.bin>