Page Comparison

Problem

I need to connect to a Spinup PostgreSQL database

Solution

...

There are a number of clients you may use to connect to a Spinup PostgreSQL database (see https://wiki.postgresql.org/wiki/PostgreSQL_Clients).
To test the initial connection we recommend using the official psql client on the command line.

From a machine with the psql client installed, you may just type:
  psql -h DATABASEURL -U USERNAME DATABASENAME

Note that the default database is called postgres - once you connect initially you can create additional ones.

...

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 clicking enterexecuting the command, you will be asked for the password (the one given when creating the database - prompted to enter your password. This is the password set during the database creation, which you can reset at any time).That's it! - At this point if necessary. Once connected, you have full administrator rights on the database and can create or drop tables, add or delete data, etc.Note: When connecting to the database always use SSL encryption. If you use the psql client this is done automatically but when connecting from an application you may need to explicitly enable encryptionadministrative rights to create, modify, or delete tables and data.

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:

     Code Block
     language bash
     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:

     Code Block
     language bash
     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.

Related articles