Re: My Database getting dropped anonymously

Hi Muhammad,  Thank you!

and I do thank everybody who responded kindly with appropriate recommendations.  I never thought a database like mine (a small/new test db) will be a target for attackers :)) its like launching a missile on a baby :))  But, now it gives me a good lesson as how serious it is.  I have changed the authentication from 'md5' to 'scram-sha-256'.  So far, no drops :).

I use pgAdmin5 on my windows desktop to connect to PostgreSQL DB in Azure.  I tried to replace "0.0.0.0" with my desktop IP (private - 192.168.23.232) to limit the incoming source but I was not able to connect after I replaced it.  So, I reverted it.  What is your recommendation in this case? How do we limit only our desktop to connect through pgAdmin.

Thank you all!

Sekar

On Fri, Jun 21, 2024 at 3:26 PM Muhammad Ikram <mmikram@gmail.com> wrote:

Hi Sekar,

If this is a test environment then you can drop the VM and recreate it, keeping it secure. ( Advice above by Zaid is quite thorough and well explained).

If this is your production environment, then hope you have a backup strategy in place.  Secure your environment and restore from backups.

The listen address '*'  in Postgresql.conf and   listening on IPv4 address "0.0.0.0", port 5432  are giving intruders the right to do whatever they wish to do.

Just another thought

  You may also check if your some test script runs and drops the database.....

Regards,

Muhammad Ikram

Bitnine Global.

On Fri, Jun 21, 2024 at 11:51 AM Piergiorgio Valli <piervalli@gmail.com> wrote:

Hi,

If the database is deleted you should check security of VM, it should be an external intrusion. Delete VM and recreate it.

Pier

Il ven 21 giu 2024, 07:34 Zaid Shabbir <zaidshabbir@gmail.com> ha scritto:

Hello Sekar,

It looks like you need to configure your server as per the security standards. There are some best practices you should consider during the configurations.

• The first part of any security review is to look at how the server is connected to and accessed. As with any security configuration, follow the principle of least privilege when considering how to configure your system; that is, only allow as much access as is required to implement a working system, and no more.
• There are two ways to connect to a Postgres server; via a Unix Domain Socket or a TCP/IP Socket. Unix Domain Sockets (UDS) are the default method for connecting to a Postgres database on Unix-like platforms.
• For TCP/IP socket connection Use the listen_addresses configuration parameter in postgresql.conf to ensure Postgres only listens and accepts connections on the required network addresses, thus preventing access from, say, the storage network.
• Firewall: It's an important tool to prevent access to network ports from unauthorized sources. Most cloud providers recommend against using firewalls in virtual instances. Use proper firewalls settings as per the cloud platform recommendations. 
• Transport Encryption: Use appropriate encryption techniques to secure your data. For more details you can consult the PostgreSQL documentation.
• Authentication: Make sure you are using the right and secure authentication method. PostgreSQL supports multiple authentication methods including trust. Configure the most suitable authentication method as per the PostgreSQL provided guidelines [Link]. 
• pg_hba.conf: Make sure you correctly configure your configurations and are aware about the standards and best practices. For more details please review the postgresql pg_hba.conf guide.

Regards,

Zaid 

Bitnine

On Fri, Jun 21, 2024 at 9:29 AM Sekar S <sekar.newtran@gmail.com> wrote:

Hi,

I'm new the PostgreSQL.  I have setup my PostgreSQL instance in Azure cloud.

VM & OS         - Linux, Ubuntu 22.04, Size - Standard B2s (2 vcpus, 4 GiB memory)

PostgreSQL     -  psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))

Past few days, I'm seeing repeated instances of my Database getting dropped which is weird.

When I checked the logs in the /var/log/postgresql/postgresql-14-main.log, here is what it shows.

