[PATCH] psql: Allow connection string parameters as positional arguments
Hi hackers,
I'd like to propose a small enhancement to psql that improves usability
when working with connection services and other libpq connection parameters.
Problem
Currently, when users try to use connection string parameters like
service=myservice as positional arguments to psql, the behavior is
confusing. For example:
psql service=production dbname=postgres -c "SELECT current_database()"
This fails with:
Password for user dbname=postgres:
psql: error: password authentication failed for user "dbname=postgres"
The issue is that psql interprets service=centraldb as the database name
(first positional arg) and dbname=postgres as the username (second
positional arg), treating them as literal strings rather than connection
parameters.
Use Case
This is particularly inconvenient when working with pg_service.conf, where
users define connection profiles but occasionally need to override specific
parameters. Currently, the workaround requires quoting the entire
connection string:
# Current workaround (verbose)
psql -d "service=centraldb dbname=postgres" -c "SELECT current_database()"
# Desired syntax (intuitive)
psql service=centraldb -d postgres -c "SELECT current_database()"
The proposed syntax is more intuitive and aligns with how other tools
handle similar parameter overrides.
Solution
This patch modifies psql to recognize positional arguments containing = as
connection string parameters. These are collected into a connection string
that serves as the base for the connection. Explicit options (-d, -h, -p,
-U) override corresponding values.
The implementation:
- Adds a connstring field to struct adhoc_opts
- Modifies parse_psql_options() to detect and collect key=value arguments
- Updates the connection logic to merge connstring with explicit options
Backward Compatibility
This change is fully backward compatible:
- Regular positional arguments (without =) continue to work as database
name and username
- Database names containing = are rare and can still be specified via -d
"db=name"
- All existing command-line patterns remain functional
Examples
# Use service with database override
psql service=production -d postgres
# Multiple connection parameters
psql host=localhost port=5433 sslmode=require -d mydb
# Mix key=value with regular positional arg
psql host=localhost port=5433 mydb
Testing
The patch includes a new TAP test file (t/002_connstring.pl) with 17 test
cases covering:
- Basic key=value positional arguments
- Override behavior with -d flag
- Multiple key=value parameters
- Mixed key=value and regular positional arguments
- Parameter ordering variations
All existing psql tests continue to pass.
Patch attached.
Feedback welcome!
---
Best regards, Daymel Bonne
Attachments:
v1-0001-Allow-connection-string-parameters-as-positional-arguments.patchapplication/octet-stream; name=v1-0001-Allow-connection-string-parameters-as-positional-arguments.patchDownload
From 33a424faa63adb0e6112064532d808eb33e04458 Mon Sep 17 00:00:00 2001
From: Daymel Bonne <7658145+dbonne@users.noreply.github.com>
Date: Thu, 18 Dec 2025 15:20:09 -0500
Subject: [PATCH v1] Allow connection string parameters as positional arguments
Previously, psql only accepted a database name and username as positional
arguments. Any argument containing '=' was interpreted literally as the
database name, which was confusing when users tried to use connection
string parameters like "service=myservice" as positional arguments.
This commit modifies psql to recognize positional arguments containing '='
as connection string parameters (e.g., "host=localhost", "service=mydb",
"port=5433"). These parameters are collected into a connection string
that serves as the base for the connection. Explicit options like -d,
-h, -p, and -U override the corresponding values in the connection string.
This enables convenient usage patterns like:
psql service=myservice -d postgres -c "SELECT version()"
which connects using the service definition but overrides the database
name with 'postgres'.
The implementation:
- Adds a 'connstring' field to store key=value positional arguments
- Modifies parse_psql_options() to detect and collect key=value arguments
- Updates the connection logic to merge connstring with explicit options
- Maintains full backward compatibility with existing positional args
Regular positional arguments (without '=') continue to work as before,
being interpreted as database name and username respectively.
---
doc/src/sgml/ref/psql-ref.sgml | 31 +++++++++++++++-
src/bin/psql/meson.build | 1 +
src/bin/psql/startup.c | 66 ++++++++++++++++++++++++++++++++--
3 files changed, 95 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f56c70263e0..41248e017d4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -25,6 +25,7 @@ PostgreSQL documentation
<cmdsynopsis>
<command>psql</command>
<arg rep="repeat"><replaceable class="parameter">option</replaceable></arg>
+ <arg rep="repeat"><replaceable class="parameter">connparam</replaceable></arg>
<arg choice="opt"><replaceable class="parameter">dbname</replaceable>
<arg choice="opt"><replaceable class="parameter">username</replaceable></arg></arg>
</cmdsynopsis>
@@ -164,7 +165,10 @@ EOF
argument on the command line. The <replaceable>dbname</replaceable>
can be a <link linkend="libpq-connstring">connection string</link>.
If so, connection string parameters will override any conflicting
- command line options.
+ command line options. This option also overrides any
+ <literal>dbname</literal> specified in connection parameters passed
+ as positional arguments (e.g., <literal>psql service=myservice -d postgres</literal>
+ uses <literal>postgres</literal> as the database name).
</para>
</listitem>
</varlistentry>
@@ -667,6 +671,31 @@ EOF
administrator should have informed you about your access rights.
</para>
+ <para>
+ Additionally, any non-option argument containing an equals sign
+ (<literal>=</literal>) is treated as a connection string parameter
+ rather than a database name or user name. This allows specifying
+ connection parameters such as <literal>service</literal>,
+ <literal>host</literal>, or <literal>sslmode</literal> directly on the
+ command line. For example:
+<programlisting>
+$ <userinput>psql service=myservice</userinput>
+$ <userinput>psql host=localhost port=5433 sslmode=require mydb</userinput>
+</programlisting>
+ Multiple connection parameters can be specified this way, and they are
+ combined into a connection string. Explicit options such as
+ <option>-d</option>, <option>-h</option>, <option>-p</option>, and
+ <option>-U</option> override any corresponding values specified as
+ connection parameters. This is particularly useful when using
+ connection services defined in <filename>pg_service.conf</filename>,
+ allowing you to use a service but override specific parameters:
+<programlisting>
+$ <userinput>psql service=myservice -d postgres</userinput>
+</programlisting>
+ This connects using the service definition but overrides the database
+ name with <literal>postgres</literal>.
+ </para>
+
<para>
When the defaults aren't quite right, you can save yourself
some typing by setting the environment variables
diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index d344053c23b..eb11c4e0c07 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -75,6 +75,7 @@ tests += {
'env': {'with_readline': readline.found() ? 'yes' : 'no'},
'tests': [
't/001_basic.pl',
+ 't/002_connstring.pl',
't/010_tab_completion.pl',
't/020_cancel.pl',
't/030_pager.pl',
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 249b6aa5169..d699e5799d2 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -70,6 +70,7 @@ struct adhoc_opts
char *port;
char *username;
char *logfilename;
+ char *connstring; /* connection string from positional args */
bool no_readline;
bool no_psqlrc;
bool single_txn;
@@ -248,6 +249,48 @@ main(int argc, char *argv[])
password = simple_prompt("Password: ", false);
}
+ /*
+ * Build the effective dbname for connection. If we have a connection
+ * string from positional args (e.g., "service=myservice"), use it as
+ * the base. Explicit -d/--dbname overrides the dbname within it.
+ */
+ {
+ char *effective_dbname = NULL;
+
+ if (options.connstring != NULL)
+ {
+ if (options.dbname != NULL)
+ {
+ /* Combine connstring with explicit dbname override */
+ effective_dbname = psprintf("%s dbname=%s",
+ options.connstring, options.dbname);
+ }
+ else if (options.list_dbs)
+ {
+ /* For -l, default to postgres database */
+ effective_dbname = psprintf("%s dbname=postgres",
+ options.connstring);
+ }
+ else
+ {
+ /* Just use the connection string as-is */
+ effective_dbname = pg_strdup(options.connstring);
+ }
+ }
+ else
+ {
+ /* No connection string, use explicit dbname or default */
+ if (options.list_dbs && options.dbname == NULL)
+ effective_dbname = pg_strdup("postgres");
+ else if (options.dbname != NULL)
+ effective_dbname = pg_strdup(options.dbname);
+ /* else effective_dbname stays NULL */
+ }
+
+ /* Store for use in connection loop */
+ options.dbname = effective_dbname;
+ }
+
/* loop until we have a password if requested by backend */
do
{
@@ -728,11 +771,30 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts *options)
}
/*
- * if we still have arguments, use it as the database name and username
+ * if we still have arguments, use it as the database name and username.
+ * Arguments containing '=' are treated as connection string parameters
+ * (e.g., "service=myservice" or "host=localhost") and collected into
+ * a connection string that will be used as the base for the connection.
*/
while (argc - optind >= 1)
{
- if (!options->dbname)
+ /*
+ * If argument contains '=', treat it as a connection string parameter.
+ * This allows syntax like: psql service=myservice -d postgres
+ */
+ if (strchr(argv[optind], '=') != NULL)
+ {
+ if (options->connstring == NULL)
+ options->connstring = pg_strdup(argv[optind]);
+ else
+ {
+ /* Append to existing connection string with space separator */
+ char *newstr = psprintf("%s %s", options->connstring, argv[optind]);
+ pg_free(options->connstring);
+ options->connstring = newstr;
+ }
+ }
+ else if (!options->dbname)
options->dbname = argv[optind];
else if (!options->username)
options->username = argv[optind];
base-commit: d49936f3028b0bb6ac8ff83e07e421ca2a4f5c3f
--
2.50.1 (Apple Git-155)