How do I debug an error using IAM authentication?

[garimasingh2016]

Question

I am trying to connect to a PostgresSQL database using a service account and automatic IAM authentication. I have followed all the steps in "Before you begin" here and am using the Python connector with the asyncpg driver.

Additionally I have run gcloud auth activate-service-account and can see that the current account the Gcloud CLI is using is the service account. However, I still get an authentication error and it's pretty undescriptive. I have no idea how to proceed or how to debug. Most likely, it is something very simple but I see very little documentation online or methods for troubleshooting. The code is below (I've changed the exact names of the database, instance, and service account for security):

def query_postgres(query, params):
    with Connector() as connector:
        cursor = connector.connect(
            "instance_name",
            "asyncpg",
            user="service_account@project_name.iam",
            db="database_name",
            enable_iam_auth=True,
        ).cursor()
        cursor.execute(query, params)
        rows = cursor.fetchall()
        return rows

This is the error:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: Cloud SQL IAM service account authentication failed for user "service_account@project_name.iam"

Would appreciate if someone could give me some pointers for debugging!

[jackwotherspoon]

Hi @garimasingh2016, thanks for raising a question and using the Cloud SQL Python Connector!

I can help give you some guidance and hopefully help you debug this!

First off, if you are using the asyncpg driver you will want to follow the guidance here to connect in a way that supports asyncio and asynchronous connections. It is slightly different than the synchronous pg8000 driver.

You will want to update your code to look something like:

# add extra import
import asyncio

# make function async
async def query_postgres(query, params):
    # get current running event loop to be used with Connector
    loop = asyncio.get_running_loop()
    # intialize Connector object as async context manager
    async with Connector(loop=loop) as connector:
        # connect with connector.connect_async instead
        conn = await connector.connect_async(
            "instance_name",
            "asyncpg",
            user="service_account@project_name.iam",
            db="database_name",
            enable_iam_auth=True,
        )

You can find the info to interact with the connection object as a Cursor in the asyncpg documentation

However, that may not be the source of the issue. The issue is probably related to the fact that the Python Connector sources credentials from the environment using Application Default Credentials (ADC).

The command you referenced gcloud auth activate-service-account does not actually set the ADC credentials required for automatic IAM database authentication.

Instead you will want to set your ADC credentials through one of the following ways:

1. Run the following command:

gcloud auth application-default login --impersonate-service-account=<SERVICE_ACCOUNT_EMAIL>

This requires a little additional setup in permissions as explained in this documentation

2. Set GOOGLE_APPLICATION_CREDENTIALS environment variable to your service account keys. Either one of the following methods will work.

a) In your Python application code have the following:

import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/keys.json"

b) export in terminal

export GOOGLE_APPLICATION_CREDENTIALS="path/to/keys.json"

3. If your are running the code within a Google Cloud Application (App Engine, Cloud Run, Cloud Run), the ADC credentials will automatically be sourced as the service account used to run the application :)

Let me know if the above helps debug your issue! I am currently working on designing better error handling surrounding automatic IAM authentication for the Python Connector as I agree the errors are not very helpful for debugging currently.

[garimasingh2016]

Hi! Yes, the issue was the ADC credentials! Running gcloud auth application-default login --impersonate-service-account=<SERVICE_ACCOUNT_EMAIL> worked. I also totally missed the async section in the readme, thank you for pointing me to that!

I think it would definitely help to have a bit more detailed explanation on how to authenticate with a service account. It's very well documented for using a personal account, but the instructions don't seem to translate to service accounts all the time. I do think I will eventually run this within a Google Cloud Application, so hopefully authentication is smoother then.

Thanks for working on that, a more descriptive error message would be really great!

[garimasingh2016]

I am now getting an error that says

google.auth.exceptions.DefaultCredentialsError: The file /Users/garimas/.config/gcloud/application_default_credentials.json does not have a valid type. Type is impersonated_service_account, expected one of ('authorized_user', 'service_account', 'external_account').

It seems like the file created by gcloud auth application-default login --impersonate-service-account=<SERVICE_ACCOUNT_EMAIL> is formatted incorrectly?

[jackwotherspoon]

@garimasingh2016 do you have the latest version of google-auth? It may be that you currently have an older version installed.

Try running:

pip install google-auth==2.11.0

I will make a PR to force a newer version as a dependency.

[garimasingh2016]

Ok! FYI, running that gave me a bunch of dependency errors I had to upgrade manually. Letting you know in case forcing a newer version may create issues. I have Google Cloud SDK version 397.0.0.

Also, that fixed that issue, I'm still getting a permission denied error. Let me know if this is out of your wheelhouse though, seems more like a general permissions issue.

  File "/Users/garimas/opt/miniconda3/envs/brand_affinity/lib/python3.8/site-packages/google/auth/impersonated_credentials.py", line 103, in _make_iam_token_request
    raise exceptions.RefreshError(_REFRESH_ERROR, response_body)
google.auth.exceptions.RefreshError: ('Unable to acquire impersonated credentials', '{\n  "error": {\n    "code": 403,\n    "message": "The caller does not have permission",\n    "status": "PERMISSION_DENIED"\n  }\n}\n')

[jackwotherspoon]

@garimasingh2016 I believe you probably just need to add the role serviceAccountTokenCreator to your IAM user to allow them to impersonate the service account.

The documentation for the --impersonate-service-account flag describes it a bit more: documentation

[jackwotherspoon]

@garimasingh2016 let me know if that worked? If it did I will go ahead and close out this issue 😄

[garimasingh2016]

Yes that worked! Thank you!

[jackwotherspoon]

amazing! Glad you were able to get it to work. Thanks for using the Python connector and giving great feedback, we really appreciate it. 😄

I will be working on better error handling in the weeks to come!