[How to] connect from Local Machine using Public IP

[mahimairaja]

Hi, i am facing an authentication issue while connection using the pg800 python connectors;

✅ Enabled Public IP for the instance

Code:

import pg8000
import sqlalchemy
from typing import Tuple

from google.cloud.alloydb.connector import Connector, IPTypes

def create_sqlalchemy_engine(
    inst_uri: str,
    user: str,
    password: str,
    db: str,
) -> Tuple[sqlalchemy.engine.Engine, Connector]:
    connector = Connector()

    def getconn() -> pg8000.dbapi.Connection:
        conn: pg8000.dbapi.Connection = connector.connect(
            inst_uri,
            "pg8000",
            user=user,
            password=password,
            db=db,
            # use ip_type to specify Public IP
            ip_type=IPTypes.PUBLIC,
        )
        return conn

    # create SQLAlchemy connection pool
    engine = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn,
    )
    engine.dialect.description_encoding = None
    return engine, connector

Error Message:

---------------------------------------------------------------------------
TransportError                            Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/google/auth/compute_engine/credentials.py in refresh(self, request)
    127         try:
--> 128             self._retrieve_info(request)
    129             self.token, self.expiry = _metadata.get_service_account_token(

28 frames
TransportError: ("Failed to retrieve http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/default/?recursive=true from the Google Compute Engine metadata service. Status: 404 Response:\nb''", <google.auth.transport.requests._Response object at 0x7e16ec1a5180>)

The above exception was the direct cause of the following exception:

RefreshError                              Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/google/auth/compute_engine/credentials.py in refresh(self, request)
    132         except exceptions.TransportError as caught_exc:
    133             new_exc = exceptions.RefreshError(caught_exc)
--> 134             raise new_exc from caught_exc
    135 
    136     @property

RefreshError: ("Failed to retrieve http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/default/?recursive=true from the Google Compute Engine metadata service. Status: 404 Response:\nb''", <google.auth.transport.requests._Response object at 0x7e16ec1a5180>)

Please let me know, how to connect from local machine using the Public IP method.

[enocom]

Have you configured gcloud and run gcloud auth application-default login?

[jackwotherspoon]

@mahimairaja Like Eno mentioned above this points at an auth issue probably from not setting ADC credentials properly which the Python Connector uses for authentication. You can read more about setting ADC credentials here.

Are you running your code on a GCE VM or local machine? It seems the auth library thinks you are running from a compute engine based on the error message.

[mahimairaja]

Hi @enocom and @jackwotherspoon.

From my understanding. setting ADC will automatically involves the initialization of IAM right?

BTW, I am facing this issue while deploying the app using FastAPI

(Also, this works well in the GCE VM @jackwotherspoon )

[jackwotherspoon]

From my understanding. setting ADC will automatically involves the initialization of IAM right?

Not sure I fully understand the sentence. For local deployment on your local machine (aka FastAPI development) you need to set ADC credentials following the steps for setting up ADC.

Either gcloud auth application-default login for setting IAM user credentials or you can set env variable GOOGLE_APPLICATION_CREDENTIALS to the path of a service account JSON file for an IAM service account.

In a deployed Google application environment (GCE, Cloud Run etc.) the ADC credentials are automatically set to the service account in the environment which is why you don't see this issue when deploying in other environments and only locally.

[enocom]

From my understanding. setting ADC will automatically involves the initialization of IAM right?

Setting ADC up will ensure that the Python Connector running on your local machine can retrieve an OAuth2 token when sending requests to the API.

BTW, I am facing this issue while deploying the app using FastAPI

On your local machine? Or elsewhere?

[mahimairaja]

I recently resolved on my local machine using the ADC. But, then I would like to setup CI using the pytest on Github actions. How can I authenticate in side the github actions checkout?

[mahimairaja]

@enocom and @jackwotherspoon any idea?

[jackwotherspoon]

@mahimairaja Yes you can just use the google-github-actions/auth Github Action. We recommend using the Github Action with Workload Identity Federation for the best workflow.

Here is a great blog showcasing the two together.

This is matter of fact the exact setup we use for our own integration tests in this repo: https://github.com/GoogleCloudPlatform/alloydb-python-connector/blob/f2906196ef699ae056ba1ce1777b86cdbecf0a68/.github/workflows/tests.yaml#L155-L161

Hope this helps 😄 Going to close out this issue as the original question was answered and resolved.

[mahimairaja]

Thanks you it resolves!!!