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

[M Sarwar <sarwarmd02@xxxxxxxxxxx>]

Hi Ron,

I am not using pg_restore while doing this exercise.

Today some change is noticed by system architecture. This guy is working like auditor or project manager. I need to just answer his questions 🙂

This view is created long time ago but someone might have dropped and recreated which is a possible scenario.

Thanks,

Sarwar

---

From: Ron Johnson <ronljohnsonjr@xxxxxxxxx>
Sent: Thursday, June 6, 2024 8:47 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: 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.g.johnston@xxxxxxxxx> wrote:

On Thursday, June 6, 2024, M Sarwar <sarwarmd02@xxxxxxxxxxx> 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."