Technical Tip: How to Remove user from CMDB when Encountering an error

[koolishami]

Description	This article describes how to remove a user from the CMDB when attempting to delete the device(s) results in the following error: 'Error PSQLException: ERROR: update or delete on table "ph_notification" violates foreign key constraint "ph_delivery2notification_fk" on table "ph_delivery" Detail: Key (id)=(XXXXXXXXXXX) is still referenced from table "ph_delivery".'
Scope	FortiSIEM v7.x+.
Solution	Verify User Information in Relevant Tables. Export the ph_user, ph_notification, and ph_delivery tables to review the entries for the id in ph_user: psql -U phoenix phoenixdb -c "SELECT * FROM ph_user;” --csv > /tmp/log/ph_user.csv Note: The --csv > /tmp/log/ option is optional and can be omitted if not needed. Identify the device_id or id (for ph_device) of the target device. This article will use below as an example: id = 0123456789 Verify Associated Entries in Other Tables. Check for related entries for owner_id in ph_notification and ph_delivery tables: psql -U phoenix phoenixdb -c "SELECT * FROM ph_notification;" --csv > /tmp/log/ ph_notification.csv psql -U phoenix phoenixdb -c "SELECT * FROM ph_delivery;" --csv > /tmp/log/ph_delivery.csv Access the PostgreSQL Database. Login to PostgreSQL to execute SQL commands: psql -U phoenix -d phoenixdb Query Entries for the Target Device. Check for rows in ph_notification and ph_delivery where owner_id = 0123456789: SELECT * FROM ph_notification WHERE owner_id = 0123456789; SELECT * FROM ph_delivery WHERE owner_id = 0123456789; Remove Associated Entries. Delete rows in the database to disassociate and clean up the records: Delete entries from ph_notification: DELETE FROM ph_notification WHERE owner_id = 0123456789; Delete the device entry from ph_delivery: DELETE FROM ph_delivery WHERE owner_id = 0123456789; Delete the User from the GUI. Once the above steps are completed, attempt the deletion via the FortiSIEM GUI.