Synchronization of Postgres users and GCP IAM users

[jwaffe]

Hello,

I have a question about how users are synchronized to and from the running postgres sql instance.

Background information:

• I notice that GCP has a permission `cloudsql.users.create`, https://cloud.google.com/sql/docs/sqlserver/iam-permissions
• I found these articles on IAM users (https://cloud.google.com/sql/docs/postgres/iam-authentication) and built in users (https://cloud.google.com/sql/docs/postgres/users)

My experimentation:

• I used a standard SQL command to add another user, as the `postgres` user, with something like `CREATE USER username WITH PASSWORD 'a strong password here';`
• I went into GCP and noticed that it detected that account, it was listed as a user on the site

My conclusions:

• There must be some kind of means for GCP to know that a user has been added to the database even if it's not done via gcloud

My questions:

1. How did GCP know that I added a user to the database using SQL? Does it periodically poll the database for the list of users or something? Or is the version of postgres that Google is running a fork of open source postgres with some kind of hook to notify GCP whenever a user is added?
2. Using SQL, is there any way to add a user to postgres as an IAM user instead of a builtin user? Maybe it has some weird rule like it assumes any user with an "@" in the username is a GCP serviceaccount, or something similar to that?
3. If a user is added to the cloud SQL server using SQL, and there's a failover, will Google bring the database back up with the users added through SQL still present, or will it be restarted with just the users that the server was configured with through terraform/gcloud/etc.?
4. I'm not sure I understand what the point of the `cloudsql.users.create` permission rule is, if you can just create a user using SQL. Does that permission have any use if it's so easily bypassed?

Thanks in advance

[mcbsalceda]

Hi @jwaffe,

Welcome to Google Cloud Community!

To answer your questions:

1. There are multiple ways in creating built-in users as mentioned in this document - including creation via psql client. GCP doesn’t directly monitor for SQL commands like CREATE USER, but it keeps track of the state of your Cloud SQL instance. So, when you add a user through SQL, it eventually syncs with GCP’s management interface as part of regular database monitoring and replication.
2. Unfortunately, you can’t create IAM users using SQL commands like CREATE USER. IAM users are managed by GCP separately from the PostgreSQL database itself, so they aren’t part of the database’s built-in user management system.
3. Cloud SQL failover ensures that all data, including user accounts created with SQL commands, are replicated and restored on the failover instance.
4. The cloudsql.users.create permission is specifically for creating users through GCP’s interfaces, like the GCP console or APIs. Even though you can create users with SQL, this permission gives you a more centralized, GCP-managed way to handle user creation. It's part of managing access and permissions within the broader GCP ecosystem. As mentioned earlier, using psql client is one way of creating users. Users with cloudsql.users.create permission can create users using CREATE USER command.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.