pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH] Add ALTER SYSTEM RELOAD
11+ messages / 9 participants
[nested] [flat]
* [PATCH] Add ALTER SYSTEM RELOAD
@ 2026-09-23 09:44 Yuhang Qiu <iamqyh@gmail.com>
2026-09-23 11:34 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Vik Fearing <vik@postgresfriends.org>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Yuhang Qiu @ 2026-09-23 09:44 UTC (permalink / raw)
To: pgsql-hackers
Hi hackers,
For configuration parameters that can be reloaded, administrators often need
to perform two steps in succession:
```sql
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();
```
I propose adding `ALTER SYSTEM RELOAD`, so the sequence can be written as:
```sql
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM RELOAD;
```
`ALTER SYSTEM` is already the SQL interface for changing server configuration,
and reloading is a common next step. Keeping both steps in the same command
family would make the workflow easier to discover, without requiring users to
find a separate function for the second step. I also think `ALTER SYSTEM` is a
more natural interface for administrative operations than function calls.
`pg_reload_conf()` would remain available.
The privilege difference is a possible concern: this patch requires a
superuser for `ALTER SYSTEM RELOAD`, while `pg_reload_conf()` can be granted to
other roles. I am unsure whether this divergence would be acceptable. The new
command cannot run inside a transaction block, but I consider this a benefit: a
reload request cannot be rolled back.
In my experience with `psql`, commands are also easier to complete with Tab;
I cannot complete the function call in the same way. Adding `RELOAD` means
that `RE` will match both `RELOAD` and `RESET`, so the `RE` prefix will no
longer uniquely complete either command. Even with that drawback, I find the
command form more convenient overall.
I think log rotation (`ALTER SYSTEM ROTATE LOG`) and standby promotion
(`ALTER SYSTEM PROMOTE`) are also promising directions for this command family.
This patch proposes only `RELOAD`. I would appreciate feedback on whether
`ALTER SYSTEM` is the right place for this operation.
Best regards,
Yuhang Qiu
Attachments:
0001-Add-ALTER-SYSTEM-RELOAD.patch (application/octet-stream, 14.0K) download
From 6bb2ff0d16ed183e4a56c41e43de00b1e2550ce8 Mon Sep 17 00:00:00 2001
From: Yuhang Qiu <iamqyh@gmail.com>
Date: Tue, 22 Sep 2026 14:55:29 +0000
Subject: [PATCH] Add ALTER SYSTEM RELOAD
ALTER SYSTEM changes server configuration, but requesting a reload
afterward requires a separate call to pg_reload_conf(). Add ALTER
SYSTEM RELOAD so both steps can use the ALTER SYSTEM command family.
The command form makes the workflow easier to discover and its
administrative purpose clearer. The existing pg_reload_conf()
function remains available.
---
doc/src/sgml/ref/alter_system.sgml | 39 ++++++---
src/backend/parser/gram.y | 13 ++-
src/backend/tcop/utility.c | 20 ++++-
src/backend/utils/misc/guc.c | 22 +++++
src/bin/psql/tab-complete.in.c | 4 +-
src/include/nodes/parsenodes.h | 8 ++
src/include/parser/kwlist.h | 1 +
src/include/utils/guc.h | 1 +
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/016_alter_system_reload.pl | 84 +++++++++++++++++++
10 files changed, 176 insertions(+), 17 deletions(-)
create mode 100644 src/test/modules/test_misc/t/016_alter_system_reload.pl
diff --git a/doc/src/sgml/ref/alter_system.sgml b/doc/src/sgml/ref/alter_system.sgml
index b28919d1b26..7ff4f88d04a 100644
--- a/doc/src/sgml/ref/alter_system.sgml
+++ b/doc/src/sgml/ref/alter_system.sgml
@@ -16,7 +16,7 @@ PostgreSQL documentation
<refnamediv>
<refname>ALTER SYSTEM</refname>
- <refpurpose>change a server configuration parameter</refpurpose>
+ <refpurpose>change or reload the server configuration</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -25,6 +25,7 @@ ALTER SYSTEM SET <replaceable class="parameter">configuration_parameter</replace
ALTER SYSTEM RESET <replaceable class="parameter">configuration_parameter</replaceable>
ALTER SYSTEM RESET ALL
+ALTER SYSTEM RELOAD
</synopsis>
</refsynopsisdiv>
@@ -32,11 +33,11 @@ ALTER SYSTEM RESET ALL
<title>Description</title>
<para>
- <command>ALTER SYSTEM</command> is used for changing server configuration
- parameters across the entire database cluster. It can be more convenient
- than the traditional method of manually editing
- the <filename>postgresql.conf</filename> file.
- <command>ALTER SYSTEM</command> writes the given parameter setting to
+ The <command>SET</command> and <command>RESET</command> variants of
+ <command>ALTER SYSTEM</command> change server configuration parameters
+ across the entire database cluster. They can be more convenient than
+ manually editing the <filename>postgresql.conf</filename> file.
+ <command>SET</command> writes the given parameter setting to
the <filename>postgresql.auto.conf</filename> file, which is read in
addition to <filename>postgresql.conf</filename>.
Setting a parameter to <literal>DEFAULT</literal>, or using the
@@ -45,20 +46,27 @@ ALTER SYSTEM RESET ALL
ALL</literal> to remove all such configuration entries.
</para>
+ <para>
+ The <command>RELOAD</command> variant requests a reload of the server
+ configuration files.
+ </para>
+
<para>
Values set with <command>ALTER SYSTEM</command> will be effective after
the next server configuration reload, or after the next server restart
in the case of parameters that can only be changed at server start.
- A server configuration reload can be commanded by calling the SQL
- function <function>pg_reload_conf()</function>, running <literal>pg_ctl reload</literal>,
- or sending a <systemitem>SIGHUP</systemitem> signal to the main server process.
+ A server configuration reload can also be requested by calling the SQL
+ function <function>pg_reload_conf()</function> or running
+ <literal>pg_ctl reload</literal>.
</para>
<para>
Only superusers and users granted <literal>ALTER SYSTEM</literal> privilege
- on a parameter can change it using <command>ALTER SYSTEM</command>. Also, since
- this command acts directly on the file system and cannot be rolled back,
- it is not allowed inside a transaction block or function.
+ on a parameter can change it using <command>ALTER SYSTEM</command>.
+ <command>ALTER SYSTEM RELOAD</command> can only be executed by a
+ superuser. The <command>SET</command> and <command>RESET</command> variants
+ act directly on the file system and cannot be rolled back. All variants
+ are disallowed inside a transaction block or function.
</para>
</refsect1>
@@ -141,6 +149,13 @@ ALTER SYSTEM RESET wal_level;
</programlisting>
</para>
+ <para>
+ Request a reload of the server configuration files:
+<programlisting>
+ALTER SYSTEM RELOAD;
+</programlisting>
+ </para>
+
<para>
Set the list of preloaded extension modules to be empty:
<programlisting>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0563453fe24..f271a77f34e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -783,7 +783,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
QUOTE QUOTES
RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
- REFRESH REINDEX RELATIVE_P RELEASE RENAME REPACK REPEATABLE REPLACE REPLICA
+ REFRESH REINDEX RELATIVE_P RELEASE RELOAD RENAME REPACK REPEATABLE REPLACE REPLICA
RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
@@ -11846,6 +11846,7 @@ AlterSystemStmt:
{
AlterSystemStmt *n = makeNode(AlterSystemStmt);
+ n->action = ALTER_SYSTEM_SET;
n->setstmt = $4;
$$ = (Node *) n;
}
@@ -11853,9 +11854,17 @@ AlterSystemStmt:
{
AlterSystemStmt *n = makeNode(AlterSystemStmt);
+ n->action = ALTER_SYSTEM_RESET;
n->setstmt = $4;
$$ = (Node *) n;
}
+ | ALTER SYSTEM_P RELOAD
+ {
+ AlterSystemStmt *n = makeNode(AlterSystemStmt);
+
+ n->action = ALTER_SYSTEM_RELOAD;
+ $$ = (Node *) n;
+ }
;
@@ -18301,6 +18310,7 @@ unreserved_keyword:
| REINDEX
| RELATIVE_P
| RELEASE
+ | RELOAD
| RENAME
| REPACK
| REPEATABLE
@@ -18937,6 +18947,7 @@ bare_label_keyword:
| REINDEX
| RELATIVE_P
| RELEASE
+ | RELOAD
| RENAME
| REPACK
| REPEATABLE
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1512d2df196..d10655638ff 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -869,8 +869,24 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_AlterSystemStmt:
- PreventInTransactionBlock(isTopLevel, "ALTER SYSTEM");
- AlterSystemSetConfigFile((AlterSystemStmt *) parsetree);
+ {
+ AlterSystemStmt *stmt = (AlterSystemStmt *) parsetree;
+
+ PreventInTransactionBlock(isTopLevel, "ALTER SYSTEM");
+ switch (stmt->action)
+ {
+ case ALTER_SYSTEM_SET:
+ case ALTER_SYSTEM_RESET:
+ AlterSystemSetConfigFile(stmt);
+ break;
+ case ALTER_SYSTEM_RELOAD:
+ AlterSystemReloadConfig();
+ break;
+ default:
+ elog(ERROR, "unrecognized ALTER SYSTEM action: %d",
+ (int) stmt->action);
+ }
+ }
break;
case T_VariableSetStmt:
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 21c9ccec1e2..4a3bdeeafe5 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -26,6 +26,7 @@
#include <limits.h>
#include <math.h>
+#include <signal.h>
#include <sys/stat.h>
#include <unistd.h>
@@ -4785,6 +4786,27 @@ AlterSystemSetConfigFile(AlterSystemStmt *altersysstmt)
LWLockRelease(AutoFileLock);
}
+/*
+ * Execute ALTER SYSTEM RELOAD.
+ */
+void
+AlterSystemReloadConfig(void)
+{
+ if (!AllowAlterSystem)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER SYSTEM is not allowed in this environment")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to perform ALTER SYSTEM RELOAD")));
+
+ if (kill(PostmasterPid, SIGHUP))
+ ereport(ERROR,
+ (errmsg("failed to send signal to postmaster: %m")));
+}
+
/*
* Common code for DefineCustomXXXVariable subroutines: allocate the
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index b3bfe050b18..a93fed24511 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2658,9 +2658,9 @@ match_previous_words(int pattern_id,
/* ALTER SERVER <name> VERSION <version> */
else if (Matches("ALTER", "SERVER", MatchAny, "VERSION", MatchAny))
COMPLETE_WITH("OPTIONS");
- /* ALTER SYSTEM SET, RESET, RESET ALL */
+ /* ALTER SYSTEM SET, RESET, RESET ALL, RELOAD */
else if (Matches("ALTER", "SYSTEM"))
- COMPLETE_WITH("SET", "RESET");
+ COMPLETE_WITH("SET", "RESET", "RELOAD");
else if (Matches("ALTER", "SYSTEM", "SET|RESET"))
COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_alter_system_set_vars,
"ALL");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0debcd193ab..120073f448f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3987,9 +3987,17 @@ typedef struct DropdbStmt
* Alter System Statement
* ----------------------
*/
+typedef enum AlterSystemAction
+{
+ ALTER_SYSTEM_SET,
+ ALTER_SYSTEM_RESET,
+ ALTER_SYSTEM_RELOAD
+} AlterSystemAction;
+
typedef struct AlterSystemStmt
{
NodeTag type;
+ AlterSystemAction action;
VariableSetStmt *setstmt; /* SET subcommand */
} AlterSystemStmt;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 53ae96c0399..a5d6dd17565 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -375,6 +375,7 @@ PG_KEYWORD("refresh", REFRESH, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("reload", RELOAD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("repack", REPACK, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 164efba6b51..95c265ae2e6 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -461,6 +461,7 @@ extern int set_config_with_handle(const char *name, config_handle *handle,
int elevel, bool is_reload);
extern config_handle *get_config_handle(const char *name);
extern void AlterSystemSetConfigFile(AlterSystemStmt *altersysstmt);
+extern void AlterSystemReloadConfig(void);
extern char *GetConfigOptionByName(const char *name, const char **varname,
bool missing_ok);
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 5d81f5b13be..3eeff8c3f54 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -24,6 +24,7 @@ tests += {
't/013_temp_obj_multisession.pl',
't/014_log_statement_max_length.pl',
't/015_temp_schema_exit_deferrable.pl',
+ 't/016_alter_system_reload.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/016_alter_system_reload.pl b/src/test/modules/test_misc/t/016_alter_system_reload.pl
new file mode 100644
index 00000000000..5b7049a1c91
--- /dev/null
+++ b/src/test/modules/test_misc/t/016_alter_system_reload.pl
@@ -0,0 +1,84 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+$node->safe_psql(
+ 'postgres',
+ q{
+ALTER SYSTEM SET log_connections = 'all';
+ALTER SYSTEM RELOAD;
+});
+ok( $node->poll_query_until(
+ 'postgres', q{SELECT current_setting('log_connections') = 'all'}),
+ 'ALTER SYSTEM RELOAD applies configuration changes');
+
+$node->safe_psql('postgres', 'CREATE ROLE regress_alter_system_reload');
+$node->safe_psql(
+ 'postgres',
+ 'GRANT ALTER SYSTEM ON PARAMETER log_connections TO regress_alter_system_reload');
+my ($ret, $stdout, $stderr) = $node->psql(
+ 'postgres',
+ q{
+SET ROLE regress_alter_system_reload;
+ALTER SYSTEM SET log_connections = 'all';
+});
+is($ret, 0, 'parameter privilege allows ALTER SYSTEM SET');
+
+$node->safe_psql(
+ 'postgres',
+ 'GRANT EXECUTE ON FUNCTION pg_reload_conf() TO regress_alter_system_reload');
+($ret, $stdout, $stderr) = $node->psql(
+ 'postgres',
+ q{
+SET ROLE regress_alter_system_reload;
+ALTER SYSTEM RELOAD;
+});
+isnt($ret, 0, 'non-superuser cannot run ALTER SYSTEM RELOAD');
+like(
+ $stderr,
+ qr/permission denied to perform ALTER SYSTEM RELOAD/,
+ 'permission error is reported');
+
+($ret, $stdout, $stderr) =
+ $node->psql('postgres', 'BEGIN; ALTER SYSTEM RELOAD;');
+isnt($ret, 0, 'ALTER SYSTEM RELOAD is rejected in a transaction block');
+like(
+ $stderr,
+ qr/ALTER SYSTEM cannot run inside a transaction block/,
+ 'transaction block error is reported');
+
+$node->append_conf('postgresql.conf', 'allow_alter_system = off');
+$node->safe_psql('postgres', 'SELECT pg_reload_conf()');
+ok( $node->poll_query_until(
+ 'postgres', q{SELECT current_setting('allow_alter_system') = 'off'}),
+ 'allow_alter_system is disabled');
+($ret, $stdout, $stderr) =
+ $node->psql('postgres', 'ALTER SYSTEM RELOAD;');
+isnt($ret, 0, 'ALTER SYSTEM RELOAD obeys allow_alter_system');
+like(
+ $stderr,
+ qr/ALTER SYSTEM is not allowed in this environment/,
+ 'allow_alter_system error is reported');
+
+# Restore the GUC without relying on the command that it disables.
+$node->append_conf('postgresql.conf', 'allow_alter_system = on');
+$node->safe_psql('postgres', 'SELECT pg_reload_conf()');
+ok( $node->poll_query_until(
+ 'postgres', q{SELECT current_setting('allow_alter_system') = 'on'}),
+ 'allow_alter_system is restored');
+$node->safe_psql(
+ 'postgres',
+ q{
+ALTER SYSTEM RESET log_connections;
+ALTER SYSTEM RELOAD;
+});
+
+done_testing();
--
2.43.7
=
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
@ 2026-09-23 11:34 ` Vik Fearing <vik@postgresfriends.org>
1 sibling, 0 replies; 11+ messages in thread
From: Vik Fearing @ 2026-09-23 11:34 UTC (permalink / raw)
To: Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
On 23/09/2026 11:44, Yuhang Qiu wrote:
> Hi hackers,
>
> For configuration parameters that can be reloaded, administrators often need
> to perform two steps in succession:
> ```sql
> ALTER SYSTEM SET work_mem = '64MB';
> SELECT pg_reload_conf();
> ```
>
> I propose adding `ALTER SYSTEM RELOAD`, so the sequence can be written as:
> ```sql
> ALTER SYSTEM SET work_mem = '64MB';
> ALTER SYSTEM RELOAD;
> ```
I think this is a good idea.
> I think log rotation (`ALTER SYSTEM ROTATE LOG`) and standby promotion
> (`ALTER SYSTEM PROMOTE`) are also promising directions for this command family.
> This patch proposes only `RELOAD`. I would appreciate feedback on whether
> `ALTER SYSTEM` is the right place for this operation.
I like those ideas as well.
--
Vik Fearing
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
@ 2026-09-24 07:39 ` Chao Li <li.evan.chao@gmail.com>
2026-09-24 09:35 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Michael Banck <mbanck@gmx.net>
2026-09-24 11:38 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
2026-09-24 15:34 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-24 19:29 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 4 replies; 11+ messages in thread
From: Chao Li @ 2026-09-24 07:39 UTC (permalink / raw)
To: Yuhang Qiu <iamqyh@gmail.com>; +Cc: pgsql-hackers
> On Sep 23, 2026, at 17:44, Yuhang Qiu <iamqyh@gmail.com> wrote:
>
> Hi hackers,
>
> For configuration parameters that can be reloaded, administrators often need
> to perform two steps in succession:
> ```sql
> ALTER SYSTEM SET work_mem = '64MB';
> SELECT pg_reload_conf();
> ```
>
> I propose adding `ALTER SYSTEM RELOAD`, so the sequence can be written as:
> ```sql
> ALTER SYSTEM SET work_mem = '64MB';
> ALTER SYSTEM RELOAD;
> ```
>
> `ALTER SYSTEM` is already the SQL interface for changing server configuration,
> and reloading is a common next step. Keeping both steps in the same command
> family would make the workflow easier to discover, without requiring users to
> find a separate function for the second step. I also think `ALTER SYSTEM` is a
> more natural interface for administrative operations than function calls.
> `pg_reload_conf()` would remain available.
>
> The privilege difference is a possible concern: this patch requires a
> superuser for `ALTER SYSTEM RELOAD`, while `pg_reload_conf()` can be granted to
> other roles. I am unsure whether this divergence would be acceptable. The new
> command cannot run inside a transaction block, but I consider this a benefit: a
> reload request cannot be rolled back.
>
> In my experience with `psql`, commands are also easier to complete with Tab;
> I cannot complete the function call in the same way. Adding `RELOAD` means
> that `RE` will match both `RELOAD` and `RESET`, so the `RE` prefix will no
> longer uniquely complete either command. Even with that drawback, I find the
> command form more convenient overall.
>
> I think log rotation (`ALTER SYSTEM ROTATE LOG`) and standby promotion
> (`ALTER SYSTEM PROMOTE`) are also promising directions for this command family.
> This patch proposes only `RELOAD`. I would appreciate feedback on whether
> `ALTER SYSTEM` is the right place for this operation.
>
> Best regards,
> Yuhang Qiu
>
> <0001-Add-ALTER-SYSTEM-RELOAD.patch>
Thanks for the patch. After reading it, I have a concern. pg_reload_conf() relies on normal function privileges, so a super user can grant EXECUTE on it to a non-superuser. With this patch, the two interfaces for doing essentially the same thing would have different privilege models:
```
GRANT EXECUTE ON FUNCTION pg_reload_conf() TO some-user;
-- allowed
SELECT pg_reload_conf();
-- not allowed
ALTER SYSTEM RELOAD;
```
Is there a reason ALTER SYSTEM RELOAD needs stronger privileges than pg_reload_conf()?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
@ 2026-09-24 09:35 ` Michael Banck <mbanck@gmx.net>
2026-09-24 11:28 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
3 siblings, 1 reply; 11+ messages in thread
From: Michael Banck @ 2026-09-24 09:35 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
Hi,
On Thu, Sep 24, 2026 at 03:39:22PM +0800, Chao Li wrote:
> Is there a reason ALTER SYSTEM RELOAD needs stronger privileges than
> pg_reload_conf()?
Well, it is clear that you won't be able to GRANT this on a SQL-level.
But what could be done is add the pg_maintain (or something similar)
default role as an allowed user for running this command besides
Superuser.
Michael
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
2026-09-24 09:35 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Michael Banck <mbanck@gmx.net>
@ 2026-09-24 11:28 ` Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
0 siblings, 0 replies; 11+ messages in thread
From: Dagfinn Ilmari Mannsåker @ 2026-09-24 11:28 UTC (permalink / raw)
To: Michael Banck <mbanck@gmx.net>; +Cc: Chao Li <li.evan.chao@gmail.com>; Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
Michael Banck <mbanck@gmx.net> writes:
> Hi,
>
> On Thu, Sep 24, 2026 at 03:39:22PM +0800, Chao Li wrote:
>> Is there a reason ALTER SYSTEM RELOAD needs stronger privileges than
>> pg_reload_conf()?
>
> Well, it is clear that you won't be able to GRANT this on a SQL-level.
>
> But what could be done is add the pg_maintain (or something similar)
> default role as an allowed user for running this command besides
> Superuser.
There's already GRANT ALTER SYSTEM ON PARAMETER, it would be natural to
extend that with GRANT ALTER SYSTEM RELOAD and whatver other operations
we decide to add.
> Michael
- ilmari
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
@ 2026-09-24 11:38 ` Chao Li <li.evan.chao@gmail.com>
2026-09-24 11:57 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Daniel Gustafsson <daniel@yesql.se>
3 siblings, 1 reply; 11+ messages in thread
From: Chao Li @ 2026-09-24 11:38 UTC (permalink / raw)
To: Michael Banck <mbanck@gmx.net>; +Cc: Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
> On Sep 24, 2026, at 17:35, Michael Banck <mbanck@gmx.net> wrote:
>
> Hi,
>
> On Thu, Sep 24, 2026 at 03:39:22PM +0800, Chao Li wrote:
>> Is there a reason ALTER SYSTEM RELOAD needs stronger privileges than
>> pg_reload_conf()?
>
> Well, it is clear that you won't be able to GRANT this on a SQL-level.
>
> But what could be done is add the pg_maintain (or something similar)
> default role as an allowed user for running this command besides
> Superuser.
>
>
> Michael
Yeah, that could be a solution. But the current patch only allows super user:
```
+/*
+ * Execute ALTER SYSTEM RELOAD.
+ */
+void
+AlterSystemReloadConfig(void)
+{
+ if (!AllowAlterSystem)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER SYSTEM is not allowed in this environment")));
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to perform ALTER SYSTEM RELOAD")));
+
+ if (kill(PostmasterPid, SIGHUP))
+ ereport(ERROR,
+ (errmsg("failed to send signal to postmaster: %m")));
+}
```
That’s what I got the concern.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
2026-09-24 11:38 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
@ 2026-09-24 11:57 ` Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 11+ messages in thread
From: Daniel Gustafsson @ 2026-09-24 11:57 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Michael Banck <mbanck@gmx.net>; Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
> On 24 Sep 2026, at 13:38, Chao Li <li.evan.chao@gmail.com> wrote:
> Yeah, that could be a solution. But the current patch only allows super user:
> + if (!superuser())
> + ereport(ERROR,
> + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> + errmsg("permission denied to perform ALTER SYSTEM RELOAD")));
I don't have a strong opinion either way, but if this check remains the error
message we commonly use is "must be superuser to ..."
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
@ 2026-09-24 15:34 ` Dmitry Dolgov <9erthalion6@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Dmitry Dolgov @ 2026-09-24 15:34 UTC (permalink / raw)
To: Michael Banck <mbanck@gmx.net>; +Cc: Chao Li <li.evan.chao@gmail.com>; Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
> On Thu, Sep 24, 2026 at 11:35:23AM +0200, Michael Banck wrote:
>
> But what could be done is add the pg_maintain (or something similar)
> default role as an allowed user for running this command besides
> Superuser.
+1, pg_maintain seems reasonable here.
Regarding the patch itself:
+typedef enum AlterSystemAction
+{
+ ALTER_SYSTEM_SET,
+ ALTER_SYSTEM_RESET,
+ ALTER_SYSTEM_RELOAD
+} AlterSystemAction;
What's the reason for separating SET and RESET once more? They're already
differentiated via setstmt->kind, aren't they?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
@ 2026-09-24 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-28 23:03 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Andreas Karlsson <andreas@proxel.se>
2026-09-29 07:46 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
3 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2026-09-24 19:29 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
Chao Li <li.evan.chao@gmail.com> writes:
>> On Sep 23, 2026, at 17:44, Yuhang Qiu <iamqyh@gmail.com> wrote:
>> For configuration parameters that can be reloaded, administrators often need
>> to perform two steps in succession:
>> ```sql
>> ALTER SYSTEM SET work_mem = '64MB';
>> SELECT pg_reload_conf();
>> ```
>>
>> I propose adding `ALTER SYSTEM RELOAD`, so the sequence can be written as:
>> ```sql
>> ALTER SYSTEM SET work_mem = '64MB';
>> ALTER SYSTEM RELOAD;
>> ```
> Thanks for the patch. After reading it, I have a concern. pg_reload_conf() relies on normal function privileges, so a super user can grant EXECUTE on it to a non-superuser. With this patch, the two interfaces for doing essentially the same thing would have different privilege models:
I'm pretty down on this proposal even without the privilege question.
"There's more than one way to do it" isn't a great thing for
security-relevant operations, and this surely is one.
Also, I don't like the loss of an explainable scope for what
ALTER SYSTEM does. I see the patch changes the command's summary
like so:
<refname>ALTER SYSTEM</refname>
- <refpurpose>change a server configuration parameter</refpurpose>
+ <refpurpose>change or reload the server configuration</refpurpose>
which is entirely not self-consistent. (For one thing, it implies
that you can change any part of the server's configuration through
this command, which is not so: stuff like pg_hba.conf remains out
of scope.)
This lack of intellectual consistency would get ten times worse
if we followed through on the idea of overloading ALTER SYSTEM
with unrelated actions like log rotation and promotion.
The reason ALTER SYSTEM exists, IMO, is to re-use the infrastructure
we had for SET and RESET commands to allow alteration of GUC values
system-wide as well as locally in a session. It does not exist for
discoverability.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
2026-09-24 19:29 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-09-28 23:03 ` Andreas Karlsson <andreas@proxel.se>
1 sibling, 0 replies; 11+ messages in thread
From: Andreas Karlsson @ 2026-09-28 23:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Chao Li <li.evan.chao@gmail.com>; +Cc: Yuhang Qiu <iamqyh@gmail.com>; pgsql-hackers
On 9/24/26 9:29 PM, Tom Lane wrote:
> Chao Li <li.evan.chao@gmail.com> writes:
>>> On Sep 23, 2026, at 17:44, Yuhang Qiu <iamqyh@gmail.com> wrote:
>>> For configuration parameters that can be reloaded, administrators often need
>>> to perform two steps in succession:
>>> ```sql
>>> ALTER SYSTEM SET work_mem = '64MB';
>>> SELECT pg_reload_conf();
>>> ```
>>>
>>> I propose adding `ALTER SYSTEM RELOAD`, so the sequence can be written as:
>>> ```sql
>>> ALTER SYSTEM SET work_mem = '64MB';
>>> ALTER SYSTEM RELOAD;
>>> ```
>
>> Thanks for the patch. After reading it, I have a concern. pg_reload_conf() relies on normal function privileges, so a super user can grant EXECUTE on it to a non-superuser. With this patch, the two interfaces for doing essentially the same thing would have different privilege models:
>
> I'm pretty down on this proposal even without the privilege question.
> "There's more than one way to do it" isn't a great thing for
> security-relevant operations, and this surely is one.
+1
I do not see how this adds anything worth the increased maintenance
burden. How to reload the config is clearly discoverable in the docs for
ALTER SYSTEM.
Andreas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Add ALTER SYSTEM RELOAD
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-24 07:39 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Chao Li <li.evan.chao@gmail.com>
2026-09-24 19:29 ` Re: [PATCH] Add ALTER SYSTEM RELOAD Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-09-29 07:46 ` Yuhang Qiu <iamqyh@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Yuhang Qiu @ 2026-09-29 07:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chao Li <li.evan.chao@gmail.com>; pgsql-hackers
Hi all,
Sorry for the late reply.
The v1 patch was mainly intended to discuss whether `RELOAD` belongs
under `ALTER SYSTEM`, so I did not cover the privilege design in detail.
I had in mind a separate ACL for `ALTER SYSTEM` operations, allowing
privileges to be granted per operation. If we add more operations,
predefined roles may be too broad to allow fine-grained control.
> I'm pretty down on this proposal even without the privilege question.
> "There's more than one way to do it" isn't a great thing for
> security-relevant operations, and this surely is one.
>
> Also, I don't like the loss of an explainable scope for what
> ALTER SYSTEM does.
I agree with Tom's concerns about adding a second interface and blurring
the scope of the command. I also agree with Andreas about the maintenance
cost. `pg_reload_conf()` already provides the reload operation, and the
`ALTER SYSTEM` documentation explains how to request a reload.
> This lack of intellectual consistency would get ten times worse
> if we followed through on the idea of overloading ALTER SYSTEM
> with unrelated actions like log rotation and promotion.
Agreed. Log rotation and promotion were simply the first operations that
came to mind as possible fits for a broader command family. Adding just
those two would not give ALTER SYSTEM a coherent scope. Oracle has a much
broader ALTER SYSTEM command family, though. If PostgreSQL developed a
similarly broad family of instance-level operations, could that give the
command a clearer scope? But still, it would increase the overlap with
existing functions that concerns Tom, as well as the maintenance cost
Andreas noted.
Thanks everyone for the reviews.
Best regards,
Yuhang Qiu
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-09-29 07:46 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-23 09:44 [PATCH] Add ALTER SYSTEM RELOAD Yuhang Qiu <iamqyh@gmail.com>
2026-09-23 11:34 ` Vik Fearing <vik@postgresfriends.org>
2026-09-24 07:39 ` Chao Li <li.evan.chao@gmail.com>
2026-09-24 09:35 ` Michael Banck <mbanck@gmx.net>
2026-09-24 11:28 ` Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
2026-09-24 11:38 ` Chao Li <li.evan.chao@gmail.com>
2026-09-24 11:57 ` Daniel Gustafsson <daniel@yesql.se>
2026-09-24 15:34 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-24 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-28 23:03 ` Andreas Karlsson <andreas@proxel.se>
2026-09-29 07:46 ` Yuhang Qiu <iamqyh@gmail.com>
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