CODEWITHSUNDEEP
postgresqlgoogle-cloud-sqlpg8000
PgGroot
PgGroot

Reputation: 1

How to Enforce SSL Mode in SQLAlchemy with pg8000 for Cloud SQL Connection Using IAM Auth

I'm attempting to establish a connection to a Google Cloud SQL instance utilizing pg8000 and IAM authentication. My goal is to create a connection pool and execute queries against the database. Here's the code snippet I'm working with:


def test_iam_connection():
    connector = Connector()

    # Function to get connection using IAM auth, no password required
    def getconn():
        conn = connector.connect(
            INSTANCE_CONNECTION_NAME,
            "pg8000",
            user=IAM_USER,
            db=DB_NAME,
            enable_iam_auth=True,
            ip_type=IPTypes.PRIVATE,
        )
        return conn

    # Establishing connection pool
    pool = sqlalchemy.create_engine(
        "postgresql+pg8000://",
        creator=getconn
    )

    # Initiating connection to pool
    with pool.connect() as db_conn:
        # Retrieving current datetime from the database
        results = db_conn.execute(sqlalchemy.text("SELECT NOW()")).fetchone()
        results2 = db_conn.execute(sqlalchemy.text("SELECT * from public.test")).fetchone()

        # Displaying results
        print("Current time: ", results[0])
        print("results: ", results2[0])

    # Connector cleanup
    connector.close()

if __name__ == "__main__":
    test_iam_connection()

My question is: How can I incorporate sslmode=require into this setup to enforce SSL connections? I'm looking for guidance on the correct approach to ensure my connections to Cloud SQL over pg8000 use SSL.

In my attempts to enforce SSL mode for connections to a Google Cloud SQL instance using SQLAlchemy and pg8000, I expected to easily specify the sslmode=require parameter within my connection string or through the connect_args parameter when creating the SQLAlchemy engine. My goal was to ensure that all database connections are encrypted for security compliance.

However, I encountered difficulties in applying the sslmode=require parameter directly. Despite following examples and documentation, I couldn't find a clear way to integrate this SSL enforcement directly within the pg8000 connection setup, especially when using IAM authentication for Google Cloud SQL.

Upvotes: 0

Views: 328

Answers (1)

enocom
enocom

Reputation: 1836

The Cloud SQL Python Connector creates an mTLS connection for the database driver. So even though your connection string might have sslmode=disable or sslmode=prefer (which is the default), you're always using an encrypted connection when using the Python Connector.

So, short answer, the Python Connector handles encrypting the connection for you, and your app can connect as if the database is running on localhost.

Upvotes: 0

Related Questions