Re: Some questions about schema privileges

On Wed, Oct 20, 2021 at 8:59 AM Anna Akenteva <akenteva(dot)annie(at)gmail(dot)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.