[[ PostgreSQL SSL ]] Updated revOpenDatabase to handle the SSL connection options as key value pairs.

livecodemichael · livecodemichael · commit bee57c81adb9 · 2015-10-15T09:40:07.000+01:00
It was decided that key=value parameters were easier to use than one specific parameter per SSL option.
Now any number of additional paramters can be passed to revOpenDatabase (for PostgreSQL connections) each specifying an SSL option in the form option_name=option_value.
The docs note and dictionary entry have been updated accordingly.
diff --git a/docs/dictionary/function/revOpenDatabase.lcdoc b/docs/dictionary/function/revOpenDatabase.lcdoc
@@ -8,7 +8,7 @@ Syntax: revOpenDatabase("odbc", <host> [: <port>], <databaseName>, [<userName>],
 
 Syntax: revOpenDatabase("sqlite",filepath,[<sqliteOptions>])
 
-Syntax: revOpenDatabase("postgresql", <host> [: <port>], <databaseName>, [<userName>], [<password>], [<sslmode>], [<sslcompression>], [<sslcert>], [<sslkey>], [<sslrootcert>], [<sslcrl>])
+Syntax: revOpenDatabase("postgresql", <host> [: <port>], <databaseName>, [<userName>], [<password>], [<sslOption>],...)
 
 Syntax: revOpenDatabase("oracle", <host> [: <port>], <databaseName>, [<userName>],[<password>])
 
@@ -40,7 +40,7 @@ Example:
 get revOpenDatabase("mysql", "localhost", "dbName", myUsr, myPass, false, "/var/mysql.sock", 1, true)
 
 Example:
-get revOpenDatabase("postgresql", "192.168.1.100", "dbName", myUsr, myPass, "require")
+get revOpenDatabase("postgresql", "192.168.1.100", "dbName", myUsr, myPass, "sslmode=require", "sslcompression=0")
 
 Parameters:
 host (string): A string specifying the IP address or domain name of the system hosting the database. For SQLite databases, the host should be the full path to the database file.
@@ -67,19 +67,21 @@ databaseType (enum): A string specifying the database type to use. One of the fo
 - "valentina"
 - "sqlite"
 filename: (SQLite Only) A string specifying the path to the SQLite database.
-sslmode: (PostgreSQL Only) A string specifying the SSL connection mode to use. There are 6 options:
-- "disable": Only try a non-SSL connection.
-- "allow": First try a non-SSL connection; if that fails, try an SSL connection.
-- "prefer": First try an SSL connection; if that fails, try a non-SSL connection.
-- "require": Only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified.
-- "verify-ca": Only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA).
-- "verify-full": Only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate.
-If no SSL mode is specified, the default will be "prefer" is the security library can be loaded, "disable" otherwise.
-sslcompression: (PostgreSQL Only) A string specifying the any SSL compression to use. If set to "1" (default), data sent over SSL connections will be compressed. If set to "0", compression will be disabled. This parameter is ignored if a connection without SSL is made. Compression uses CPU time, but can improve throughput if the network is the bottleneck. Disabling compression can improve response time and throughput if CPU performance is the limiting factor.
-sslcert: (PostgreSQL Only) A string specifying the file name of the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt. This parameter is ignored if an SSL connection is not made.
-sslkey: (PostgreSQL Only) A string specifying the location for the secret key used for the client certificate.  This parameter is ignored if an SSL connection is not made.
-sslrootcert: (PostgreSQL Only) A string specifying the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server's certificate will be verified to be signed by one of these authorities. The default is ~/.postgresql/root.crt.
-sslcrl: (PostgreSQL Only) A string specifying the file name of the SSL certificate revocation list (CRL). Certificates listed in this file, if it exists, will be rejected while attempting to authenticate the server's certificate. The default is ~/.postgresql/root.crl.
+sslOption (string): (PostgreSQL Only) A string of the form "key=value" specifying the SSL options to use when connecting. The key is the name of the option you want to set, the value is the value you want the option to take. Any number of key value pairs can be specified, each in a new parameter. The set of recognized SSL option and their expected values are as follows:
+- **"sslmode"**: A string specifying the SSL connection mode to use. There are 6 options:
+  - "disable": Only try a non-SSL connection.
+  - "allow": First try a non-SSL connection; if that fails, try an SSL connection.
+  - "prefer": First try an SSL connection; if that fails, try a non-SSL connection.
+  - "require": Only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified.
+  - "verify-ca": Only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA).
+  - "verify-full": Only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate.
+
+  If no SSL mode is specified, the default will be "prefer" if the security library can be loaded, "disable" if not. If you use SSL connections in a standalone application, remember to select 'SSL  Encryption' from among the available 'script libraries' in the standalone application settings panel.
+- **"sslcompression"**: A string specifying the any SSL compression to use. If set to "1" (default), data sent over SSL connections will be compressed. If set to "0", compression will be disabled. This parameter is ignored if a connection without SSL is made. Compression uses CPU time, but can improve throughput if the network is the bottleneck. Disabling compression can improve response time and throughput if CPU performance is the limiting factor.
+- **"sslcert"**: A string specifying the file name of the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt. This parameter is ignored if an SSL connection is not made.
+- **"sslkey"**: A string specifying the location for the secret key used for the client certificate.  This parameter is ignored if an SSL connection is not made.
+- **sslrootcert"**: A string specifying the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server's certificate will be verified to be signed by one of these authorities. The default is ~/.postgresql/root.crt.
+- **"sslcrl"**: A string specifying the file name of the SSL certificate revocation list (CRL). Certificates listed in this file, if it exists, will be rejected while attempting to authenticate the server's certificate. The default is ~/.postgresql/root.crl.
 
 Returns:
 The <revOpenDatabase> function returns a database ID which can be used to refer to the database in other Database library commands and functions. The database ID is always an integer.
