[jira] [Created] (HAWQ-1445) PXF JDBC Security, Extra Props, and Max Queries

["Jon Roberts (JIRA)" <ji...@apache.org>]

Jon Roberts created HAWQ-1445:
---------------------------------

             Summary: PXF JDBC Security, Extra Props, and Max Queries
                 Key: HAWQ-1445
                 URL: https://issues.apache.org/jira/browse/HAWQ-1445
             Project: Apache HAWQ
          Issue Type: Improvement
          Components: PXF
            Reporter: Jon Roberts
            Assignee: Ed Espino


1) Security
The example has the username and password in the connection string.  

LOCATION ('pxf://localhost:51200/demodb.myclass'
              '?PROFILE=JDBC'
              '&JDBC_DRIVER=com.mysql.jdbc.Driver'
              '&DB_URL=jdbc:mysql://192.168.200.6:3306/demodb&USER=root&PASS=root'
              )

This creates  security issue because anyone that can connect to the database will be able to see the username and password of the JDBC connection.

I suggest changing the URL to a connection profile that points to a file outside of the database.  For Greenplum database and S3, the LOCATION syntax includes "config=/path/to/config_file".  The config_file contains the S3 credentials.  This seems like a good pattern to follow here too.

2) Extra Properties
Some JDBC drivers will need many additional properties beyond the URL and this requires setting it with a put to a Properties variable.  An example of this is Oracle's defaultRowPrefetch property that needs to be updated from the default of 10 which is designed for OLTP to something larger like 2000 which is more ideal for data extracts.  

Additionally, you will need the ability to set the isolation level which is done with setTransactionIsolation on the Connection.  I don't believe you can set this on the connection URL either.  Many SQL Server and DB2 database still don't use snapshot isolation and use dirty reads instead to prevent blocking locks.  The configuration file I suggested above will need an "extra properties" variable that is a delimited list of key/value pairs so you can add multiple extra properties.

3) Max Queries
The external table definition doesn't limit how many concurrent queries can be executed on the remote server.  It would be pretty simple to create a single external table using PXF JDBC that would issue thousands of concurrent queries to a single source database when doing a single SELECT in HAWQ.

Initially, we should add a max_queries variable to the configuration file that I'm suggesting, that will reject queries from proceeding when a greater number of PXF instances are being requested than the max_queries variable.  Longer term, we should implement a queueing system so we can support external tables that partitions data from the source at a very small grain but without killing the source database.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)