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

[markustoivonen]

@jackwotherspoon Has the behaviour of the gcloud command changed in the past 3 months? I am trying to elevate an IAM user to role alloydbsuperuser, so that it could grant granular permissions for other IAM users.

Running the statement GRANT SELECT ON TABLE public.test TO "foo-bar@baz.iam" with my IAM user with role alloydbsuperuser via psycopg2 results in error permission denied for table test. So do I still need to use a built in user to grant the permissions for my "IAM superuser", so that it can in turn grant permissions?

This is a point of friction that several customers have noticed and one that we are trying to find a solution to.

I will add my voice to the choir. The permission/role management is quite painful for AlloyDB currently. Making it possible to do it via Terraform would be a great help.

[jackwotherspoon]

@markustoivonen You can grant IAM database users alloydbsuperuser privileges in Terraform already, you no longer need an intermediate built-in user.

You can do by adding the role to the database_roles field.:

resource "google_alloydb_user" "user" {
  cluster = google_alloydb_cluster.default.name
  user_id = "user@foo.com"
  user_type = "ALLOYDB_IAM_USER"

  database_roles = ["alloydbiamuser", "alloydbsuperuser"]
  depends_on = [google_alloydb_instance.default]
}