All of the other CLI tools seems to support connection strings via the
PGDATABASE environment variable and the --maintenance-db command line
option, but this functionality is missing from createuser and dropuser.
Attached is a patch to bring them up to par with the other CLI tools.
There is an argument to be made for renaming or aliasing the
--maintenance-db parameter as --connection-string or similar for all of
these tools of course, but all of the others (except createdb and dropdb
afaics) accepts a "raw" connection string on the command line so it
might not matter much for them.
--
Anders Åstrand
Percona
From 1cc657cbfef2bd8ac482e88fe99a45a00e7392be Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Anders=20=C3=85strand?= <[email protected]>
Date: Fri, 16 Jan 2026 14:53:17 +0100
Subject: [PATCH] Add support for connection strings in user CLI tools
The other CLI tools support specifying connection strings either via the
PGDATABASE environment variable or the --maintenance-db command line
parameter.
This functionality was missing from createuser and dropuser, so this
commit adds support for it using either method.
---
doc/src/sgml/ref/createuser.sgml | 15 ++++++++++++++
doc/src/sgml/ref/dropuser.sgml | 15 ++++++++++++++
src/bin/scripts/createuser.c | 11 +++++++++-
src/bin/scripts/dropuser.c | 11 +++++++++-
src/bin/scripts/t/040_createuser.pl | 28 ++++++++++++++++++++++++++
src/bin/scripts/t/070_dropuser.pl | 31 +++++++++++++++++++++++++++++
6 files changed, 109 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 0c061428514..bc2b460afa0 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -442,6 +442,21 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the name of the database to connect to when creating the
+ new user. If not specified, the <literal>postgres</literal>
+ database will be used; if that does not exist,
+ <literal>template1</literal> will be used.
+ This can be a <link linkend="libpq-connstring">connection
+ string</link>. If so, connection string parameters will override any
+ conflicting command line options.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index b6be26d5b0a..dcb425a97bc 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -201,6 +201,21 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the name of the database to connect to when removing the
+ user. If not specified, the <literal>postgres</literal> database will
+ be used; if that does not exist, <literal>template1</literal> will be
+ used.
+ This can be a <link linkend="libpq-connstring">connection
+ string</link>. If so, connection string parameters will override any
+ conflicting command line options.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/src/bin/scripts/createuser.c b/src/bin/scripts/createuser.c
index b3ab607afa7..070ef675454 100644
--- a/src/bin/scripts/createuser.c
+++ b/src/bin/scripts/createuser.c
@@ -57,6 +57,7 @@ main(int argc, char *argv[])
{"interactive", no_argument, NULL, 3},
{"bypassrls", no_argument, NULL, 4},
{"no-bypassrls", no_argument, NULL, 5},
+ {"maintenance-db", required_argument, NULL, 6},
{NULL, 0, NULL, 0}
};
@@ -67,6 +68,7 @@ main(int argc, char *argv[])
char *host = NULL;
char *port = NULL;
char *username = NULL;
+ const char *maintenance_db = NULL;
SimpleStringList roles = {NULL, NULL};
SimpleStringList members = {NULL, NULL};
SimpleStringList admins = {NULL, NULL};
@@ -190,6 +192,9 @@ main(int argc, char *argv[])
case 5:
bypassrls = TRI_NO;
break;
+ case 6:
+ maintenance_db = pg_strdup(optarg);
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -283,7 +288,10 @@ main(int argc, char *argv[])
if (login == TRI_DEFAULT)
login = TRI_YES;
- cparams.dbname = NULL; /* this program lacks any dbname option... */
+ if (maintenance_db == NULL)
+ maintenance_db = getenv("PGDATABASE");
+
+ cparams.dbname = maintenance_db;
cparams.pghost = host;
cparams.pgport = port;
cparams.pguser = username;
@@ -453,6 +461,7 @@ help(const char *progname)
printf(_(" -U, --username=USERNAME user name to connect as (not the one to create)\n"));
printf(_(" -w, --no-password never prompt for password\n"));
printf(_(" -W, --password force password prompt\n"));
+ printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
}
diff --git a/src/bin/scripts/dropuser.c b/src/bin/scripts/dropuser.c
index 50952b46c45..5630dbe6875 100644
--- a/src/bin/scripts/dropuser.c
+++ b/src/bin/scripts/dropuser.c
@@ -35,6 +35,7 @@ main(int argc, char *argv[])
{"echo", no_argument, NULL, 'e'},
{"interactive", no_argument, NULL, 'i'},
{"if-exists", no_argument, &if_exists, 1},
+ {"maintenance-db", required_argument, NULL, 2},
{NULL, 0, NULL, 0}
};
@@ -46,6 +47,7 @@ main(int argc, char *argv[])
char *host = NULL;
char *port = NULL;
char *username = NULL;
+ const char *maintenance_db = NULL;
enum trivalue prompt_password = TRI_DEFAULT;
ConnParams cparams;
bool echo = false;
@@ -90,6 +92,9 @@ main(int argc, char *argv[])
case 0:
/* this covers the long options */
break;
+ case 2:
+ maintenance_db = pg_strdup(optarg);
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -132,7 +137,10 @@ main(int argc, char *argv[])
exit(0);
}
- cparams.dbname = NULL; /* this program lacks any dbname option... */
+ if (maintenance_db == NULL)
+ maintenance_db = getenv("PGDATABASE");
+
+ cparams.dbname = maintenance_db;
cparams.pghost = host;
cparams.pgport = port;
cparams.pguser = username;
@@ -183,6 +191,7 @@ help(const char *progname)
printf(_(" -U, --username=USERNAME user name to connect as (not the one to drop)\n"));
printf(_(" -w, --no-password never prompt for password\n"));
printf(_(" -W, --password force password prompt\n"));
+ printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
}
diff --git a/src/bin/scripts/t/040_createuser.pl b/src/bin/scripts/t/040_createuser.pl
index 0fbb91ce330..a2bee7c623d 100644
--- a/src/bin/scripts/t/040_createuser.pl
+++ b/src/bin/scripts/t/040_createuser.pl
@@ -90,4 +90,32 @@ $node->command_fails(
],
'fails for too many non-options');
+# These tests needs to run last as we have to break the cluster somewhat to run them.
+
+$node->safe_psql('postgres', 'CREATE DATABASE maintenance');
+$node->safe_psql('maintenance', 'DROP DATABASE postgres');
+$node->safe_psql('maintenance',
+ "UPDATE pg_database SET datistemplate=false WHERE datname='template1'");
+$node->safe_psql('maintenance', 'DROP DATABASE template1');
+
+$node->command_fails(
+ [ 'createuser', 'regress_user13' ],
+ 'fails when default databases do not exist');
+
+$node->command_ok(
+ [
+ 'createuser',
+ '--maintenance-db' => 'maintenance',
+ 'regress_user14',
+ ],
+ 'succeeds when maintenance database name is specified');
+
+$node->command_ok(
+ [
+ 'createuser',
+ '--maintenance-db' => 'postgresql:///maintenance',
+ 'regress_user15',
+ ],
+ 'succeeds when connection string is specified');
+
done_testing();
diff --git a/src/bin/scripts/t/070_dropuser.pl b/src/bin/scripts/t/070_dropuser.pl
index 6b2e8711d7c..43e777a68a0 100644
--- a/src/bin/scripts/t/070_dropuser.pl
+++ b/src/bin/scripts/t/070_dropuser.pl
@@ -27,4 +27,35 @@ $node->command_fails_like(
qr/role "regress_nonexistent" does not exist/,
'fails with nonexistent user');
+# These tests needs to run last as we have to break the cluster somewhat to run them.
+
+$node->safe_psql('postgres', 'CREATE ROLE regress_foobar2');
+$node->safe_psql('postgres', 'CREATE ROLE regress_foobar3');
+$node->safe_psql('postgres', 'CREATE ROLE regress_foobar4');
+
+$node->safe_psql('postgres', 'CREATE DATABASE maintenance');
+$node->safe_psql('maintenance', 'DROP DATABASE postgres');
+$node->safe_psql('maintenance',
+ "UPDATE pg_database SET datistemplate=false WHERE datname='template1'");
+$node->safe_psql('maintenance', 'DROP DATABASE template1');
+
+$node->command_fails([ 'dropuser', 'regress_foobar2' ],
+ 'fails when default databases do not exist');
+
+$node->command_ok(
+ [
+ 'dropuser',
+ '--maintenance-db' => 'maintenance',
+ 'regress_foobar3',
+ ],
+ 'succeeds when maintenance database name is specified');
+
+$node->command_ok(
+ [
+ 'dropuser',
+ '--maintenance-db' => 'postgresql:///maintenance',
+ 'regress_foobar4',
+ ],
+ 'succeeds when connection string is specified');
+
done_testing();
--
2.43.0
