Re: Detect who ran DROP schema

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