Re: How to find the view modified date and time and user name

On Thu, Jun 6, 2024 at 5:49 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

>
>
> On Thursday, June 6, 2024, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:
>
>>
>> Hello,
>> Today in our environment, we noticed that view is altered by someone.
>> We want to know the date, time and modified user name.
>>
>> Our environment :
>> 1. aws / rds
>> 2. Postgress 13.5
>> 3. database with default configuration is running
>>
>> We have not enabled any additional audit, security on top of default
>> configuration.
>>
>> I have check led aws / rds / Instance / database / logs and events / log
>> / all today's logs
>>
>> and could not find any evidence.
>>
>> Any hint / help will be greatly appreciated.
>>
>>
> If you didn’t take steps to record such information it doesn’t exist.
>

Which is a shame. pg_class (and other relevant catalog tables) should
store created_on, created_by, last_modified_on and last_modified_by.

"But pg_restore does CREATE TABLE!! That's not when you _originally_
created the table."

How often do you run pg_restore? Developers certainly do it a lot, but our
production systems have tables that were created six years ago when we
migrated from 8.4 to 9.6. Is that when they were originally created?
Doesn't matter.

What matters is that the DBA can see "ah, Bob altered table foo last
Thursday at 14:30. Let's check the log file to see what he did."