I have an application that is using Google CloudSQL (postgres). I am trying to use an IAM user in one of the database using a circleCI orb provided by my organization. But, problem is the orb is using a generic IAM user and I want to create an application specific IAM user. When I try to do it, I am getting following error-
cloudsql database username provided by client does not match the authenticated user's email
I also tried to search on the web using the above error but did not find anything useful.
Isn’t there any way or workaround to bypass this rule? Do I have to always logged in to Google cloud using same IAM user as I am trying to create in DB?
2
You are facing this error because the IAM user you are trying to use is not matching with the database username.
As per this GCP’s official document
You must create a new user account for each individual IAM user or service account that you are adding to the Cloud SQL instance in order to access databases. If you are adding an IAM group, then you don’t need to create a user account for each member of that group.
The database username must be the IAM user’s email address and all lowercase. For example, [email protected].
So in your case you can try by creating a new database user that matches the IAM identity you are trying to use.
The other way is to ensure your IAM user has sufficient permissions for cloud sql and permission to create and manage database users and roles.
Refer these documents to know more about IAM database authentication and Connecting to Cloud SQL instances.
1
Do I have to always be logged in to Google cloud using same IAM user as I am trying to create in DB?
Yes, the IAM service account or user logged in (authenticated) to Google Cloud from the environment you are running in must be the same as the database user for IAM database authentication to work.
The reason for this being security. Otherwise any authenticated user could log in to the database as a different IAM user that they shouldn’t be authorized to.
Essentially IAM database authentication boils down to logging into a database using an OAuth2 token as the database password. This OAuth2 token must belong to the IAM service account or user being passed in as the database user.
Isn’t there any way or workaround to bypass this rule?
Have you tried using service account impersonation?
You could add your application specific IAM principal as an IAM database user on your Cloud SQL instance and then grant your circleCI generic IAM user the proper permissions to impersonate the application principal.
It is worth noting that service account impersonation support is built-in to the Cloud SQL Proxy and example command is shown here.
1