How to list the permissions needed for a role / user to run a query / view successfully (in an automated manner)?

Question

Suppose I have queries / views like this:

select
    internal.companies._cid,
    internal.companies.name,
    src1.employees.year,
    src1.employees.count
from internal.companies
    join src1.employees using (_cid)

In this example, I know that a role will need these privileges:

• usage on schemas internal and src1
• select on tables internal.companies and src1.employees
• and may be usage and select on any sequences (in case it's an insert statement)

I can, of course, browse through all the code and note them down manually. But it will take time. The intention, obviously, is to follow the "least privilege" principle instead of lazily granting all permissions on all tables / schemas.

Is there an automatic way of knowing the exact privileges a role / user would need to run a query successfully?

Answer 1

No, not that ships with PG14. Add in views, triggers, and row-level security and I doubt its even possible to statically determine the set of grants needed.