SSH Tunnel with copilot

[doneumark]

I would like to know if there's a way to achieve SSH tunnel from a local machine. Im using (very enjoyably, if I may add) copilot, to manage my RDS storage and a load balanced web service (who, of course, talk to each other). I would like to write queries to my database locally (through the console, or by pgAdmin). How would I achieve a tunnel to connect through the database and my localhost? should I use another EC2 instance? or use any configuration that was already created by copilot? Thanks in advance!

[efekarakus]

Hi @doneumark !

How would I achieve a tunnel to connect through the database and my localhost? should I use another EC2 instance?

Like you pointed out one way would be to spin up an EC2 instance using the same security groups as the service and subnets. This guide might help: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html

or use any configuration that was already created by copilot?

I managed to achieve connecting to the database by chaining copilot task run and copilot task exec which might be an alternative that you can explore.

1. I created the following Dockerfile locally:

   FROM public.ecr.aws/bitnami/postgresql:latest
   ENTRYPOINT ["tail", "-f", "/dev/null"]
   

2. I ran copilot task run to create a task with the Dockerfile above and the database secret:

   $ copilot task run -n dbtester --dockerfile Dockerfile  --secrets DB_SECRET=arn:aws:secretsmanager:us-west-2:111111:secret:wwwclusterAuroraSecret-aaaaaaaaa-aaaaa
   

   Note that I had to give additional permission to the task execution role to be able to read the secret above.
3. I ran copilot task exec to execute into the container:

   $ echo $DB_SECRET
   $ psql --host=<host> --port=5432 --username=postgres --password --dbname=<db name>
   

   And I was able to make queries against the database.

Hope this helps! It'd be cool if Copilot could provide a nice shortcut for these commands to quickly connect to the database though.

[dmathewwws]

Hey @efekarakus,

If im understanding what you did, you created a task which had psql installed and so you were able to connect to Postgres + and run SQL queries.

I can't speak for @doneumark, but for my use case (which sounded similar), I was able to do what you suggested but what I really would like is access to the visual GUI provided by pgAdmin (or Postico). ie) in the image below I know everything contained in the red box as it is my database info. but I dont know how to tunnel into a Server which has access to that Postgres server and what I believe @doneumark was saying, is that you can create an EC2 instance (which will have some IP access that can I can use to fill in that SSH tunnelling details).

My question is, is there anyway we can get the information needed from copilot task run / exec to populate the information in the blue box?

[Lou1415926]

@dmathewwws Hi 👋🏼 Thank you for the clarification!

I believe the solution that Efe suggested above would be another way to access the db, alternative to SSH tunneling. Unfortunately the ECS tasks spun up doesn't have work for SSH tunneling😔.

[matthewhembree]

Easy/integrated port forwarding over the SSM channel would be nice. Something along the lines of this: https://stackoverflow.com/a/67641633

Edit: And a way to disable it via the manifest. Similar or exactly as exec: false.

[github-actions[bot]]

This issue is stale because it has been open 60 days with no response activity. Remove the stale label, add a comment, or this will be closed in 14 days.

[github-actions[bot]]

This issue is closed due to inactivity. Feel free to reopen the issue if you have any further questions!

[iamhopaul123]

Related to https://github.com/aws/copilot-cli/discussions/5263.