Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

tgl@xxxxxxxxxxxxx wrote:

(I wonder if it'd be practical or useful to emit a warning when granting permissions on an object that already has a grant of
the same permissions to PUBLIC.  That would at least cue people who don't understand about this behavior that they ought to look more closely.)

We did something similar a while ago where we now warn if you try to revoke a privilege on a role that is actually inherited from PUBLIC and so the revoke on the role doesn't actually do anything.  The inverse seems reasonable, and consistent that, at first blush.

I'll wait with interest to see what might get implemented.

There's another common approach in this general space—when a fear arises that privileges have been granted too liberally so that vulnerabilities might have (presumably unintentionally) been exposed. It's to write various report generators—sometimes extended to become recommendation generators.

I looked at Chapter 52, "System Catalogs" at https://www.postgresql.org/docs/current/catalogs.html. It lists 97 relations. I'll have to defer reading about every one of these to another day. I searched the page for likely names looking for ones with "priv" and "rol". There's just a small number of hits. I drilled down on these. But none seemed to help finding out which objects, of which kinds, have which privileges (or roles) granted to which grantees.

Which catalog relations are sufficient to support a query that lists out, for example, every user-defined function and procedure with its (at least first-level) grantees?