pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find the view modified date and time and user name
23+ messages / 8 participants
[nested] [flat]
* How to find the view modified date and time and user name
@ 2024-06-06 21:06 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 2 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-06 21:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Sarwar
Sent from my Galaxy
^ permalink raw reply [nested|flat] 23+ messages in thread
* RE: How to find the view modified date and time and user name
@ 2024-06-06 21:11 M Sarwar <sarwarmd02@outlook.com>
parent: M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 23+ messages in thread
From: M Sarwar @ 2024-06-06 21:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Some additional information:-
select relfilenode from pg_class where relname = 'vw_tab_mcm_net_temp_yield';
is giving the following output
0
0
-------- Original message --------
From: M Sarwar <sarwarmd02@outlook.com>
Date: 6/6/24 5:06 PM (GMT-05:00)
To: pgsql-admin@lists.postgresql.org
Subject: How to find the view modified date and time and user name
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.
Sarwar
Sent from my Galaxy
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-06 21:48 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 23+ messages in thread
From: David G. Johnston @ 2024-06-06 21:48 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Some additional information:-
>
> select relfilenode from pg_class where relname =
> 'vw_tab_mcm_net_temp_yield';
>
>
>
> is giving the following output
>
> 0
>
> 0
>
Views don’t have underlying files since they don’t have anything to store.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-06 21:49 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
1 sibling, 2 replies; 23+ messages in thread
From: David G. Johnston @ 2024-06-06 21:49 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.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.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 00:47 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 2 replies; 23+ messages in thread
From: Ron Johnson @ 2024-06-07 00:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Jun 6, 2024 at 5:49 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
>
>
> On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.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."
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 01:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 23+ messages in thread
From: Tom Lane @ 2024-06-07 01:14 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> 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."
I'm not finding that argument terribly convincing. If you have a
DDL log file, you can grep it to find the last change (and the
ones before that, in case it was Alice's fault not Bob's). If
you don't have such a log file, how much does a last-changed
timestamp really help you?
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 01:40 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 23+ messages in thread
From: Ron Johnson @ 2024-06-07 01:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Jun 6, 2024 at 9:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > 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."
>
> I'm not finding that argument terribly convincing. If you have a
> DDL log file, you can grep it to find the last change (and the
> ones before that, in case it was Alice's fault not Bob's). If
> you don't have such a log file, how much does a last-changed
> timestamp really help you?
>
1. That's not terribly helpful if it was altered three weeks ago, but you
only keep two weeks of log files.
2. "I'm telling you, PHB, that table hasn't been modified in the past two
years. See? Says so right here in the database."
3. "What happened to the index that's needed for the monthly reports?"
Bottom line: sometimes, "everyone else does it" for very good and important
reasons that are *vital* but rare.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 03:34 M Sarwar <sarwarmd02@outlook.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-07 03:34 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
David,
Thanks for the update!
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Thursday, June 6, 2024 5:49 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to find the view modified date and time and user name
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.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.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 03:40 M Sarwar <sarwarmd02@outlook.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-07 03:40 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@gmail.com>
Sent: Thursday, June 6, 2024 8:47 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@gmail.com<mailto:david.g.johnston@gmail.com>> wrote:
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.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."
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 03:44 M Sarwar <sarwarmd02@outlook.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-07 03:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Tom,
I do not have DDL logs.
Are you saying that I should have manually maintain it or are you referring to any existing logs on the database side?
This is taken very seriously by our architect. This guy behaves like everything like auditor, Project Manager or whatever we can think of. 🙂
Thanks,
Sarwar
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, June 6, 2024 9:14 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to find the view modified date and time and user name
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> 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."
I'm not finding that argument terribly convincing. If you have a
DDL log file, you can grep it to find the last change (and the
ones before that, in case it was Alice's fault not Bob's). If
you don't have such a log file, how much does a last-changed
timestamp really help you?
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 03:54 M Sarwar <sarwarmd02@outlook.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-07 03:54 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Ron,
Please see my comments below in your email.
Actually, I am also shocked to see the difference in the view code. I am saying this because suspected guy does not have write privileges on view. I need to find the evidence in either case.
Thanks,
Sarwar
OCP DBA ( 12c, 11g, 10g, 9i, 8i, 8, 7),
Oracle 10g Beta Version Evaluator,
OEM Imp Spcl,
OCI DBA 19 Ver., etc.
________________________________
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Thursday, June 6, 2024 9:40 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to find the view modified date and time and user name
On Thu, Jun 6, 2024 at 9:14 PM Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
Ron Johnson <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>> writes:
> 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."
I'm not finding that argument terribly convincing. If you have a
DDL log file, you can grep it to find the last change (and the
ones before that, in case it was Alice's fault not Bob's). If
you don't have such a log file, how much does a last-changed
timestamp really help you?
1. That's not terribly helpful if it was altered three weeks ago, but you only keep two weeks of log files.
2. "I'm telling you, PHB, that table hasn't been modified in the past two years. See? Says so right here in the database."
3. "What happened to the index that's needed for the monthly reports?"
Bottom line: sometimes, "everyone else does it" for very good and important reasons that are *vital* but rare. ---> This phrase is true in this case.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 04:11 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
2 siblings, 1 reply; 23+ messages in thread
From: Kashif Zeeshan @ 2024-06-07 04:11 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Sarwar
If you have WAL Files then you can decode them but that will be a tedious
task.
Regards
Kashif Zeeshan
On Fri, Jun 7, 2024 at 8:44 AM M Sarwar <sarwarmd02@outlook.com> wrote:
> Hi Tom,
> I do not have DDL logs.
> Are you saying that I should have manually maintain it or are you
> referring to any existing logs on the database side?
> This is taken very seriously by our architect. This guy behaves like
> everything like auditor, Project Manager or whatever we can think of. 🙂
> Thanks,
> Sarwar
>
>
> ------------------------------
> *From:* Tom Lane <tgl@sss.pgh.pa.us>
> *Sent:* Thursday, June 6, 2024 9:14 PM
> *To:* Ron Johnson <ronljohnsonjr@gmail.com>
> *Cc:* pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> *Subject:* Re: How to find the view modified date and time and user name
>
> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > 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."
>
> I'm not finding that argument terribly convincing. If you have a
> DDL log file, you can grep it to find the last change (and the
> ones before that, in case it was Alice's fault not Bob's). If
> you don't have such a log file, how much does a last-changed
> timestamp really help you?
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 04:14 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
2 siblings, 2 replies; 23+ messages in thread
From: David G. Johnston @ 2024-06-07 04:14 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> I do not have DDL logs.
>
>
Then you don’t have any evidence. But you now know what you need to work
on for the future. Until then, restore the view to what it should be and
call it a lesson learned. And double-check your role memberships,
especially for roles that own objects.
Feel free to help Ron build consensus that additional metadata is desired -
though you’d still have to find someone willing to write the patches.
Also, 13.5 is long out of support…
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 04:17 David G. Johnston <david.g.johnston@gmail.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2024-06-07 04:17 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; +Cc: M Sarwar <sarwarmd02@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thursday, June 6, 2024, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
>
> If you have WAL Files then you can decode them but that will be a tedious
> task.
>
I’m doubtful WAL contains role information.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 04:25 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Kashif Zeeshan @ 2024-06-07 04:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: M Sarwar <sarwarmd02@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, Jun 7, 2024 at 9:17 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Thursday, June 6, 2024, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
>>
>> If you have WAL Files then you can decode them but that will be a tedious
>> task.
>>
>
> I’m doubtful WAL contains role information.
>
Thanks for this information David.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 04:29 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Ron Johnson @ 2024-06-07 04:29 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Jun 7, 2024 at 12:14 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
[snip]
> though you’d still have to find someone willing to write the patches.
>
That's always the sticky wicket.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 13:01 Rui DeSousa <rui.desousa@icloud.com>
parent: M Sarwar <sarwarmd02@outlook.com>
2 siblings, 1 reply; 23+ messages in thread
From: Rui DeSousa @ 2024-06-07 13:01 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Jun 6, 2024, at 11:44 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Hi Tom,
> I do not have DDL logs.
> Are you saying that I should have manually maintain it or are you referring to any existing logs on the database side?
> This is taken very seriously by our architect. This guy behaves like everything like auditor, Project Manager or whatever we can think of. 🙂
> Thanks,
> Sarwar
>
A simple solution I used was to do a schema only dump daily and check in any changes into a git repo via a cronjob Then the repo will track schema changes.=
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-07 15:35 Muhammad Ikram <mmikram@gmail.com>
parent: Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 1 reply; 23+ messages in thread
From: Muhammad Ikram @ 2024-06-07 15:35 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: M Sarwar <sarwarmd02@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi M Sarwar,
If you have some dumps of the database in near past, then you may analyze
those and note till what time view was in original state (May be narrowing
down on things)
As some geek said above, this incident can serve as a lesson learnt and,
for future you can configure pgAudit to capture all DDLs or set a log level
that captures ddls e.g. log_statement = ddl
Regards,
Muhammad Ikram
Bitnine
On Fri, Jun 7, 2024 at 6:01 PM Rui DeSousa <rui.desousa@icloud.com> wrote:
>
>
> On Jun 6, 2024, at 11:44 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Hi Tom,
> I do not have DDL logs.
> Are you saying that I should have manually maintain it or are you
> referring to any existing logs on the database side?
> This is taken very seriously by our architect. This guy behaves like
> everything like auditor, Project Manager or whatever we can think of. 🙂
> Thanks,
> Sarwar
>
>
> A simple solution I used was to do a schema only dump daily and check in
> any changes into a git repo via a cronjob Then the repo will track schema
> changes.
>
--
Muhammad Ikram
^ permalink raw reply [nested|flat] 23+ messages in thread
* RE: [EXTERNAL] Re: How to find the view modified date and time and user name
@ 2024-06-07 17:29 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-06-07 17:29 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Why do you expect the database to do something globally. This is an open source platform with vastly different use cases.
If you need detailed stats on changes, you can build _history tables that track this.
Google will get you there.
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Thursday, June 6, 2024 6:40 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: How to find the view modified date and time and user name
On Thu, Jun 6, 2024 at 9:14 PM Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
Ron Johnson <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>> writes:
> 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."
I'm not finding that argument terribly convincing. If you have a
DDL log file, you can grep it to find the last change (and the
ones before that, in case it was Alice's fault not Bob's). If
you don't have such a log file, how much does a last-changed
timestamp really help you?
1. That's not terribly helpful if it was altered three weeks ago, but you only keep two weeks of log files.
2. "I'm telling you, PHB, that table hasn't been modified in the past two years. See? Says so right here in the database."
3. "What happened to the index that's needed for the monthly reports?"
Bottom line: sometimes, "everyone else does it" for very good and important reasons that are *vital* but rare.
----------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-09 16:35 M Sarwar <sarwarmd02@outlook.com>
parent: Muhammad Ikram <mmikram@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: M Sarwar @ 2024-06-09 16:35 UTC (permalink / raw)
To: Muhammad Ikram <mmikram@gmail.com>; Rui DeSousa <rui.desousa@icloud.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi / AA Ikram,
Probably I will not be able to depend or organize thru pgdumps at this time. I am on Postgres 13.5 / aws-rds.
I am unable to find pgaudit from my client. Do you I need to install any additional sw for pgaudit?
Thanks,
Sarwar
________________________________
From: Muhammad Ikram <mmikram@gmail.com>
Sent: Friday, June 7, 2024 11:35 AM
To: Rui DeSousa <rui.desousa@icloud.com>
Cc: M Sarwar <sarwarmd02@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to find the view modified date and time and user name
Hi M Sarwar,
If you have some dumps of the database in near past, then you may analyze those and note till what time view was in original state (May be narrowing down on things)
As some geek said above, this incident can serve as a lesson learnt and, for future you can configure pgAudit to capture all DDLs or set a log level that captures ddls e.g. log_statement = ddl
Regards,
Muhammad Ikram
Bitnine
On Fri, Jun 7, 2024 at 6:01 PM Rui DeSousa <rui.desousa@icloud.com<mailto:rui.desousa@icloud.com>> wrote:
On Jun 6, 2024, at 11:44 PM, M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
Hi Tom,
I do not have DDL logs.
Are you saying that I should have manually maintain it or are you referring to any existing logs on the database side?
This is taken very seriously by our architect. This guy behaves like everything like auditor, Project Manager or whatever we can think of. 🙂
Thanks,
Sarwar
A simple solution I used was to do a schema only dump daily and check in any changes into a git repo via a cronjob Then the repo will track schema changes.
--
Muhammad Ikram
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: [EXTERNAL] Re: How to find the view modified date and time and user name
@ 2024-06-09 16:41 M Sarwar <sarwarmd02@outlook.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
0 siblings, 0 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-09 16:41 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Matthew,
Thanks for taking a look on this issue.
I am feeling the same way like Ron that it would be handy if this can be made available thru the database itself.
You may take the feedback with few other experienced DBAs ( I know that this group has lot of experienced members ) on this.
Thanks,
Sarwar
________________________________
From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
Sent: Friday, June 7, 2024 1:29 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: [EXTERNAL] Re: How to find the view modified date and time and user name
Why do you expect the database to do something globally. This is an open source platform with vastly different use cases.
If you need detailed stats on changes, you can build _history tables that track this.
Google will get you there.
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Thursday, June 6, 2024 6:40 PM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: How to find the view modified date and time and user name
On Thu, Jun 6, 2024 at 9:14 PM Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
Ron Johnson <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>> writes:
> 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."
I'm not finding that argument terribly convincing. If you have a
DDL log file, you can grep it to find the last change (and the
ones before that, in case it was Alice's fault not Bob's). If
you don't have such a log file, how much does a last-changed
timestamp really help you?
1. That's not terribly helpful if it was altered three weeks ago, but you only keep two weeks of log files.
2. "I'm telling you, PHB, that table hasn't been modified in the past two years. See? Says so right here in the database."
3. "What happened to the index that's needed for the monthly reports?"
Bottom line: sometimes, "everyone else does it" for very good and important reasons that are *vital* but rare.
________________________________
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-09 17:11 M Sarwar <sarwarmd02@outlook.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: M Sarwar @ 2024-06-09 17:11 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi David,
I appreciate your response.
Sure, I will happy to share any information with Ron on this.
Thanks,
Sarwar
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, June 7, 2024 12:14 AM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to find the view modified date and time and user name
On Thursday, June 6, 2024, M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
I do not have DDL logs.
Then you don’t have any evidence. But you now know what you need to work on for the future. Until then, restore the view to what it should be and call it a lesson learned. And double-check your role memberships, especially for roles that own objects.
Feel free to help Ron build consensus that additional metadata is desired - though you’d still have to find someone willing to write the patches.
Also, 13.5 is long out of support…
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: How to find the view modified date and time and user name
@ 2024-06-10 07:41 Muhammad Ikram <mmikram@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 23+ messages in thread
From: Muhammad Ikram @ 2024-06-10 07:41 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Rui DeSousa <rui.desousa@icloud.com>; Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello M Sarwar,
It's an extension you can install. Do some googling and you will find steps
Regards,
Ikram
On Sun, Jun 9, 2024 at 9:35 PM M Sarwar <sarwarmd02@outlook.com> wrote:
> Hi / AA Ikram,
> Probably I will not be able to depend or organize thru pgdumps at this
> time. I am on Postgres 13.5 / aws-rds.
> I am unable to find pgaudit from my client. Do you I need to install any
> additional sw for pgaudit?
> Thanks,
> Sarwar
>
> ------------------------------
> *From:* Muhammad Ikram <mmikram@gmail.com>
> *Sent:* Friday, June 7, 2024 11:35 AM
> *To:* Rui DeSousa <rui.desousa@icloud.com>
> *Cc:* M Sarwar <sarwarmd02@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>;
> Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <
> pgsql-admin@lists.postgresql.org>
> *Subject:* Re: How to find the view modified date and time and user name
>
> Hi M Sarwar,
>
> If you have some dumps of the database in near past, then you may analyze
> those and note till what time view was in original state (May be narrowing
> down on things)
> As some geek said above, this incident can serve as a lesson learnt and,
> for future you can configure pgAudit to capture all DDLs or set a log level
> that captures ddls e.g. log_statement = ddl
>
> Regards,
> Muhammad Ikram
> Bitnine
>
> On Fri, Jun 7, 2024 at 6:01 PM Rui DeSousa <rui.desousa@icloud.com> wrote:
>
>
>
> On Jun 6, 2024, at 11:44 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Hi Tom,
> I do not have DDL logs.
> Are you saying that I should have manually maintain it or are you
> referring to any existing logs on the database side?
> This is taken very seriously by our architect. This guy behaves like
> everything like auditor, Project Manager or whatever we can think of. 🙂
> Thanks,
> Sarwar
>
>
> A simple solution I used was to do a schema only dump daily and check in
> any changes into a git repo via a cronjob Then the repo will track schema
> changes.
>
>
>
> --
> Muhammad Ikram
>
>
--
Muhammad Ikram
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2024-06-10 07:41 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-06 21:06 How to find the view modified date and time and user name M Sarwar <sarwarmd02@outlook.com>
2024-06-06 21:11 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-06 21:48 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-06 21:49 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-07 00:47 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-07 01:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-07 01:40 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-07 03:54 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-07 17:29 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2024-06-09 16:41 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-07 03:44 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-07 04:11 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-06-07 04:17 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-07 04:25 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-06-07 04:14 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-07 04:29 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-09 17:11 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-07 13:01 ` Rui DeSousa <rui.desousa@icloud.com>
2024-06-07 15:35 ` Muhammad Ikram <mmikram@gmail.com>
2024-06-09 16:35 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-10 07:41 ` Muhammad Ikram <mmikram@gmail.com>
2024-06-07 03:40 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-07 03:34 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox