Re: Detect who ran DROP schema

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