Hi,
I like to bundle all my database connections in a .pg_service.conf. Over
time I collected a bunch of such service files. A while back I discovered
that the service file can only be specified as an environment variable. It
cannot be given as part of the connection string like
psql "service=$MY_SERVICE servicefile=MY_SERVICE_FILE"
The attached patch allows that.
Regards.
--
Torsten
From f419584d2fc7766c143d304ba2f2fad98501d9ea Mon Sep 17 00:00:00 2001
From: Torsten Foertsch <tfoertsch...@gmail.com>
Date: Sat, 16 Nov 2024 20:17:20 +0100
Subject: [PATCH v1] PGSERVICEFILE as part of the connection string
Libpq interprets the PGSERVICEFILE environment variable. However,
the servicefile cannot be specified as part of the connection
string itself.
This change implements that.
---
doc/src/sgml/libpq.sgml | 16 ++++-
src/interfaces/libpq/fe-connect.c | 18 ++++-
src/interfaces/libpq/t/006_service.pl | 97 +++++++++++++++++++++++++++
3 files changed, 129 insertions(+), 2 deletions(-)
create mode 100644 src/interfaces/libpq/t/006_service.pl
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index bfefb1289e..de8830bf51 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2202,6 +2202,19 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
</listitem>
</varlistentry>
+ <varlistentry id="libpq-connect-servicefile" xreflabel="servicefile">
+ <term><literal>servicefile</literal></term>
+ <listitem>
+ <para>
+ This option specifies the name of the per-user connection service file
+ (see <xref linkend="libpq-pgservice"/>).
+ Defaults to <filename>~/.pg_service.conf</filename>, or
+ <filename>%APPDATA%\postgresql\.pg_service.conf</filename> on
+ Microsoft Windows.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-connect-target-session-attrs" xreflabel="target_session_attrs">
<term><literal>target_session_attrs</literal></term>
<listitem>
@@ -9337,7 +9350,8 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
On Microsoft Windows, it is named
<filename>%APPDATA%\postgresql\.pg_service.conf</filename> (where
<filename>%APPDATA%</filename> refers to the Application Data subdirectory
- in the user's profile). A different file name can be specified by
+ in the user's profile). A different file name can be specified using the
+ <literal>servicefile</literal> key word in a libpq connection string or by
setting the environment variable <envar>PGSERVICEFILE</envar>.
The system-wide file is named <filename>pg_service.conf</filename>.
By default it is sought in the <filename>etc</filename> directory
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 51083dcfd8..117588c0e8 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -192,6 +192,9 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
{"service", "PGSERVICE", NULL, NULL,
"Database-Service", "", 20, -1},
+ {"servicefile", "PGSERVICEFILE", NULL, NULL,
+ "Database-Service-File", "", 64, -1},
+
{"user", "PGUSER", NULL, NULL,
"Database-User", "", 20,
offsetof(struct pg_conn, pguser)},
@@ -5493,6 +5496,7 @@ static int
parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage)
{
const char *service = conninfo_getval(options, "service");
+ const char *service_fname = conninfo_getval(options, "servicefile");
char serviceFile[MAXPGPATH];
char *env;
bool group_found = false;
@@ -5515,7 +5519,9 @@ parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage)
* Try PGSERVICEFILE if specified, else try ~/.pg_service.conf (if that
* exists).
*/
- if ((env = getenv("PGSERVICEFILE")) != NULL)
+ if (service_fname != NULL)
+ strlcpy(serviceFile, service_fname, sizeof(serviceFile));
+ else if ((env = getenv("PGSERVICEFILE")) != NULL)
strlcpy(serviceFile, env, sizeof(serviceFile));
else
{
@@ -5678,6 +5684,16 @@ parseServiceFile(const char *serviceFile,
goto exit;
}
+ if (strcmp(key, "servicefile") == 0)
+ {
+ libpq_append_error(errorMessage,
+ "nested servicefile specifications not supported in service file \"%s\", line %d",
+ serviceFile,
+ linenr);
+ result = 3;
+ goto exit;
+ }
+
/*
* Set the parameter --- but don't override any previous
* explicit setting.
diff --git a/src/interfaces/libpq/t/006_service.pl b/src/interfaces/libpq/t/006_service.pl
new file mode 100644
index 0000000000..5de84b78af
--- /dev/null
+++ b/src/interfaces/libpq/t/006_service.pl
@@ -0,0 +1,97 @@
+# Copyright (c) 2023-2024, PostgreSQL Global Development Group
+use strict;
+use warnings FATAL => 'all';
+use Config;
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+# This tests "service" and "servicefile"
+
+# Cluster setup which is shared for testing both load balancing methods
+my $node = PostgreSQL::Test::Cluster->new('node');
+
+# Create a data directory with initdb
+$node->init();
+
+# Start the PostgreSQL server
+$node->start();
+
+my $td = PostgreSQL::Test::Utils::tempdir;
+my $srvfile = "$td/pgsrv.conf";
+
+open my $fh, '>', $srvfile or die $!;
+print $fh "[my_srv]\n";
+print $fh +($node->connstr =~ s/ /\n/gr), "\n";
+close $fh;
+
+{
+ local $ENV{PGSERVICEFILE} = $srvfile;
+ $node->connect_ok(
+ 'service=my_srv',
+ 'service=my_srv',
+ sql => "SELECT 'connect1'",
+ expected_stdout => qr/connect1/);
+
+ $node->connect_ok(
+ 'postgres://?service=my_srv',
+ 'postgres://?service=my_srv',
+ sql => "SELECT 'connect2'",
+ expected_stdout => qr/connect2/);
+
+ local $ENV{PGSERVICE} = 'my_srv';
+ $node->connect_ok(
+ '',
+ 'envvar: PGSERVICE=my_srv',
+ sql => "SELECT 'connect3'",
+ expected_stdout => qr/connect3/);
+
+ $node->connect_fails(
+ 'service=non-existent-service',
+ 'service=non-existent-service',
+ expected_stderr => qr/definition of service "non-existent-service" not found/);
+}
+
+{
+ $node->connect_ok(
+ q{service=my_srv servicefile='}.$srvfile.q{'},
+ 'service=my_srv servicefile=...',
+ sql => "SELECT 'connect4'",
+ expected_stdout => qr/connect4/);
+
+ $node->connect_ok(
+ 'postgresql:///?service=my_srv&servicefile='.($srvfile =~ s!/!%2F!gr),
+ 'postgresql:///?service=my_srv&servicefile=...',
+ sql => "SELECT 'connect5'",
+ expected_stdout => qr/connect5/);
+
+ local $ENV{PGSERVICE} = 'my_srv';
+ $node->connect_ok(
+ q{servicefile='}.$srvfile.q{'},
+ 'envvar: PGSERVICE=my_srv + servicefile=...',
+ sql => "SELECT 'connect6'",
+ expected_stdout => qr/connect6/);
+
+ $node->connect_ok(
+ 'postgresql://?servicefile='.($srvfile =~ s!/!%2F!gr),
+ 'envvar: PGSERVICE=my_srv + postgresql://?servicefile=...',
+ sql => "SELECT 'connect6'",
+ expected_stdout => qr/connect6/);
+}
+
+{
+ local $ENV{PGSERVICEFILE} = 'non-existent-file.conf';
+
+ $node->connect_fails(
+ 'service=my_srv',
+ 'service=... fails with wrong PGSERVICEFILE',
+ expected_stderr => qr/service file "non-existent-file\.conf" not found/);
+
+ $node->connect_ok(
+ q{service=my_srv servicefile='}.$srvfile.q{'},
+ 'servicefile= takes precedence over PGSERVICEFILE',
+ sql => "SELECT 'connect7'",
+ expected_stdout => qr/connect7/);
+}
+
+done_testing();
--
2.34.1
