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.