Cloud SQL IAM Database Authentication: Manage User Access to the Databases in an Efficient Way
Introduction
Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server offered by Google Cloud Platform. At Zeotap, we use Cloud SQL for our relational databases and our databases are predominantly PostgreSQL.
Every time we create a Cloud SQL instance, we create one admin account for the instance and this admin account has superuser permissions to the databases.
After the instance is created, we log in using the superuser account and create new users with permissions to the databases as required. These new user accounts are not bound to any individual, so it is shared within the team and all team members use the same credentials to log in.
There were multiple issues with this approach -
- “Whodunit”: Since the user account typically has readwrite permissions, anyone can delete tables and rows. With shared credentials, we were unable to identify who executed the queries.
- Overhead in managing users: An alternative to shared credentials would be creating individual user accounts for each user — but with a large team, this becomes difficult to manage.
- Non-repudiation of actions taken by user credentials: Even with the individual user accounts, passwords are set and shared by the admins, so we couldn’t establish that the actions were performed only by that user.
- Password rotation: Per the enterprise password policy, passwords have to be rotated, which adds more overhead to the operations.
With team members working remotely, it was difficult to work with shared credentials or create individual user accounts for each user. Thus, we looked for a more efficient solution to manage user access to the databases with proper auditing.
Cloud SQL IAM Database Authentication
Last year Google cloud announced support for IAM integration to the Cloud SQL. With this, users can login to the Cloud SQL using their email addresses (cloud identity) and the user permissions to the databases can be managed. This requires no passwords, authentication tokens are short-lived, and the users’ login activity are tracked in the logs.
We have implemented Cloud SQL IAM database authentication to all our databases and it made the user management of databases safer, secure, and efficient.
Let’s see how we can setup IAM database authentication in the Cloud Sql.
Two Levels of Access Control
With IAM database authentication, we have to grant permissions at two levels:
- IAM
- Databases
IAM access
This level allows individuals to connect to the Cloud SQL instance using their email addresses.
Steps involved:
- Add the below flag while creating a new instance or update the existing instance. This will allow the Cloud SQL instance to accept IAM authentication.
cloudsql.iam_authentication=on
2. Grant “Cloud SQL Instance user” role to the users. This is to allow users to log in to the instance.
3. Add the users to the Cloud SQL.
Navigate to ‘Console → SQL → Select Instance → Users → Add user account→ Cloud IAM → Enter email address → Add’
Now, the users can connect to the Cloud SQL instance and log in to the databases successfully. However, for querying and modifying the databases, we must grant access at the database level.
Databases access
In PostgreSQL, we can grant permissions directly to the database users. However, this is not a good practice and it is tedious to manage permissions for each user. The solution would be to make use of the roles in PostgreSQL which would simplify the permissions.
To recap: Users, groups, and roles are the same thing in PostgreSQL — the only difference being that users have permission to log in by default.
- Role = User — Login Access
- User = Role + Login Access
We can create multiple roles with specific sets of permissions based on requirements and then assign the appropriate role to each user.
To simplify, let’s create two roles: one with Readonly access, and another with Readwrite access.
ReadOnly role
This role only allows you to run select queries on the tables of the database:
If there are multiple databases in the instance, we can repeat the above commands (except create role) by connecting to those databases.
ReadWrite role
This role allows you to run insert, update, and delete queries on the tables of the database:
If there are multiple databases in the instance, we can repeat the above commands (except create role) by connecting to those databases.
Grant role to the users
After the roles are created, we can grant roles to the users accordingly.
GRANT read_only to firstname.lastname@example.comGRANT read_write to firstname.lastname@example.com
Since the roles are already defined, we just have to grant roles to the users, and we can create new roles or modify the roles as per the requirements. Any changes to the roles will reflect on the users’s access as well.
How to Access Databases Using Cloud SQL IAM Database Authentication
The secure way to connect to a Cloud SQL instance is via CloudSQL Auth proxy.
After installing of latest version Cloud SQL auth proxy binary, we then authenticate to the Google Cloud IAM.
# This will generate a unique token for our user account$ gcloud auth login
After the authentication is successful, we create the proxy connection to the Cloud SQL instance.
$ ./cloud_sql_proxy -enable_iam_login -instances=PROJECT:REGION:CLOUDSQL_INSTANCE_NAME=tcp:portOnce the connection is established, we can connect to the database using our email address.
$ psql "host=127.0.0.1 port=$CLOUDSQLPROXYPORT dbname=$DBNAME user=firstname.lastname@zeotap.com sslmode=disable"or$ PGPASSWORD=$(gcloud auth print-access-token) psql --host=localhost --username=firstname.lastname@zeotap.com --dbname=$DBNAME --port=$CLOUDSQLPROXYPORT --sslmode=disable
Since we are connecting to the CloudSQL instance publicly, it will expect a SSL certificate — so, thesslmode parameter is set to disable.However, the Cloud SQL Auth proxy does provide an encrypted connection.
For users using Postgres utilities such as ‘PGAdmin’, ‘PGCommander’, ‘TablePlus’, the authentication token can be provided as the password.
# To get the authentication token, after gcloud auth login$ gcloud auth print-access-token
We also have the option to session timeout for Google Cloud Platform for a specific window of time, so the authentication token is valid only for the time specified. After the the token expires, a user must repeat ‘gcloud auth login’ to connect to the databases.
Summary
Cloud SQL IAM database authentication helps us better manage database access to users. Access to the instance controlled via IAM, authentication tokens as login passwords, and session timeout for tokens are the most secure, robust ways to manage database user access.
In our next series of posts, we will show how we automate the process of granting IAM access to the individual users.
