Re: ToDo: possible more rights to database owners

2013/7/29 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2013/7/29 Stephen Frost <sfrost(at)snowman(dot)net>:
>> Szymon,
>>
>> * Szymon Guz (mabewlun(at)gmail(dot)com) wrote:
>>> On 29 July 2013 11:25, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> > In 9.3 super user can cancel all queries or user can cancel own sessions.
>>> >
>>> > Is possible enhance this possibility to database owners? So owner can
>>> > cancel or can terminate sessions related to owned databases?
>>
>> Interesting idea but I'm not sure that database ownership is really how
>> we want to drive this. I can certainly see cases where I'd like user
>> 'X' to be able to cancel queries but where that user isn't the database
>> owner. Reducing the set of things that only a superuser can do is
>> certainly a worthwhile goal though.

Here is a implementation based on ownership - so database owner can
control execution in this his database. But it cannot to cancel or
terminate superusers.

Regards

Pavel

>
> there are two ideas:
>
> 1. some user (not super user) can terminate queries other users (not
> only own queries)
> 2. the limits are based on owning.
>
> Probably there is agreement on @1. I think so @2 is simple and natural
> - like "owner is small superuser", and it doesn't need any new
> objects.
>
> Second possibility is new kind of rights - possibility to terminate
> some other users. Possibility to terminate can be based on ownership
> or specific rights. We can support both.
>
> For me is mainly important @1. Design or implementation is not
> important - we would to decrease a usage of super user and we would to
> use more application users and only a few management users.
>
>>
>>> But this means that a db owner could cancel superuser's super important
>>> database query. Maybe let's make a default that the owner can cancel all
>>> queries except for superuser's ones. And additionaly a special right that
>>> superuser can grant it to the db owner, so the owner can cancel even
>>> superuser's queries?
>>
>> I'm not sure that I buy this argument either, particularly as
>> "superuser-ness status" can change due to a simple 'set role' and you'd
>> always have a race condition where the sending process might not realize
>> that the receiving process suddenly became a superuser process. This
>> strikes me as an option we might attach to a role (ala create-user)
>> rather than drive it by database ownership and forget the whole thing
>> about trying to block it for superuser processes- either you can
>> terminate backends that aren't yours, or you can't.
>>
>
> yes, it is valid option - when I thinking about security - it can be
> nice possibility to REVOKE right kill own tasks to some selected
> users.
>
> Regards
>
> Pavel
>
>> Thanks,
>>
>> Stephen