Can we access Snowflake using a private key and passphrase instead of a password?

[BFASRachelS]

Your docs say the user and password need to be set as environment variables.

SQL_MOCK_SNOWFLAKE_ACCOUNT: Your snowflake account
SQL_MOCK_SNOWFLAKE_USER: The name of your Snowflake user
SQL_MOCK_SNOWFLAKE_PASSWORD: The password for your Snowflake user

https://github.com/DeepLcom/sql-mock/blob/main/docs/snowflake.md

I am using key-pair authentication to access Snowflake. Does sql-mock support this?

[Somtom]

@BFASRachelS the Python library seems to support it so it should be doable. Right now, the library only uses the authentication via the env variables you linked above.

It should be pretty straight forward to support it. Without having tested the code, it could be something like this:

(1) Adjust the settings to accept the other auth values (and add some validation).

class SnowflakeSettings(BaseSettings):
    model_config = SettingsConfigDict(env_prefix="SQL_MOCK_SNOWFLAKE_")
    account: str
    user: str
    password: Optional[str]
    private_key_file: Optional[str]
    private_key_file_pwd: Optional[str]

    @root_validator
    def validate_settings(cls, values):
        password_present = values.get('password') is not None
        private_key_file_present = values.get('private_key_file') is not None
        private_key_file_pwd_present = values.get('private_key_file_pwd') is not None
        
        # Check if either password auth or key-pair auth is present
        if (private_key_file_present and private_key_file_pwd_present) or password_present:
            return values
        
        raise ValueError("Either both private_key_file and private_key_file_pwd must be set, or password must be set")
    
    @computed_field
    def auth_args(self):
        if self.password:
            return {
                'user': self.user,
                'password': self.password
            }
        else:
            return {
                'user': self.user,
                'private_key_file': self.private_key_file,
                'private_key_file_pwd': self.private_key_file_pwd
            }

(2) Add some logic here to see which settings are present and choose the auth method accordingly

class SnowflakeTableMock(BaseTableMock):
    _sql_dialect = "snowflake"

    def __init__(
        self,
        *args,
        **kwargs,
    ):
        self.settings = SnowflakeSettings()
        super().__init__(*args, **kwargs)

    def _get_results(self, query: str) -> list[dict]:
        with connect(
            user=self.settings.user,
            **self.settings.auth_args,
        ) as conn:
            with conn.cursor(DictCursor) as cur:
                cur.execute(query)
                return cur.fetchall()

In case you feel curious, feel free to give it a try and create a PR. If you have the key-pair setup running for your Snowflake, then it should be quick to test.

The new env vars would also take the SQL_MOCK_SNOWFLAKE_ prefix:

• SQL_MOCK_SNOWFLAKE_PRIVATE_KEY_FILE
• SQL_MOCK_SNOWFLAKE_PRIVATE_KEY_FILE_PWD

[BFASRachelS]

Thanks. I will definitely give that a try.

…

