What are some recommendations to manage Postgres database users in Cloud SQL at scale in an enterprise environment? For example when many internal individual users (each with their individual IAM user account) want to query a Postgres database in Cloud SQL?
In Microsoft SQL Server and Active Directory, I would use Windows Integrated Auth with AD Security groups; assign the least privileges to the groups and let security group membership/inheritance handle who has access to what.
In Cloud SQL/Postgres, there does not appear to be an option to accomplish similar with Google Cloud IAM Security Groups. I reviewed the documentation on Project Access Control but that does not appear to provide what I am looking for.
Is creating explicit Postgres database users for each and every user that requests it the only option?
If this is the only way, then is there a way to minimize the administrative overhead (e.g. password expiration, password reset requests, disabling ex-employees, etc.)?
Is there a better way to accomplish this that scales well and eases the administrative overhead?
Thank you for any insights!!
As clarified in the official documentation PostgreSQL users, Cloud SQL with PostgreSQL works with the PostgreSQL users and roles. So, you will have the features and limitations that comes with PostgreSQL.
You should be able to configure roles for new users and all your users in general by using the psql client, as indicated here. To summarize, the permissions and access for users within Cloud SQL PostgreSQL will be limited by the features available directly on PostgreSQL and the configuration and alteration needs to be made via PostgreSQL client-side.
Besides that, as a plus, the below documentation provides you general best practices for you to follow in your Cloud SQL.
Let me know if the information helped you!