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>]

bryn@xxxxxxxxxxxx wrote:

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?

adrian.klaver@xxxxxxxxxxx wrote:

Tip if you do:

psql -d test -U postgres -h localhost -E

the -E will get you the queries for the \ meta-commands. So:

\df+ my_function

Should have mentioned, if you want to include system functions then it would be:

\dfS+

Thanks, Adrian. Ah, yes… I have used that technique before. The query that's shown is quite a mouthful. I could prune it down to what I need, of course. But, for now, looking at what the \df+ metacommand outputs will do. I made a new small test-case and copied it at the end.

The "Access privileges" column in the \df+ output for "s.f()", at its first use, is empty. I read this in the section "5.7 Privileges" that we've already mentioned:

«

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then modify them per the specified request.

»

The \df+ output at its second use shows this:

  =X/x
  x=X/x
  z=X/x

The \df+ output at its third use shows this:

  x=X/x

  z=X/x

And finally, the \df+ output at its fourth use shows this (again):

  =X/x
  x=X/x
  z=X/x

I s'pose that I can interpret this output in the light of the "miriam" example by guessing than an empty LHS means "public" and that the initial "X" means "execute". It looks like what follows the slash is the owner of the object (a denormalization of what the "Owner" column shows.)

Where is this notation, "miriam=arwdDxt/miriam", explained?

I does seem, then, that with enough effort, what I've learned here would be enough to allow writing (say) a table function that reports owner, schema, name, and arg signature for every user defined function and procedure that has "execute" never revoked from, or re-granted to, "public".

I wonder if such a thing, if written and reviewed carefully, could find its way into a future PG release.

--------------------------------------------------------------------------------

\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;

\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s authorization postgres;

drop user if exists x;
create user x login password 'p';
drop user if exists y;
create user y login password 'p';
drop user if exists z;
create user z login password 'p';

create function s.f(i in int)
  returns int
  language plpgsql
as $body$
begin
  return i*2;
end;
$body$;

alter function s.f(int) owner to x;
\df+ s.f

grant execute on function s.f(int) to z;
\df+ s.f

revoke execute on function s.f(int) from public;
\df+ s.f

grant execute on function s.f(int) to public;
\df+ s.f