1

I'm really confused about how IAM login works with AlloyDB. I don't see anything in the docs about mapping postgres users to IAM service accounts.

With Cloud SQL Postgres I can do this: https://cloud.google.com/sql/docs/postgres/authentication

  1. I create a service account
  2. Create a cloud sql user of type CLOUD_IAM_SERVICE_ACCOUNT, (gcloud sql users create <GSA> --type=CLOUD_IAM_SERVICE_ACCOUNT)
  3. Log in to postgres and give that postgres user access to only its own database.

Applications auth to their own postgres DB through their IAM service account (via the proxy) and cannot access other DBs on the postgres server.

How do I do this with AlloyDB?

Looking at the docs I see some mention of CLOUD_IAM_USER https://cloud.google.com/alloydb/docs/database-users/about#view-list

But I cannot see how to create AlloyDB users via the API:

enter image description here

Does the sql users API also support AlloyDB? With Cloud SQL I can create IAM service account users via the Cloud SQL API.

red888
  • 27,709
  • 55
  • 204
  • 392

3 Answers3

3

Not yet, no. But it's coming. No promises on dates of course, but it's in active dev.

Gabe Weiss
  • 3,134
  • 1
  • 12
  • 15
  • Will it be possible to create AlloyDB users via the API? Also, there is a alloydbiamuser postgres role. If I manually create a postgres user with the right naming convention and add it to that role will it work? – red888 Mar 29 '23 at 18:56
  • 1
    Sort of yes... I don't think (currently) the team is working on db-level user creation. So once we have IAM auth, then you can create IAM identities via the APIs. So from that perspective yes, but I'm not sure yet what level of db-level permissions/access you'll be able to fine tune as part of that. I need to see the feature internally before I know. :) And it might change before it gets to you anyway. – Gabe Weiss Mar 29 '23 at 19:09
  • Is there currently no way to control access on a per database level without manually creating and authenticating with regular postgres users? – red888 Mar 29 '23 at 19:11
  • Also, before mysql supported this I did this in my grants to restrict a DB users access to the proxy only: `grant usage on DB.TABLE to 'username'@'cloudsqlproxy~%';`. Can I do this with AlloyDB postgres? – red888 Mar 29 '23 at 19:32
  • For the access question: Sort of could, you could script psql commands with an IAM authenticated user to do the GRANTs (super hacky, but could do it that way). And sadly no, that trick won't work because it's a MySQL trick. Postgres doesn't populate the user/host like that currently. – Gabe Weiss Mar 29 '23 at 20:23
  • To control access to proxy only, you could/should use firewall rules to control access. I'm lookin to see if we have specific docs on how to. – Gabe Weiss Mar 29 '23 at 20:27
  • https://cloud.google.com/alloydb/docs/firewall – Gabe Weiss Mar 29 '23 at 20:32
2

Manual IAM authentication is now available.

https://cloud.google.com/alloydb/docs/manage-iam-authn

Automatic IAM Authentication (where the AlloyDB Proxy handles the OAuth2 token for you) isn't available yet, though.

enocom
  • 1,496
  • 1
  • 15
  • 22
0

Successfully connected interactively using my IAM credentials to a database in an AlloyDB PostgreSQL instance using both a service account and my Google IAM account.

Helps me to think of connection on two levels. First is using alloydb-auth-proxy to connect to the AlloyDB instance. This works for me using a GCE service account with appropriate roles defined. Basically port 5432 is opened for a PostgreSQL client. The second level is connecting via a PostgreSQL client like psql to a specific database like 'postgres'.

The gcloud alloydb users create email@domain.com --type=IAM_BASED creates an IAM user. There is a database flag, "alloydb.iam_authentication" = "on" that needs to be applied. I think that is it for configuration.

Then each time I want to connect as myself, 1) gcloud auth login --update-adc; 2)open an gcloud ssh --iap-tunnel to the alloydb-auth-proxy and then 3) login to the database with:

PGPASSWORD=$(gcloud auth print-access-token) psql --host=127.0.0.1 --dbname='postgres' --username="email@domain.com"

I just tried where the service account was active using the command above but with the service account IAM username and it worked. The service account has to be added as alloydb user type IAM_BASED and needs "roles/alloydb.databaseUser" and "roles/serviceusage.serviceUsageConsumer".

See https://cloud.google.com/alloydb/docs/manage-iam-authn

user14494
  • 1
  • 1