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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
szymon
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 29 July 2013 13:20, Pavel Stehule <pavel.stehule@gmail.com> wrote:
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.
I agree.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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
Show quoted text
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
Attachments:
owner-can-control-session.patchapplication/octet-stream; name=owner-can-control-session.patchDownload
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***************
*** 112,118 **** pg_signal_backend(int pid, int sig)
return SIGNAL_BACKEND_ERROR;
}
! if (!(superuser() || proc->roleId == GetUserId()))
return SIGNAL_BACKEND_NOPERMISSION;
/*
--- 112,125 ----
return SIGNAL_BACKEND_ERROR;
}
! /*
! * Superuser can send signal without restriction, owner can send signals to all
! * users attached to owned databases without superuser, others can signals only to
! * sessions with same account.
! */
! if (!(superuser() || proc->roleId == GetUserId()
! || (pg_database_ownercheck(proc->databaseId, GetUserId())
! && !superuser_arg(proc->roleId))))
return SIGNAL_BACKEND_NOPERMISSION;
/*
***************
*** 151,157 **** pg_cancel_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or have the same role to cancel queries running in other server processes"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
--- 158,164 ----
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or database owner or have the same role to cancel queries running in other server processes"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
***************
*** 168,174 **** pg_terminate_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or have the same role to terminate other server processes"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
--- 175,181 ----
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or database owner or have the same role to terminate other server processes"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 656,663 **** pg_stat_get_activity(PG_FUNCTION_ARGS)
else
nulls[3] = true;
! /* Values only available to same user or superuser */
! if (superuser() || beentry->st_userid == GetUserId())
{
switch (beentry->st_state)
{
--- 656,664 ----
else
nulls[3] = true;
! /* Values only available to same user or superuser or owner */
! if (superuser() || beentry->st_userid == GetUserId()
! || pg_database_ownercheck(beentry->st_databaseid, GetUserId()))
{
switch (beentry->st_state)
{