CODEWITHSUNDEEP
pythonamazon-web-servicessshsqlalchemyssh-tunnel
Sean
Sean

Reputation: 670

SSH Tunnel from CI box Thru Bastion to Redshift

I've got a redshift instance that is accessible by a bastion host.

I'm trying to trigger a circle CI job that alters a db schema to be renamed to include a git release tag version. We want this to be triggered when we create the git release targeted against master.

The CI workflow does the following:

1) Authenticate with AWS

2) Run python script that creates a security group, attaches it to a bastion host, and configures ingress rules to allow the CI box's IP to be whitelisted as a cidr.

3) Open an ssh tunnel to forward sqlalchemy commands thru the bastion to the redshift host.

4) Run another python script that alters the db schema

The first two steps are not having any problems. The problem seems to be occurring with the ssh tunnel.

ssh tunnel running in background and calling python script: ssh -o StrictHostKeyChecking=no -N -L 127.0.0.1:5439:$PRODUCTION_REDSHIFT_DNS:5439 -i /home/circleci/.ssh/id_rsa_d88b40ac655a406a2c26ef75202fc144 circle_ci@$PRODUCTION_BASTION_DNS & python3 archive_schema.py

$PROD_REDSHIFT_CONNECTION_STRING: redshift+psycopg2://$USER:$PASS@localhost:5439/$DB_NAME

I've got a simple python script that prints the first table name in the database and exits. I ran it in the bastion successfully with the same connection string, but when I run it after setting up the ssh tunnel in the background things blow up.

import os
from sqlalchemy import create_engine, inspect

engine = create_engine(os.getenv("PROD_REDSHIFT_CONNECTION_STRING"))
inspector = inspect(engine)
for table_name in inspector.get_table_names():
    print(table_name)
    break

Any help would be greatly greatly appreciated. Here's the error that I get when running the script from circle CI:

Traceback (most recent call last):
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 154, in reraise
    raise value
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 451, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5439?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5439?
could not connect to server: Cannot assign requested address
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5439?


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "z.py", line 7, in <module>
    inspector = inspect(engine)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/inspection.py", line 63, in inspect
    ret = reg(subject)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 141, in _insp
    return Inspector.from_engine(bind)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 136, in from_engine
    return bind.dialect.inspector(bind)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2209, in __init__
    reflection.Inspector.__init__(self, conn)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 111, in __init__
    bind.connect().close()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2193, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2293, in raw_connection
    self.pool.unique_connection, _connection
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2266, in _wrap_pool_connect
    e, dialect, self
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1536, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value.with_traceback(tb)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 154, in reraise
    raise value
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 451, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/circleci/repo/venv/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5439?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5439?
could not connect to server: Cannot assign requested address
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5439?

(Background on this error at: http://sqlalche.me/e/e3q8)
Exited with code 1

Upvotes: 1

Views: 392

Answers (1)

Haider Jafri
Haider Jafri

Reputation: 66

This could be happening because the first command has not established the tunnel when the second command was ran. You should avoid using & and use -f in your ssh tunnel command.

Upvotes: 1

Related Questions