Thread: How to find the view modified date and time and user name
select relfilenode from pg_class where relname = 'vw_tab_mcm_net_temp_yield';
is giving the following output
0
0
Some additional information:-select relfilenode from pg_class where relname = 'vw_tab_mcm_net_temp_yield';
is giving the following output
0
0
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.
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.
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
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?
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.
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 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.
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
> 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
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
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?
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
I do not have DDL logs.
If you have WAL Files then you can decode them but that will be a tedious task.
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.
though you’d still have to find someone willing to write the patches.
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
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.
--
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
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
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.
--
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
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
I do not have DDL logs.
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