<commit_before># terraform-google-sql for Secure MySQL

The module sets up a MySQL installation that relies on Cloud IAM for
authenticating its users. The use of Cloud IAM centralises identty
management, access control, and permits to strengthen the credentials
being used for authentication.

## Cloud IAM Policy Recommendation

We have two levels of access to Cloud SQL: access to the data, and
administrative access to the server's configurations. There two
level of access correspond to the `roles/cloudsql.admin` and
`roles/cloudsql.client`.

`roles/cloudsql.admin`:

-   Modifications to the instance configurations should be very rare, we suggest
    to assign such permissions only to automation users (e.g.,
    Terraform run as part of a CI/CD pipeline), or as part of breakglass access
    to the project's resources.

`roles/cloudsql.client`

-   Service accounts of applications can be bound directly to the role (e.g.,
    `<EMAIL>`)

-   Humans that need to access the data for debugging purposes should get the
    access via membership group. Make sure that the group allows only members
    from within the organization, and that only invited members can join.

You can add the following Cloud IAM snippet to the project policy:

```
    - auditLogConfigs:
    - logType: DATA_READ
    - logType: DATA_WRITE
    service: cloudsql.googleapis.com

    - members:
      - group:<EMAIL>
    role: roles/cloudsql.admin

    - members:
      - group:<EMAIL>
      - serviceAccount:<EMAIL>
    role: roles/cloudsql.client
```

## Define MySQL users and passwords on your instance

MySQL usernames and passwords are a secondary access control mechanisms (after
Cloud IAM) that can be used to further restrict access for reliability, safety
purposes. For example, removing the ability of modifying tables from production
users that don't need such a capability.

The module, by default, creates users that:

-   only allowed from host ~cloudsqlproxy to ensure that nobody can access the
    data from outside cloudproxy.
-   have randomly generated passwords, which can be stored in configuration
    files. Such passwords can be considered as secure as API Keys rather than
    strong credentials for access.

To maintain the user list manageable, we suggest to consider MySQL users in a
way similar to roles rather than individual human users. For example, a simple
application could define the following roles:

-   **admin**: root-like users with full permissions on all tables and data.
    This can be used in breakglass emergencies, or as part of the release cycle
    to perform structural modifications of the DB (e.g., add tables and columns)

-   **app**: permissions required for running the application. Generally, it
    will have read/write permissions on data, but no ability of performing
    structural modifications to the DB.

    *   Multiple roles for different apps can be defined if more protection
        against accidental errors is desired.

-   **readonly**: permissions required for reading data in the database for
    debugging or customer support.

*   Once users are created, you need to assign them
    permissions by executing the following commands on a MySQL client.
    *   The permissions can be customized further is more fine-grained access is
        appropriate.

```
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'cloudproxy~%';
GRANT SELECT, INSERT, UPDATE, LOCK TABLES ON your_app_db.*
  TO 'app'@'cloudproxy~%';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'readonly'@'cloudproxy~%';
GRANT SELECT ON your_app_db.* TO 'readonly'@'cloudproxy~%';

FLUSH PRIVILEGES;
```


## SWE / SRE Workflows

The rest of the workflow is implemented by SREs and SWEs using the instance.

### Access the Cloud SQL instance from production

*   Create a service account and assign to it the `roles/cloudsql.client`
    permissions as described above.
*   Use the service account in your VMs or GKE node pools, so that it can be
    used by application as the default service account of the machine.
