pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDetect who ran DROP schema
15+ messages / 13 participants
[nested] [flat]
* Detect who ran DROP schema
@ 2024-07-24 07:14 Siraj G <tosiraj.g@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Siraj G @ 2024-07-24 07:14 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello -
A schema was dropped from our UAT environment on 18th July. We are trying
to find out who did. We have only one user in our environment, i.e.,
postgres, but we are trying to identify from where the connection was
initiated, which issued DROP schema. Can anyone help?
Regards
Siraj
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 07:30 Muhammad Imtiaz <imtiazpg712@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Muhammad Imtiaz @ 2024-07-24 07:30 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
Please check the database server logs for the drop schema command. This
should provide details about the user and the session origin.
Configurations like* log_connections = on *and* log_statement = 'all' *are
recommended to capture the required details in db server logs.
*Muhammad Imtiaz*
*PostgreSQL Technical Support Lead *
*/ Pakistan R&D*
*Mobile: +923345072521*
*Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
> Hello -
>
> A schema was dropped from our UAT environment on 18th July. We are trying
> to find out who did. We have only one user in our environment, i.e.,
> postgres, but we are trying to identify from where the connection was
> initiated, which issued DROP schema. Can anyone help?
>
> Regards
> Siraj
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 07:34 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2024-07-24 07:34 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2024-07-24 at 12:44 +0530, Siraj G wrote:
> A schema was dropped from our UAT environment on 18th July. We are trying to
> find out who did. We have only one user in our environment, i.e., postgres,
> but we are trying to identify from where the connection was initiated, which
> issued DROP schema. Can anyone help?
Unless you have configured logging, nobody can figure that out.
Among the things you are doing wrong is that you are using a superuser.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 07:50 Siraj G <tosiraj.g@gmail.com>
parent: Muhammad Imtiaz <imtiazpg712@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Siraj G @ 2024-07-24 07:50 UTC (permalink / raw)
To: Muhammad Imtiaz <imtiazpg712@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Imtiaz
I could see this from the log, but not sure from which client or the
tool/program the drop was issued.
2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
STATEMENT: DROP SCHEMA public CASCADE
On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
wrote:
> Hi,
>
> Please check the database server logs for the drop schema command. This
> should provide details about the user and the session origin.
> Configurations like* log_connections = on *and* log_statement = 'all' *are
> recommended to capture the required details in db server logs.
>
>
>
> *Muhammad Imtiaz*
>
> *PostgreSQL Technical Support Lead *
> */ Pakistan R&D*
> *Mobile: +923345072521*
> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>
>
> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>
>> Hello -
>>
>> A schema was dropped from our UAT environment on 18th July. We are trying
>> to find out who did. We have only one user in our environment, i.e.,
>> postgres, but we are trying to identify from where the connection was
>> initiated, which issued DROP schema. Can anyone help?
>>
>> Regards
>> Siraj
>>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 08:00 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Kashif Zeeshan @ 2024-07-24 08:00 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
> Hi Imtiaz
>
> I could see this from the log, but not sure from which client or the
> tool/program the drop was issued.
>
> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
> STATEMENT: DROP SCHEMA public CASCADE
>
It clearly shows that the user postgres dropped the schema, and if you want
to capture the application name as well then you have to configure the
logging.
>
> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
> wrote:
>
>> Hi,
>>
>> Please check the database server logs for the drop schema command. This
>> should provide details about the user and the session origin.
>> Configurations like* log_connections = on *and* log_statement = 'all' *are
>> recommended to capture the required details in db server logs.
>>
>>
>>
>> *Muhammad Imtiaz*
>>
>> *PostgreSQL Technical Support Lead *
>> */ Pakistan R&D*
>> *Mobile: +923345072521*
>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>
>>
>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>
>>> Hello -
>>>
>>> A schema was dropped from our UAT environment on 18th July. We are
>>> trying to find out who did. We have only one user in our environment, i.e.,
>>> postgres, but we are trying to identify from where the connection was
>>> initiated, which issued DROP schema. Can anyone help?
>>>
>>> Regards
>>> Siraj
>>>
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 08:53 Wasim Devale <wasimd60@gmail.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wasim Devale @ 2024-07-24 08:53 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; +Cc: Siraj G <tosiraj.g@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
If logging is on then install pgbadger to pull report from log and have all
details
On Wed, 24 Jul, 2024, 1:30 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com>
wrote:
>
>
> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
>
>> Hi Imtiaz
>>
>> I could see this from the log, but not sure from which client or the
>> tool/program the drop was issued.
>>
>> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
>> STATEMENT: DROP SCHEMA public CASCADE
>>
> It clearly shows that the user postgres dropped the schema, and if you
> want to capture the application name as well then you have to configure the
> logging.
>
>>
>> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> Please check the database server logs for the drop schema command. This
>>> should provide details about the user and the session origin.
>>> Configurations like* log_connections = on *and* log_statement = 'all' *are
>>> recommended to capture the required details in db server logs.
>>>
>>>
>>>
>>> *Muhammad Imtiaz*
>>>
>>> *PostgreSQL Technical Support Lead *
>>> */ Pakistan R&D*
>>> *Mobile: +923345072521*
>>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>>
>>>
>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>
>>>> Hello -
>>>>
>>>> A schema was dropped from our UAT environment on 18th July. We are
>>>> trying to find out who did. We have only one user in our environment, i.e.,
>>>> postgres, but we are trying to identify from where the connection was
>>>> initiated, which issued DROP schema. Can anyone help?
>>>>
>>>> Regards
>>>> Siraj
>>>>
>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 09:31 sagar jadhav <sagarjdhv5@gmail.com>
parent: Wasim Devale <wasimd60@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: sagar jadhav @ 2024-07-24 09:31 UTC (permalink / raw)
To: Wasim Devale <wasimd60@gmail.com>; +Cc: Kashif Zeeshan <kashi.zeeshan@gmail.com>; Siraj G <tosiraj.g@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Execute the below command on the shell terminal; This will return all the
statements, including connections (if enabled),
grep '[774513]' log_file_name
Thanks
On Wed, Jul 24, 2024 at 2:23 PM Wasim Devale <wasimd60@gmail.com> wrote:
> If logging is on then install pgbadger to pull report from log and have
> all details
>
> On Wed, 24 Jul, 2024, 1:30 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com>
> wrote:
>
>>
>>
>> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>
>>> Hi Imtiaz
>>>
>>> I could see this from the log, but not sure from which client or the
>>> tool/program the drop was issued.
>>>
>>> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
>>> STATEMENT: DROP SCHEMA public CASCADE
>>>
>> It clearly shows that the user postgres dropped the schema, and if you
>> want to capture the application name as well then you have to configure the
>> logging.
>>
>>>
>>> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> Please check the database server logs for the drop schema command. This
>>>> should provide details about the user and the session origin.
>>>> Configurations like* log_connections = on *and* log_statement = 'all' *are
>>>> recommended to capture the required details in db server logs.
>>>>
>>>>
>>>>
>>>> *Muhammad Imtiaz*
>>>>
>>>> *PostgreSQL Technical Support Lead *
>>>> */ Pakistan R&D*
>>>> *Mobile: +923345072521*
>>>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>>>
>>>>
>>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>
>>>>> Hello -
>>>>>
>>>>> A schema was dropped from our UAT environment on 18th July. We are
>>>>> trying to find out who did. We have only one user in our environment, i.e.,
>>>>> postgres, but we are trying to identify from where the connection was
>>>>> initiated, which issued DROP schema. Can anyone help?
>>>>>
>>>>> Regards
>>>>> Siraj
>>>>>
>>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 10:34 Siraj G <tosiraj.g@gmail.com>
parent: sagar jadhav <sagarjdhv5@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Siraj G @ 2024-07-24 10:34 UTC (permalink / raw)
To: sagar jadhav <sagarjdhv5@gmail.com>; +Cc: Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello All!
PgSQL instance is a cloud SQL managed by GCP. I used the GCP observability
tab to get the log, but I guess more logging is required to get more
granular details.
On Wed, Jul 24, 2024 at 3:01 PM sagar jadhav <sagarjdhv5@gmail.com> wrote:
> Execute the below command on the shell terminal; This will return all the
> statements, including connections (if enabled),
>
> grep '[774513]' log_file_name
>
> Thanks
>
> On Wed, Jul 24, 2024 at 2:23 PM Wasim Devale <wasimd60@gmail.com> wrote:
>
>> If logging is on then install pgbadger to pull report from log and have
>> all details
>>
>> On Wed, 24 Jul, 2024, 1:30 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com>
>> wrote:
>>
>>>
>>>
>>> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>
>>>> Hi Imtiaz
>>>>
>>>> I could see this from the log, but not sure from which client or the
>>>> tool/program the drop was issued.
>>>>
>>>> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
>>>> STATEMENT: DROP SCHEMA public CASCADE
>>>>
>>> It clearly shows that the user postgres dropped the schema, and if you
>>> want to capture the application name as well then you have to configure the
>>> logging.
>>>
>>>>
>>>> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Please check the database server logs for the drop schema command.
>>>>> This should provide details about the user and the session origin.
>>>>> Configurations like* log_connections = on *and* log_statement = 'all'
>>>>> *are recommended to capture the required details in db server logs.
>>>>>
>>>>>
>>>>>
>>>>> *Muhammad Imtiaz*
>>>>>
>>>>> *PostgreSQL Technical Support Lead *
>>>>> */ Pakistan R&D*
>>>>> *Mobile: +923345072521*
>>>>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>>>>
>>>>>
>>>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>>
>>>>>> Hello -
>>>>>>
>>>>>> A schema was dropped from our UAT environment on 18th July. We are
>>>>>> trying to find out who did. We have only one user in our environment, i.e.,
>>>>>> postgres, but we are trying to identify from where the connection was
>>>>>> initiated, which issued DROP schema. Can anyone help?
>>>>>>
>>>>>> Regards
>>>>>> Siraj
>>>>>>
>>>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 10:42 Zaid Shabbir <zaidshabbir@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Zaid Shabbir @ 2024-07-24 10:42 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Siraj,
I am expecting you to have access to the PostgreSQL logs normally stored
under the data directory on postgresql running instance. As suggested by
Imtiaz and other members you need to check the PostgreSQL logs.
For more details regarding the PostgreSQL logs management you can consult
the PostgreSQL documentation
https://www.postgresql.org/docs/current/runtime-config-logging.html
Thanks & Regards
Zaid Shabbir
Bitnine
On Wed, Jul 24, 2024 at 3:34 PM Siraj G <tosiraj.g@gmail.com> wrote:
> Hello All!
>
> PgSQL instance is a cloud SQL managed by GCP. I used the GCP observability
> tab to get the log, but I guess more logging is required to get more
> granular details.
>
> On Wed, Jul 24, 2024 at 3:01 PM sagar jadhav <sagarjdhv5@gmail.com> wrote:
>
>> Execute the below command on the shell terminal; This will return all the
>> statements, including connections (if enabled),
>>
>> grep '[774513]' log_file_name
>>
>> Thanks
>>
>> On Wed, Jul 24, 2024 at 2:23 PM Wasim Devale <wasimd60@gmail.com> wrote:
>>
>>> If logging is on then install pgbadger to pull report from log and have
>>> all details
>>>
>>> On Wed, 24 Jul, 2024, 1:30 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>
>>>>> Hi Imtiaz
>>>>>
>>>>> I could see this from the log, but not sure from which client or the
>>>>> tool/program the drop was issued.
>>>>>
>>>>> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
>>>>> STATEMENT: DROP SCHEMA public CASCADE
>>>>>
>>>> It clearly shows that the user postgres dropped the schema, and if you
>>>> want to capture the application name as well then you have to configure the
>>>> logging.
>>>>
>>>>>
>>>>> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Please check the database server logs for the drop schema command.
>>>>>> This should provide details about the user and the session origin.
>>>>>> Configurations like* log_connections = on *and* log_statement =
>>>>>> 'all' *are recommended to capture the required details in db server
>>>>>> logs.
>>>>>>
>>>>>>
>>>>>>
>>>>>> *Muhammad Imtiaz*
>>>>>>
>>>>>> *PostgreSQL Technical Support Lead *
>>>>>> */ Pakistan R&D*
>>>>>> *Mobile: +923345072521*
>>>>>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>>>>>
>>>>>>
>>>>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>>>
>>>>>>> Hello -
>>>>>>>
>>>>>>> A schema was dropped from our UAT environment on 18th July. We are
>>>>>>> trying to find out who did. We have only one user in our environment, i.e.,
>>>>>>> postgres, but we are trying to identify from where the connection was
>>>>>>> initiated, which issued DROP schema. Can anyone help?
>>>>>>>
>>>>>>> Regards
>>>>>>> Siraj
>>>>>>>
>>>>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 12:42 khan Affan <bawag773@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: khan Affan @ 2024-07-24 12:42 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
Unfortunately, as of July 24, 2024, Cloud SQL Insights is currently
*unavailable* for Cloud SQL for PostgreSQL instances. This feature is
currently in preview for PG and only supports cloud SQL for MySQL instances.
So you have to enable logs from conf and use the pgaudit extension to
enable log parameters in pgaudit.cfg .
Note: These changes will enable all detail logs for the future.
Thanks & regards
*Muhammad Affan (*아판*)*
*PostgreSQL Technical Support Engineer** / Pakistan R&D*
Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan
On Wed, Jul 24, 2024 at 3:34 PM Siraj G <tosiraj.g@gmail.com> wrote:
> Hello All!
>
> PgSQL instance is a cloud SQL managed by GCP. I used the GCP observability
> tab to get the log, but I guess more logging is required to get more
> granular details.
>
> On Wed, Jul 24, 2024 at 3:01 PM sagar jadhav <sagarjdhv5@gmail.com> wrote:
>
>> Execute the below command on the shell terminal; This will return all the
>> statements, including connections (if enabled),
>>
>> grep '[774513]' log_file_name
>>
>> Thanks
>>
>> On Wed, Jul 24, 2024 at 2:23 PM Wasim Devale <wasimd60@gmail.com> wrote:
>>
>>> If logging is on then install pgbadger to pull report from log and have
>>> all details
>>>
>>> On Wed, 24 Jul, 2024, 1:30 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>
>>>>> Hi Imtiaz
>>>>>
>>>>> I could see this from the log, but not sure from which client or the
>>>>> tool/program the drop was issued.
>>>>>
>>>>> 2024-07-18 14:34:50.044 UTC [774513]: [3-1] db=umarslog,user=postgres
>>>>> STATEMENT: DROP SCHEMA public CASCADE
>>>>>
>>>> It clearly shows that the user postgres dropped the schema, and if you
>>>> want to capture the application name as well then you have to configure the
>>>> logging.
>>>>
>>>>>
>>>>> On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Please check the database server logs for the drop schema command.
>>>>>> This should provide details about the user and the session origin.
>>>>>> Configurations like* log_connections = on *and* log_statement =
>>>>>> 'all' *are recommended to capture the required details in db server
>>>>>> logs.
>>>>>>
>>>>>>
>>>>>>
>>>>>> *Muhammad Imtiaz*
>>>>>>
>>>>>> *PostgreSQL Technical Support Lead *
>>>>>> */ Pakistan R&D*
>>>>>> *Mobile: +923345072521*
>>>>>> *Email: imtiaz.m@bitnine.net <imtiaz.m@bitnine.net>*
>>>>>>
>>>>>>
>>>>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@gmail.com> wrote:
>>>>>>
>>>>>>> Hello -
>>>>>>>
>>>>>>> A schema was dropped from our UAT environment on 18th July. We are
>>>>>>> trying to find out who did. We have only one user in our environment, i.e.,
>>>>>>> postgres, but we are trying to identify from where the connection was
>>>>>>> initiated, which issued DROP schema. Can anyone help?
>>>>>>>
>>>>>>> Regards
>>>>>>> Siraj
>>>>>>>
>>>>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-24 13:33 David G. Johnston <david.g.johnston@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: David G. Johnston @ 2024-07-24 13:33 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wednesday, July 24, 2024, Siraj G <tosiraj.g@gmail.com> wrote:
>
>
> PgSQL instance is a cloud SQL managed by GCP. I used the GCP observability
> tab to get the log, but I guess more logging is required to get more
> granular details.
>
Pretty sure you are sunk if you allow multiple people to connect over IP as
the same user. Though you might be able to locate a client IP address
somewhere.
Be glad it was UAT and that you can learn from your mistakes with minimal
damage. Enable better logging and require personal login credentials.
David J.
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [EXTERNAL] Re: Detect who ran DROP schema
@ 2024-07-24 16:18 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-07-24 16:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Siraj G <tosiraj.g@gmail.com>; +Cc: sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
This is a major issue in the DBA world as enterprise management lawyers get more popular.
At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance. This is how it was set up.
As the DBA I couldn’t even log into the linux box where postgres was installed.
I couldn’t even change any logging without a two day ticket to do the work.
Not specifically this issue, but this is more the norm now-a-days then not.
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, July 24, 2024 6:33 AM
To: Siraj G <tosiraj.g@gmail.com>
Cc: sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Detect who ran DROP schema
On Wednesday, July 24, 2024, Siraj G <tosiraj.g@gmail.com<mailto:tosiraj.g@gmail.com>> wrote:
PgSQL instance is a cloud SQL managed by GCP. I used the GCP observability tab to get the log, but I guess more logging is required to get more granular details.
Pretty sure you are sunk if you allow multiple people to connect over IP as the same user. Though you might be able to locate a client IP address somewhere.
Be glad it was UAT and that you can learn from your mistakes with minimal damage. Enable better logging and require personal login credentials.
David J.
----------------------------------------------------------------------
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] 15+ messages in thread
* Re: [EXTERNAL] Re: Detect who ran DROP schema
@ 2024-07-24 17:22 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
0 siblings, 1 reply; 15+ messages in thread
From: Alvaro Herrera @ 2024-07-24 17:22 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Siraj G <tosiraj.g@gmail.com>; sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2024-Jul-24, Wetmore, Matthew (CTR) wrote:
> This is a major issue in the DBA world as enterprise management lawyers get more popular.
>
> At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance. This is how it was set up.
>
> As the DBA I couldn’t even log into the linux box where postgres was installed.
>
> I couldn’t even change any logging without a two day ticket to do the work.
>
> Not specifically this issue, but this is more the norm now-a-days then not.
Yeah. This is an important if there are any potential attackers at all,
which given today's Internet, you can be pretty sure is always the case.
A database where people are allowed to connect as superuser is a sure
way to get in trouble sooner rather than later. Having layered security
is one of the first things you should be thinking about.
FWIW I think even that one elevated user to which several people have
user/pass is a bad idea; forensics would require to know who used the
password when. It's better to have one elevated user _without login privs_,
to which people can SET ROLE when they require it. This leaves a better
trail.
If you add something like pgAudit to the mix and direct its logs (or all
Postgres logs) to a remote server where they can't easily be tampered
with by attackers, you'll have a better trail of who did what, when,
with what credentials.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [EXTERNAL] Re: Detect who ran DROP schema
@ 2024-07-24 18:45 George Weaver <gweaver@shaw.ca>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 15+ messages in thread
From: George Weaver @ 2024-07-24 18:45 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-admin@lists.postgresql.org
>It's better to have one elevated user _without login privs_, to which
people can SET ROLE when they require it.
This sounds interesting, but I'm not sure how to do it. Would you mind
sharing an example?
Thanks,
George
On 24/07/2024 12:22 p.m., Alvaro Herrera wrote:
> On 2024-Jul-24, Wetmore, Matthew (CTR) wrote:
>
>> This is a major issue in the DBA world as enterprise management lawyers get more popular.
>>
>> At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance. This is how it was set up.
>>
>> As the DBA I couldn’t even log into the linux box where postgres was installed.
>>
>> I couldn’t even change any logging without a two day ticket to do the work.
>>
>> Not specifically this issue, but this is more the norm now-a-days then not.
> Yeah. This is an important if there are any potential attackers at all,
> which given today's Internet, you can be pretty sure is always the case.
>
> A database where people are allowed to connect as superuser is a sure
> way to get in trouble sooner rather than later. Having layered security
> is one of the first things you should be thinking about.
>
> FWIW I think even that one elevated user to which several people have
> user/pass is a bad idea; forensics would require to know who used the
> password when. It's better to have one elevated user _without login privs_,
> to which people can SET ROLE when they require it. This leaves a better
> trail.
>
> If you add something like pgAudit to the mix and direct its logs (or all
> Postgres logs) to a remote server where they can't easily be tampered
> with by attackers, you'll have a better trail of who did what, when,
> with what credentials.
>
--
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Detect who ran DROP schema
@ 2024-07-30 12:37 Muhammad Waqas <waqas.m@bitnine.net>
parent: Siraj G <tosiraj.g@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Muhammad Waqas @ 2024-07-30 12:37 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
you can only find by logfile
2024년 7월 24일 (수) 오후 12:14, Siraj G <tosiraj.g@gmail.com>님이 작성:
> Hello -
>
> A schema was dropped from our UAT environment on 18th July. We are trying
> to find out who did. We have only one user in our environment, i.e.,
> postgres, but we are trying to identify from where the connection was
> initiated, which issued DROP schema. Can anyone help?
>
> Regards
> Siraj
>
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2024-07-30 12:37 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-24 07:14 Detect who ran DROP schema Siraj G <tosiraj.g@gmail.com>
2024-07-24 07:30 ` Muhammad Imtiaz <imtiazpg712@gmail.com>
2024-07-24 07:50 ` Siraj G <tosiraj.g@gmail.com>
2024-07-24 08:00 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-07-24 08:53 ` Wasim Devale <wasimd60@gmail.com>
2024-07-24 09:31 ` sagar jadhav <sagarjdhv5@gmail.com>
2024-07-24 10:34 ` Siraj G <tosiraj.g@gmail.com>
2024-07-24 10:42 ` Zaid Shabbir <zaidshabbir@gmail.com>
2024-07-24 12:42 ` khan Affan <bawag773@gmail.com>
2024-07-24 13:33 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-24 16:18 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2024-07-24 17:22 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-07-24 18:45 ` George Weaver <gweaver@shaw.ca>
2024-07-24 07:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-30 12:37 ` Muhammad Waqas <waqas.m@bitnine.net>
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