I have been wondering about some things related to schema privileges:
1) Why do visibility rules apply to the \d command, but not to system tables? What is the purpose of hiding stuff from \d output while users can get the same info another way?
IMO the intended usage for \d is to help people write queries. It seems reasonable to only show those things that would be resolved to if included in such a query. Its a convenience thing, not a security thing.
2) What is the reasoning behind separating schema privileges specifically into CREATE and USAGE? And is it something that may be changed in PG in the future?
Well, because "it is defined this way in the SQL Standard" seems to apply here (at least, the grant command compatibility notes doesn't indicate we are non-compliant here).
The current logic allows a situation where after creating a table, a user is not able to do anything with it despite being the owner. This can be confusing, and I can't really imagine a scenario where it would be useful from a security standpoint.
Yes, granting create but not usage isn't all that useful. But granting usage without create is. That is only possible if they are separate grants. I suppose create could imply usage, but that just isn't how it works, and isn't going to be changed.
Alternative approaches could be: - Separating schema privileges into more categories, such as CREATE, ALTER, DROP, SELECT, UPDATE, INSERT etc, [...]
Then it allows more granular control which seems useful forsecurity.
So, kinda like default privileges but done at the schema, not database/dba-role, scope. I'd rather there be better tools for managing permissions but still have them applied at the individual object level. Adding a layer of indirection takes an already complicated model and just complicates it further. It doesn't seem like a development and maintenance burden that the core project would benefit from taking on.
- To avoid many categories, only have USAGE to fully allow or fully prohibit someone to do stuff in the schema. Then it at least prevents the weird situation where a user can create an object but can't do anything with it.
This doesn't seem like a problem that it is worth spending time avoiding.
