postgresqlgoogle-cloud-platformgoogle-cloud-sql

How to create GCP Cloud SQL IAM Users

The first answer here is a guide on how to create a Cloud SQL IAM user for your Google Platform Cloud SQL instance.

Here is a guide on how to connect after you've created the user.

Solution
    1. Click on "Edit" on your instance on the upper tool bar. Go down to "Flags" and set "cloudsql.iam_authentication" to "on"

    set flag

    1. Go to "Users" tab on the left navigation pane > Click on "Add User Account"

    add user account

    1. Use the person's GCP email address in the principal field. Anyone can create this account but only IAM Editors can change IAM privileges. IAM privileges affect GCP objects, not the database objects. There is no need to set IAM privileges to connect to the database.

    enter email address

    1. Connect to the database using the instance IP address and postgres user. Using this user we can assign privileges. IAM users are created with zero privileges to database objects.
    grant connect on database database_name to "username@email.com";

-- Grant usage on current objects in a schema
grant all on SCHEMA schema_name to "username@email.com";
grant all on all TABLES in SCHEMA schema_name to "username@email.com";
grant all on all FUNCTIONS IN SCHEMA schema_name to "username@email.com";
grant all on all PROCEDURES IN SCHEMA schema_name to "username@email.com";
grant all on all ROUTINES IN SCHEMA schema_name to "username@email.com";
grant all on all SEQUENCES IN SCHEMA schema_name to "username@email.com";

-- Grant usage of any newly created objects in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT all ON FUNCTIONS TO "username@email.com";
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT all ON ROUTINES TO "username@email.com";
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT all ON SEQUENCES TO "username@email.com";
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT all ON TABLES TO "username@email.com";
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT all ON types TO "username@email.com";
    1. Test permissions for the user by changing sessions
    set session authorization "username@email.com";

    reset session authorization;
    1. Now you can connect to the database using the IAM details and Cloud SQL Proxy