Snowflake Go - DEFAULT_ROLE behavior is different for ADBC vs. ODBC

[davidhcoe]

What feature or improvement would you like to see?

In our testing, we have found that the user has to specify the role they are in order to have successful queries to Snowflake using the Go driver. According to https://www.snowflake.com/en/blog/using-sso-between-power-bi-and-snowflake/, with ODBC, the role for the user will be DEFAULT_ROLE, if it has been set, otherwise it will be the PUBLIC role.

Is there a way to enable this capability with ADBC?

[zeroshade]

You should be able to specify the role to use via the adbc.snowflake.sql.role option being provided to the NewDatabase or connect methods, or by using SetOption/set_option on the connection object. Is that sufficient?

[davidhcoe]

Yes, we can specify the role and that works fine. But there doesn't seem to be a way to find the default role and have that work without the user having to specify. In our testing, many times the users don't even know their role. They just know it doesn't work for them.

[zeroshade]

In theory this should be operating identically then, if the DEFAULT_ROLE isn't being used, this likely needs to get upstreamed to the gosnowflake repository since the authentication logic is handled by that. If the role option isn't set then we just use the defaults and let gosnowflake handle it. Could you try connecting to snowflake directly using gosnowflake to confirm that you run into the same issue if you don't set a role there? That will confirm the issue is upstream in there rather than something we're doing wrong here in ADBC.

[davidhcoe]

I was able to confirm the DEFAULT_ROLE behavior behaves the same with ADBC. I created a new test in C# to verify and will be submitting a PR to close this issue.