Hi Jurij,
Thanks for the great review! v2 attached: all your points addressed,
plus a rebase onto current master.
> I think the simplest option is to keep the proposed public signature
> and document that NULL is returned if no matching password is found or
> if the lookup cannot be completed, for example because of an allocation
> failure. Alternatively, the public API would need an error output
> parameter.
Done the simple way: the docs and the function's comment now say NULL
means no matching password was found or the lookup could not be
completed. I also took your other suggestion and made
passwordFromFile() accept a NULL error pointer, so the wrapper no
longer needs a dummy variable.
> There is no need to copy the environment value here. passfile can
> point directly to pgpassenv; the local buffer is needed only when
> constructing the default path.
Done exactly that way, the truncation is gone.
> * NULL and empty port should use DEF_PGPORT_STR. The current
> localhost test uses a wildcard port, so it does not test that
> default. The test should account for a non-standard compiled-in
> port rather than assuming 5432.
Added. The test client grew a --passfile-defaults mode that prints
DEF_PGPORT_STR and DEFAULT_PGSOCKET_DIR, and the TAP test writes its
passfile entry under whatever port that reports, so no 5432 assumption
anywhere. NULL and empty port are both covered. I tried it against a
build configured with -Dpgport=5499 and it passes there too.
> * NULL dbname and username are tested, but their empty-string forms
> are not.
Added both. Small deviation here: the test client maps an argument of
"=" to an empty string instead of taking a real empty argument. No
TAP test in the tree passes an empty argv element today, and I did not
want to find out on the Windows CI whether one survives process
spawning there.
> * NULL passfile with PGPASSFILE is tested, but empty passfile and the
> fallback to the default password file location are not.
Added both: an empty passfile falling back to PGPASSFILE, and a NULL
passfile with no PGPASSFILE falling back to ~/.pgpass, by pointing
HOME at a temporary directory (skipped on Windows, where the default
location does not come from the environment).
> * The documented default-socket-directory-to-localhost rule is not
> currently tested.
Added, using the socket directory reported by --passfile-defaults
(skipped if the platform reports none).
> Testing that trailing fields are silently ignored appears to preserve
> an undocumented parser behaviour. Since that behaviour is inherited
> from passwordFromFile() rather than introduced by this API, I would
> prefer to leave it out of this patch.
Agreed, I removed the test case and its passfile line.
> * test_passfile_lookup() checks argc < 7 and silently accepts extra
> arguments. argc != 7 would match the usage string more closely.
Changed.
> * The C comment describing hostname defaults does not mention the
> default socket directory rule that is described in the SGML docs.
Added.
> * The returned allocation contains a plaintext password.
> PQfreemem() does not erase it, whereas libpq explicitly clears
> passwords stored in PGconn before freeing them. It would be useful
> for the documentation to mention that callers are responsible for
> securely clearing the result where appropriate.
Added a paragraph to the docs: the result is a cleartext password,
PQfreemem() does not erase it, and callers that do not want it
lingering in memory should overwrite it before freeing.
v2 also wraps the doc synopsis like the neighboring entries do, and
now spells out that PGPASSFILE is the only environment variable the
function looks at (PGHOST and PGPORT are not applied to the lookup
keys).
I'll move the CF entry back to Needs review.
Thank you,
BR,
Diego
From 7f6c251fcb063fdd72533c723065ff0576dfe836 Mon Sep 17 00:00:00 2001
From: Diego <[email protected]>
Date: Mon, 31 Aug 2026 16:38:53 -0300
Subject: [PATCH v2] libpq: Add PQpassfileLookup()
An application that connects through an intermediary, such as a local
SSH tunnel, connects to a host and port that no longer match the
password file entry written for the real server, so libpq's password
file lookup comes up empty during connection establishment. Until now,
such an application had to reimplement the password file parser on its
side to keep .pgpass working.
Expose the existing lookup as a public function, PQpassfileLookup(), so
that a client can look up the password under the real server's host and
port and pass the result as the password connection parameter while
connecting to the intermediary's address. The function applies the
same rules as connection establishment: the same field matching and
de-escaping, the same localhost and default-port substitutions for
missing values, the same permission checks, and the same fallback to
PGPASSFILE and the default password file location when no file is
given. PGPASSFILE is the only environment variable consulted; the
lookup keys are used as given.
Also add --passfile and --passfile-defaults modes to libpq_testclient,
and a TAP test exercising the lookup; it needs no server.
Author: Diego <[email protected]>
Reviewed-by: Yuriy Grigoryev <[email protected]>
Suggested-by: Denis Smirnov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/libpq.sgml | 73 +++++++
src/interfaces/libpq/exports.txt | 1 +
src/interfaces/libpq/fe-connect.c | 63 +++++-
src/interfaces/libpq/libpq-fe.h | 9 +
src/interfaces/libpq/meson.build | 1 +
src/interfaces/libpq/t/007_passfile.pl | 201 +++++++++++++++++++
src/interfaces/libpq/test/libpq_testclient.c | 60 +++++-
7 files changed, 403 insertions(+), 5 deletions(-)
create mode 100644 src/interfaces/libpq/t/007_passfile.pl
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 68487a3954f..4b37faf7741 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -7976,6 +7976,73 @@ char *PQencryptPassword(const char *passwd, const char *user);
</listitem>
</varlistentry>
+ <varlistentry id="libpq-PQpassfileLookup">
+ <term><function>PQpassfileLookup</function><indexterm><primary>PQpassfileLookup</primary></indexterm></term>
+
+ <listitem>
+ <para>
+ Looks up a password in a password file
+ (see <xref linkend="libpq-pgpass"/>).
+<synopsis>
+char *PQpassfileLookup(const char *hostname, const char *port,
+ const char *dbname, const char *username,
+ const char *passfile);
+</synopsis>
+ </para>
+
+ <para>
+ This function performs the same password file lookup that connection
+ establishment performs when no password has been specified, and
+ returns the password from the first matching line. It is intended
+ for applications that connect through an intermediary, for example a
+ local SSH tunnel: such an application can look up the password under
+ the real server's host and port, and then pass the result as the
+ <xref linkend="libpq-connect-password"/> connection parameter while
+ connecting to the intermediary's address.
+ </para>
+
+ <para>
+ The <parameter>hostname</parameter>, <parameter>port</parameter>,
+ <parameter>dbname</parameter> and <parameter>username</parameter>
+ arguments correspond to the first four fields of a password file
+ line. If <parameter>hostname</parameter> is <symbol>NULL</symbol> or
+ empty, or matches <application>libpq</application>'s default socket
+ directory path, the host name <literal>localhost</literal> is
+ searched for; if <parameter>port</parameter> is <symbol>NULL</symbol>
+ or empty, the compiled-in default port is used. No defaults are
+ applied for <parameter>dbname</parameter> and
+ <parameter>username</parameter>; if either is <symbol>NULL</symbol>
+ or empty, no password is returned.
+ <parameter>passfile</parameter> is the password file to use; if it is
+ <symbol>NULL</symbol> or empty, the file named by the
+ <envar>PGPASSFILE</envar> environment variable is used if set, else
+ the default password file location.
+ <envar>PGPASSFILE</envar> is the only environment variable this
+ function consults; in particular, <envar>PGHOST</envar> and
+ <envar>PGPORT</envar> are not applied to the lookup keys.
+ </para>
+
+ <para>
+ The return value is a string allocated by <function>malloc</function>,
+ or <symbol>NULL</symbol> if no matching password was found or the
+ lookup could not be completed, for example because of a memory
+ allocation failure. Use <xref linkend="libpq-PQfreemem"/> to free
+ the result when done with it.
+ </para>
+
+ <para>
+ Note that the result contains a cleartext password, and that
+ <xref linkend="libpq-PQfreemem"/> does not erase it. Callers that
+ do not want to keep the password around in memory are responsible
+ for overwriting the result before freeing it. The password file
+ permission requirements described in
+ <xref linkend="libpq-pgpass"/> apply, and, as during connection
+ establishment, a warning is written to <filename>stderr</filename>
+ if the file is ignored because of them.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-PQmakeEmptyPGresult">
<term><function>PQmakeEmptyPGresult</function><indexterm><primary>PQmakeEmptyPGresult</primary></indexterm></term>
@@ -9462,6 +9529,12 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
is assumed that the file is stored in a directory that is secure, so
no special permissions check is made.
</para>
+
+ <para>
+ An application can perform the same password file lookup that
+ connection establishment performs by calling
+ <xref linkend="libpq-PQpassfileLookup"/>.
+ </para>
</sect1>
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index 1e3d5bd5867..def61d63724 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -211,3 +211,4 @@ PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
PQgetThreadLock 211
+PQpassfileLookup 212
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 2ce128da157..e16ff17cb46 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -8001,7 +8001,8 @@ pwdfMatchesString(char *buf, const char *token)
* Get a password from the password file. Return value is malloc'd.
*
* On failure, *errmsg is set to an error to be returned. It is
- * left NULL on success, or if no password could be found.
+ * left NULL on success, or if no password could be found. Callers
+ * that do not care about the distinction can pass errmsg as NULL.
*/
static char *
passwordFromFile(const char *hostname, const char *port,
@@ -8014,7 +8015,8 @@ passwordFromFile(const char *hostname, const char *port,
#endif
PQExpBufferData buf;
- *errmsg = NULL;
+ if (errmsg)
+ *errmsg = NULL;
if (dbname == NULL || dbname[0] == '\0')
return NULL;
@@ -8083,7 +8085,8 @@ passwordFromFile(const char *hostname, const char *port,
/* Make sure there's a reasonable amount of room in the buffer */
if (!enlargePQExpBuffer(&buf, 128))
{
- *errmsg = libpq_gettext("out of memory");
+ if (errmsg)
+ *errmsg = libpq_gettext("out of memory");
break;
}
@@ -8124,7 +8127,8 @@ passwordFromFile(const char *hostname, const char *port,
if (!ret)
{
- *errmsg = libpq_gettext("out of memory");
+ if (errmsg)
+ *errmsg = libpq_gettext("out of memory");
return NULL;
}
@@ -8152,6 +8156,57 @@ passwordFromFile(const char *hostname, const char *port,
}
+/*
+ * PQpassfileLookup
+ *
+ * Look up a password in a password file, applying the same rules that
+ * connection establishment applies when no password has been specified.
+ * This lets applications that connect through an intermediary (for
+ * example, a local SSH tunnel) look up the password under the real
+ * server's host and port while connecting elsewhere.
+ *
+ * The first four arguments correspond to the fields of a password file
+ * line, and NULL or empty values are treated the same way as during
+ * connection establishment: hostname is matched as "localhost" (as is
+ * a hostname equal to the default Unix-socket directory), port
+ * defaults to DEF_PGPORT_STR, while dbname and username must be
+ * supplied. If passfile is NULL or empty, PGPASSFILE or the default
+ * password file location is used.
+ *
+ * Returns a malloc'd string the caller must free with PQfreemem(), or
+ * NULL if no matching password was found or the lookup could not be
+ * completed.
+ */
+char *
+PQpassfileLookup(const char *hostname, const char *port,
+ const char *dbname, const char *username,
+ const char *passfile)
+{
+ char pgpassfile[MAXPGPATH];
+
+ if (passfile == NULL || passfile[0] == '\0')
+ {
+ const char *pgpassenv = getenv("PGPASSFILE");
+
+ if (pgpassenv != NULL && pgpassenv[0] != '\0')
+ passfile = pgpassenv;
+ else
+ {
+ char homedir[MAXPGPATH];
+
+ if (!pqGetHomeDirectory(homedir, sizeof(homedir)))
+ return NULL;
+ snprintf(pgpassfile, sizeof(pgpassfile), "%s/%s",
+ homedir, PGPASSFILE);
+ passfile = pgpassfile;
+ }
+ }
+
+ return passwordFromFile(hostname, port, dbname, username,
+ passfile, NULL);
+}
+
+
/*
* If the connection failed due to bad password, we should mention
* if we got the password from the pgpassfile.
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index f51fd620b0a..b63489a3bfe 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -69,6 +69,10 @@ extern "C"
/* Indicates presence of the PQAUTHDATA_OAUTH_BEARER_TOKEN_V2 authdata hook */
#define LIBPQ_HAS_OAUTH_BEARER_TOKEN_V2 1
+/* Features added in PostgreSQL v20: */
+/* Indicates presence of PQpassfileLookup */
+#define LIBPQ_HAS_PASSFILE_LOOKUP 1
+
/*
* Option flags for PQcopyResult
*/
@@ -367,6 +371,11 @@ extern PQconninfoOption *PQconninfo(PGconn *conn);
/* free the data structure returned by PQconndefaults() or PQconninfoParse() */
extern void PQconninfoFree(PQconninfoOption *connOptions);
+/* look up a password in a password file */
+extern char *PQpassfileLookup(const char *hostname, const char *port,
+ const char *dbname, const char *username,
+ const char *passfile);
+
/*
* close the current connection and reestablish a new one with the same
* parameters
diff --git a/src/interfaces/libpq/meson.build b/src/interfaces/libpq/meson.build
index b0ae72167a1..b9f93ddb852 100644
--- a/src/interfaces/libpq/meson.build
+++ b/src/interfaces/libpq/meson.build
@@ -161,6 +161,7 @@ tests += {
't/004_load_balance_dns.pl',
't/005_negotiate_encryption.pl',
't/006_service.pl',
+ 't/007_passfile.pl',
],
'env': {
'with_ssl': ssl_library,
diff --git a/src/interfaces/libpq/t/007_passfile.pl b/src/interfaces/libpq/t/007_passfile.pl
new file mode 100644
index 00000000000..2bd165e4993
--- /dev/null
+++ b/src/interfaces/libpq/t/007_passfile.pl
@@ -0,0 +1,201 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+use strict;
+use warnings FATAL => 'all';
+use File::Copy;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Test PQpassfileLookup(), via libpq_testclient --passfile. The lookup is
+# purely client-side, so no server is involved. An argument of "-" is
+# passed to the function as NULL, and an argument of "=" as an empty
+# string (an empty argv element is not portable).
+
+my $td = PostgreSQL::Test::Utils::tempdir;
+my $passfile = "$td/pgpass";
+
+delete $ENV{PGPASSFILE};
+
+# The compiled-in defaults that the lookup falls back to.
+my ($defaults) = run_command([ 'libpq_testclient', '--passfile-defaults' ]);
+$defaults =~ s/\r//g;
+my ($defport, $socketdir) = split /\n/, $defaults;
+
+append_to_file($passfile, <<'EOF');
+# a comment line
+server.example.com:5432:proddb:diego:secret1
+server.example.com:5433:*:diego:secret2
+localhost:*:mydb:me:localpw
+special.example.com:5432:db\:colon:us\\er:pa\\ss\:word
+server.example.com:5432:proddb:diego:shadowed
+EOF
+append_to_file($passfile,
+ "defport.example.com:$defport:defdb:defuser:defportpw\n");
+chmod 0600, $passfile or die "chmod: $!";
+
+my ($out, $err);
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', 'proddb', 'diego'
+ ]);
+is($out, 'secret1', 'exact match returns the first matching password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5433', 'anydb', 'diego'
+ ]);
+is($out, 'secret2', 'wildcard field matches any value');
+
+($out, $err) = run_command(
+ [ 'libpq_testclient', '--passfile', $passfile, '-', '-', 'mydb', 'me' ]);
+is($out, 'localpw', 'NULL hostname and port match a localhost entry');
+
+($out, $err) = run_command(
+ [ 'libpq_testclient', '--passfile', $passfile, '=', '-', 'mydb', 'me' ]);
+is($out, 'localpw', 'empty hostname matches a localhost entry');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'defport.example.com', '-', 'defdb', 'defuser'
+ ]);
+is($out, 'defportpw', 'NULL port matches an entry for the default port');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'defport.example.com', '=', 'defdb', 'defuser'
+ ]);
+is($out, 'defportpw', 'empty port matches an entry for the default port');
+
+SKIP:
+{
+ skip 'no default Unix-socket directory on this platform', 1
+ unless defined $socketdir && $socketdir =~ m{^/};
+
+ ($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ $socketdir, '-', 'mydb', 'me'
+ ]);
+ is($out, 'localpw',
+ 'default socket directory matches a localhost entry');
+}
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'special.example.com', '5432', 'db:colon', 'us\\er'
+ ]);
+is($out, 'pa\\ss:word', 'escaped characters are matched and de-escaped');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', 'otherdb', 'diego'
+ ]);
+is($err, 'no password found', 'no matching line returns no password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', "$td/does_not_exist",
+ 'server.example.com', '5432', 'proddb', 'diego'
+ ]);
+is($err, 'no password found', 'missing password file returns no password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', '-', 'diego'
+ ]);
+is($err, 'no password found', 'NULL dbname returns no password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', '=', 'diego'
+ ]);
+is($err, 'no password found', 'empty dbname returns no password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', 'proddb', '-'
+ ]);
+is($err, 'no password found', 'NULL username returns no password');
+
+($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile,
+ 'server.example.com', '5432', 'proddb', '='
+ ]);
+is($err, 'no password found', 'empty username returns no password');
+
+# A NULL or empty passfile falls back to the PGPASSFILE environment
+# variable.
+{
+ local $ENV{PGPASSFILE} = $passfile;
+
+ ($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', '-',
+ 'server.example.com', '5432', 'proddb', 'diego'
+ ]);
+ is($out, 'secret1', 'NULL passfile falls back to PGPASSFILE');
+
+ ($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', '=',
+ 'server.example.com', '5432', 'proddb', 'diego'
+ ]);
+ is($out, 'secret1', 'empty passfile falls back to PGPASSFILE');
+}
+
+SKIP:
+{
+ skip 'default password file location cannot be redirected on Windows', 1
+ if $windows_os;
+
+ # Without PGPASSFILE, the lookup falls back to ~/.pgpass.
+ my $homedir = PostgreSQL::Test::Utils::tempdir;
+ my $homepassfile = "$homedir/.pgpass";
+
+ append_to_file($homepassfile,
+ "home.example.com:5432:homedb:homeuser:homepw\n");
+ chmod 0600, $homepassfile or die "chmod: $!";
+
+ local $ENV{HOME} = $homedir;
+
+ ($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', '-',
+ 'home.example.com', '5432', 'homedb', 'homeuser'
+ ]);
+ is($out, 'homepw', 'NULL passfile falls back to ~/.pgpass');
+}
+
+SKIP:
+{
+ skip 'password file permissions are not checked on Windows', 2
+ if $windows_os;
+
+ my $passfile_insecure = "$td/pgpass_insecure";
+ copy($passfile, $passfile_insecure)
+ or die "could not copy $passfile to $passfile_insecure: $!";
+ chmod 0644, $passfile_insecure or die "chmod: $!";
+
+ ($out, $err) = run_command(
+ [
+ 'libpq_testclient', '--passfile', $passfile_insecure,
+ 'server.example.com', '5432', 'proddb', 'diego'
+ ]);
+ like(
+ $err,
+ qr/has group or world access/,
+ 'insecure password file draws a warning');
+ like($err, qr/no password found/, 'insecure password file is ignored');
+}
+
+done_testing();
diff --git a/src/interfaces/libpq/test/libpq_testclient.c b/src/interfaces/libpq/test/libpq_testclient.c
index 20730709ee7..87309837489 100644
--- a/src/interfaces/libpq/test/libpq_testclient.c
+++ b/src/interfaces/libpq/test/libpq_testclient.c
@@ -23,6 +23,57 @@ print_ssl_library(void)
printf("%s\n", lib);
}
+/*
+ * Print the compiled-in defaults that the passfile lookup falls back to,
+ * for use by the TAP test.
+ */
+static void
+print_passfile_defaults(void)
+{
+ printf("%s\n%s\n", DEF_PGPORT_STR, DEFAULT_PGSOCKET_DIR);
+}
+
+/*
+ * Look up a password with PQpassfileLookup(). The arguments are passfile,
+ * hostname, port, dbname and username; an argument of "-" is passed as
+ * NULL, and an argument of "=" as an empty string (an empty command-line
+ * argument cannot be relied on to survive process spawning everywhere).
+ */
+static int
+test_passfile_lookup(int argc, char *argv[])
+{
+ const char *args[5];
+ char *password;
+
+ if (argc != 7)
+ {
+ fprintf(stderr, "usage: libpq_testclient --passfile PASSFILE HOSTNAME PORT DBNAME USERNAME\n");
+ return 1;
+ }
+
+ for (int i = 0; i < 5; i++)
+ {
+ if (strcmp(argv[i + 2], "-") == 0)
+ args[i] = NULL;
+ else if (strcmp(argv[i + 2], "=") == 0)
+ args[i] = "";
+ else
+ args[i] = argv[i + 2];
+ }
+
+ password = PQpassfileLookup(args[1], args[2], args[3], args[4], args[0]);
+
+ if (!password)
+ {
+ fprintf(stderr, "no password found\n");
+ return 1;
+ }
+
+ printf("%s\n", password);
+ PQfreemem(password);
+ return 0;
+}
+
int
main(int argc, char *argv[])
{
@@ -31,7 +82,14 @@ main(int argc, char *argv[])
print_ssl_library();
return 0;
}
+ else if ((argc > 1) && !strcmp(argv[1], "--passfile"))
+ return test_passfile_lookup(argc, argv);
+ else if ((argc > 1) && !strcmp(argv[1], "--passfile-defaults"))
+ {
+ print_passfile_defaults();
+ return 0;
+ }
- printf("currently only --ssl is supported\n");
+ printf("currently only --ssl, --passfile and --passfile-defaults are supported\n");
return 1;
}
base-commit: 11fb89fdbd319a53e32dce77685118979cafac0e
--
2.43.0
