IAM roles tables permissions for cloud sql

I am trying to understand how to set up the postgres permissions such that my database tables can be read and edited by both a service account and other database users. This has proved impossible. [1]

I am trying to achieve the following setup:

Use IAM authentication, so I don't need to bother with passwords.
Have a dedicated service account in charge of applying database migrations, triggered from a github action.
Have other service accounts / IAM users able to read and edit the tables created by the github service account.

The problem is that it seems impossible to get to the right set of table permissions.

I have done the following on a brand new cloud sql instance:

Created a service account and add it (plus my IAM user) to the list of SQL users.
Logged in as postgres (normal authentication with password, not IAM authentication) and granted a bunch of privileges to both the service account and my main user. See below [0].
Started a job from github actions to apply migrations (creating tables, etc.) logging in as the service account. This worked fine and apparently the job was able to create everything required.
Went back to the Cloud Studio. Now logging in both as postgres and my IAM role shows no tables added. I ran SELECT * from "myTable" and was greeted with "permission denied for table myTable". If I try running GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "postgres"; it again complains with permission denied on table myTable.

So the service account can alter the tables, but apparently he's the only one that can do it. Any other user does not have even read permission, even though I explicitly granted all privileges on all tables.

Would be very grateful for any help!

[0] These are the commands I ran as the postgres user. I ran this for both the service account and my main IAM user.

ALTER USER "IAM_USER" CREATEDB;

-- Grants privileges at the database level (e.g., connecting, creating schemas, temporary tables).
GRANT ALL PRIVILEGES ON DATABASE postgres TO "IAM_USER";

-- Allow Creating & Managing Tables in Public Schema
GRANT ALL PRIVILEGES ON SCHEMA public TO "IAM_USER";
GRANT CREATE, USAGE ON SCHEMA public TO "IAM_USER";

-- Ensure Full Access to Existing Tables & Sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "IAM_USER";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "IAM_USER";

-- Ensure Access to Future Tables Automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO "IAM_USER";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO "IAM_USER";

Would it be possible to provide a minimal example on how to allow a service account to create and alter tables, while allowing other users read and edit access to those tables, too?

[1] For the past week I have been trying to unsuccessfully use the IAM authentication to cloud SQL. The fact that you don't have access to superuser powers means that it's basically impossible to fix a problem if it arises.

Solution

It turns out that while the commands are correct, I was running them from the cloud sql console as the postgres user.

This is correct, and needs to be done. However, since the owner of the tables will be the service account, you also need to login to the db as the service account and run those commands again to give permissions to postgres and the other IAM roles on the tables created by the service account.

There is no easy way to connect to the DB as the service account to run these commands though (i.e. cloud studio does not support this). You need to run a local cloud auth proxy, impersonating the service account (there's a flag for this), and then run SQL queries manually through this proxy.

Pretty messy to be fair. There should be easier ways of doing this IMO. But this seems to work!