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

概述需求描述 场景 在某些场景下，希望保证某个nosuper用户必须能连上数据库。（superuser权限过大） postgresql原生只可以给超级用户预留连接数，该特性可以使指定的某个用户（reserved_user_name）使用给超级用户预留的连接数。 功能说明 1、增加参数 reserved_user_name = ” 2、参数说明 指定的用户名与superuser共享superuser_r 需求描述 场景

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

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

功能说明

1、增加参数

reserved_user_name = ”

2、参数说明

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

测试结果

当前数据库服务器参数

@H_502_23@max_connections = 5 superuser_reserved_connections = 3 reserved_user_name = 'tu1'

当前用户情况

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

当前连接情况

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

使用超级用户连接结果

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

使用指定用户tu1连接结果

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

使用其他用户连接结果

@H_502_23@[jackgo@MiWiFi-R1CM-srv:~]<PG96-60>$psql -U tu2psql: FATAL: remaining connection slots are reserved for non-replication superuser connections 源码 @H_502_23@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,PerformAuthentication(MyProcPort); InitializeSessionUserID(username,useroID); am_superuser = superuser();+ am_reserveduser = reserveduser(); } /* @@ -770,8 +772,12 @@ InitPostgres(const char *in_dbname,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+ },++ /* End-of-List marker */ { {NulL,0,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 function diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h old mode 100644 new mode 100755 index 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.h old mode 100644 new mode 100755 index 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; 总结

以上是内存溢出为你收集整理的Postgresql小特性：为nosuper用户预留连接数全部内容，希望文章能够帮你解决Postgresql小特性：为nosuper用户预留连接数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。