From: Nathan Bossart <nathandbossart@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: almost-super-user problems that we haven't fixed yet
Date: Thu, 19 Jan 2023 11:46:01 -0800
Message-ID: <20230119194601.GA4105788@nathanxps13> (raw)
In-Reply-To: <CA+TgmoYEQTEPi_PD3_TW+N86+dR5Qkfy+JKJoT0YAfXWhAQjbA@mail.gmail.com>
References: <20230117184230.GB3015764@nathanxps13>
<CA+TgmoaOXE4rDzenFuaEFKy7DLN-3BRuP1WAetNEuswFc3yuvA@mail.gmail.com>
<20230118001523.GB3321806@nathanxps13>
<CA+Tgmoa3qkqVpU2=riFfKchOLDMuaQcFXOF-a_B-RRJm1Njq_w@mail.gmail.com>
<20230118190012.GA3540486@nathanxps13>
<CA+Tgmob0SMMZhDEtRTsqOFP3dbGQoqBs=O2x6bp_UVeH1RVuVw@mail.gmail.com>
<20230118211414.GA3702050@nathanxps13>
<CA+Tgmob3mpQWienjeewWym+7gU-JcCXYf39Bh6Z2LkGEVGMzhA@mail.gmail.com>
<20230119175421.GA3934756@nathanxps13>
<CA+TgmoYEQTEPi_PD3_TW+N86+dR5Qkfy+JKJoT0YAfXWhAQjbA@mail.gmail.com>
On Thu, Jan 19, 2023 at 02:17:35PM -0500, Robert Haas wrote:
> On Thu, Jan 19, 2023 at 12:54 PM Nathan Bossart
> <nathandbossart@gmail.com> wrote:
>> > OK. Might be worth a short comment.
>>
>> I added one.
>
> Thanks. I'd move it to the inner indentation level so it's closer to
> the test at issue.
I meant for it to cover the call to HaveNFreeProcs() as well since the same
idea applies. I left it the same for now, but if you still think it makes
sense to move it, I'll do so.
> I would also suggest reordering the documentation and the
> postgresql.conf.sample file so that reserved_connections precedes
> superuser_reserved_connections, instead of following it.
Makes sense.
> Other than that, this seems like it might be about ready to commit,
> barring objections or bug reports.
Awesome.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v5-0001-Code-review-for-ea92368.patch (1.6K, ../20230119194601.GA4105788@nathanxps13/2-v5-0001-Code-review-for-ea92368.patch)
download | inline diff:
From a6811b643df94c9057373fd687398c85a807fd5e Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Wed, 18 Jan 2023 12:43:41 -0800
Subject: [PATCH v5 1/3] Code review for ea92368.
This commit missed an error message and a line in the docs.
---
doc/src/sgml/config.sgml | 3 +--
src/backend/utils/init/postinit.c | 2 +-
2 files changed, 2 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 89d53f2a64..e019a1aac9 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -725,8 +725,7 @@ include_dir 'conf.d'
number of active concurrent connections is at least
<varname>max_connections</varname> minus
<varname>superuser_reserved_connections</varname>, new
- connections will be accepted only for superusers, and no- new replication connections will be accepted.+ connections will be accepted only for superusers.
</para>
<para>
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.cindex ae5a85ed65..9145d96b38 100644--- a/src/backend/utils/init/postinit.c+++ b/src/backend/utils/init/postinit.c@@ -931,7 +931,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
!HaveNFreeProcs(ReservedBackends))
ereport(FATAL,
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
- errmsg("remaining connection slots are reserved for non-replication superuser connections")));+ errmsg("remaining connection slots are reserved for superusers")));
/* Check replication permissions needed for walsender processes. */
if (am_walsender)
--
2.25.1
[text/x-diff] v5-0002-Rename-ReservedBackends-to-SuperuserReservedConne.patch (4.1K, ../20230119194601.GA4105788@nathanxps13/3-v5-0002-Rename-ReservedBackends-to-SuperuserReservedConne.patch)
download | inline diff:
From e0390f0120315746ea04a9fa1bf709def76e6196 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Tue, 17 Jan 2023 13:58:56 -0800
Subject: [PATCH v5 2/3] Rename ReservedBackends to
SuperuserReservedConnections.
This is in preparation for adding a new reserved_connections GUC.
---
src/backend/postmaster/postmaster.c | 20 ++++++++++----------
src/backend/utils/init/postinit.c | 4 ++--
src/backend/utils/misc/guc_tables.c | 2 +-
src/include/postmaster/postmaster.h | 2 +-
4 files changed, 14 insertions(+), 14 deletions(-)
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.cindex 9cedc1b9f0..3f799c4ac8 100644--- a/src/backend/postmaster/postmaster.c+++ b/src/backend/postmaster/postmaster.c@@ -204,15 +204,15 @@ char *Unix_socket_directories;
char *ListenAddresses;
/*
- * ReservedBackends is the number of backends reserved for superuser use.- * This number is taken out of the pool size given by MaxConnections so- * number of backend slots available to non-superusers is- * (MaxConnections - ReservedBackends). Note what this really means is- * "if there are <= ReservedBackends connections available, only superusers- * can make new connections" --- pre-existing superuser connections don't- * count against the limit.+ * SuperuserReservedConnections is the number of backends reserved for+ * superuser use. This number is taken out of the pool size given by+ * MaxConnections so number of backend slots available to non-superusers is+ * (MaxConnections - SuperuserReservedConnections). Note what this really+ * means is "if there are <= SuperuserReservedConnections connections+ * available, only superusers can make new connections" --- pre-existing+ * superuser connections don't count against the limit.
*/
-int ReservedBackends;+int SuperuserReservedConnections;
/* The socket(s) we're listening to. */
#define MAXLISTEN 64
@@ -908,11 +908,11 @@ PostmasterMain(int argc, char *argv[])
/*
* Check for invalid combinations of GUC settings.
*/
- if (ReservedBackends >= MaxConnections)+ if (SuperuserReservedConnections >= MaxConnections)
{
write_stderr("%s: superuser_reserved_connections (%d) must be less than max_connections (%d)\n",
progname,
- ReservedBackends, MaxConnections);+ SuperuserReservedConnections, MaxConnections);
ExitPostmaster(1);
}
if (XLogArchiveMode > ARCHIVE_MODE_OFF && wal_level == WAL_LEVEL_MINIMAL)
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.cindex 9145d96b38..40f145e0ab 100644--- a/src/backend/utils/init/postinit.c+++ b/src/backend/utils/init/postinit.c@@ -927,8 +927,8 @@ InitPostgres(const char *in_dbname, Oid dboid,
* limited by max_connections or superuser_reserved_connections.
*/
if (!am_superuser && !am_walsender &&
- ReservedBackends > 0 &&- !HaveNFreeProcs(ReservedBackends))+ SuperuserReservedConnections > 0 &&+ !HaveNFreeProcs(SuperuserReservedConnections))
ereport(FATAL,
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("remaining connection slots are reserved for superusers")));
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.cindex cd0fc2cb8f..0fa9fdd3c5 100644--- a/src/backend/utils/misc/guc_tables.c+++ b/src/backend/utils/misc/guc_tables.c@@ -2163,7 +2163,7 @@ struct config_int ConfigureNamesInt[] =
gettext_noop("Sets the number of connection slots reserved for superusers."),
NULL
},
- &ReservedBackends,+ &SuperuserReservedConnections,
3, 0, MAX_BACKENDS,
NULL, NULL, NULL
},
diff --git a/src/include/postmaster/postmaster.h b/src/include/postmaster/postmaster.hindex 203177e1ff..0e4b8ded34 100644--- a/src/include/postmaster/postmaster.h+++ b/src/include/postmaster/postmaster.h@@ -15,7 +15,7 @@
/* GUC options */
extern PGDLLIMPORT bool EnableSSL;
-extern PGDLLIMPORT int ReservedBackends;+extern PGDLLIMPORT int SuperuserReservedConnections;
extern PGDLLIMPORT int PostPortNumber;
extern PGDLLIMPORT int Unix_socket_permissions;
extern PGDLLIMPORT char *Unix_socket_group;
--
2.25.1
[text/x-diff] v5-0003-Introduce-reserved_connections-and-pg_use_reserve.patch (13.2K, ../20230119194601.GA4105788@nathanxps13/4-v5-0003-Introduce-reserved_connections-and-pg_use_reserve.patch)
download | inline diff:
From 107d753e1eead744edd960b255bcf1b29ef41514 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Tue, 17 Jan 2023 15:36:59 -0800
Subject: [PATCH v5 3/3] Introduce reserved_connections and
pg_use_reserved_connections.
This provides a way to reserve connection slots for non-superusers.
superuser_reserved_connections remains as a final reserve in case
reserved_connections has been exhausted.
---
doc/src/sgml/config.sgml | 39 ++++++++++++++++++-
doc/src/sgml/user-manag.sgml | 5 +++
src/backend/postmaster/postmaster.c | 29 +++++++++-----
src/backend/storage/lmgr/proc.c | 16 +++++---
src/backend/utils/init/postinit.c | 31 +++++++++++----
src/backend/utils/misc/guc_tables.c | 11 ++++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/catalog/pg_authid.dat | 5 +++
src/include/postmaster/postmaster.h | 1 +
src/include/storage/proc.h | 2 +-
10 files changed, 115 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex e019a1aac9..dc9b78b0b7 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -708,6 +708,37 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-reserved-connections" xreflabel="reserved_connections">+ <term><varname>reserved_connections</varname> (<type>integer</type>)+ <indexterm>+ <primary><varname>reserved_connections</varname> configuration parameter</primary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Determines the number of connection <quote>slots</quote> that are+ reserved for connections by roles with privileges of the+ <link linkend="predefined-roles-table"><literal>pg_used_reserved_connections</literal></link>+ role. Whenever the number of free connection slots is greater than+ <xref linkend="guc-superuser-reserved-connections"/> but less than or+ equal to the sum of <varname>superuser_reserved_connections</varname>+ and <varname>reserved_connections</varname>, new connections will be+ accepted only for superusers and roles with privileges of+ <literal>pg_use_reserved_connections</literal>. If+ <varname>superuser_reserved_connections</varname> or fewer connection+ slots are available, new connections will be accepted only for+ superusers.+ </para>++ <para>+ The default value is zero connections. The value must be less than+ <varname>max_connections</varname> minus+ <varname>superuser_reserved_connections</varname>. This parameter can+ only be set at server start.+ </para>+ </listitem>+ </varlistentry>+
<varlistentry id="guc-superuser-reserved-connections"
xreflabel="superuser_reserved_connections">
<term><varname>superuser_reserved_connections</varname>
@@ -725,12 +756,16 @@ include_dir 'conf.d'
number of active concurrent connections is at least
<varname>max_connections</varname> minus
<varname>superuser_reserved_connections</varname>, new
- connections will be accepted only for superusers.+ connections will be accepted only for superusers. The connection slots+ reserved by this parameter are intended as final reserve for emergency+ use after the slots reserved by+ <xref linkend="guc-reserved-connections"/> have been exhausted.
</para>
<para>
The default value is three connections. The value must be less
- than <varname>max_connections</varname>.+ than <varname>max_connections</varname> minus+ <varname>reserved_connections</varname>.
This parameter can only be set at server start.
</para>
</listitem>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgmlindex 71a2d8f298..002c1e3aff 100644--- a/doc/src/sgml/user-manag.sgml+++ b/doc/src/sgml/user-manag.sgml@@ -689,6 +689,11 @@ DROP ROLE doomed_role;
and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all
relations.</entry>
</row>
+ <row>+ <entry>pg_use_reserved_connections</entry>+ <entry>Allow use of connection slots reserved via+ <xref linkend="guc-reserved-connections"/>.</entry>+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.cindex 3f799c4ac8..aca1ef91b5 100644--- a/src/backend/postmaster/postmaster.c+++ b/src/backend/postmaster/postmaster.c@@ -205,14 +205,24 @@ char *ListenAddresses;
/*
* SuperuserReservedConnections is the number of backends reserved for
- * superuser use. This number is taken out of the pool size given by- * MaxConnections so number of backend slots available to non-superusers is- * (MaxConnections - SuperuserReservedConnections). Note what this really- * means is "if there are <= SuperuserReservedConnections connections- * available, only superusers can make new connections" --- pre-existing- * superuser connections don't count against the limit.+ * superuser use, and ReservedConnections is the number of backends reserved+ * for use by roles with privileges of the pg_use_reserved_connections+ * predefined role. These are taken out of the pool of MaxConnections backend+ * slots, so the number of backend slots available for roles that are neither+ * superuser nor have privileges of pg_use_reserved_connections is+ * (MaxConnections - SuperuserReservedConnections - ReservedConnections).+ *+ * If the number of remaining slots is less than or equal to+ * SuperuserReservedConnections, only superusers can make new connections. If+ * the number of remaining slots is greater than SuperuserReservedConnections+ * but less than or equal to+ * (SuperuserReservedConnections + ReservedConnections), only superusers and+ * roles with privileges of pg_use_reserved_connections can make new+ * connections. Note that pre-existing superuser and+ * pg_use_reserved_connections connections don't count against the limits.
*/
int SuperuserReservedConnections;
+int ReservedConnections;
/* The socket(s) we're listening to. */
#define MAXLISTEN 64
@@ -908,11 +918,12 @@ PostmasterMain(int argc, char *argv[])
/*
* Check for invalid combinations of GUC settings.
*/
- if (SuperuserReservedConnections >= MaxConnections)+ if (SuperuserReservedConnections + ReservedConnections >= MaxConnections)
{
- write_stderr("%s: superuser_reserved_connections (%d) must be less than max_connections (%d)\n",+ write_stderr("%s: superuser_reserved_connections (%d) plus reserved_connections (%d) must be less than max_connections (%d)\n",
progname,
- SuperuserReservedConnections, MaxConnections);+ SuperuserReservedConnections, ReservedConnections,+ MaxConnections);
ExitPostmaster(1);
}
if (XLogArchiveMode > ARCHIVE_MODE_OFF && wal_level == WAL_LEVEL_MINIMAL)
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.cindex f8ac4edd6f..22b4278610 100644--- a/src/backend/storage/lmgr/proc.c+++ b/src/backend/storage/lmgr/proc.c@@ -645,27 +645,33 @@ GetStartupBufferPinWaitBufId(void)
}
/*
- * Check whether there are at least N free PGPROC objects.+ * Check whether there are at least N free PGPROC objects. If false is+ * returned, *nfree will be set to the number of free PGPROC objects.+ * Otherwise, *nfree will be set to n.
*
* Note: this is designed on the assumption that N will generally be small.
*/
bool
-HaveNFreeProcs(int n)+HaveNFreeProcs(int n, int *nfree)
{
dlist_iter iter;
+ Assert(n > 0);+ Assert(nfree);+
SpinLockAcquire(ProcStructLock);
+ *nfree = 0;
dlist_foreach(iter, &ProcGlobal->freeProcs)
{
- n--;- if (n == 0)+ (*nfree)++;+ if (*nfree == n)
break;
}
SpinLockRelease(ProcStructLock);
- return (n <= 0);+ return (*nfree == n);
}
/*
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.cindex 40f145e0ab..2f07ca7a0e 100644--- a/src/backend/utils/init/postinit.c+++ b/src/backend/utils/init/postinit.c@@ -719,6 +719,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
bool am_superuser;
char *fullpath;
char dbname[NAMEDATALEN];
+ int nfree = 0;
elog(DEBUG3, "InitPostgres");
@@ -922,16 +923,30 @@ InitPostgres(const char *in_dbname, Oid dboid,
}
/*
- * The last few connection slots are reserved for superusers. Replication- * connections are drawn from slots reserved with max_wal_senders and not- * limited by max_connections or superuser_reserved_connections.+ * The last few connection slots are reserved for superusers and roles with+ * privileges of pg_use_reserved_connections. Replication connections are+ * drawn from slots reserved with max_wal_senders and are not limited by+ * max_connections, superuser_reserved_connections, or+ * reserved_connections.+ *+ * Note: At this point, the new backend has already claimed a proc struct,+ * so we must check whether the number of free slots is strictly less than+ * the reserved connection limits.
*/
if (!am_superuser && !am_walsender &&
- SuperuserReservedConnections > 0 &&- !HaveNFreeProcs(SuperuserReservedConnections))- ereport(FATAL,- (errcode(ERRCODE_TOO_MANY_CONNECTIONS),- errmsg("remaining connection slots are reserved for superusers")));+ (SuperuserReservedConnections + ReservedConnections) > 0 &&+ !HaveNFreeProcs(SuperuserReservedConnections + ReservedConnections, &nfree))+ {+ if (nfree < SuperuserReservedConnections)+ ereport(FATAL,+ (errcode(ERRCODE_TOO_MANY_CONNECTIONS),+ errmsg("remaining connection slots are reserved for superusers")));++ if (!has_privs_of_role(GetUserId(), ROLE_PG_USE_RESERVED_CONNECTIONS))+ ereport(FATAL,+ (errcode(ERRCODE_TOO_MANY_CONNECTIONS),+ errmsg("remaining connection slots are reserved for roles with privileges of pg_use_reserved_connections")));+ }
/* Check replication permissions needed for walsender processes. */
if (am_walsender)
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.cindex 0fa9fdd3c5..e1753a40fa 100644--- a/src/backend/utils/misc/guc_tables.c+++ b/src/backend/utils/misc/guc_tables.c@@ -2168,6 +2168,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {+ {"reserved_connections", PGC_POSTMASTER, CONN_AUTH_SETTINGS,+ gettext_noop("Sets the number of connection slots reserved for roles "+ "with privileges of pg_use_reserved_connections."),+ NULL+ },+ &ReservedConnections,+ 0, 0, MAX_BACKENDS,+ NULL, NULL, NULL+ },+
{
{"min_dynamic_shared_memory", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Amount of dynamic shared memory reserved at startup."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sampleindex 4cceda4162..d06074b86f 100644--- a/src/backend/utils/misc/postgresql.conf.sample+++ b/src/backend/utils/misc/postgresql.conf.sample@@ -63,6 +63,7 @@
# (change requires restart)
#port = 5432 # (change requires restart)
#max_connections = 100 # (change requires restart)
+#reserved_connections = 0 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/tmp' # comma-separated list of directories
# (change requires restart)
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.datindex 2a2fee7d28..f2e5663c9f 100644--- a/src/include/catalog/pg_authid.dat+++ b/src/include/catalog/pg_authid.dat@@ -89,5 +89,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '4550', oid_symbol => 'ROLE_PG_USE_RESERVED_CONNECTIONS',+ rolname => 'pg_use_reserved_connections', rolsuper => 'f', rolinherit => 't',+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
diff --git a/src/include/postmaster/postmaster.h b/src/include/postmaster/postmaster.hindex 0e4b8ded34..3b3889c58c 100644--- a/src/include/postmaster/postmaster.h+++ b/src/include/postmaster/postmaster.h@@ -16,6 +16,7 @@
/* GUC options */
extern PGDLLIMPORT bool EnableSSL;
extern PGDLLIMPORT int SuperuserReservedConnections;
+extern PGDLLIMPORT int ReservedConnections;
extern PGDLLIMPORT int PostPortNumber;
extern PGDLLIMPORT int Unix_socket_permissions;
extern PGDLLIMPORT char *Unix_socket_group;
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.hindex dd45b8ee9b..4258cd92c9 100644--- a/src/include/storage/proc.h+++ b/src/include/storage/proc.h@@ -445,7 +445,7 @@ extern void InitAuxiliaryProcess(void);
extern void SetStartupBufferPinWaitBufId(int bufid);
extern int GetStartupBufferPinWaitBufId(void);
-extern bool HaveNFreeProcs(int n);+extern bool HaveNFreeProcs(int n, int *nfree);
extern void ProcReleaseLocks(bool isCommit);
extern ProcWaitStatus ProcSleep(LOCALLOCK *locallock, LockMethod lockMethodTable);
--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, robertmhaas@gmail.com
Subject: Re: almost-super-user problems that we haven't fixed yet
In-Reply-To: <20230119194601.GA4105788@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox