1, Background introduction
In view of security considerations, the superuser of the database is generally only in the hands of DBA students.
Recently, many times such a scenario has happened. Students in the business make complaints about the structure of the table. The Tucao table is locked by the system process. After investigation, it is found that the autovacuum worker is blocked. It often needs to get up and support, which also blocks the efficiency of publishing.
Can you give the terminate permission of autovacuum worker to nonsuper user? It is convenient for the business account to directly handle the situation blocked by the autovacuum worker.
After studying in my spare time, I will PG_ terminate_ The backend () has made a small change to support the ability of nonsuper user kill autovacuum worker
2, Code analysis
Let's look at PG first_ terminate_ The logic of the backend() function is native
/* * Signal to terminate a backend process. This is allowed if you are a member * of the role whose process is being terminated. * * Note that only superusers can signal superuser-owned processes. */ Datum pg_terminate_backend(PG_FUNCTION_ARGS) { int r = pg_signal_backend(PG_GETARG_INT32(0), SIGTERM); if (r == SIGNAL_BACKEND_NOSUPERUSER) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be a superuser to terminate superuser process")))); if (r == SIGNAL_BACKEND_NOPERMISSION) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend")))); PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS); }
It mainly calls pg_signal_backend(PG_GETARG_INT32(0), SIGTERM) sends SIGTERM signal, i.e. signal 15, to the process.
In PG_ signal_ In the backend function:
1. First, call the BackendPidGetProc function to obtain the Backendpid list. Here, select the Backendpid list from allProc, and loop match the pid we passed in that needs to be terminate d. If not, return: pid% d is not a PostgreSQL server process;
2. Judge whether the proc - > roleid of the pid we want to terminate is superuser, and whether the user we operate has superuser permission. If the conditions are not met, return: must be a superuser to terminate superuser process;
3. Here, first judge whether the user currently executing terminate is a member of proc - > roleid; Then judge whether the user has pg_signal_backend permission; If the conditions are not met here, return:
must be a member of the role whose process is being terminated or member of pg_signal_backend
pg is a native design. When a nonsuperuser user terminates an autovacuum worker, it will follow the third logic. Then we can modify it here to realize the function of nonsuper user kill autovacuum worker.
4. Call kill interface to send signal to pid
3, Scheme design
The principle of modifying the code is to keep the original logic as much as possible. We can consider adding a control parameter, which is closed by default and follows the original logic; After the parameter is turned on, the new function is turned on.
Add a bool type control parameter enable_ nonsuper_ kill_ The default value of autovacuumworker is off, which can be modified through alter system. Overloading the configuration file takes effect.
postgres=> select * from pg_settings where name like '%enable_nonsuper_kill_autovacuumworker%'; -[ RECORD 1 ]---+-------------------------------------------------------------------------- name | enable_nonsuper_kill_autovacuumworker setting | off unit | category | Preset Options short_desc | Whether nonsuperuser could kill autovacuum worker process, default false. extra_desc | context | sighup vartype | bool source | default min_val | max_val | enumvals | boot_val | off reset_val | off sourcefile | sourceline | pending_restart | f postgres=>
Modify PG_ signal_ The logic of the backend function is as follows:
static int pg_signal_backend(int pid, int sig) { PGPROC *proc = BackendPidGetProc(pid); /* * BackendPidGetProc returns NULL if the pid isn't valid; but by the time * we reach kill(), a process for which we get a valid proc here might * have terminated on its own. There's no way to acquire a lock on an * arbitrary process to prevent that. But since so far all the callers of * this mechanism involve some request for ending the process anyway, that * it might end on its own first is not a problem. */ if (proc == NULL) { /* * This is just a warning so a loop-through-resultset will not abort * if one backend terminated on its own during the run. */ ereport(WARNING, (errmsg("PID %d is not a PostgreSQL server process", pid))); return SIGNAL_BACKEND_ERROR; } /* Only allow superusers to signal superuser-owned backends. */ if (superuser_arg(proc->roleId) && !superuser()) return SIGNAL_BACKEND_NOSUPERUSER; /* Users can signal backends they have role membership in. */ if (!has_privs_of_role(GetUserId(), proc->roleId) && !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SIGNAL_BACKENDID)) { /* Modify by Nickxyang at 2021-11-28 */ /* Here, judge enable first_ nonsuper_ kill_ Whether the autovacuumworker parameter is enabled. If it is not enabled, go through the native logic return SIGNAL_BACKEND_NOPERMISSION; */ if (!enable_nonsuper_kill_autovacuumworker) return SIGNAL_BACKEND_NOPERMISSION; /* The structure information corresponding to the process is obtained here */ LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; local_beentry = pgstat_fetch_stat_local_beentry(proc->backendId); /* If the obtained information is empty, does the process no longer exist? You don't need to terminate, just return SIGNAL_BACKEND_NOPERMISSION will do */ if (!local_beentry) return SIGNAL_BACKEND_NOPERMISSION; beentry = &local_beentry->backendStatus; /* If the process type obtained is not B_AUTOVAC_WORKER, return SIGNAL_BACKEND_NOPERMISSION * In other words, only the terminate autovacuum worker process can successfully go to the bottom and kill the logic of sending signals, * If it is any other system process, it will report an error without permission */ if (beentry->st_backendType != B_AUTOVAC_WORKER) { return SIGNAL_BACKEND_NOPERMISSION; } /* End at 2021-11-28 */ } /* * Can the process we just validated above end, followed by the pid being * recycled for a new process, before reaching here? Then we'd be trying * to kill the wrong thing. Seems near impossible when sequential pid * assignment and wraparound is used. Perhaps it could happen on a system * where pid re-use is randomized. That race condition possibility seems * too unlikely to worry about. */ /* If we have setsid(), signal the backend's whole process group */ #ifdef HAVE_SETSID if (kill(-pid, sig)) #else if (kill(pid, sig)) #endif { /* Again, just a warning to allow loops */ ereport(WARNING, (errmsg("could not send signal to process %d: %m", pid))); return SIGNAL_BACKEND_ERROR; } return SIGNAL_BACKEND_SUCCESS; }
At the same time, modify PG_ stat_ progress_ The logic of the vacuum view, because the nonsuper user in the native logic has no right to view all the information of the ongoing vacuum, and can only see the pid, datid and dataname fields;
Modify the logic here when enable_ nonsuper_ kill_ When the autovacuumworker parameter is turned on, you can see PG_ stat_ progress_ All the field information of the vacuum. According to the relid, i.e. table oid, it is convenient to terminate the corresponding worker process; When the parameter is closed, the original logic is maintained and only the default fields are displayed.
Datum pg_stat_get_progress_info(PG_FUNCTION_ARGS) { #define PG_STAT_GET_PROGRESS_COLS PGSTAT_NUM_PROGRESS_PARAM + 3 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; char *cmd = text_to_cstring(PG_GETARG_TEXT_PP(0)); ProgressCommandType cmdtype; TupleDesc tupdesc; Tuplestorestate *tupstore; ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; MemoryContext per_query_ctx; MemoryContext oldcontext; /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set"))); if (!(rsinfo->allowedModes & SFRM_Materialize)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("materialize mode required, but it is not allowed in this context"))); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); /* Translate command name into command type code. */ if (pg_strcasecmp(cmd, "VACUUM") == 0) cmdtype = PROGRESS_COMMAND_VACUUM; else if (pg_strcasecmp(cmd, "ANALYZE") == 0) cmdtype = PROGRESS_COMMAND_ANALYZE; else if (pg_strcasecmp(cmd, "CLUSTER") == 0) cmdtype = PROGRESS_COMMAND_CLUSTER; else if (pg_strcasecmp(cmd, "CREATE INDEX") == 0) cmdtype = PROGRESS_COMMAND_CREATE_INDEX; else if (pg_strcasecmp(cmd, "BASEBACKUP") == 0) cmdtype = PROGRESS_COMMAND_BASEBACKUP; else ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("invalid command name: \"%s\"", cmd))); per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; oldcontext = MemoryContextSwitchTo(per_query_ctx); tupstore = tuplestore_begin_heap(true, false, work_mem); rsinfo->returnMode = SFRM_Materialize; rsinfo->setResult = tupstore; rsinfo->setDesc = tupdesc; MemoryContextSwitchTo(oldcontext); /* 1-based index */ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) { LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; Datum values[PG_STAT_GET_PROGRESS_COLS]; bool nulls[PG_STAT_GET_PROGRESS_COLS]; int i; MemSet(values, 0, sizeof(values)); MemSet(nulls, 0, sizeof(nulls)); local_beentry = pgstat_fetch_stat_local_beentry(curr_backend); if (!local_beentry) continue; beentry = &local_beentry->backendStatus; /* * Report values for only those backends which are running the given * command. */ if (!beentry || beentry->st_progress_command != cmdtype) continue; /* Value available to all callers */ values[0] = Int32GetDatum(beentry->st_procpid); values[1] = ObjectIdGetDatum(beentry->st_databaseid); /* show rest of the values including relid only to role members */ if (HAS_PGSTAT_PERMISSIONS(beentry->st_userid)) { values[2] = ObjectIdGetDatum(beentry->st_progress_command_target); for (i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++) values[i + 3] = Int64GetDatum(beentry->st_progress_param[i]); } /* Modify by Nickxyang at 2021-11-28 */ /* When the system process executes vauum or analyze, the current user is nonsuperuser * And enable_ nonsuper_ kill_ When the autovacuumworker parameter is on, full information is displayed */ else if (!HAS_PGSTAT_PERMISSIONS(beentry->st_userid) && (beentry->st_progress_command == (PROGRESS_COMMAND_VACUUM || PROGRESS_COMMAND_ANALYZE)) && enable_nonsuper_kill_autovacuumworker) { values[2] = ObjectIdGetDatum(beentry->st_progress_command_target); for (i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++) values[i + 3] = Int64GetDatum(beentry->st_progress_param[i]); } /* End at 2021-11-28 */ else { nulls[2] = true; for (i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++) nulls[i + 3] = true; } tuplestore_putvalues(tupstore, tupdesc, values, nulls); } /* clean up and return the tuplestore */ tuplestore_donestoring(tupstore); return (Datum) 0; }
4, Realization effect
You can see that the default nonsuper user cannot terminate the autovacuum worker process.
postgres=> \du pguser List of roles Role name | Attributes | Member of -----------+------------+----------- pguser | | {} postgres=> select relid::regclass as tablename,* from pg_stat_progress_vacuum ; tablename | pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples -----------+-------+-------+----------+-------+---------------+-----------------+-------------------+--------------------+--------------------+-----------------+----------------- | 28477 | 13578 | postgres | | | | | | | | (1 row) postgres=> select pg_terminate_backend(28477); ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend postgres=>
Open parameters using superuser
postgres=# show enable_nonsuper_kill_autovacuumworker;alter system set enable_nonsuper_kill_autovacuumworker to on;select pg_reload_conf(); enable_nonsuper_kill_autovacuumworker --------------------------------------- off (1 row) ALTER SYSTEM pg_reload_conf ---------------- t (1 row) postgres=#
After the parameter is opened, terminate autovacuum worker successfully
postgres=> show enable_nonsuper_kill_autovacuumworker; enable_nonsuper_kill_autovacuumworker --------------------------------------- on (1 row) postgres=> select relid::regclass as tablename,* from pg_stat_progress_vacuum ; tablename | pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples -----------+-------+-------+----------+-------+---------------+-----------------+-------------------+--------------------+--------------------+-----------------+----------------- tbl_1 | 28477 | 13578 | postgres | 16404 | scanning heap | 833334 | 19655 | 19654 | 0 | 291 | 120 (1 row) postgres=> select pg_terminate_backend(28477); pg_terminate_backend ---------------------- t (1 row) postgres=> select relid::regclass as tablename,* from pg_stat_progress_vacuum ; tablename | pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples -----------+-------+-------+----------+-------+---------------+-----------------+-------------------+--------------------+--------------------+-----------------+----------------- | | | | | | | | | | | (0 row)
At the same time, it is verified that other system processes under terminate fail
postgres=# select pid,usename,backend_type from pg_stat_activity; pid | usename | backend_type -------+----------+------------------------------ 10138 | | autovacuum launcher 10140 | postgres | logical replication launcher 19027 | pguser | client backend 25883 | pguser | client backend 25898 | postgres | client backend 6076 | postgres | client backend 10136 | | background writer 10135 | | checkpointer 10137 | | walwriter (9 rows) postgres=>\c postgres pguser You are now connected to database "postgres" as user "pguser". postgres=> select pg_terminate_backend(10138); ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend postgres=> select pg_terminate_backend(10140); ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend postgres=> select pg_terminate_backend(10135); WARNING: PID 10135 is not a PostgreSQL server process pg_terminate_backend ---------------------- f (1 row) postgres=>
5, Summary
From the test results, the modified scheme is feasible.
At the same time, some students should have noticed the error prompt when the original logic terminates: error: must be a member of the role who process is being terminated or member of PG_ signal_ backend.
Will PG_ signal_ The backend gives nonsuperuser the ability to terminate an autovacuum worker?
Yes, but if you give PG_ signal_ Back end permission, you can terminate all system processes with proc - > roleid 0, such as autovacuum launcher, logical replication launcher, etc. This risk is relatively high, so we should strictly control this authority.