How do I see where postgres privileges are coming from for a role?

Question

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?

Answer 1

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.