________________________________ From: Thomas Schmidt ***@***.***> Sent: Thursday, February 27, 2025 10:52 PM To: DeepLcom/sql-mock ***@***.***> Cc: Rachel Scanlon ***@***.***>; Mention ***@***.***> Subject: Re: [DeepLcom/sql-mock] Can we access Snowflake using a private key and passphrase instead of a password? (Issue #59) @BFASRachelS<https://github.com/BFASRachelS> the Python library seems to support<https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect#label-python-key-pair-authn-rotation> it so it should be doable. Right now, the library only uses the authentication via the env variables you linked above. It should be pretty straight forward to support it. Without having tested the code, it could be something like this: (1) Adjust the settings<https://github.com/DeepLcom/sql-mock/blob/main/src/sql_mock/snowflake/settings.py> to accept the other auth values (and add some validation). class SnowflakeSettings(BaseSettings): model_config = SettingsConfigDict(env_prefix="SQL_MOCK_SNOWFLAKE_") account: str user: str password: Optional[str] private_key_file: Optional[str] private_key_file_pwd: Optional[str] @root_validator def validate_settings(cls, values): password_present = values.get('password') is not None private_key_file_present = values.get('private_key_file') is not None private_key_file_pwd_present = values.get('private_key_file_pwd') is not None # Check if either password auth or key-pair auth is present if (private_key_file_present and private_key_file_pwd_present) or password_present: return values raise ValueError("Either both private_key_file and private_key_file_pwd must be set, or password must be set") @computed_field def auth_args(self): if self.password: return { 'user': self.user, 'password': self.password } else: return { 'user': self.user, 'private_key_file': self.private_key_file, 'private_key_file_pwd': self.private_key_file_pwd } (2) Add some logic here<https://github.com/DeepLcom/sql-mock/blob/main/src/sql_mock/snowflake/table_mocks.py#L19-L23> to see which settings are present and choose the auth method accordingly class SnowflakeTableMock(BaseTableMock): _sql_dialect = "snowflake" def __init__( self, *args, **kwargs, ): self.settings = SnowflakeSettings() super().__init__(*args, **kwargs) def _get_results(self, query: str) -> list[dict]: with connect( user=self.settings.user, **self.settings.auth_args, ) as conn: with conn.cursor(DictCursor) as cur: cur.execute(query) return cur.fetchall() In case you feel curious, feel free to give it a try and create a PR. If you have the key-pair setup running for your Snowflake, then it should be quick to test. — Reply to this email directly, view it on GitHub<#59 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKJQCFDD7ECGPPGGZXIKJJD2SABS5AVCNFSM6AAAAABYA6U5AKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBZHA3TIMJWGM>. You are receiving this because you were mentioned.Message ID: ***@***.***> [Somtom]Somtom left a comment (DeepL/sql-mock#59)<#59 (comment)> @BFASRachelS<https://github.com/BFASRachelS> the Python library seems to support<https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect#label-python-key-pair-authn-rotation> it so it should be doable. Right now, the library only uses the authentication via the env variables you linked above. It should be pretty straight forward to support it. Without having tested the code, it could be something like this: (1) Adjust the settings<https://github.com/DeepLcom/sql-mock/blob/main/src/sql_mock/snowflake/settings.py> to accept the other auth values (and add some validation). class SnowflakeSettings(BaseSettings): model_config = SettingsConfigDict(env_prefix="SQL_MOCK_SNOWFLAKE_") account: str user: str password: Optional[str] private_key_file: Optional[str] private_key_file_pwd: Optional[str] @root_validator def validate_settings(cls, values): password_present = values.get('password') is not None private_key_file_present = values.get('private_key_file') is not None private_key_file_pwd_present = values.get('private_key_file_pwd') is not None # Check if either password auth or key-pair auth is present if (private_key_file_present and private_key_file_pwd_present) or password_present: return values raise ValueError("Either both private_key_file and private_key_file_pwd must be set, or password must be set") @computed_field def auth_args(self): if self.password: return { 'user': self.user, 'password': self.password } else: return { 'user': self.user, 'private_key_file': self.private_key_file, 'private_key_file_pwd': self.private_key_file_pwd } (2) Add some logic here<https://github.com/DeepLcom/sql-mock/blob/main/src/sql_mock/snowflake/table_mocks.py#L19-L23> to see which settings are present and choose the auth method accordingly class SnowflakeTableMock(BaseTableMock): _sql_dialect = "snowflake" def __init__( self, *args, **kwargs, ): self.settings = SnowflakeSettings() super().__init__(*args, **kwargs) def _get_results(self, query: str) -> list[dict]: with connect( user=self.settings.user, **self.settings.auth_args, ) as conn: with conn.cursor(DictCursor) as cur: cur.execute(query) return cur.fetchall() In case you feel curious, feel free to give it a try and create a PR. If you have the key-pair setup running for your Snowflake, then it should be quick to test. — Reply to this email directly, view it on GitHub<#59 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKJQCFDD7ECGPPGGZXIKJJD2SABS5AVCNFSM6AAAAABYA6U5AKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBZHA3TIMJWGM>. You are receiving this because you were mentioned.Message ID: ***@***.***>