Re: Detect who ran DROP schema

[khan Affan <bawag773@xxxxxxxxx>]

Hi 

Unfortunately, as of July 24, 2024, Cloud SQL Insights is currently unavailable for Cloud SQL for PostgreSQL instances. This feature is currently in preview for PG and only supports cloud SQL for MySQL instances.

So you have to enable logs from conf and use the pgaudit extension to enable log parameters in pgaudit.cfg . 

Note: These changes will enable all detail logs for the future.

Thanks & regards

Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Wed, Jul 24, 2024 at 3:34 PM Siraj G <tosiraj.g@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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.zeeshan@xxxxxxxxx> wrote:

On Wed, Jul 24, 2024 at 12:51 PM Siraj G <tosiraj.g@xxxxxxxxx> 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@xxxxxxxxx> 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.m@xxxxxxxxxxx

On Wed, Jul 24, 2024 at 12:14 PM Siraj G <tosiraj.g@xxxxxxxxx> 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