DeepLcom
Can we access Snowflake using a private key and passphrase instead of a password?
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?
@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
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)
@BFASRachelShttps://github.com/BFASRachelS the Python library seems to supporthttps://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 settingshttps://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 herehttps://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 GitHubhttps://github.com/DeepLcom/sql-mock/issues/59#issuecomment-2689874163, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKJQCFDD7ECGPPGGZXIKJJD2SABS5AVCNFSM6AAAAABYA6U5AKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBZHA3TIMJWGM. You are receiving this because you were mentioned.Message ID: @.***>
[Somtom]Somtom left a comment (DeepLcom/sql-mock#59)https://github.com/DeepLcom/sql-mock/issues/59#issuecomment-2689874163
@BFASRachelShttps://github.com/BFASRachelS the Python library seems to supporthttps://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 settingshttps://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 herehttps://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 GitHubhttps://github.com/DeepLcom/sql-mock/issues/59#issuecomment-2689874163, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKJQCFDD7ECGPPGGZXIKJJD2SABS5AVCNFSM6AAAAABYA6U5AKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMOBZHA3TIMJWGM. You are receiving this because you were mentioned.Message ID: @.***>