How do I connect to a Spinup PostgreSQL database?

Owned by Tenyo Grozev (Unlicensed)
Last updated: Dec 01, 2023 by Kraig Eisenman
2 min read

Introduction:

This guide provides detailed instructions on how to establish a connection to a PostgreSQL database in a Spinup environment. We primarily focus on using the official psql client for initial connections.

Initial Connection Using psql Client

  1. Ensure the psql client is installed on your machine.

  2. To connect, type the following command in your terminal:

psql -h [DATABASE_URL] -U [USERNAME] [DATABASE_NAME]

  • Replace [DATABASE_URL] with your database's URL, [USERNAME] with your username, and [DATABASE_NAME] with the name of your database.

  • Example: psql -h spinup-db0005a1.cyynsscieqtk.us-east-1.rds.amazonaws.com -U root postgres

After executing the command, you will be prompted to enter your password. This is the password set during the database creation, which you can reset if necessary. Once connected, you have full administrative rights to create, modify, or delete tables and data.

Note: Always use SSL encryption when connecting. The psql client enables this by default, but for other applications, you may need to enable encryption explicitly.

Connecting from Secure Spinup Spaces

Secure Spinup spaces, which may contain moderate to high-risk data, are protected by a firewall that restricts direct database connections from external networks. You must connect from a server within the same Spinup space.

Establishing an SSH Tunnel for External Connections

If you need to connect to the database from outside the space (e.g., for data loading), use an SSH tunnel. Ensure you have a server in the space where the database instance is located.

  1. Create an SSH Tunnel:

    • Use the following command:

      ssh -L [LOCAL_PORT]:[REMOTE_DB_URL]:5432 [SPINUP_SERVER]

      • Replace [LOCAL_PORT] with a local port number (e.g., 5433), [REMOTE_DB_URL] with your database instance's URL, and [SPINUP_SERVER] with your Spinup server's address.

      • Example: ssh -L 5433:spinup-db000b67.cyynsscieqtk.us-east-1.rds.amazonaws.com:5432 spinup-000b65.spinup.yale.edu

    • Complete the DUO authentication as prompted to log into the Spinup server.

  2. Connect to the Database via the Tunnel:

    • In a separate terminal window, use the following command:

      psql -h 127.0.0.1 -p [LOCAL_PORT] -U [USERNAME] postgres

      • Replace [LOCAL_PORT] with the local port number used in the SSH tunnel (e.g., 5433) and [USERNAME] with your database username.

    • This command connects to the remote database through the SSH tunnel using the specified local port.