*   For GKE and VM, setup CloudSQL proxy as described in the
    [public documentation](https://cloud.google.com/sql/docs/mysql/sql-proxy).
*   User and password of the MySQL user can be set as configurations of the
    application, or GKE secrets.

*   All applications should construct SQL queries following the principles and
    libraries described in go/safesqltypes to meet Requirement 3.1.

### Human Access to the Cloud SQL instance data.

*   Download and install Cloud SQL proxy on their workstation as
    described in the
    [public documentation](https://cloud.google.com/sql/docs/mysql/sql-proxy).

```
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy
```

```
mkdir $HOME/mysql_sockets
./cloud_sql_proxy --dir=$HOME/mysql_sockets --instances=myproject:region:instance

mysql -S $HOME/mysql_sockets/myproject:region:instance -u user -p
```


[^]: (autogen_docs_start)

## Inputs

| Name | Description | Type | Default | Required |
|------|-------------|:----:|:-----:|:-----:|
| activation\_policy | The activation policy for the master instance. Can be either `ALWAYS`, `NEVER` or `ON_DEMAND`. | string | `"ALWAYS"` | no |
| additional\_databases | A list of databases to be created in your cluster | list | `<list>` | no |
| additional\_users | A list of users to be created in your cluster | list | `<list>` | no |
| assign\_public\_ip | Set tp true if the master instance should also have a public IP. | string | `"false"` | no |
| authorized\_gae\_applications | The list of authorized App Engine project names | list | `<list>` | no |
| backup\_configuration | The backup configuration block of the Cloud SQL resources This argument will be passed through the master instance directrly.<br><br>See [more details](https://www.terraform.io/docs/providers/google/r/sql_database_instance.html). | map | `<map>` | no |
| create\_timeout | The optional timout that is applied to limit long database creates. | string | `"10m"` | no |
| database\_flags | The database flags for the master instance. See [more details](https://cloud.google.com/sql/docs/mysql/flags) | list | `<list>` | no |
| database\_version | The database version to use | string | n/a | yes |
| db\_charset | The charset for the default database | string | `""` | no |
| db\_collation | The collation for the default database. Example: 'utf8_general_ci' | string | `""` | no |
| db\_name | The name of the default database to create | string | `"default"` | no |
| delete\_timeout | The optional timout that is applied to limit long database deletes. | string | `"10m"` | no |
| disk\_autoresize | Configuration to increase storage size | string | `"true"` | no |
| disk\_size | The disk size for the master instance | string | `"10"` | no |
| disk\_type | The disk type for the master instance. | string | `"PD_SSD"` | no |<commit_msg>Updated text on the pattern<commit_after># terraform-google-sql for Secure MySQL

The module sets up a MySQL installation that relies on Cloud IAM for
authenticating its users. The use of Cloud IAM centralises identty
management, access control, and permits to strengthen the credentials
being used for authentication.

The module uses the terraform-google-sql module for MySQL, but controls
more strictly the type of network connections that are allowed: in all
cases, network connections need to be mediated via the Cloud SQL Proxy and,
hence, be authorized via Cloud IAM.

The most secure setup doesn't assign a public IP to the instance 
(assign_public_ip = "false"), and permits connections only from within the
given VPC. In such a setup, sharing the instance between projects requires
setting up Shared VPCs, and direct mysql access from engineering workstations
is not possible: any debugging needs to be performed via bastion hosts or
custom tooling running on VMs connected to the VPC.

If assign_public_ip = "true", the instance will be assigned a public IP. However,
the module ensures that no authorized networks can be configured on the instance,
so all accesses are need to be mediated via the Cloud SQL proxy and
authenticated via Cloud IAM. Such a setup still provide strong identity
guarantees that go beyond the use of only username/password or long-lived
certificates.

## Cloud IAM Policy Recommendation

We have two levels of access to Cloud SQL: access to the data, and
administrative access to the server's configurations. There two
level of access correspond to the `roles/cloudsql.admin` and
`roles/cloudsql.client`.

`roles/cloudsql.admin`:

-   Modifications to the instance configurations should be very rare, we suggest
    to assign such permissions only to automation users (e.g.,
    Terraform run as part of a CI/CD pipeline), or as part of breakglass access
    to the project's resources.

`roles/cloudsql.client`

-   Service accounts of applications can be bound directly to the role (e.g.,
    `<EMAIL>`)

-   Humans that need to access the data for debugging purposes should get the
    access via membership group. Make sure that the group allows only members
    from within the organization, and that only invited members can join.

You can add the following Cloud IAM snippet to the project policy:

```
    - auditLogConfigs:
    - logType: DATA_READ
    - logType: DATA_WRITE
    service: cloudsql.googleapis.com

    - members:
      - group:<EMAIL>
    role: roles/cloudsql.admin

    - members:
      - group:<EMAIL>
      - serviceAccount:<EMAIL>
    role: roles/cloudsql.client
```

## Define MySQL users and passwords on your instance

Because Cloud IAM acts as a primary athentication and authorization mechanism, 
we consider MySQL usernames and passwords are a secondary access controls that
can be used to further restrict access for reliability or safety
purposes. For example, removing the ability of modifying tables from production
users that don't need such a capability.

The module, by default, creates users that:

-   only allow connections from host ~cloudsqlproxy to ensure that nobody can
    access data without connecting via the Cloud SQL Proxy.
-   have randomly generated passwords, which can be stored in configuration
    files. Such passwords can be considered as secure as API Keys rather than
    strong credentials for access.

To maintain the user list manageable, we suggest to consider MySQL users in a
way similar to roles rather than individual human users. For example, a simple
application could define the following roles:

-   **admin**: root-like users with full permissions on all tables and data.
    This can be used in breakglass emergencies, or as part of the release cycle
    to perform structural modifications of the DB (e.g., add tables and columns)

-   **app**: permissions required for running the application. Generally, it
    will have read/write permissions on data, but no ability of performing
    structural modifications to the DB.

    *   Multiple roles for different apps can be defined if more protection
        against accidental errors is desired.

-   **readonly**: permissions required for reading data in the database for
    debugging or customer support.

*   Once users are created, you need to assign them
    permissions by executing the following commands on a MySQL client.
    *   The permissions can be customized further is more fine-grained access is
        appropriate.

```
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'cloudproxy~%';
GRANT SELECT, INSERT, UPDATE, LOCK TABLES ON your_app_db.*
  TO 'app'@'cloudproxy~%';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'readonly'@'cloudproxy~%';
GRANT SELECT ON your_app_db.* TO 'readonly'@'cloudproxy~%';

FLUSH PRIVILEGES;
```


## SWE / SRE Workflows

The rest of the workflow is implemented by SREs and SWEs using the instance.

### Access the Cloud SQL instance from production

*   Create a service account and assign to it the `roles/cloudsql.client`
    permissions as described above.
*   Use the service account in your VMs or GKE node pools, so that it can be
    used by application as the default service account of the machine.
*   For GKE and VM, setup CloudSQL proxy as described in the
    [public documentation](https://cloud.google.com/sql/docs/mysql/sql-proxy).
*   User and password of the MySQL user can be set as configurations of the
    application, or GKE secrets.

*   All applications should construct SQL queries following principles and
    libraries that protect against SQL Injection Attacks.

### Human Access to the Cloud SQL instance data.

*   Download and install Cloud SQL proxy on their workstation as
    described in the
    [public documentation](https://cloud.google.com/sql/docs/mysql/sql-proxy).

```
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy
```

Connections from engineers' workstations is directly possible only if the instance
has been assiged a public ip (assign_public_ip = "true"). If only Private IPs are
used, then access needs to be mediated by bastion hosts connected to the same VPC,
or through custom UI or other tools.

If public IP is avaible, engineers can use the following process to connect:

```
mkdir $HOME/mysql_sockets
./cloud_sql_proxy --dir=$HOME/mysql_sockets --instances=myproject:region:instance

mysql -S $HOME/mysql_sockets/myproject:region:instance -u user -p
```

[^]: (autogen_docs_start)

## Inputs

| Name | Description | Type | Default | Required |
|------|-------------|:----:|:-----:|:-----:|
| activation\_policy | The activation policy for the master instance. Can be either `ALWAYS`, `NEVER` or `ON_DEMAND`. | string | `"ALWAYS"` | no |
| additional\_databases | A list of databases to be created in your cluster | list | `<list>` | no |
| additional\_users | A list of users to be created in your cluster | list | `<list>` | no |
| assign\_public\_ip | Set tp true if the master instance should also have a public IP. | string | `"false"` | no |
| authorized\_gae\_applications | The list of authorized App Engine project names | list | `<list>` | no |
| backup\_configuration | The backup configuration block of the Cloud SQL resources This argument will be passed through the master instance directrly.<br><br>See [more details](https://www.terraform.io/docs/providers/google/r/sql_database_instance.html). | map | `<map>` | no |
| create\_timeout | The optional timout that is applied to limit long database creates. | string | `"10m"` | no |
| database\_flags | The database flags for the master instance. See [more details](https://cloud.google.com/sql/docs/mysql/flags) | list | `<list>` | no |
| database\_version | The database version to use | string | n/a | yes |
| db\_charset | The charset for the default database | string | `""` | no |
| db\_collation | The collation for the default database. Example: 'utf8_general_ci' | string | `""` | no |
| db\_name | The name of the default database to create | string | `"default"` | no |
| delete\_timeout | The optional timout that is applied to limit long database deletes. | string | `"10m"` | no |
| disk\_autoresize | Configuration to increase storage size | string | `"true"` | no |
| disk\_size | The disk size for the master instance | string | `"10"` | no |
| disk\_type | The disk type for the master instance. | string | `"PD_SSD"` | no |
{'fraction_non_alphanumeric': 0.08057582073996873, 'fraction_numerical': 0.0013027618551328818, 'mean_word_length': 3.8863781031190325, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 10, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3197263', 'n_tokens_mistral': 4205, 'n_tokens_neox': 4054, 'n_words': 1962}