Google Cloud SQL Database Instance

google_sql_database_instance (Terraform)

The Database Instance in Cloud SQL can be configured in Terraform with the resource name google_sql_database_instance. The following sections describe 5 examples of how to use the resource and its parameters.

Example Usage from GitHub

infracost/infracost

resource "google_sql_database_instance" "sql_server" {
  name             = "master-instance"
  database_version = "SQLSERVER_2017_ENTERPRISE"
  settings {
    tier              = "db-custom-16-61440"
    availability_type = "ZONAL"

Checkmarx/kics

resource "google_sql_database_instance" "positive1" {
  name             = "master-instance"
  database_version = "POSTGRES_11"
  region           = "us-central1"

  settings {

gilyas/infracost

resource "google_sql_database_instance" "sql_server" {
  name             = "master-instance"
  database_version = "SQLSERVER_2017_ENTERPRISE"
  settings {
    tier              = "db-custom-16-61440"
    availability_type = "ZONAL"

leonidweinbergcx/mykics

resource "google_sql_database_instance" "positive1" {
  name             = "master-instance"
  database_version = "POSTGRES_11"
  region           = "us-central1"

  settings {

Checkmarx/kics

resource "google_sql_database_instance" "positive1" {
    name             = "master-instance"
    database_version = "POSTGRES_11"
    region           = "us-central1"

    settings {

Security Best Practices for google_sql_database_instance

There are 12 settings in google_sql_database_instance that should be taken care of for security reasons. The following section explain an overview and example code.

Ensure to disable local_infile setting in MySQL

It is better to disable the local_infile setting in MySQL. If this is not disabled, arbitrary files might be readable.

Ensure to enable automated backups for the recovery to prevent any data-losses

It is better to enable automated backups as the default to restore the data when we face corruption or data-loss incident.

Ensure to disable cross-database ownership chaining

It is better to disable cross-database ownership chaining. The security feature provided by SQL Server allows users to access the other databases are been currently using.

Ensure to disable contained database authentication

It is better to disable contained database authentication. Users having ALTER permissions might grant access to the database.

Ensure to enable SSL connections to a SQL database instance

It is better to enable SSL connections to a SQL database instance. If in-transit data is encrypted, the data will not be exposed.

Ensure to enable checkpoints logging in PostgreSQL

It is better to enable checkpoints logging in PostgreSQL. This provides beneficial information to identify potential performance issues.

Ensure to enable logging connections in PostgreSQL

It is better to enable logging connections in PostgreSQL. This provides beneficial information such as session length to identify potential performance issues.

Ensure to enable logging disconnections in PostgreSQL

It is better to enable logging disconnections in PostgreSQL. This provides beneficial information such as session length to identify potential performance issues.

Ensure to enable temporary file logging should for all temporary files.

It is better to enable temporary file logging as the default. The `value` should be set as `0` in the `log_temp_files` flag to log all files greater than 0 bytes.

Ensure Cloud SQL database instances are not publicly exposed

It is better to avoid public access from database instances to prevent the disclosure of sensitive data.

Ensure to configure the error log severity in PostgreSQL

It is better to configure the error log severity in PostgreSQL properly. If we set the minimum log severity too high will cause errors not to be logged.

Ensure to enable logging of lock waits in PostgreSQL

It is better to enable logging of lock waits in PostgreSQL, which might be the cause of poor performance and availability issues.

Parameters

• connection_name optional computed - string

The connection name of the instance to be used in connection strings. For example, when connecting with Cloud SQL Proxy.

• database_version optional - string

The MySQL, PostgreSQL or SQL Server (beta) version to use. Supported values include MYSQL_5_6, MYSQL_5_7, MYSQL_8_0, POSTGRES_9_6,POSTGRES_11, SQLSERVER_2017_STANDARD, SQLSERVER_2017_ENTERPRISE, SQLSERVER_2017_EXPRESS, SQLSERVER_2017_WEB. Database Version Policies includes an up-to-date reference of supported versions.

• deletion_protection optional - bool

Used to block Terraform from deleting a SQL Instance.

• first_ip_address optional computed - string

The first IPv4 address of any type assigned. This is to support accessing the first address in the list in a terraform output when the resource is configured with a count.

• id optional computed - string
• ip_address optional computed - list of object
  • ip_address - string
  • time_to_retire - string
  • type - string
• master_instance_name optional computed - string

The name of the instance that will act as the master in the replication setup. Note, this requires the master to have binary_log_enabled set, as well as existing backups.

• name optional computed - string

The name of the instance. If the name is left blank, Terraform will randomly generate one when the instance is first created. This is done because after a name is used, it cannot be reused for up to one week.

• private_ip_address optional computed - string

IPv4 address assigned. This is a workaround for an issue fixed in Terraform 0.12 but also provides a convenient way to access an IP of a specific type without performing filtering in a Terraform config.

• project optional computed - string

The ID of the project in which the resource belongs. If it is not provided, the provider project is used.

• public_ip_address optional computed - string

IPv4 address assigned. This is a workaround for an issue fixed in Terraform 0.12 but also provides a convenient way to access an IP of a specific type without performing filtering in a Terraform config.

• region optional computed - string

The region the instance will sit in. Note, Cloud SQL is not available in all regions. A valid region must be provided to use this resource. If a region is not provided in the resource definition, the provider region will be used instead, but this will be an apply-time error for instances if the provider region is not supported with Cloud SQL. If you choose not to provide the region argument for this resource, make sure you understand this.

• root_password optional - string

Initial root password. Required for MS SQL Server, ignored by MySQL and PostgreSQL.

• self_link optional computed - string

The URI of the created resource.

• server_ca_cert optional computed - list of object
  • cert - string
  • common_name - string
  • create_time - string
  • expiration_time - string
  • sha1_fingerprint - string
• service_account_email_address optional computed - string

The service account email address assigned to the instance.

• clone list block
  • point_in_time required - string

  The timestamp of the point in time that should be restored.

  • source_instance_name required - string

  The name of the instance from which the point in time should be restored.

• replica_configuration list block
  • ca_certificate optional - string

  PEM representation of the trusted CA's x509 certificate.

  • client_certificate optional - string

  PEM representation of the replica's x509 certificate.

  • client_key optional - string

  PEM representation of the replica's private key. The corresponding public key in encoded in the client_certificate.

  • connect_retry_interval optional - number

  The number of seconds between connect retries.

  • dump_file_path optional - string

  Path to a SQL file in Google Cloud Storage from which replica instances are created. Format is gs://bucket/filename.

  • failover_target optional - bool

  Specifies if the replica is the failover target. If the field is set to true the replica will be designated as a failover replica. If the master instance fails, the replica instance will be promoted as the new master instance.

  • master_heartbeat_period optional - number

  Time in ms between replication heartbeats.

  • password optional - string

  Password for the replication connection.

  • ssl_cipher optional - string

  Permissible ciphers for use in SSL encryption.

  • username optional - string

  Username for replication connection.

  • verify_server_certificate optional - bool

  True if the master's common name value is checked during the SSL handshake.

• restore_backup_context list block
  • backup_run_id required - number

  The ID of the backup run to restore from.

  • instance_id optional - string

  The ID of the instance that the backup was taken from.

  • project optional - string

  The full project ID of the source instance.

• settings list block
  • activation_policy optional computed - string

  This specifies when the instance should be active. Can be either ALWAYS, NEVER or ON_DEMAND.

  • authorized_gae_applications optional computed - list of string

  This property is only applicable to First Generation instances. First Generation instances are now deprecated, see https://cloud.google.com/sql/docs/mysql/deprecation-notice for information on how to upgrade to Second Generation instances. A list of Google App Engine project names that are allowed to access this instance.

  • availability_type optional computed - string

  The availability type of the Cloud SQL instance, high availability (REGIONAL) or single zone (ZONAL). For MySQL instances, ensure that settings.backup_configuration.enabled and settings.backup_configuration.binary_log_enabled are both set to true.

  • crash_safe_replication optional computed - bool

  This property is only applicable to First Generation instances. First Generation instances are now deprecated, see here for information on how to upgrade to Second Generation instances. Specific to read instances, indicates when crash-safe replication flags are enabled.

  • disk_autoresize optional - bool

  Configuration to increase storage size automatically. Note that future terraform apply calls will attempt to resize the disk to the value specified in disk_size - if this is set, do not set disk_size.

  • disk_size optional computed - number

  The size of data disk, in GB. Size of a running instance cannot be reduced but can be increased.

  • disk_type optional computed - string

  The type of data disk: PD_SSD or PD_HDD.

  • pricing_plan optional - string

  Pricing plan for this instance, can only be PER_USE.

  • replication_type optional computed - string

  This property is only applicable to First Generation instances. First Generation instances are now deprecated, see here for information on how to upgrade to Second Generation instances. Replication type for this instance, can be one of ASYNCHRONOUS or SYNCHRONOUS.

  • tier required - string

  The machine type to use. See tiers for more details and supported versions. Postgres supports only shared-core machine types such as db-f1-micro, and custom machine types such as db-custom-2-13312. See the Custom Machine Type Documentation to learn about specifying custom machine types.

  • user_labels optional computed - map from string to string

  A set of key/value user label pairs to assign to the instance.

  • version optional computed - number

  Used to make sure changes to the settings block are atomic.

  • backup_configuration list block
    • binary_log_enabled optional - bool

    True if binary logging is enabled. If settings.backup_configuration.enabled is false, this must be as well. Cannot be used with Postgres.

    • enabled optional - bool

    True if backup configuration is enabled.

    • location optional - string

    Location of the backup configuration.

    • point_in_time_recovery_enabled optional - bool

    True if Point-in-time recovery is enabled.

    • start_time optional computed - string

    HH:MM format time indicating when backup configuration starts.

    • transaction_log_retention_days optional computed - number

    The number of days of transaction logs we retain for point in time restore, from 1-7.

    • backup_retention_settings list block
      • retained_backups required - number

      Number of backups to retain.

      • retention_unit optional - string

      The unit that 'retainedBackups' represents. Defaults to COUNT

  • database_flags list block
    • name required - string

    Name of the flag.

    • value required - string

    Value of the flag.

  • insights_config list block
    • query_insights_enabled optional - bool

    True if Query Insights feature is enabled.

    • query_string_length optional - number

    Maximum query length stored in bytes. Between 256 and 4500. Default to 1024.

    • record_application_tags optional - bool

    True if Query Insights will record application tags from query when enabled.

    • record_client_address optional - bool

    True if Query Insights will record client address when enabled.

  • ip_configuration list block
    • ipv4_enabled optional - bool

    Whether this Cloud SQL instance should be assigned a public IPV4 address. At least ipv4_enabled must be enabled or a private_network must be configured.

    • private_network optional - string

    The VPC network from which the Cloud SQL instance is accessible for private IP. For example, projects/myProject/global/networks/default. Specifying a network enables private IP. At least ipv4_enabled must be enabled or a private_network must be configured. This setting can be updated, but it cannot be removed after it is set.

    • require_ssl optional - bool
    • authorized_networks set block
      • expiration_time optional - string
      • name optional - string
      • value required - string
  • location_preference list block
    • follow_gae_application optional - string

    A Google App Engine application whose zone to remain in. Must be in the same region as this instance.

    • zone optional - string

    The preferred compute engine zone.

  • maintenance_window list block
    • day optional - number

    Day of week (1-7), starting on Monday

    • hour optional - number

    Hour of day (0-23), ignored if day not set

    • update_track optional - string

    Receive updates earlier (canary) or later (stable)

• timeouts single block
  • create optional - string
  • delete optional - string
  • update optional - string

Explanation in Terraform Registry

Creates a new Google SQL Database Instance. For more information, see the official documentation, or the JSON API.

NOTE on google_sql_database_instance: - Second-generation instances include a default 'root'@'%' user with no password. This user will be deleted by Terraform on instance creation. You should use google_sql_user to define a custom user with a restricted host and strong password. -> Note: On newer versions of the provider, you must explicitly set deletion_protection=false (and run terraform apply to write the field to state) in order to destroy an instance. It is recommended to not set this field (or set it to true) until you're ready to destroy the instance and its databases.