Re: My Database getting dropped anonymously
| От | Zaid Shabbir |
|---|---|
| Тема | Re: My Database getting dropped anonymously |
| Дата | |
| Msg-id | CABCJe_XHGFMfOnAqUhqzT+nJX+LM3=2ZziJ+5YVVh4+z65_PDw@mail.gmail.com обсуждение исходный текст |
| Ответ на | My Database getting dropped anonymously (Sekar S <sekar.newtran@gmail.com>) |
| Ответы |
Re: My Database getting dropped anonymously
|
| Список | pgsql-admin |
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 connectionsI 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 youSekar
В списке pgsql-admin по дате отправления: