Обсуждение: 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
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
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.
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 / rds2. Postgress 13.53. database with default configuration is runningWe 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 logsand 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.
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 / rds2. Postgress 13.53. database with default configuration is runningWe 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 logsand 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."
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
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.
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
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> 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 / rds2. Postgress 13.53. database with default configuration is runningWe 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 logsand 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.
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
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> 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 / rds2. Postgress 13.53. database with default configuration is runningWe 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 logsand 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."
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
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
> 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
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
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> 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. ---> This phrase is true in this case.
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,SarwarFrom: 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 nameRon 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
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.
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.
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.
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.
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.
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,SarwarA 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
RE: [EXTERNAL] Re: How to find the view modified date and time and user name
От
"Wetmore, Matthew (CTR)"
Дата:
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> 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.
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
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
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,SarwarA 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
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
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> 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.
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
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
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> 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.
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,SarwarFrom: 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 nameHi 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 = ddlRegards,Muhammad IkramBitnineOn 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,SarwarA 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