Is it possible to assign IAM service accounts database create usage without logging in with postgres user?

[MarkEdmondson1234]

Question

Hello, I'm setting up via terraform and wondered if its possible to automate creating the database within an AlloyDB cluster?

I'm logging in successfully and can execute SQL but when I try with a user that is alloydb.admin the code below, it says they do not have permission:

If not, is there a one time permission I can grant to that service account when I do log in with postgres that I can do?

Many thanks! Really looking forward to using AlloyDB with all the Langchain integrations.

Code

pool, connector = create_sqlalchemy_engine(
    connection_string, 
    user="sa-cloudbuild@my-project.iam",
    password=None,
    db="postgres")

with pool.connect() as conn:
    db_cmd = sqlalchemy.text(
        f"CREATE DATABASE {database_name}",
    )
    print(f"Executing {db_cmd}")
    conn.execute(db_cmd).fetchone()
    connector.close()

Additional Details

Error is:

 File "/builder/home/.local/lib/python3.10/site-packages/pg8000/core.py", line 827, in handle_messages
    raise context.error
sqlalchemy.exc.DatabaseError: (pg8000.exceptions.DatabaseError) {'S': 'ERROR', 'V': 'ERROR', 'C': '42501', 'M': 'permission denied to create database', 'F': 'dbcommands.c', 'L': '378', 'R': 'createdb'}
[SQL: CREATE DATABASE multivac_dev]

[jackwotherspoon]

@MarkEdmondson1234 Thanks for the great question! This is a point of friction that several customers have noticed and one that we are trying to find a solution to.

Currently there is an API to set an AlloyDB database user as a superuser: https://cloud.google.com/sdk/gcloud/reference/alloydb/users/set-superuser

This may allow you to grant your IAM user the superuser status and skip having to connect as a privileged user and manually granting permissions. Give this a try and let me know if it works for your use-case 😄

I am looking into alternatives such as updating the create user API to have the ability to specify permissions to grant to the IAM database user upon creation. This would allow the terraform to pass in what permissions to give instead of defaulting to no permissions.

[MarkEdmondson1234]

Thank you, yes this looks feasible. I'm triggering my script in cloud build within the VPC, so I can add a gcloud step before my create database step. I guess something like:

gcloud alloydb users set-superuser sa-cloudbuild@my-project.iam --cluster=my-cluster --region=us-central1 --superuser=true

[MarkEdmondson1234]

Yep thanks that worked, if anyone else is interested:

steps:
- name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim'
  entrypoint: 'gcloud'
  args:
    - 'alloydb'
    - 'users'
    - 'set-superuser'
    - 'sa-cloudbuild@my-project.iam' 
    - '--cluster=my-cluster'
    - '--region=europe-west1'
    - '--superuser=true'
    - '--project=my-project'
- name: 'python:3.10'
  entrypoint: 'bash'
  args:
    - '-c'
    - |
      pip install "google-cloud-alloydb-connector[pg8000]" sqlalchemy pg8000 --user && python init_db.py