Enable Connections logs on Azure Database for PostgreSQL

Question

How can I configure Azure Database for PostgreSQL to log all connection attempts, including the IP addresses of clients, whether the connection is successful or failed? I want to monitor and log every IP trying to connect to our database for auditing and security purposes.

Answer 1

Hi @Mahmoud AbdelRhaman Tag Eldeen Elzahaby,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, To configure Azure Database for PostgreSQL to log all connection attempts, including the IP addresses of clients, you need to enable and configure the database's logging and auditing features.

Please follow the below mentioned steps:

Azure Database for PostgreSQL supports query and connection logging. You can enable the relevant settings through the Azure portal.

1. Go to the Azure Portal and navigate to your Azure Database for PostgreSQL server -->Settings--> Server Parameters. Set the following parameters: • log_connections: Set this to ON to log successful connection attempts. • log_disconnections: Set this to ON to log disconnection events. • log_hostname: Set this to ON to include the client's hostname (if resolvable) or IP address in the logs. • log_statement: Optionally, set this to 'none' to log connection details without including SQL statements.
2. Azure Database for PostgreSQL logs are stored in the Azure Monitor Logs. You can set up log retention and define how long you wish to keep the logs. Go to the Azure Portal and navigate to your Azure Database for PostgreSQL server Monitoring-->Diagnostic settings. Add a diagnostic setting to send logs to an Azure Storage Account, Log Analytics workspace, or Event Hub-->Enable the logs under the Category details section-->PostgreSQL Server Logs
3. If logs are sent to Log Analytics, set up a data retention policy under your Log Analytics workspace.
4. Once logging is enabled, you can access connection logs using Azure monitor or log analytics. Use queries in Log Analytics to monitor logs.

AzureDiagnostics
| where ResourceType == "POSTGRESQLFLEXIBLESERVER"
| where Message contains "connection received" or Message contains "authentication failed"
| project TimeGenerated, Resource, Message

If you configured Azure Storage for log storage, access the logs directly in your specified storage account. you can set up alerts on specific log patterns using security and monitoring.

• Use Log Analytics or Azure Monitor Alerts.

• Create an alert rule for failed connection attempts.

Example:

AzureDiagnostics
| where Message contains "authentication failed"
| summarize Count=count() by bin(TimeGenerated, 5m)
| where Count > 5

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-audit?tabs=portal

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-server-parameters

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-and-access-logs

https://github.com/pgaudit/pgaudit/blob/main/README.md#settings

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.