Re: My Database getting dropped anonymously

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(at)gmail(dot)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(at)gmail(dot)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
>> <https://en.wikipedia.org/wiki/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
>> <https://www.postgresql.org/docs/current/encryption-options.html>.
>> - 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]
>> <https://www.postgresql.org/docs/14/auth-methods.html>.
>> - 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
>> <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>.
>>
>>
>> Regards,
>> Zaid
>>
>> Bitnine
>>
>> On Fri, Jun 21, 2024 at 9:29 AM Sekar S <sekar(dot)newtran(at)gmail(dot)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(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.031 UTC [91151] postgres(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.041 UTC [91147] postgres(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.061 UTC [91102] postgres(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.061 UTC [91121] postgres(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.072 UTC [91073] postgres(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-20 23:17:20.081 UTC [91119] postgres(at)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(at)nectardev_db01 FATAL:
>>> password authentication failed for user "nectar_dvpr"
>>> 2024-06-21 03:23:58.922 UTC [99987] nectar_dvpr(at)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(at)nectardev_db01 FATAL:
>>> password authentication failed for user "nectar_dvpr"
>>> 2024-06-21 03:23:59.033 UTC [99988] nectar_dvpr(at)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(at)template0 FATAL:
>>> database "template0" is not currently accepting connections
>>> 2024-06-21 03:24:58.029 UTC [99996] nectar_dvpr(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-21 03:25:00.673 UTC [100011] postgres(at)postgres ERROR: cannot
>>> drop the currently open database
>>> 2024-06-21 03:25:00.673 UTC [100011] postgres(at)postgres STATEMENT: DROP
>>> DATABASE postgres;
>>> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres ERROR: database
>>> "nectardev_db01" is being accessed by other users
>>> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres DETAIL: There
>>> is 1 other session using the database.
>>> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres STATEMENT: DROP
>>> DATABASE nectardev_db01;
>>> 2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr(at)nectardev_db01 ERROR:
>>> relation "mas_party" does not exist at character 15
>>> 2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr(at)nectardev_db01
>>> STATEMENT: select * from mas_party
>>> 2024-06-21 03:29:24.233 UTC [100087] postgres(at)nectardev_db01 ERROR:
>>> relation "mas_party" does not exist at character 15
>>> 2024-06-21 03:29:24.233 UTC [100087] postgres(at)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(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-21 03:35:16.593 UTC [100047] postgres(at)postgres FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-21 03:35:16.603 UTC [100039] nectar_dvpr(at)nectardev_db01 FATAL:
>>> terminating connection due to administrator command
>>> 2024-06-21 03:35:16.613 UTC [100010] nectar_dvpr(at)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