Unable to delete trial data from BI BreedBase Instances

[hkmanching]

Expected Behavior

As a curator, I should be able to delete trial trait data and the trial itself.

Actual Behavior

When trying to delete trial and/or trial trait information, the following error happens:

Error deleting trait:{UNKNOWN}: DBI Exception: DBD::Pg::st execute failed: ERROR: permission denied for schema audit LINE 1: INSERT INTO audit.nd_experiment_phenotype_audit (logged_in_u... ^ QUERY: INSERT INTO audit.nd_experiment_phenotype_audit (logged_in_user, operation, before, transactioncode, is_undo) VALUES ((SELECT max(sp_person_id) FROM logged_in_user WHERE sp_person_id IS NOT NULL), TG_OP, to_jsonb(OLD), (SELECT NOW()::TEXT || txid_current()), FALSE) CONTEXT: PL/pgSQL function nd_experiment_phenotype_audit_trig() line 22 at SQL statement SQL statement "DELETE FROM ONLY "public"."nd_experiment_phenotype" WHERE $1 OPERATOR(pg_catalog.=) "phenotype_id"" [for Statement "DELETE FROM phenotype WHERE phenotype_id IN (766040,766041,766042,766043,766044,766045,766046,766047,766048,766049,766050,766051,766052,766053,766054,766055

---

For Bugs:

Environment

CitrusBase, SoybeanBase

Steps to Reproduce

Navigate to trial. Scroll to bottom and select "Delete Experiment Data" Select "Delete Trait Data" Select traits to delete Error pops up/

[MFlores2021]

@afpowell

[hkmanching]

I tested this is demo breedbase and could delete a test trial with no problem. This looks like a problem in all of the BI Breedbase instances.

[lukasmueller]

Currently, all the schemas are owned by bi_admin, but we run the database using the postgres user. We should probably change the ownership to postgres. Ideally, we should run it using the web_usr user with highly customized permissions, and retire the other roles. Changing roles in the database has a lot of side effects that are not worth the effort imho.

[hkmanching]

Following up on this (and we can bring it up tomorrow as well), would it be very difficult to string up a new (blank) instance of CitrusBase based on the most recent BB vanilla version? I was thinking we could have CitrusBase as a true vanilla BB instance and leave Sugarcane as a BI instance and we can easily compare differences when issues like this come up. I have everything I need to populate the new instance and can do that on my end, and we can delete the BI instance once that is done.

[hkmanching]

Would this also allow for the instance to be updated in line with the other non-BI instances?

[lukasmueller]

Let’s discuss it tomorrow

Get Outlook for iOShttps://aka.ms/o0ukef

---

From: Heather Manching @.> Sent: Tuesday, July 9, 2024 2:28:55 PM To: solgenomics/sgn @.> Cc: Lukas A. Mueller @.>; Assign @.> Subject: Re: [solgenomics/sgn] Unable to delete trial data from BI BreedBase Instances (Issue #4994)

Would this also allow for the instance to be updated in line with the other non-BI instances?

— Reply to this email directly, view it on GitHubhttps://github.com/solgenomics/sgn/issues/4994#issuecomment-2218380302, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAAV7F4TJJXHQFFTEVGJBKTZLQTWPAVCNFSM6AAAAABKH4QQ36VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMJYGM4DAMZQGI. You are receiving this because you were assigned.Message ID: @.***>

[hkmanching]

@lukasmueller any updates on this fix?

[lukasmueller]

It seems this issue can be fixed by running the following command in the database:

REASSIGN OWNED BY  bi_admin TO postgres;

This removes bi_admin ownership from all tables and assigns it to the more standard postgres user.

I will run this on all affected instances.

[lukasmueller]

Heather confirmed that this worked! Closing this ticket :-)