0

Im using google cloudsql but this is a more generic postgres question.

I have a role (that is already created and managed by cloudsql) called cloudsqliamserviceaccount.

The cloudsqliamserviceaccount role is not a member of any other roles.

Whenever I create a new postgres user (through GCP console or gcloud) they are added to this role. I CANNOT remove them from the role because it will break the cloudsql IAM integration.

The cloudsqliamserviceaccount role has very sweeping permissions. It can connect to and do CRUD on all DBs. But I'm not sure how to see its full list of permissions.

In short I have this role "cloudsqliamserviceaccount". How do I see what it has access to? I gets CRUD automatically for every new database I create, but its not a member of any other role. Where is this permission coming from and how do I see that?

2

The permissions are stored on the individual objects, and there is no central view to see them all.

You could use the access privilege inquiry functions for each type of object to get that information.

For SELECT on tables, views, sequences, composite types or materialized views that could be:

SELECT oid::regclass, relkind
FROM pg_class
WHERE  has_any_column_privilege(
          'cloudsqliamserviceaccount'::regrole,
          oid,
          'SELECT'
       );

You need similar queries for other object types.

Note that such a query will only give information about objects in the current database, so you would have to run it on each database in turn.

Another crude method is to run

DROP ROLE cloudsqliamserviceaccount;

That will give you an error message and list some of the privileges the role has in the current database.

8
  • OK but when I create a new DB cloudsqliamserviceaccount just has access to them. How is it automatically getting access to all new DBs? May 6 at 13:11
  • Because the default permissions for database allow everyone in. May 6 at 13:23
  • so this is just a postgres default? Im creating a role for each one of my apps and I want each role to only have access to its own database. How do you accomplish this? May 6 at 13:57
  • 1
    Revoke the permissions to PUBLIC from the database after creating it and grant the ones you want. May 6 at 14:07
  • so im revoking the public role from new databases, not revoking the public role from new roles right? because all users are members of public and you cant change that? May 6 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.