I have created some roles, a function, and granted various privileges across a schema. Now I would like an overview: for each role, everything it has been granted.
The built-in role listing shows attributes and memberships but nothing about object privileges. I have not found a single view that pulls it all together.
Is there a straightforward way to get this, or does it genuinely require assembling it from several places?
Two shortcuts worth knowing before writing that query.
The command line client's describe commands show access privileges. Describing a table or a set of objects includes an access privileges column, and there are variants that cover functions and schemas. For a quick look at one object or one schema this is far faster than any query.
A schema-only dump contains every grant. Producing a structure-only dump of the database gives you a file with the complete set of GRANT statements in it. It is not a per-role report, and it is a complete and authoritative record you can search — and it is the thing I actually reach for when auditing, because it cannot miss an object type.
Between those two, most people's real question is answered without building anything.
The other thing that makes a true per-role view hard: inheritance.
Roles can be members of other roles, and by default a member inherits the privileges of the roles it belongs to. So listing direct grants to a role understates what it can actually do — the effective set includes everything granted to every role it inherits from, transitively.
If what you want is "what can this role actually do", the access privilege inquiry functions are the right tool. They answer the effective question directly for a given role and object, taking inheritance into account, rather than reporting what was granted.
That is a genuinely different query and usually the one people mean when they ask this.
One habit worth adopting whatever you build: grant to roles, never to individual users.
Create roles that represent a function — read-only reporting, application write access, migration — grant privileges to those, and make users members. Then this question becomes tractable, because there are five things to inspect rather than fifty.
Databases where privileges were granted ad hoc to individuals over years are the ones where this question has no good answer, and no query fixes that.
@postgres_polat · last wk.
It genuinely requires assembling it, and the reason is structural: privileges are stored on the objects, not on the roles.
Each object carries an access control list recording who may do what to it. There is no central table of grants to walk, so a per-role view has to be built by scanning every object type and filtering.
Which means the practical answer is to query the information schema views, one per object class:
Union those together with a role column and you have your overview.
Reply
Report