# Introduction To follow is a compiled list of **recommendations** on how to improve the security posture of an SQL instance hosted in the cloud. The step-by-step instructions provided below are for **Google Cloud**, but the recommendations should be applicable to **any** database instance hosted on a cloud provider or a VPS. Please note that this is **not** intended to be fully comprehensive. More like common sense basics. Additionally, this focuses on the infrastructure side of things, and does not cover things like **SQL injection** attacks (**sanitize your input!**). ## 1. No Public IP The first and most obvious recommendation is to not assign a public IP to your database. This significantly reduces your attack exposure and keeps things like malicious bots scanning the internet away. Refer to your cloud provider for instructions on how to do that. **Only your application backend should be able to access your database.** For Google Cloud, I would suggest enforcing an Organization Policy that prevents external IP assignment to Cloud SQL. The name of the org policy is `constraints/sql.restrictPublicIp`. Refer to the official [documentation](https://cloud.google.com/sql/docs/mysql/org-policy/configure-org-policy) for more detailed instructions on how to enforce the policy. ## 2. Database Flags This table provides a list of database flags you should pay attention to. I've also provided explanations for each flag as well as the value I would recommend, but you should still use your best judgement according to your needs. |Flag|Recommended Value|PostgreSQL|MySQL|SQL Server|Explanation| |---|---|---|---|---|---| |skip_show_database|On|N|Y|N|Controls whether 'SHOW DATABASES' displays a list of databases.| |local_infile|Off|N|Y|N|Allows loading data from local files using 'LOAD DATA INFILE' statement.| |log_error_verbosity|Default (or more verbose but not less)|Y|N|N|Sets the minimum verbosity of error messages.| |log_connections|On|Y|Y|Y|Logs successful and unsuccessful connection attempts.| |log_disconnections|On|Y|N|Y|Logs disconnection events.| |log_statement|Depends on your needs. ddl  is the default recommended. See [here](https://www.postgresql.org/docs/current/runtime-config-logging.html).|Y|Y|Y|Logs SQL statements.| |log_min_messages|Warning|Y|N|N|Sets the minimum message severity to be logged.| |log_min_error_statement|Error|Y|N|N|Controls which SQL statements are logged based on their error severity.| |log_min_duration_statement|-1 (disabled)|Y|N|N|Logs SQL statements that exceed a specified duration.| |cloudsql.enable_pgaudit|On|N|N|N|Specific to Google Cloud SQL for PostgreSQL. Controls the use of the pgaudit extension.| |external scripts enabled|Off|N|N|Y|Allows running external scripts (e.g., PowerShell or Python scripts) in SQL Server.| |cross db ownership chaining|Off|N|N|Y|Enables or disables cross-database ownership chaining in SQL Server.| |user connections|Non-limiting value. Discuss with your team.|N|N|Y|Specifies the maximum number of simultaneous user connections in SQL Server.| |user options|Should not be configured.|N|N|Y|Used to set various user-specific options in SQL Server.| |3625 (trace flag)|On|N|N|Y|A trace flag in SQL Server that controls specific behavior.| |contained database|Off|N|N|Y|Authentication mode in SQL Server that allows users to connect without a login.| ### Setting the flags using the Console UI: 1. Navigate to [https://console.cloud.google.com/sql/instances](https://console.cloud.google.com/sql/instances) and choose your DB instance. 2. In the navigation panel, click **Overview** to access the configuration details of the instance. 3. Click on the **Edit** button from the dashboard top menu to access the instance edit mode. 4. In the Configuration options section, click on **Flags** to expand the panel with the database flags configured for the selected instance. 5. Repeat this process for every flag that needs to be set: 1. Make sure the flag **applies** to your DB provider as per the table above. 2. Find the flag and set it to the **correct** value. 3. If you don't see the flag, then it was not set before on this instance. Click on **Add Item**, choose the flag from the drop-down menu, then set it to the correct value. ### Setting the flags using Terraform: Setting the flags through TF is straightforward. Here's a dummy example: ```json resource "google_sql_database_instance" "example_instance" { name = "example-instance" database_version = "POSTGRES_12" settings { tier = "db-f1-micro" user_labels = { "env" = "dev" } database_flags { name = "cloudsql.enable_pgaudit" value = "on" } } maintenance_window { day = 1 hour = 4 } } ``` The relevant bit is **database_flags** of course. Follow the example to set the required flags on your DB instance. ## 3. Enable Automated Backups for your Cloud SQL Instance Avoid the possibility of data loss by enabling automated backups on your Cloud SQL Instance. ### Enabling automated backups from the Console UI: 1. Navigate to [https://console.cloud.google.com/sql/instances](https://console.cloud.google.com/sql/instances) and click on the instance you want to configure. 2. In the navigation panel, select **Backups** to access the database backup configuration settings available for the selected instance. 3. In the Settings section, click on **Manage automated backups** to initiate the feature setup process. 4. Inside **Manage Backups** settings configuration box, select the **Automate backups** checkbox and choose the best 4-hour backup window for your database from the configuration dropdown list. 5. Click **Save** to apply the changes and enable automated backups for the selected Google Cloud SQL database instance. ### Enabling automated backups through Terraform: This can be done by utilizing **backup_configuration** under **settings**. Here's a dummy example: ```json settings { backup_configuration { enabled = true binary_log_enabled = true start_time = "15:00" # You can set your preferred backup start time location = "us-central1" # You can set the preferred backup location point_in_time_recovery_enabled = true } } ``` ## 4. Authorized Networks If your database has a public IP address (reconsider), then it is **critical** to ensure that the authorized subnets that are allowed access to your database **never** include 0.0.0.0/0 since that would make the database publicly accessible to **everyone**. If your database does not have a public IP address, then by default **only RFC-1918** address ranges are allowed. You still need to configure authorized networks for clients that do not have an RFC-1918 address and are attempting to connect to the instance on its private IP. ### Configure authorized networks through the Console UI: 1. Navigate to [https://console.cloud.google.com/sql/instances](https://console.cloud.google.com/sql/instances) and click on the instance you want to configure. 2. From the SQL navigation menu, select **Connections**, then click on the **Network** tab. 3. Ensure the list does **not** include 0.0.0.0/0. 4. Select the Public IP checkbox and click **Add a Network**. 5. In the **Name** field, enter a name for this new network. 6. In the **Network** field, enter the **IPv4 CIDR range** that you want to allow connections from. 7. Click **Done** then **Save**. ### Configure authorized networks through Terraform: This is configured using the **authorized_networks** block under **ip_configuration** under settings. Ensure you have no **authorized_networks** block allowing 0.0.0.0/0, and this is a dummy example of adding a range you want to allow: ```json settings { ip_configuration { ipv4_enabled = true authorized_networks { name = "my-authorized-network" value = "CIDR_IP_ADDRESS" } } } ``` ## 5. Enforce SSL You should absolutely **enforce** all incoming connections to your Cloud SQL database instances to use **SSL/TLS**. If the SSL/TLS protocol is not enforced for all Cloud SQL connections, clients without a valid certificate are allowed to connect to the database, and communication is **not** encrypted in transit. ### Enforce SSL for all incoming connections through the Console UI: 1. Navigate to [https://console.cloud.google.com/sql/instances](https://console.cloud.google.com/sql/instances) and click on the instance you want to configure. 2. In the navigation panel, select **Connections** to access the configuration. 3. In the **SSL** section, under **SSL connections**, click **Allow only SSL connections** to allow only secured connections to connect to the selected Cloud SQL database instance. Note that Cloud SQL creates an [SSL server certificate](https://cloud.google.com/sql/docs/sqlserver/configure-ssl-instance%23server-certs) automatically when you create your Cloud SQL database instance. As long as the server certificate remains valid, you don't need to manually manage this certificate. ### Enforce SSL for all incoming connections through Terraform: This is very straightforward. Here's an example: ```json settings { # Enforce SSL for incoming connections require_ssl = true } ``` ## 6. Enable Delete Protection It's a good practice to enable delete protection on your sensitives instances to avoid accidental deletion. Note that there are two relevant **Terraform** parameters: - [*deletion_protection_enabled*](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#deletion_protection_enabled): enables deletion protection of an instance **at the GCP level**. Enabling this protection will guard against accidental deletion across **all surfaces** (API, gcloud, Cloud Console and Terraform) by enabling the [GCP Cloud SQL instance deletion protection](https://cloud.google.com/sql/docs/postgres/deletion-protection). - [*deletion_protection*](https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance#deletion_protection): whether or not to allow **Terraform** to destroy the instance. Unless this field is set to false in Terraform state, a `terraform destroy` or `terraform apply` command that deletes the instance will fail. Defaults to `true`. # Stay vigilant!