@@ -92,7 +94,7 @@ Use the <revOpenDatabase> function to start working with a database.
 
 >*Important:*  The revOpenDatabase function is part of the Database library. To ensure that the function works in a standalone application, you must include this custom library when you create your standalone. In the Inclusions section of the General screen of the Standalone Application Settings window, make sure the Database Support checkbox is checked and the database drivers you are using are selected in the list of database drivers.
 
->*Important:*  If you are using any of the MySQL or PostgreSQL SSL connection options, make sure to select 'SSL  Encryption' from among the available 'script libraries' in the standalone application settings panel.
+>*Important:*  If you are using any of the MySQL or PostgreSQL SSL connection options in a standalone application, make sure to select 'SSL  Encryption' from among the available 'script libraries' in the standalone application settings panel.
 
 The version of SQLite has been updated to 3.8.2.
 
diff --git a/docs/notes/feature-postgresql_ssl.md b/docs/notes/feature-postgresql_ssl.md
@@ -1,9 +1,9 @@
 # SSL Support for PostgreSQL Connections
 
-The PostgreSQL database driver has been updated to support secure connections. The desired SSL connection mode can be specified in the sixth parameter of revOpenDatabase.
+The PostgreSQL database driver has been updated to support secure connections. The desired SSL connection options can be specified as key value pairs in the additional parameters of revOpenDatabase.
 
 The syntax for connecting to PostgreSQL databases is now as follows:
 
-***revOpenDatabase(*** "postgresql", *<host>[<:port>]*, *<databasename>*, *[<username>]*, *[<password>]*, *[<sslmode>]*, *[<sslcompression>]*, *[<sslcert>]*, *[<sslkey>]*, *[<sslrootcert>]*, *[<sslcrl>]* ***)***
+**revOpenDatabase(** "postgresql", *<host>[<:port>]*, *<databasename>*, *[<username>]*, *[<password>]*, *[<ssloption>*=*<ssloptionvalue>]*,...**)**
 
-For full information on the new parameters see the dictionary entry for ***revOpenDatabase***.
+For full information on the new parameters see the dictionary entry for **revOpenDatabase**.
diff --git a/revdb/src/postgresql_connection.cpp b/revdb/src/postgresql_connection.cpp
@@ -64,58 +64,72 @@ Bool DBConnection_POSTGRESQL::connect(char **args, int numargs)
 		*t_delimiter = '\0';
 	}
 
