How can I make the modified by columns for objects show the user that modified it and not the owner
How can I make the modified by columns for objects show the user that modified it and not the owner of the object? For instance, I have a table named foo that is owned by enterprisedb user in the xyz shema. I log in as user 'simple' and perform a delete on a row from table foo and it completes. When I look at the table info is shows modified by enterprisedb instead of being modified by 'simple'. Is there a parameter that can be changed or something I can do to change that so that we can tell who made what changes in the database?
Accepted Solutions (0)
There are no existing statistics table or view which maintains this information on the database.
Following are the ways from which you can achieve this :
- Logging :
Setting the logging parameters which will collect the details into the database logs :
i. logging_collector -- This will enable logging
ii. log_line_prefix -- What all to log
iii. log_statement -- Which statements to log
Note: This change will cause extra logging. Please make sure adequate disk space is available.
- Creating Triggers :
i. Create an audit table to maintain such records.
ii. Create a function which will collect the user name for the respective operation and will insert it into the audit table.
iii. Create a trigger on the audit table which will invoke the function created above for DML operations.
Note: Please test the trigger creation method is on test environment prior to applying on production, as it might cause the slowness for complex queries.