Postgresql小特性：为nosuper用户预留连接数

需求描述

场景

在某些场景下，希望保证某个nosuper用户必须能连上数据库。（superuser权限过大）

postgresql原生只可以给超级用户预留连接数，该特性可以使指定的某个用户（reserved_user_name）使用给超级用户预留的连接数。

功能说明

1、增加参数

reserved_user_name = ”

2、参数说明

指定的用户名与superuser共享superuser_reserved_connections预留的连接数。

测试结果

当前数据库服务器参数

max_connections = 5             superuser_reserved_connections = 3    reserved_user_name = 'tu1' 

当前用户情况

postgres=# select rolname,rolsuper from pg_authid;      rolname      | rolsuper-------------------+---------- jackgo            | t pg_signal_backend | f tu1               | f tu2               | f

当前连接情况

postgres=# select datname, usename from pg_stat_activity; datname  | usename----------+--------- postgres | jackgo postgres | jackgo postgres | jackgo(3 rows)

使用超级用户连接结果

[jackgo@MiWiFi-R1CM-srv:~]<PG96-60>$psqlpsql (9.6.2)Type "help" for help.postgres=#

使用指定用户tu1连接结果

[jackgo@MiWiFi-R1CM-srv:~]<PG96-60>$psql -U tu1psql (9.6.2)Type "help" for help.postgres=>

使用其他用户连接结果

[jackgo@MiWiFi-R1CM-srv:~]<PG96-60>$psql -U tu2psql: FATAL:  remaining connection slots are reserved for non-replication superuser connections

源码

diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.cold mode 100644new mode 100755index 533252a..2cf0037--- a/src/backend/postmaster/postmaster.c+++ b/src/backend/postmaster/postmaster.c@@ -207,6 +207,7 @@ char       *ListenAddresses;  * count against the limit.  */ int            ReservedBackends;+char        *ReservedUserName = NULL; /* The socket(s) we're listening to. */ #define MAXLISTEN  64diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.cold mode 100644new mode 100755index 0a4295b..fb72bee--- a/src/backend/utils/init/postinit.c+++ b/src/backend/utils/init/postinit.c@@ -560,6 +560,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username, {    bool        bootstrap = IsBootstrapProcessingMode();    bool        am_superuser;+    bool        am_reserveduser = false;    char       *fullpath;    char        dbname[NAMEDATALEN];@@ -735,6 +736,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,        PerformAuthentication(MyProcPort);        InitializeSessionUserId(username, useroid);        am_superuser = superuser();+        am_reserveduser = reserveduser();    }    /*@@ -770,8 +772,12 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,     * replication connections currently require superuser privileges, we     * don't allow them to consume the reserved slots, which are intended for     * interactive use.+     *+    * Put am_reserveduser in the AND logic.So am_reserveduser doesn't have to +    * consider whether it's a super user.     */    if ((!am_superuser || am_walsender) &&+        !am_reserveduser &&        ReservedBackends > 0 &&        !HaveNFreeProcs(ReservedBackends))        ereport(FATAL,diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.cold mode 100644new mode 100755index 4f1891f..250d18c--- a/src/backend/utils/misc/guc.c+++ b/src/backend/utils/misc/guc.c@@ -3552,6 +3552,18 @@ static struct config_string ConfigureNamesString[] =        check_cluster_name, NULL, NULL    },+    {+        {"reserved_user_name", PGC_SIGHUP, CONN_AUTH_SETTINGS,+            gettext_noop("Sets the reserved user name."),+            NULL,+            GUC_SUPERUSER_ONLY+        },+        &ReservedUserName,+        "",+        NULL, NULL, NULL+    },++    /* End-of-list marker */    {        {NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULLdiff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sampleold mode 100644new mode 100755index fa6c0ea..8676106--- a/src/backend/utils/misc/postgresql.conf.sample+++ b/src/backend/utils/misc/postgresql.conf.sample@@ -63,6 +63,7 @@ #port = 5432               # (change requires restart) #max_connections = 100         # (change requires restart) #superuser_reserved_connections = 3    # (change requires restart)+#reserved_user_name = ''       # (change requires restart) #unix_socket_directories = '/tmp'  # comma-separated list of directories                    # (change requires restart) #unix_socket_group = ''            # (change requires restart)diff --git a/src/backend/utils/misc/superuser.c b/src/backend/utils/misc/superuser.cold mode 100644new mode 100755index dd70e02..0c3a252--- a/src/backend/utils/misc/superuser.c+++ b/src/backend/utils/misc/superuser.c@@ -24,7 +24,9 @@ #include "catalog/pg_authid.h" #include "utils/inval.h" #include "utils/syscache.h"+#include "utils/acl.h" #include "miscadmin.h"+#include "postmaster/postmaster.h" /*@@ -96,6 +98,40 @@ superuser_arg(Oid roleid)    return result; }++bool+reserveduser(void)+{+   return reserveduser_arg(GetUserId());+}++bool+reserveduser_arg(Oid roleid)+{+    bool       result;+    Oid         reservedoid;++    if (ReservedUserName && !(*ReservedUserName))+    {+        result = false;+    }++    reservedoid = get_role_oid(ReservedUserName, true);++    if (!OidIsValid(reservedoid))+    {+        result = false;+    }++    if (roleid == reservedoid)+    {+        result = true;+    }+    +    return result;+}++ /*  * RoleidCallback  *     Syscache inval callback functiondiff --git a/src/include/miscadmin.h b/src/include/miscadmin.hold mode 100644new mode 100755index 78545da..6399632--- a/src/include/miscadmin.h+++ b/src/include/miscadmin.h@@ -326,6 +326,8 @@ extern void SwitchBackToLocalLatch(void); /* in utils/misc/superuser.c */ extern bool superuser(void);   /* current user is superuser */ extern bool superuser_arg(Oid roleid); /* given user is superuser */+extern bool reserveduser(void);    /* current user is reserveduser */+extern bool reserveduser_arg(Oid roleid);  /* given user is reserveduser */ /*****************************************************************************diff --git a/src/include/postmaster/postmaster.h b/src/include/postmaster/postmaster.hold mode 100644new mode 100755index b2d7776..99edac8--- a/src/include/postmaster/postmaster.h+++ b/src/include/postmaster/postmaster.h@@ -16,6 +16,8 @@ /* GUC options */ extern bool EnableSSL; extern int ReservedBackends;+extern char *ReservedUserName;+ extern int PostPortNumber; extern int Unix_socket_permissions; extern char *Unix_socket_group;