Hi,
this is the first time that I post here, so if I wrong please don't kill me ...
I see that pg_terminate_backend and pg_cancel_backend can be execute
only by admin users.
This approach seems to be too restrictive in a lots of real situation.
In dept, I have a situation where it is created one database machine
for all the postgresql database.
This database machine is managed by IT staff that have created two
user for each application.
One user is the owner db user that create, drop, grant on this db,
while the other user is the application db.
In this situation I (the developer) not able to disconnect any client
and stop any high weight queries.
Unfortunately the application run on application server that is
manager, again, by IT staff and I not have the right to stop it.
I suppose that give the right to the owner db user to terminate or
cancel other session connected to the database which it is owner is a
good thing.
I not see any security problem because this user can cancel or
terminate only the session related with the own database,
but if you think that this is a problem, a configuration parameter can be used.
Of course I can create a function with admin right that do the same
thing but the IT staff need to install, configure, and give the right
grant.
So, I suppose, that this can to be only a workaround, not the solution.
Sorry for my English.
I attach a path for this
Best Regards, Torello
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 5bda4af..5327447 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -33,6 +33,7 @@
#include "storage/procarray.h"
#include "utils/builtins.h"
#include "tcop/tcopprot.h"
+#include "pgstat.h"
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
@@ -75,9 +76,33 @@ static bool
pg_signal_backend(int pid, int sig)
{
if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to signal other server processes"))));
+ {
+ bool haveRight = false;
+ PgBackendStatus *backend;
+
+ /* If the user not is the superuser, need to be the db owner. */
+ if (pg_database_ownercheck(MyDatabaseId, GetUserId())) {
+
+ /* Check for the specify backend in the stat info table */
+ int nBackend = pgstat_fetch_stat_numbackends();
+ int i;
+ for (i = 1; i<=nBackend; ++i) {
+ backend = pgstat_fetch_stat_beentry(i);
+ if (backend->st_procpid == pid) {
+ if (backend->st_databaseid == MyDatabaseId)
+ haveRight = true;
+ break;
+ }
+ }
+ }
+
+ if (!haveRight)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser or database destination owner to signal other server processes"))));
+ }
+
+
if (!IsBackendPid(pid))
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
