david.g.johnston@gmail.com wrote:
bryn@yugabyte.com wrote:
grant usage on schema s to z;
revoke execute on function s.f() from z; -- Yes, really!
*This surprises me*
The PG doc on, in the "5.7. Privileges" section at https://www.postgresql.org/docs/current/ddl-priv.html (under "USAGE" following "The available privileges are"), says this:
«
For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema...
»
Is the design of my test-case faulty? Have I found a bug? Or is the doc wrong?
WADaD
https://www.postgresql.org/docs/current/ddl-priv.html
"PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command."
Ah... so that's it. Thanks to David J, Tom Lane, and Jeremy Smith who all told me the same thing. And thanks especially for the super-fast responses. I'm pleased to learn that the design of my test-case was faulty. It's corrected now.
I confess that I'm surprised by the choice of the default behavior. It seems to be at odds with the principle of least privilege that insists that you actively opt in to any relevant privilege.
I'll read up on ALTER DEFAULT PRIVILEGES and test it.
p.s., What's "WADaD", David. Internet search doesn't find me the translation except for, maybe, the Muslim name "Wadad" meaning "Love, friendship.