+    const char *t_sslmode;
+    t_sslmode = NULL;
+    const char *t_sslcompression;
+    t_sslcompression = NULL;
+    const char *t_sslcert;
+    t_sslcert = NULL;
+    const char *t_sslkey;
+    t_sslkey = NULL;
+    const char *t_sslrootcert;
+    t_sslrootcert = NULL;
+    const char *t_sslcrl;
+    t_sslcrl = NULL;
+
+    // extract any ssl options spcified as key value pairs in the final args to revOpenDatabase
+    // e.g. sslmode=require
+    const char *t_ssl_opt_key;
+    t_ssl_opt_key = NULL;
+    const char *t_ssl_opt_val;
+    t_ssl_opt_val = NULL;
+    for (int i = 4; i < numargs; i++)
+    {
+        t_delimiter = strchr(args[i], '=');
+        if (t_delimiter != NULL)
+        {
+            *t_delimiter = '\0';
+            t_ssl_opt_key = args[i];
+            t_ssl_opt_val = (t_delimiter + (1 * sizeof(char)));
+
+            if (*t_ssl_opt_val != '\0')
+            {
+                if (strcmp(t_ssl_opt_key, "sslmode") == 0)
+                    t_sslmode = t_ssl_opt_val;
+                else if (strcmp(t_ssl_opt_key, "sslcompression") == 0)
+                    t_sslcompression = t_ssl_opt_val;
+                else if (strcmp(t_ssl_opt_key, "sslcert") == 0)
+                    t_sslcert = t_ssl_opt_val;
+                else if (strcmp(t_ssl_opt_key, "sslkey") == 0)
+                    t_sslkey = t_ssl_opt_val;
+                else if (strcmp(t_ssl_opt_key, "sslrootcert") == 0)
+                    t_sslrootcert = t_ssl_opt_val;
+                else if (strcmp(t_ssl_opt_key, "sslcrl") == 0)
+                    t_sslcrl = t_ssl_opt_val;
+            }
+        }
+    }
+
     bool t_have_ssl;
     t_have_ssl = load_ssl_library();
 
     // if an ssl mode (other than disable) has been passed, make sure we can load libopenssl
-    // if no ssl mode has been passed, use prefer if we have libopenssl (try an ssl connection,
-    // if that fails try non-ssl), if we don't have libopenssl, don't attempt an ssl connection (disable sslmode)
-    char *t_sslmode;
-    t_sslmode = NULL;
-    if (numargs > 4)
+    // if no ssl mode has been passed, use prefer if we have libopenssl (try an ssl connection, if that fails try non-ssl)
+    // if we don't have libopenssl, use disable (don't attempt an ssl connection)
+    const char *t_sslmode_prefer = "prefer";
+    const char *t_sslmode_disable = "disable";
+    if (t_sslmode != NULL)
     {
-        if (strcmp(args[4], "disable") != 0 && !t_have_ssl)
+        if (strcmp(t_sslmode, "disable") != 0 && !t_have_ssl)
         {
             errorMessageSet("revdb,unable to load SSL library");
             return false;
         }
-        t_sslmode = strdup(args[4]);
     }
     else if (t_have_ssl)
-        t_sslmode = strdup("prefer");
+        t_sslmode = t_sslmode_prefer;
     else
-        t_sslmode = strdup("disable");
-
-    if (t_sslmode == NULL)
-    {
-        errorMessageSet("revdb,unable to extract SSL mode");
-        return false;
-    }
-
-    char *t_sslcompression;
-    t_sslcompression = NULL;
-    if (numargs > 5)
-        t_sslcompression = args[5];
-
-    char *t_sslcert;
-    t_sslcert = NULL;
-    if (numargs > 6)
-        t_sslcert = args[6];
-
-    char *t_sslkey;
-    t_sslkey = NULL;
-    if (numargs > 7)
-        t_sslkey = args[7];
-
-    char *t_sslrootcert;
-    t_sslrootcert = NULL;
-    if (numargs > 8)
-        t_sslrootcert = args[8];
-
-    char *t_sslcrl;
-    t_sslcrl = NULL;
-    if (numargs > 9)
-        t_sslcrl = args[9];
+        t_sslmode = t_sslmode_disable;
 
     const char *t_connect_keys[] =
     {
@@ -155,8 +169,6 @@ Bool DBConnection_POSTGRESQL::connect(char **args, int numargs)
     dbconn = NULL;
     dbconn = PQconnectdbParams(t_connect_keys, t_connect_values, 0);
 
-    free(t_sslmode);
-
 	// OK-2008-05-16 : Bug where failed connections to postgres databases would
 	// not return any error information. According to the postgres docs, dbconn
 	// will only be null if there was not enough memory to allocate it.
