Re: Detect who ran DROP schema

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(dot)g(at)gmail(dot)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(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
>>>>>>>
>>>>>>