From: | Piergiorgio Valli <piervalli(at)gmail(dot)com> |
---|---|
To: | Zaid Shabbir <zaidshabbir(at)gmail(dot)com> |
Cc: | Sekar S <sekar(dot)newtran(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: My Database getting dropped anonymously |
Date: | 2024-06-21 06:51:00 |
Message-ID: | CALJ6nyw2p8B1RZ8oJ479ccvu5hsZQZZUE91rRcRw_302UW_VHg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>>
>>
>>
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Muhammad Ikram | 2024-06-21 09:56:07 | Re: My Database getting dropped anonymously |
Previous Message | Zaid Shabbir | 2024-06-21 05:34:06 | Re: My Database getting dropped anonymously |