Postgres Pro
Downloads
Home   >   mailing lists

Thread: Detect who ran DROP schema

Detect who ran DROP schema

From
Siraj G
Date:
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

Re: Detect who ran DROP schema

From
Muhammad Imtiaz
Date:

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


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

Re: Detect who ran DROP schema

From
Laurenz Albe
Date:
On Wed, 2024-07-24 at 12:44 +0530, Siraj G wrote:
> 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?

Unless you have configured logging, nobody can figure that out.

Among the things you are doing wrong is that you are using a superuser.

Yours,
Laurenz Albe

Re: Detect who ran DROP schema

From
Siraj G
Date:
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

On Wed, Jul 24, 2024 at 1:01 PM Muhammad Imtiaz <imtiazpg712@gmail.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


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

Re: Detect who ran DROP schema

From
Kashif Zeeshan
Date:


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


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

Re: Detect who ran DROP schema

From
Wasim Devale
Date:

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@gmail.com> wrote:


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


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

Re: Detect who ran DROP schema

From
sagar jadhav
Date:
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@gmail.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.zeeshan@gmail.com> wrote:


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


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

Re: Detect who ran DROP schema

From
Siraj G
Date:
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@gmail.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@gmail.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.zeeshan@gmail.com> wrote:


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


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

Re: Detect who ran DROP schema

From
Zaid Shabbir
Date:
Hello Siraj,

I am expecting you to have access to the PostgreSQL logs normally stored under the data directory on postgresql running instance. As suggested by Imtiaz and other members you need to check the PostgreSQL logs. 

For more details regarding the PostgreSQL logs management you can consult the PostgreSQL documentation https://www.postgresql.org/docs/current/runtime-config-logging.html


Thanks & Regards
Zaid Shabbir
Bitnine 

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


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


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

Re: Detect who ran DROP schema

From
khan Affan
Date:
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@gmail.com> 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@gmail.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@gmail.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.zeeshan@gmail.com> wrote:


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


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

Re: Detect who ran DROP schema

From
"David G. Johnston"
Date:
On Wednesday, July 24, 2024, Siraj G <tosiraj.g@gmail.com> wrote:

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.

Pretty sure you are sunk if you allow multiple people to connect over IP as the same user.  Though you might be able to locate a client IP address somewhere.

Be glad it was UAT and that you can learn from your mistakes with minimal damage.  Enable better logging and require personal login credentials.

David J.

RE: [EXTERNAL] Re: Detect who ran DROP schema

From
"Wetmore, Matthew (CTR)"
Date:

This is a major issue in the DBA world as enterprise management lawyers get more popular.

At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance.  This is how it was set up.

 

As the DBA I couldn’t even log into the linux box where postgres was installed.

 

I couldn’t even change any logging without a two day ticket to do the work.

 

Not specifically this issue, but this is more the norm now-a-days then not.

 

 

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, July 24, 2024 6:33 AM
To: Siraj G <tosiraj.g@gmail.com>
Cc: sagar jadhav <sagarjdhv5@gmail.com>; Wasim Devale <wasimd60@gmail.com>; Kashif Zeeshan <kashi.zeeshan@gmail.com>; Muhammad Imtiaz <imtiazpg712@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Detect who ran DROP schema

 

On Wednesday, July 24, 2024, Siraj G <tosiraj.g@gmail.com> wrote:

 

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.

 

Pretty sure you are sunk if you allow multiple people to connect over IP as the same user.  Though you might be able to locate a client IP address somewhere.

 

Be glad it was UAT and that you can learn from your mistakes with minimal damage.  Enable better logging and require personal login credentials.

 

David J.

 

CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth

Re: [EXTERNAL] Re: Detect who ran DROP schema

From
Alvaro Herrera
Date:
On 2024-Jul-24, Wetmore, Matthew  (CTR) wrote:

> This is a major issue in the DBA world as enterprise management lawyers get more popular.
> 
> At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our
personalaccounts cannot do much due to modern corporate governance.  This is how it was set up.
 
> 
> As the DBA I couldn’t even log into the linux box where postgres was installed.
> 
> I couldn’t even change any logging without a two day ticket to do the work.
> 
> Not specifically this issue, but this is more the norm now-a-days then not.

Yeah.  This is an important if there are any potential attackers at all,
which given today's Internet, you can be pretty sure is always the case.

A database where people are allowed to connect as superuser is a sure
way to get in trouble sooner rather than later.  Having layered security
is one of the first things you should be thinking about.

FWIW I think even that one elevated user to which several people have
user/pass is a bad idea; forensics would require to know who used the
password when.  It's better to have one elevated user _without login privs_,
to which people can SET ROLE when they require it.  This leaves a better
trail.

If you add something like pgAudit to the mix and direct its logs (or all
Postgres logs) to a remote server where they can't easily be tampered
with by attackers, you'll have a better trail of who did what, when,
with what credentials.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)

Re: [EXTERNAL] Re: Detect who ran DROP schema

From
George Weaver
Date:

>It's better to have one elevated user _without login privs_, to which people can SET ROLE when they require it.

This sounds interesting, but I'm not sure how to do it. Would you mind sharing an example?

Thanks,

George

On 24/07/2024 12:22 p.m., Alvaro Herrera wrote:
On 2024-Jul-24, Wetmore, Matthew  (CTR) wrote:


This is a major issue in the DBA world as enterprise management lawyers get more popular.

At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance.  This is how it was set up.

As the DBA I couldn’t even log into the linux box where postgres was installed.

I couldn’t even change any logging without a two day ticket to do the work.

Not specifically this issue, but this is more the norm now-a-days then not.

Yeah.  This is an important if there are any potential attackers at all,
which given today's Internet, you can be pretty sure is always the case.

A database where people are allowed to connect as superuser is a sure
way to get in trouble sooner rather than later.  Having layered security
is one of the first things you should be thinking about.

FWIW I think even that one elevated user to which several people have
user/pass is a bad idea; forensics would require to know who used the
password when.  It's better to have one elevated user _without login privs_,
to which people can SET ROLE when they require it.  This leaves a better
trail.

If you add something like pgAudit to the mix and direct its logs (or all
Postgres logs) to a remote server where they can't easily be tampered
with by attackers, you'll have a better trail of who did what, when,
with what credentials.


-- 
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell

Re: Detect who ran DROP schema

From
Muhammad Waqas
Date:
you can only find by logfile

2024년 7월 24일 (수) 오후 12:14, Siraj G <tosiraj.g@gmail.com>님이 작성:
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