On Wed, Oct 20, 2021 at 8:59 AM Anna Akenteva <akenteva.an...@gmail.com> wrote:
> Hi all, > > 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 for security. 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.
