Error in connecting to AlloyDB database with IAM user

[ff-sdesai]

Bug Description

For Postgre Cloudsql, we have to use google cloud-sql-python-connector to connect to DB. When we establish a connection using IAM user, we don't have to pass any password if we pass a flag enable_iam_auth as true . I confirmed this by looking at source-code of this connector that password is optional (Line 58 here) However, for AlloyDB, we have to use alloydb-python-connector and when I am not passing any password for IAM user, it is resulting an error. This can be confirmed from source code where password is not optional (Line 50 here). I tried setting password as blank string or None but it didn't work.I also tried setting the flag enable_iam_auth but I got an error that it's an invalid argument.

The error is because alloy-db-connector has not specified any default value while poping password. Is there any other way we can use this connector with AlloyDB?

Example code (or command)

No response

Stacktrace

No response

Steps to reproduce?

Try connecting to any AlloyDB using an IAM user

Environment

1. OS type and version: Ubuntu 22
2. Python version:3.10
3. AlloyDB Python Connector version: 0.1.5

Additional Details

No response

[jackwotherspoon]

@ff-sdesai This is because automatic IAM database authentication is not currently a supported feature of the AlloyDB Python Connector. It is currently being worked and will be released in the near future. I have opened #152 as a public tracking bug for this feature.

However, you can actually configure/wire your own SQLAlchemy engine to connect using IAM authentication with some very minimal code.

We have an example that uses this method with psycopg2 to connect directly without using the AlloyDB Python Connector.

[ff-sdesai]

@jackwotherspoon I am trying the second work-around you provided with pg8000. When I run this code, I am getting an error NameError: name 'engine' is not defined for line having annotation event.listens_for.
If I import engine from sqlalchemy, then I get the following error-
sqlalchemy.exc.InvalidRequestError: No such event 'do_connect' for target '<module 'sqlalchemy.engine' from '/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/__init__.py'>'

Can you please check?

[jackwotherspoon]

When I run this code, I am getting an error NameError: name 'engine' is not defined for line having annotation event.listens_for. If I import engine from sqlalchemy, then I get the following error- sqlalchemy.exc.InvalidRequestError: No such event 'do_connect' for target '<module 'sqlalchemy.engine' from '/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/__init__.py'>'

You do not need to import engine, the event listener should be set on the variable engine from engine = sqlalchemy.create_engine..., what version of SQLAlchemy do you have installed?

[ff-sdesai]

I am using sqlalchemy 2.0.23

[jackwotherspoon]

It looks like creator and the event listener end up overriding eachother so that the workaround does not work as intended. I removed it as a suggestion and would recommend using the direct conennection in the meantime if you are looking for IAM authentication currently

[ff-sdesai]

Thanks @jackwotherspoon . Will try this approach

[ff-sdesai]

@jackwotherspoon If we decide to use non-IAM user with alloydb-connector, will this non-IAM user also need access to the GCP project?

[jackwotherspoon]

@jackwotherspoon If we decide to use non-IAM user with alloydb-connector, will this non-IAM user also need access to the GCP project?

@ff-sdesai Not the built-in database user no, but the IAM Principal (service account or user) used in the environment to authorize connections will still require the AlloyDB client IAM role.

[ff-sdesai]

@jackwotherspoon I tried the solution you suggested using psycopg2 . I confirmed that a password is being set using the event but I am still getting an error that password authentication failed for IAM user.
In the IAM username parameter, I tried passing complete IAM user name including iam.gsserviceaccount.com and also without gsserviceaccount.com but got error in both cases. Any idea what might be wrong?

[jackwotherspoon]

I confirmed that a password is being set using the event but I am still getting an error that password authentication failed for IAM user. In the IAM username parameter, I tried passing complete IAM user name including iam.gsserviceaccount.com and also without gsserviceaccount.com but got error in both cases. Any idea what might be wrong?

You should not pass in the complete IAM service account name, it should be in the format service-account@project-id.iam without the gserviceaccount.com suffix.

You most likely have missed a configuration step, I would read through https://cloud.google.com/alloydb/docs/manage-iam-authn and make sure all steps have been followed in detail.

TLDR steps;

• Enabled IAM authentication on the AlloyDB instance via alloydb.iam_authentication flag
• Granted the IAM Principal (user or service account) the Cloud AlloyDB Client and Cloud AlloyDB Database User roles
• Create AlloyDB IAM database user on your AlloyDB cluster
• Login as database admin user (ex. postgres user) and grant the new IAM database user the proper database privileges to the appropriate tables etc.