2024-06-20 23:17:20.030 UTC [91103] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.031 UTC [91151] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.041 UTC [91147] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.061 UTC [91102] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.061 UTC [91121] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.072 UTC [91073] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.081 UTC [91119] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-20 23:17:20.312 UTC [90636] LOG:  background worker "logical replication launcher" (PID 90643) exited with exit code 1
2024-06-20 23:17:20.334 UTC [90638] LOG:  shutting down
2024-06-20 23:17:20.504 UTC [90636] LOG:  database system is shut down
2024-06-21 03:13:29.202 UTC [99847] LOG:  starting PostgreSQL 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-06-21 03:13:29.202 UTC [99847] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-06-21 03:13:29.202 UTC [99847] LOG:  listening on IPv6 address "::", port 5432
2024-06-21 03:13:29.231 UTC [99847] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-06-21 03:13:29.282 UTC [99848] LOG:  database system was shut down at 2024-06-20 23:17:20 UTC
2024-06-21 03:13:29.393 UTC [99847] LOG:  database system is ready to accept connections
2024-06-21 03:23:58.922 UTC [99987] nectar_dvpr@nectardev_db01 FATAL:  password authentication failed for user "nectar_dvpr"
2024-06-21 03:23:58.922 UTC [99987] nectar_dvpr@nectardev_db01 DETAIL:  Connection matched pg_hba.conf line 93: "host    all             all             0.0.0.0/0               md5"
2024-06-21 03:23:59.033 UTC [99988] nectar_dvpr@nectardev_db01 FATAL:  password authentication failed for user "nectar_dvpr"
2024-06-21 03:23:59.033 UTC [99988] nectar_dvpr@nectardev_db01 DETAIL:  Connection matched pg_hba.conf line 93: "host    all             all             0.0.0.0/0               md5"
2024-06-21 03:24:42.305 UTC [100002] postgres@template0 FATAL:  database "template0" is not currently accepting connections
2024-06-21 03:24:58.029 UTC [99996] nectar_dvpr@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-21 03:25:00.673 UTC [100011] postgres@postgres ERROR:  cannot drop the currently open database
2024-06-21 03:25:00.673 UTC [100011] postgres@postgres STATEMENT:  DROP DATABASE postgres;
2024-06-21 03:25:08.314 UTC [100022] postgres@postgres ERROR:  database "nectardev_db01" is being accessed by other users
2024-06-21 03:25:08.314 UTC [100022] postgres@postgres DETAIL:  There is 1 other session using the database.
2024-06-21 03:25:08.314 UTC [100022] postgres@postgres STATEMENT:  DROP DATABASE nectardev_db01;
2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr@nectardev_db01 ERROR:  relation "mas_party" does not exist at character 15
2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr@nectardev_db01 STATEMENT:  select * from mas_party
2024-06-21 03:29:24.233 UTC [100087] postgres@nectardev_db01 ERROR:  relation "mas_party" does not exist at character 15
2024-06-21 03:29:24.233 UTC [100087] postgres@nectardev_db01 STATEMENT:  select * from mas_party;
2024-06-21 03:35:16.573 UTC [99847] LOG:  received fast shutdown request
2024-06-21 03:35:16.584 UTC [99847] LOG:  aborting any active transactions
2024-06-21 03:35:16.585 UTC [100053] postgres@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-21 03:35:16.593 UTC [100047] postgres@postgres FATAL:  terminating connection due to administrator command
2024-06-21 03:35:16.603 UTC [100039] nectar_dvpr@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-21 03:35:16.613 UTC [100010] nectar_dvpr@nectardev_db01 FATAL:  terminating connection due to administrator command
2024-06-21 03:35:16.654 UTC [99847] LOG:  background worker "logical replication launcher" (PID 99854) exited with exit code 1
2024-06-21 03:35:16.746 UTC [99849] LOG:  shutting down
2024-06-21 03:35:16.902 UTC [99847] LOG:  database system is shut down
2024-06-21 03:35:17.912 UTC [100172] LOG:  starting PostgreSQL 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-06-21 03:35:17.912 UTC [100172] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-06-21 03:35:17.912 UTC [100172] LOG:  listening on IPv6 address "::", port 5432
2024-06-21 03:35:17.932 UTC [100172] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-06-21 03:35:18.008 UTC [100173] LOG:  database system was shut down at 2024-06-21 03:35:16 UTC
2024-06-21 03:35:18.093 UTC [100172] LOG:  database system is ready to accept connections

I have not issued any database drop command.  I'm also using pgAdmin4 (8.6) in my windows desktop to work on Azure cloud database.  I tried to find solution through google but not found any answer why this is happening.  I'm at the verge of switching other databases if this issue not solved.  I request your help to solve this issue and would be grateful for that.

Issues facing:

1) Database instance getting stopped after a period of time whereas I want the database to be always on.

2) Database is getting dropped (happened multiple times in the last one week).  This needs to be fixed.

Thank you

Sekar

--

Muhammad Ikram