Re: Detect who ran DROP schema

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