Re: ToDo: possible more rights to database owners - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: ToDo: possible more rights to database owners
Date
Msg-id CAFj8pRAK-odZqj1vaAnyErs=WKaRLwKjoAvy7Cm+Z91ckgWrvw@mail.gmail.com
Whole thread Raw
In response to Re: ToDo: possible more rights to database owners  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Proposal - Support for National Characters functionality
Next
From: Josh Berkus
Date:
Subject: Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])