Re: My Database getting dropped anonymously

Hi Sekar,

A little confused, try following

- Telnet the IP and Port from your windows machine. It will let you
know whether the ip and port are accessible or not from your desktop
machine.

- I believe for connecting from outside, AZURE must be giving public IP,
try to connect via that.

- You may add a new firewall rule, to allow your desktop's public IP
address, by going to the Azure portal, navigating to your PostgreSQL server
and adding rule in Networking or Firewall rules.
- In the postgres.conf file update the listen_address with localhost,
your IP address.
- In pg_hba.conf add host all all 192.168.xxx.xxx/32 scram-sha-256

On Azure portal you may explore networking and look for setting up virtual
private network as well.

Hope you will be able to connect. Sorry I don't have much experience with
Azure cloud... In the past I had used it for a very brief time to work on
an assignment but things are not on top of my mind now.

- Regards,
- Muhammad Ikram
-

On Sat, Jun 22, 2024 at 2:35 PM Sekar S <sekar(dot)newtran(at)gmail(dot)com> wrote:

> 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(at)gmail(dot)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(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
>>
>>

--
Muhammad Ikram