Google Cloud SQL

PostgreSQL Connection Logs Disabled

Ensures that log_connections flag is enabled for PostgreSQL instances. 

Risk Level: Low

Description

This plugin ensures that the log connections flag is enabled for SQL instances of the PostgreSQL type. The log connections flag is available in SQL instances for PostgreSQL databases and is turned off by default. It's used to keep track of all attempts to connect to the database server. It also logs all the successful client authentication and authorization attempts. Thus, enabling it will ensure that all connection attempts are logged.

About the Service

Google Cloud SQL:

Google Cloud SQL is a relational database for MySQL, PostgreSQL, and SQL Server that is fully managed. It automates database provisioning, storage capacity management, replication, and backups while lowering maintenance costs. It can be set up easily using the built-in migration tools and lets you scale your instances effortlessly. To know more about Cloud SQL, read here

Impact

If the log_connections flag is not set to on, the connection attempts will not be logged. Additionally, there will be no record of all successful client authentication and authorization completions. As a result, in the event of a configuration error, troubleshooting and fixing it will be far more complex.

Steps to Reproduce

Using GCP Console-

  1. Log In to your GCP Console.
  2. From the top navigation bar, select the GCP project you want to investigate.
  3. From the navigation panel on the left side of the console, go to SQL. You can use this link here to navigate directly if you’re already logged in.
  4. Set Type to PostgreSQL in the Filter box to only see PostgreSQL database instances.
  5. Select the ID of the SQL instance you want to investigate from the list of instances available and click on the OVERVIEW tab to check the configuration settings of the selected instance.
  6. In the Database flags section under Configuration, check the configuration of log_connections. If it is set to off or if there is no log_connections flag set then the log_connections flag is disabled for the selected SQL instance.

    (or)
  7. Repeat steps 5 and 6 for all the SQL instances you want to investigate in the selected project.
  8. If you have multiple projects, repeat steps 2 to 7 for each project in your GCP Console. 

Steps for Remediation

Determine whether or not you truly require connection logs to be disabled for your SQL instances. If not, make the necessary changes to enable it using the steps below.


Using GCP Console-

  1. Log In to your GCP Console.
  2. From the top navigation bar, select the GCP project you want to investigate.
  3. From the navigation panel on the left side of the console, go to SQL. You can use this link here to navigate directly if you’re already logged in.
  4. Set Type to PostgreSQL in the Filter box to only see PostgreSQL database instances.
  5. Select the ID of the SQL instance you want to reconfigure in the list of instances available. (In case you aren’t sure which SQL instance needs to be configured, follow the steps to reproduce listed above to determine which to choose.)
  6. Go to the OVERVIEW tab and click on the Edit button found on the top navigation bar.

  7. Under the Configuration section, click on Flags and set the status of log_connections to on. Click the SAVE button to save all the changes.
    Note: If you do not find the log_connections flag, click on the Add item button, choose log_connections from the dropdown list provided and set the status to on.
  8. Repeat steps 5 to 7 for all the SQL instances you want to reconfigure in the selected project.
  9. If you have multiple projects, repeat steps 2 to 8 for each project in your GCP console.