Snowflake integration with c3p0 fails when private key is set in properties

[coded9]

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.StringReader;
import java.security.PrivateKey;
import java.security.Provider;
import java.security.Security;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.bouncycastle.asn1.pkcs.PrivateKeyInfo;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.openssl.PEMParser;
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter;
import org.bouncycastle.openssl.jcajce.JceOpenSSLPKCS8DecryptorProviderBuilder;
import org.bouncycastle.operator.InputDecryptorProvider;
import org.bouncycastle.operator.OperatorCreationException;
import org.bouncycastle.pkcs.PKCS8EncryptedPrivateKeyInfo;
import org.bouncycastle.pkcs.PKCSException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TestSnowflake {
	public static void main(String[] args) throws SQLException, PropertyVetoException{
		  // get connection
	    System.out.println("Create JDBC connection");
	    Connection connection = getConnection();
	    System.out.println("Done creating JDBC connectionn");
	    
        }
	   private static Connection getConnection()
	          throws SQLException, PropertyVetoException
	  {
	    try
	    {
	      Class.forName("com.snowflake.client.jdbc.SnowflakeDriver");
	    }
	    catch (ClassNotFoundException ex)
	    {
	     System.err.println("Driver not found");
	    }
	    // build connection properties
	    Properties properties = new Properties();
	    properties.put("user", "test_user");     
	  
	    // create a new connection
	    String privateKeyStr = "-----BEGIN ENCRYPTED PRIVATE KEY-----\r\n" + 
	    		"7g==\r\n" + 
	    		"-----END ENCRYPTED PRIVATE KEY-----\r\n" + 
	    		"".replaceAll(System.lineSeparator(), "");
	    PrivateKeyInfo privateKeyInfo = null;
	    Provider provider = new BouncyCastleProvider();
	    if (Security.getProvider(BouncyCastleProvider.PROVIDER_NAME) == null) {
	        Security.addProvider(new BouncyCastleProvider());
	    }
	    PEMParser pemParser = new PEMParser(new StringReader(privateKeyStr));
	    PrivateKey privateKey = null;
	    try {
			Object pemObject = pemParser.readObject();
			 if (pemObject instanceof PKCS8EncryptedPrivateKeyInfo) {
			        // Handle the case where the private key is encrypted.
			        PKCS8EncryptedPrivateKeyInfo encryptedPrivateKeyInfo = (PKCS8EncryptedPrivateKeyInfo) pemObject;
			        String passphrase = "snowflake123";
			        InputDecryptorProvider pkcs8Prov = new JceOpenSSLPKCS8DecryptorProviderBuilder().setProvider(new BouncyCastleProvider()).build(passphrase.toCharArray());
			        privateKeyInfo = encryptedPrivateKeyInfo.decryptPrivateKeyInfo(pkcs8Prov);
			      } else if (pemObject instanceof PrivateKeyInfo) {
			        // Handle the case where the private key is unencrypted.
			        privateKeyInfo = (PrivateKeyInfo) pemObject;
			      }
			      pemParser.close();
			      JcaPEMKeyConverter converter = new JcaPEMKeyConverter().setProvider(BouncyCastleProvider.PROVIDER_NAME);
			      privateKey = converter.getPrivateKey(privateKeyInfo);
		} catch (IOException | PKCSException | OperatorCreationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	    properties.put("privateKey", privateKey);
	  
	     connectStr = "jdbc:snowflake://https://test.us-east-1.snowflakecomputing.com/?db=kkkk&schema=hhhh&warehouse=wh"; 
	    }
	    ComboPooledDataSource cpds = new ComboPooledDataSource();
	    cpds.setProperties(properties);
	    cpds.setJdbcUrl(connectStr);
	    cpds.setDriverClass("com.snowflake.client.jdbc.SnowflakeDriver");
	    return cpds.getConnection();
	  }
}

> The above code fails with Nullpointer function, upon debugging found that C3p0 doesn't support authentication properties other than username and password.

Is there any workaround to support PrivateKey object?

[seargentTony]

Is this solved? I was also looking for implementation of c3p0 for connection pooling on snowflake db.

[swaldman]

The properties object is passed to the driver. c3p0 does not in any way restrict authentication info to user and password. I haven't tried connecting to Snowflake DB. I hope to see if I can.