From: | Siraj G <tosiraj(dot)g(at)gmail(dot)com> |
---|---|
To: | sagar jadhav <sagarjdhv5(at)gmail(dot)com> |
Cc: | Wasim Devale <wasimd60(at)gmail(dot)com>, Kashif Zeeshan <kashi(dot)zeeshan(at)gmail(dot)com>, Muhammad Imtiaz <imtiazpg712(at)gmail(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Detect who ran DROP schema |
Date: | 2024-07-24 10:34:25 |
Message-ID: | CAC5iy62LQYNhLp52_mXdiOcbcPXkQc8n9WT0aUOyUBP6_yLxiA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)gmail(dot)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(at)gmail(dot)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(dot)zeeshan(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj(dot)g(at)gmail(dot)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(at)gmail(dot)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(dot)m(at)bitnine(dot)net <imtiaz(dot)m(at)bitnine(dot)net>*
>>>>>
>>>>>
>>>>> On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj(dot)g(at)gmail(dot)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
>>>>>>
>>>>>
From | Date | Subject | |
---|---|---|---|
Next Message | Zaid Shabbir | 2024-07-24 10:42:30 | Re: Detect who ran DROP schema |
Previous Message | sagar jadhav | 2024-07-24 09:31:39 | Re: Detect who ran DROP schema |