Thread: ToDo: possible more rights to database owners

Hello

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?

Regards

Pavel

2013/7/29 Szymon Guz <mabewlun@gmail.com>:
> On 29 July 2013 11:25, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>> Hello
>>
>> 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?
>>
>
> 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 am thinking so owner cannot cancel super user is enough. It allows
simply cancelling and terminating connects where we have more
application roles without superuser rights.

Regards

Pavel

>
> szymon

Szymon,

* Szymon Guz (mabewlun@gmail.com) wrote:
> On 29 July 2013 11:25, Pavel Stehule <pavel.stehule@gmail.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.

> 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.
Thanks,
    Stephen

2013/7/29 Stephen Frost <sfrost@snowman.net>:
> Szymon,
>
> * Szymon Guz (mabewlun@gmail.com) wrote:
>> On 29 July 2013 11:25, Pavel Stehule <pavel.stehule@gmail.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.

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

2013/7/29 Pavel Stehule <pavel.stehule@gmail.com>:
> 2013/7/29 Stephen Frost <sfrost@snowman.net>:
>> Szymon,
>>
>> * Szymon Guz (mabewlun@gmail.com) wrote:
>>> On 29 July 2013 11:25, Pavel Stehule <pavel.stehule@gmail.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