@ff-sdesai have you granted the IAM principal the Cloud AlloyDB Database User IAM role?

[ff-sdesai]

@jackwotherspoon Yes, I have granted those roles. Only thing I have not done is enabling alloydb.iam_authentication flag. Will try enabling it

[jackwotherspoon]

@ff-sdesai Let me know if that resolves the connection problems 🤞

[ff-sdesai]

@jackwotherspoon I am able to connect to AlloyDB from my application now. Thanks for your help!

[Nofugnosis]

Is there a way to use IAM authentication using Python and Cloud Function? Do you have documentation about this? I was able to connect CF to an AlloyDB instance using psycopg2 library but using postgres user and their password.
@ff-sdesai you were able to connect using IAM authentication from where? VM?

Thanks!

[jackwotherspoon]

@Nofugnosis you can use the following psycopg2 sample to make your own IAM authN in Cloud Functions without the Python Connector: https://github.com/GoogleCloudPlatform/alloydb-python-connector/blob/main/tests/system/test_psycopg2_direct_connection.py

[enocom]

In addition, the Python connector now supports IAM authentication regardless of where you're running your code.

See https://github.com/GoogleCloudPlatform/alloydb-python-connector?tab=readme-ov-file#automatic-iam-database-authentication.

[Nofugnosis]

Thanks @jackwotherspoon and @enocom !
I'm testing the example from you Jack, using a CF that connects to an AlloyDB. I see this in my Visual Studio (before the CF creation):

And when I executed the CF, I got this error:

File "/layers/google.python.pip/pip/lib/python3.7/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/psycopg2/init.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: password authentication failed for user "SA Runtime name@projectID.iam"

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

File "/layers/google.python.pip/pip/lib/python3.7/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/psycopg2/init.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: password authentication failed for user "SA Runtime name@projectID.iam"

Do you have any idea? Sorry, I don't know if this the correct place to ask this.

Thanks in advance!

[enocom]

FATAL: password authentication failed for user "SA Runtime name@projectID.iam"

This usually means the IAM principal that you're running as isn't the same as your IAM user in your database. Does your function execute as name@projectID.iam?

[Nofugnosis]

FATAL: password authentication failed for user "SA Runtime name@projectID.iam"

This usually means the IAM principal that you're running as isn't the same as your IAM user in your database. Does your function execute as name@projectID.iam?

Thanks @enocom !!
Yes, I forgot to configured the IAM user properly in the database and in the AlloyDB cluster

[Nofugnosis]

Now I have another doubt.
What's the difference between using IAM authentication with library pg800 (https://github.com/GoogleCloudPlatform/alloydb-python-connector/blob/main/tests/system/test_pg8000_iam_authn.py ) and psycopg2 (https://github.com/GoogleCloudPlatform/alloydb-python-connector/blob/main/tests/system/test_psycopg2_direct_connection.py ) ??
I understand there are different Python Libraries. My doubt coming because I'm still getting a time out error when I tried to execute my CF when I use Python connector (https://github.com/GoogleCloudPlatform/alloydb-python-connector?tab=readme-ov-file#automatic-iam-database-authentication )
In this case I use AlloyDB URI, driver, etc

alloydb_connector.py:

from google.cloud.alloydb.connector import Connector
import sqlalchemy

connector = Connector()

def getconn():
print("running getconn()...")
conn: pg8000.dbapi.Connection = connector.connect(
"projects/<YOUR_PROJECT>/locations/<YOUR_REGION>/clusters/<YOUR_CLUSTER>/instances/<YOUR_INSTANCE>",
"pg8000",
user="name@projectID.iam",
enable_iam_auth=True,
db="postgres",
)
return conn

print("running pool()...")
pool = sqlalchemy.create_engine(
"postgresql+pg8000://",
creator=getconn,)

def select_query(query):
print("running query...")
with pool.connect() as db_conn:
result = db_conn.execute(sqlalchemy.text(query)).fetchone()
db_conn.commit()
return result

main.py

import sqlalchemy
from alloydb_connector import select_query
import sqlalchemy
import json
from google.cloud.alloydb.connector import Connector

def main(request):
print("Running main function...")
request_json = request.get_json()
query_type = request_json["query_type"]
if query_type == "select":
result = select_query("SELECT * FROM students;")
print(result)
return "Task Completed Successfully"

[enocom]

@Nofugnosis would you mind opening a new issue? We can try to address it and see if there's a general fix for other people having the same question.