Re: Re: Is it possible to set psql connection parameters before an ODBC connection?

Attached and copied below is a patch against master commit 825c29b to add a connection parameter for a postgres service name. The keyword is either "service" or the abbreviation "se". The string value gets passed in to the libpq connection.

The named service must match a service definition in a service file. The service file can be located in the default location (~/.pg_service.conf or %user%/AppData/postgresql) or in a location specified in a PGSERVICEFILE environment variable. As mentioned below, the service file allows specifying parameters like hostaddr, krbsrvname, sslcert (and others) for a connection.

Since there's no libpq servicefile parameter, I had a go at setting the service file for the libpq connection using a DSN servicefile keyword and _putenv, but that didn't seem to work, and I thought it would be better not to mess around with overriding user environment variables.

Also I haven't yet got my head around the ODBC manager dialog box stuff yet but this works just fine when specifying the parameter in a FileDSN or ODBC connection string. I also had trouble getting the installer to build - the light.exe step always failed for no apparent reason.

Best regards,

Lindsay

*** start patch ***

diff --git a/connection.c b/connection.c
index 131a0e7..63af8ca 100644
--- a/connection.c
+++ b/connection.c
@@ -312,6 +312,7 @@ CC_copy_conninfo(ConnInfo *ci, const ConnInfo *sci)
     NAME_TO_NAME(ci->password, sci->password);
     CORR_STRCPY(port);
     CORR_STRCPY(sslmode);
+    CORR_STRCPY(service);
     CORR_STRCPY(onlyread);
     CORR_STRCPY(fake_oid_index);
     CORR_STRCPY(show_oid_column);
@@ -2510,6 +2511,10 @@ LIBPQ_connect(ConnectionClass *self)
             opts[cnt] = "sslmode";
             vals[cnt++] = ci->sslmode;
     }
+    if (ci->service[0])
+    {
+        opts[cnt] = "service";        vals[cnt++] = ci->service;
+    }
     if (NAME_IS_VALID(ci->password))
     {
         opts[cnt] = "password";    vals[cnt++] = SAFE_NAME(ci->password);
diff --git a/connection.h b/connection.h
index 20d0bbb..f8aa808 100644
--- a/connection.h
+++ b/connection.h
@@ -218,6 +218,7 @@ typedef struct
     pgNAME        password;
     char        port[SMALL_REGISTRY_LEN];
     char        sslmode[16];
+    char        service[MEDIUM_REGISTRY_LEN];
     char        onlyread[SMALL_REGISTRY_LEN];
     char        fake_oid_index[SMALL_REGISTRY_LEN];
     char        show_oid_column[SMALL_REGISTRY_LEN];
diff --git a/dlg_specific.c b/dlg_specific.c
index 6ab7464..cdc1a0d 100644
--- a/dlg_specific.c
+++ b/dlg_specific.c
@@ -289,6 +289,7 @@ inolog("hlen=%d", hlen);
             strcpy(protocol_and, "7.4");
         olen = snprintf(&connect_string[hlen], nlen, ";"
             INI_SSLMODE "=%s;"
+            INI_SERVICE "=%s;"
             INI_READONLY "=%s;"
             INI_PROTOCOL "=%s;"
             INI_FAKEOIDINDEX "=%s;"
@@ -323,6 +324,7 @@ inolog("hlen=%d", hlen);
             INI_XAOPT "=%d"    /* XAOPT */
 #endif /* _HANDLE_ENLIST_IN_DTC_ */
             ,ci->sslmode
+            ,ci->service
             ,ci->onlyread
             ,protocol_and
             ,ci->fake_oid_index
@@ -660,6 +662,8 @@ copyAttributes(ConnInfo *ci, const char *attribute, const char *value)
                 break;
         }
     }
+    else if (stricmp(attribute, INI_SERVICE) == 0 || stricmp(attribute, ABBR_SERVICE) == 0)
+        strcpy(ci->service, value);
     else if (stricmp(attribute, INI_ABBREVIATE) == 0)
         unfoldCXAttribute(ci, value);
 #ifdef    _HANDLE_ENLIST_IN_DTC_
@@ -1000,6 +1004,9 @@ getDSNinfo(ConnInfo *ci, char overwrite)
     if (ci->sslmode[0] == '\0' || overwrite)
         SQLGetPrivateProfileString(DSN, INI_SSLMODE, "", ci->sslmode, sizeof(ci->sslmode), ODBC_INI);
 
+    if (ci->service[0] == '\0' || overwrite)
+        SQLGetPrivateProfileString(DSN, INI_SERVICE, "", ci->service, sizeof(ci->service), ODBC_INI);
+
 #ifdef    _HANDLE_ENLIST_IN_DTC_
     if (ci->xa_opt < 0 || overwrite)
     {
@@ -1270,6 +1277,10 @@ writeDSNinfo(const ConnInfo *ci)
                                  INI_SSLMODE,
                                  ci->sslmode,
                                  ODBC_INI);
+    SQLWritePrivateProfileString(DSN,
+                                 INI_SERVICE,
+                                 ci->service,
+                                 ODBC_INI);
     sprintf(temp, "%d", ci->keepalive_idle);
     SQLWritePrivateProfileString(DSN,
                                  INI_KEEPALIVETIME,
diff --git a/dlg_specific.h b/dlg_specific.h
index 30ea389..ceb3bea 100644
--- a/dlg_specific.h
+++ b/dlg_specific.h
@@ -157,6 +157,8 @@ extern "C" {
 #define ABBR_LOWERCASEIDENTIFIER    "C9"
 #define INI_SSLMODE            "SSLmode"
 #define ABBR_SSLMODE            "CA"
+#define INI_SERVICE            "service"
+#define ABBR_SERVICE            "SE"
 #define INI_EXTRAOPTIONS        "AB"
 #define INI_LOGDIR            "Logdir"
 #define INI_GSSAUTHUSEGSSAPI        "GssAuthUseGSS"

*** end patch ***

On 21 March 2015 at 11:59, Lindsay Stevens <lindsay.stevens.au@gmail.com> wrote:

Thanks, I tried this but couldn't seem to get it to work, e.g. ConnSettings=/*krbsrvname=myvalue*/, or placing the comment in braces, or putting myvalue in quotes, or making the whole comment url encoded.

I haven't written a line of C before but I had a read of the source anyway and it seems that most of the DSN values get put in a ConnInfo object in dlg_specific which is passed in to libpq  PQconnectdb. So it might just be a matter of adding "service" and/or the relevant keywords to the conninfo object. It also seems that there is no servicefile keyword, unfortunately. I'll have a go at this and see if it works, I think it would be a useful feature.

I'm using pg 9.3.5 and the latest odbc driver, by the way.

On 20/03/2015 4:17 AM, "Jacobo" <jsanchez@denodo.com> wrote:

I was currently digging into the code to find a way to specify krbsrvname and
just found that before reading the PGKRBSRVNAME environment variable it
calls a function in order to find the krbsrvname.

Checking the implementation i realized that in order to read this parameter
from Connect Settings at this level they should be added inside a comment.

To verify this i have configured a DSN with Connect Settings
/*krbsrvname=myvalue*/ and from the logs it seems to be using this value.

I don't know for the rest of parameters but this may also work for them.

Hope this helps

Regards,

Jacobo



--
View this message in context: http://postgresql.nabble.com/Is-it-possible-to-set-psql-connection-parameters-before-an-ODBC-connection-tp5842291p5842571.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc