[patch] Add support for connection strings to createuser and dropuser
Started by Anders Åstrand8 days ago1 messages
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
Attachments:
0001-Add-support-for-connection-strings-in-user-CLI-tools.patchtext/x-patch; charset=UTF-8; name=0001-Add-support-for-connection-strings-in-user-CLI-tools.patchDownload
From 1cc657cbfef2bd8ac482e88fe99a45a00e7392be Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Anders=20=C3=85strand?= <anders.astrand@percona.com>
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