Re: Any need of GRANT/REVOKE CREATE TABLE | POLICY | ETC

On Tue, Aug 2, 2016 at 3:25 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jul 19, 2016 at 2:59 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> During the discussion of supporting multi tenancy with the help of
>> row level security, because of some problems of executing any
>> policy that was created by an unprivileged user [1].
>>
>> To avoid that problem, If we have some kind of new mechanism to
>> GRANT/REVOKE only CREATE POLICY from all the unprivileged
>> users by providing other CREATE access to them.
>>
>> I just want to know is there any other such requirements that if such
>> option is available, it would be good or not? I don't know whether
>> this option is possible or not? If any such requirements are present
>> other than POLICY, i would like to analyze and propose a patch for
>> the same.
>
> Well, there are lots of things that users can do that might cause
> security exposures for other users. I think it's fair to say that
> PostgreSQL - like many other systems, really - is going to work best
> when either all of the users trust each other or when they are
> well-isolated from each other (i.e. in separate databases). If one
> user can get another to execute code, then that second user can usurp
> the privileges of the first user. CREATE POLICY provides one way of
> accomplishing that, but it's not necessarily qualitatively different
> from any other mechanisms that we've had for a long time: CREATE
> TRIGGER, CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and
> CREATE VIEW are all plausible ways of inducing another user to run
> your evil, malicious code.

Thanks for your valuable opinion and details.

> So I don't think that the right solution is to restrict CREATE POLICY
> in particular. It might be useful to have some kind of general system
> for limiting the ability of certain users to run certain commands, but
> I'm not sure how much demand there really is for such a thing. I
> think it's already possible using ProcessUtilityHook, if you're
> willing to write a small amount of C code. Do our users want more?
> Very possibly. Do I know exactly what they want? No, I don't.

Here I attached a POC patch based on ProcessUtilityHook to restrict the
CREATE TRIGGER, CREATE FUNCTION, CREATE AGGREGATE,
CREATE OPERATOR, CREATE VIEW and CREATE POLICY commands
from normal user.

If any one is interested in this approach to restrict some of the operations
from some specific users, this patch can be further enhanced.

Regards,
Hari Babu
Fujitsu Australia