Cannot mount Snowflake as a data source behind a proxy

[mapshen]

We are evaluating splitgraph in our corporate env, where we have to go through a proxy to reach Snowflake.

When we do

sgr --version
sgr, version 0.2.10

sgr -v DEBUG mount snowflake test_snowflake -o@- <<EOF
heredoc> {
heredoc>     "username": "XXX",
heredoc>     "password": "XXX",
heredoc>     "account": "XXX",
heredoc>     "database": "XXX",
heredoc>     "schema": "XXX"
heredoc> }
heredoc> EOF

we get the following error

error: Traceback (most recent call last):
error:   File "/home/XXX/python3.7/site-packages/splitgraph/commandline/__init__.py", line 116, in invoke
error:     result = super(click.Group, self).invoke(ctx)
error:   File "/home/XXX/python3.7/site-packages/click/core.py", line 1259, in invoke
error:     return _process_result(sub_ctx.command.invoke(sub_ctx))
error:   File "/home/XXX/python3.7/site-packages/click/core.py", line 1259, in invoke
error:     return _process_result(sub_ctx.command.invoke(sub_ctx))
error:   File "/home/XXX/python3.7/site-packages/click/core.py", line 1066, in invoke
error:     return ctx.invoke(self.callback, **ctx.params)
error:   File "/home/XXX/python3.7/site-packages/click/core.py", line 610, in invoke
error:     return callback(*args, **kwargs)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/commandline/mount.py", line 69, in _callback
error:     mount(schema, mount_handler=handler_name, handler_kwargs=handler_options)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/hooks/mount_handlers.py", line 69, in mount
error:     source.mount(schema=mountpoint, overwrite=overwrite, tables=tables)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 134, in mount
error:     self._create_foreign_tables(schema, server_id, tables)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 144, in _create_foreign_tables
error:     _import_foreign_schema(self.engine, schema, remote_schema, server_id, tables)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 299, in _import_foreign_schema
error:     engine.run_sql(query)
error:   File "/home/XXX/python3.7/site-packages/splitgraph/engine/postgres/engine.py", line 501, in run_sql
error:     cur.execute(statement, _convert_vals(arguments) if arguments else None)
error: psycopg2.errors.InternalError_: Error in python: OperationalError
error: DETAIL:  (snowflake.connector.errors.OperationalError) 250003: Failed to execute request: HTTPSConnectionPool(host='XXX.snowflakecomputing.com', port=443): Max retries exceeded with url: /session/v1/login-request?request_id=XXX&databaseName=XXX&schemaName=XXX&request_guid=XXX(Caused by ConnectTimeoutError(<snowflake.connector.vendored.urllib3.connection.HTTPSConnection object at 0x7f31c8e0df98>, 'Connection to XXX.snowflakecomputing.com timed out. (connect timeout=60)'))
error: (Background on this error at: http://sqlalche.me/e/14/e3q8)

It seems although the snowflake connector Splitgraph uses supports proxy settings via HTTPS_PROXY, HTTP_PROXY and NO_PROXY [1], but it psycopg2 doesn't pass them over while creating new servers [2]?

[1] https://github.com/splitgraph/snowflake-sqlalchemy#using-a-proxy-server
[2] https://github.com/splitgraph/splitgraph/blob/3cc20ef9021c153344cb0e52247dcc9162812d50/splitgraph/hooks/data_source/fdw.py#L259

[mildbyte]

Thanks for noticing this! I added a parameter to the data source that can be used to inject arbitrary envvars on the engine side when running the query. Testing (don't have a proxy handy but can verify it fails to connect, though it does take some time to fail, could be some retry logic in snowflake-connector-python):

$ sgr mount snowflake test_snowflake -o@- <<EOF
{
    "username": "uname",
    "password": "pass"
    "account": "acc",
    "database": "SNOWFLAKE_SAMPLE_DATA",
    "schema": "TPCH_SF100",
    "envvars": {"HTTPS_PROXY": "https://127.0.0.1"}
}
EOF
error: psycopg2.errors.InternalError_: Error in python: OperationalError
error: DETAIL:  (snowflake.connector.errors.OperationalError) 250003: 250003: Failed to execute request: HTTPSConnectionPool(host='wp71792.west-europe.azure.snowflakecomputing.com', port=443): Max retries exceeded with url: /session/v1/login-request?request_id=019f2a01-f306-4446-b224-ab28481ab1b6&databaseName=SNOWFLAKE_SAMPLE_DATA&schemaName=TPCH_SF100&request_guid=0e7a9b9a-2aae-4648-919f-4e2f40f1d6f7 (Caused by ProxyError('Cannot connect to proxy.', NewConnectionError('<snowflake.connector.vendored.urllib3.connection.HTTPSConnection object at 0x7f8394940f28>: Failed to establish a new connection: [Errno 111] Connection refused')))
error: (Background on this error at: http://sqlalche.me/e/14/e3q8)

[mapshen]

@mildbyte thanks for the quick response. Much appreciated.

Looking at the errors, I realized I am running sgr on my localhost but the connection to snowflake is issued from my splitgraph engine, which is running inside a container that doesn't have proxy settings configured because I created it with sgr engine add.

So I worked around this by starting up our split engine with HTTPS_PROXY, HTTP_PROXY and NO_PROXY passed in

docker run -d \
    -e POSTGRES_PASSWORD=supersecure \
    -e POSTGRES_USER=sgr \
    -e POSTGRES_DB=splitgraph \
    -e SG_CONFIG_FILE=/.sgconfig \
    -e HTTPS_PROXY=http://proxy-host:9400 \
    -e HTTP_PROXY=http://proxy-host:9400 \
    -e NO_PROXY=localhost \
    -p 5432:5432 \
    -v $PWD/splitgraph_data:/var/lib/splitgraph/objects \
    -v $PWD/splitgraph_metadata:/var/lib/postgresql/data \
    -v $PWD/.splitgraph/.sgconfig:/.sgconfig \
    --name splitgraph_engine_default \
    splitgraph/engine:0.2.10

Back to this issue, would allowing sgr engine add to accept env vars a better solution?

[mildbyte]

It's also an option. The difference is, on splitgraph.com/Splitgraph Cloud we manage these engines separately from connectors (e.g. one engine might end up handling several queries, powered by different connectors), so we can't set the envvars there globally, since we might not know them at the engine startup and we don't want to change them in the context of the whole engine.

[mapshen]

That makes sense.

On a side note, would we consider supporting key pair auth [1]? Password auth is frowned upon inside the firm.

[1] https://github.com/splitgraph/snowflake-sqlalchemy#key-pair-authentication-support

[mildbyte]

@mapshen It's definitely possible. We could pass this arg to the adapter:

    connect_args={
        'private_key': pkb,
        },
    )

which would also mean other SQLAlchemy adapters would be able to use connect_args (though we normally use another route for data sources to pass credentials: on the command line they're merged together, but in the cloud they're stored in a separate storage and treated as a secret: https://github.com/splitgraph/splitgraph/blob/master/splitgraph/ingestion/snowflake/__init__.py#L9-L20).

The only issue in this case is that you'd need to pass your passwordless private key as a connection param on the command line, rather than point sgr mount to it.

[mildbyte]

(PR autoclosed; will keep open for the private_key functionality)

[mapshen]

The only issue in this case is that you'd need to pass your passwordless private key as a connection param on the command line, rather than point sgr mount to it.

There seems no better way based on the current design. Nevertheless, we don't have to send the key over the public internet as we do with the password.

[mildbyte]

@mapshen Privkey support is now available:

sgr mount snowflake test_snowflake -o@- <<EOF
{
    "username": "uname",
    "private_key": "MIIEvQIBADANBgkq...",      <-- can also use the standard ===== BEGIN PRIVATE KEY ===== ... format
    "account": "acc",
    "database": "SNOWFLAKE_SAMPLE_DATA",
    "schema": "TPCH_SF100"
}
EOF

[mapshen]

@mildbyte not sure if this is a regression caused by ef427f6, but when I run

sgr -v DEBUG mount snowflake test_snowflake -o@- <<EOF
heredoc> {
heredoc>     "username": "XXX",
heredoc>     "password": "XXX",
heredoc>     "account": "XXX",
heredoc>     "database": "XXX",
heredoc>     "schema": "XXX"
heredoc> }
heredoc> EOF

in 0.2.11, i get the following error:

error: Traceback (most recent call last):
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/commandline/__init__.py", line 116, in invoke
error:     result = super(click.Group, self).invoke(ctx)
error:   File "/XXX/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
error:     return _process_result(sub_ctx.command.invoke(sub_ctx))
error:   File "/XXX/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
error:     return _process_result(sub_ctx.command.invoke(sub_ctx))
error:   File "/XXX/lib/python3.7/site-packages/click/core.py", line 1066, in invoke
error:     return ctx.invoke(self.callback, **ctx.params)
error:   File "/XXX/lib/python3.7/site-packages/click/core.py", line 610, in invoke
error:     return callback(*args, **kwargs)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/commandline/mount.py", line 69, in _callback
error:     mount(schema, mount_handler=handler_name, handler_kwargs=handler_options)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/hooks/mount_handlers.py", line 69, in mount
error:     source.mount(schema=mountpoint, overwrite=overwrite, tables=tables)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 134, in mount
error:     self._create_foreign_tables(schema, server_id, tables)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 144, in _create_foreign_tables
error:     _import_foreign_schema(self.engine, schema, remote_schema, server_id, tables)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/hooks/data_source/fdw.py", line 299, in _import_foreign_schema
error:     engine.run_sql(query)
error:   File "/XXX/lib/python3.7/site-packages/splitgraph/engine/postgres/engine.py", line 507, in run_sql
error:     cur.execute(statement, _convert_vals(arguments) if arguments else None)
error: psycopg2.errors.InternalError_: Error in python: TypeError
error: DETAIL:  'NoneType' object is not iterable
error: