Thread: Some questions about schema privileges
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? 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? 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. Alternative approaches could be: - Separating schema privileges into more categories, such as CREATE, ALTER, DROP, SELECT, UPDATE, INSERT etc, like it was done here [1] for example. Then it allows more granular control which seems useful for security. - 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. [1] https://www.ibm.com/docs/en/db2/11.5?topic=privileges-schema Thank you, Anna
On Wed, Oct 20, 2021 at 8:59 AM Anna Akenteva <akenteva.annie@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.