SNOW-1689931: Snowflake JDBC Driver does not allow using the SPCS /snowflake/session/token due to its permissions being globally read-write.

[sfc-gh-dflippo]

In the latest version of the JDBC driver, users are unable to utilize a Snowpark Container Services OAuth token file because it has more than user read/write permissions.

I am one of our Principal Solutions Architects in Snowflake PS and I am building a container for one of my RSA customers. In my SPCS container I am leveraging the following connections.toml.template:

[default]
authenticator = "oauth"
token_file_path = "/snowflake/session/token"
account = "${SNOWFLAKE_ACCOUNT}"
host = "${SNOWFLAKE_HOST}"
database = "${SNOWFLAKE_DATABASE}"
schema = "${SNOWFLAKE_SCHEMA}"

During the container startup, I am rendering this using

envsubst < /connections.toml.template > ~/.snowflake/connections.toml
chmod 0600 ~/.snowflake/*

This works fine as intended until my application tries to connect using the jdbc:snowflake:auto URL that leverages my connections.toml file. The Java Spring application provides these messages:

OHDSI_TOOLS/0 2024-09-24 14:04:54.366 WARN http-nio-8080-exec-4 net.snowflake.client.log.JDK14Logger - [] - Inconsistent host and account values in file configuration. ACCOUNT: {0} , HOST: {1}. The host value will be used.
OHDSI_TOOLS/0 2024-09-24 14:04:54.371 ERROR http-nio-8080-exec-4 net.snowflake.client.log.JDK14Logger - [] - Reading from file %s is not safe because file permissions are different than read/write for user
OHDSI_TOOLS/0 2024-09-24 14:04:54.567 WARN http-nio-8080-exec-6 net.snowflake.client.log.JDK14Logger - [] - Inconsistent host and account values in file configuration. ACCOUNT: {0} , HOST: {1}. The host value will be used.
OHDSI_TOOLS/0 2024-09-24 14:04:54.567 ERROR http-nio-8080-exec-6 net.snowflake.client.log.JDK14Logger - [] - Reading from file %s is not safe because file permissions are different than read/write for user
OHDSI_TOOLS/0 2024-09-24 14:04:54.569 INFO http-nio-8080-exec-6 com.odysseusinc.logging.LoggingService - [] - Could not get JDBC Connection; nested exception is net.snowflake.client.jdbc.SnowflakeSQLException: There is a problem during reading token from file

This is occurring because these are the permissions on the /snowflake/session/token file in a Snowpark container:

> ls -al "/snowflake/session/token"
lrwxrwxrwx. 1 root root 12 Sep 24 14:12 /snowflake/session/token -> ..data/token

The JDBC code checks whether the oauth token file has a 0600 chmod. The following code calls the verifyFilePermissionSecure() method at lines 111-121 of SFConnectionConfigParser.java:

        try {
          verifyFilePermissionSecure(path);
          String token = new String(Files.readAllBytes(path), Charset.defaultCharset());
          if (!token.isEmpty()) {
            putPropertyIfNotNull(conectionProperties, "token", token.trim());
          } else {
            logger.warn("The token has empty value");
          }
        } catch (Exception ex) {
          throw new SnowflakeSQLException(ex, "There is a problem during reading token from file");
        }

https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/config/SFConnectionConfigParser.java

All Snowflake drivers should ignore the token permissions if the token file is /snowflake/session/token. The driver at the lines above should skip calling verifyFilePermissionSecure() when the token is /snowflake/session/token.

Additionally, in my opinion, it would be ideal if the jdbc:snowflake:auto URL would automatically use the token and the SNOWFLAKE_ACCOUNT, SNOWFLAKE_HOST, SNOWFLAKE_DATABASE, and SNOWFLAKE_SCHEMA environment variables provided by SPCS whenever the values are not provided in a connections.toml. That would make the use of SPCS OAuth trivial in many packaged java applications and users would not need to use envsubst to populate the connections.toml file at runtime as I currently do.

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   3.19.0

2. What operating system and processor architecture are you using?
   Snowpark Container running an Ubuntu-based container image
   FROM --platform=linux/amd64 eclipse-temurin:8-jre

3. What version of Java are you using?
   Java 1.8

4. What did you do?

See above. I can provide the exact container if you Slack me internally.

5. What did you expect to see?

See above. The JDBC driver should have allowed opening the connection using the SPCS token file.

6. Can you set logging to DEBUG and collect the logs?

I believe the logs above should be sufficient but I can if necessary try to get the DEBUG

7. What is your Snowflake account identifier, if any?
   AWS_CAS2 (Snowflake Professional Services)

[sfc-gh-dszmolka]

(handling internally)