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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply