4

I'd like to limit the privileges afforded to any given user that I create via the Google Terraform provider. By default, any user created is placed in the cloudsqlsuperuser group, and any new database created has that role/group as owner. This gives any user created via the GCP console or google_sql_user Terraform resource total control over any database that is (or was) created in a similar fashion.

So far, the best we've been able to come up with is creating and altering a user via a single-run k8s job. This seems circuitous, at best, especially given that that resource must then be manually imported later if we want to manage it via Terraform.

Is there a better way to create a user that has privileges limited to a single, application-specific database?

Improve this question

1 Answer 1

Reset to default
5

I was puzzled by this behaviour too. Its probably not the answer you want but if you can use GCP IAM accounts the user gets created in the PostgreSQL instance with NO roles.

There are 3 types of account you can create from "gcloud sql users create" or terraform module "google_sql_user" "CLOUD_IAM_USER", "CLOUD_IAM_SERVICE_ACCOUNT" or "BUILT_IN"

The default is the built_in type if not specified. CLOUD_IAM_USER and CLOUD_IAM_SERVICE_ACCOUNTS get created with NO roles.

We are using these as integration with IAM is useful in lots of ways (no managing passwords at database level is a major plus esp. when used in conjunction with SQL Auth Proxy).

BUILT_IN accounts (ie old school need a postgres username and password) for some reason are granted the "cloudsqlsuperuser" role.

In the absence of being allowed the superuser role on GCP this is about as privileged as you can get so to me (and you) seems a bizarre default.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

How did you manage to log in using an IAM account? I have the IAM account setup with the Cloud SQL Instance User and Cloud SQL Client roles and I'm running the proxy with -enable_iam_login, but I can't even login into the database using the IAM account. Which grants do I need to give to the newly created IAM account to at least login?
You have to enable the CloudSQL instance to accept IAM authenticated users: "gcloud sql instances patch <instance-name> --database-flags cloudsql.iam_authentication=on"
2. Add the IAM user "gcloud sql users create "name@domain" --instance=<instance-name> --type=cloud_iam_user". 3. Only role you need for cloud_sql_auth_proxy is roles/cloudsql.client
I checked everything you said but In the end I realized that I was using an IAM account for the proxy and a different account for the database. You have to use the same account on both the proxy and the db connection! Thanks for the help!
Not strictly true. You could just use the sql auth proxy to create the ssl tunnel. You can then use a different username/password combi on the psql connection string. Bit ugly but possible. In this particular case you wouldn't need the -enable_iam_login on the proxy

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.