Re: My Database getting dropped anonymously

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
>
>
>
>
>