IAM Groups: Secure Cloud SQL Authentication!

IAM Groups access to Cloud SQL authentication:

Simplify scalable database authentication and access

Scaling data access management and auditing can be difficult for a fleet of databases with many users. Cloud SQL IAM groups authentication is now available. This release improves security, simplifies user administration and database authentication at scale, and allows database and security administrators to manage database access using IAM-based authentication.

The Cloud SQL Advanced group-based database authentication, IAM groups authentication, lets customers use Google Cloud’s identity service to manage Cloud SQL instance connectivity, access, and permissions. IAM groups authentication adds group-level database access management to IAM authentication. IAM Groups Auth requires MySQL 8.0+.

IAM group authentication adds to Cloud SQL’s IAM database authentication and username/password authentication. IAM authentication provides better security, granular controls, centralized management, and more than usernames and passwords. Using IAM instead of username and password authentication will protect your data without any inconvenience. As with IAM database authentication, IAM Groups authentication provides a familiar user experience.

This blog shows how easy IAM group authentication is to set up and use and provides examples of its utility.

Cloud SQL IAM groups authentication compared. existing?

This upgraded authentication mechanism has various benefits that can save DBAs and security admins time when managing database access. Consider a scenario where numerous users need to conduct queries against a set of tables, store returns data in a retail database, and store catalog data in a products database.They want to efficiently provide and revoke access for all these users at the end of the quarter.

How to do this with Cloud SQL’s traditional IAM authentication and new IAM groups flows:

• Security admin task: granting/revoking group login rights
• Current IAM Authentication Flow: The security admin grants each user enough IAM rights to logon to instance. This manual method is a major admin burden.

Security admins can build a group and allow users to spoof it in other solutions. Account sharing and accountability holes damage security, but this works.

A security admin builds retail and product IAM groups and grants them login access with new IAM Groups Auth. The Google group can be changed to remove users at the end of the quarter when they no longer need retail or product databases. Group IAM and database credentials will be removed from the user.

Security admins can now grant/revoke login credentials to the full group of users more easily using new IAM groups authentication.

• DBA task: Giving/removing group access to database objects like tables
• Present IAM Authentication Flow: The DBA must add all users to the instance and assign database credentials to distinct databases. Again, this is a manual and ad-hoc method that can pose a security risk if the DBA does not revoke access when the quarter ends.

With new IAM Groups Auth, DBAs merely need to add the employee Google group to the instance and provide DB credentials. The DBA does not need to create and grant user privileges. Cloud SQL automatically adds new users and service accounts to the instance without DBA intervention.

• Compliance/audit task: Display user-specific audit logs.
• Retail IAM users will log in as themselves. Audit logs are generated when IAM Group users and service accounts access database data with audit logging enabled.
• Retail table users must log in as Retail IAM and then log out and log in as Product IAM to access Product tables.
• With new IAM group auth, users login and can access the right tables if they belong to the right group.
• IAM group authorization improves user experience by allowing users to access tables or complete operations that require diverse group capabilities in one session.
• Use cases for IAM group authentication

Segregated teams or user roles may need distinct Cloud SQL instance access. IAM group authentication lets you divide teams by roles and responsibilities. Development teams can have a group with solely development database access, whereas operations teams can have a production database group.

• Access for key database tasks: Dropping/creating databases can be restricted to approved groups.
• Audit logs reveal user activity and database access, helping uncover unauthorized access and reduce security threats.
• Start IAM group auth.

• Step 1: Add Cloud Identity groups and members to the project where you control Cloud SQL instances.
• Configure Cloud Identity to federate Google and other identity providers like Active Directory and Azure Active Directory.
• Second, utilize Cloud Identity to add or remove users. Refer to Step 1’s link.
• Step 3: Grant groups Cloud SQL instance login IAM roles. Grant roles using the Google Cloud console on the project’s IAM page using the script below.

Since group members inherit rights, this is only needed for the group. You can use the console or gcloud command to do this.

Assign roles/cloudsql.instanceUser to the group.

Gcloud projects: add-iam-policy-binding <PROJECT_ID>

  Group email address: <GROUP_EMAIL_ADDRESS>

  –role=roles/cloudsql.instanceUser

Step 4: Create a MySQL instance and enable cloudsql_iam_authentication. Otherwise, proceed to Step 5 for an existing instance. This can be done via console.

Gcloud SQL instance creation: INSTANCE_NAME

The database version is MYSQL_VERSION.

CPU: NUMBER_OF_CORES

Setting memory to AMOUNT_OF_MEMORY

Zone: ZONE_NAME

• Root password: PASSWORD

–database-flags=cloudsql_iam_authentication=on

Step 5: Create MySQL groups. To migrate from IAM authentication to IAM Group authentication, you must first remove the existing user. See here for IAM user deletion instructions.

gcloud sql users create <GROUP_EMAIL_ADDRESS> –instance=<YOUR_INSTANCE> –type=cloud_iam_group

Step 6: Give the new group DB access.

Database grants and privileges are done on groups, not individuals. Give users database privileges with the GRANT statement as groups don’t have them.

For a Google Group with the email “example-group@google.com”, the username is example-group.

​

The hostname for a Google Group with the email “example-group@google.com” is google.com.

<TABLE_NAME>: Table name for user access.

Allow select on <TABLE_NAME>.* to “<USERNAME>”@”<HOSTNAME>”

Step 7: Only SSL connections support IAM authentication. See the SSL connection instructions to generate the Certificate Authority (CA), client public key certificate, and client private key needed to connect.

Step 8: Users sign in to MySQL using gcloud auth login for IAM and auth activate-service-account for service accounts. For IAM users, this is their email address without the @ or domain name. Enter test-user@gmail.com. Service accounts have email addresses without the @project-id.iam.gserviceaccount.com suffix.

MYSQL_PWD=`gcloud sql generate-login-token` mysql –enable-cleartext-plugin

         –ssl-ca=server-ca.pem –ssl-cert=client-cert.pem

         SSL=client-key.pem –host=<INSTANCE_IP>

         –user=<USERNAME>

The database automatically creates the user or service account upon first login. Group database privileges are passed to users and service accounts.

Users can login via Cloud SQL Auth Proxy.

Run the following command to list all instance IAM group users:

“gcloud sql users list –instance=<INSTANCE_NAME>”

User type CLOUD_IAM_GROUP_USER displays IAM group authentication users.

Service accounts authenticated by IAM Group will be presented as CLOUD_IAM_GROUP_SERVICE_ACCOUNT.

Instance-added groups are represented as CLOUD_IAM_GROUP.

Cloud Identity may alter group membership rights as user needs change.

• Last thoughts
• Why bother with Cloud SQL IAM group authentication? Answer is simple:
• Better security
• For better user management
• Simplify temporary access management and operations
• To segregate data and regulate user group access in a multi-tenant or shared database.
• Enforcement of granular security
• For scale
• AND for improved DB usability

The runbook above can help you switch to Cloud SQL IAM group auth to increase access control, security, operational efficiency, and user permissions.