Re: Detect who ran DROP schema

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
>>>>>
>>>>