How do I connect to a Spinup PostgreSQL database?

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.

E.g. the connection may look like this:
  psql -h spinup-db0005a1.cyynsscieqtk.us-east-1.rds.amazonaws.com -U root postgres

After clicking enter, you will be asked for the password (the one given when creating the database - which you can reset at any time).

That's it! - At this point 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 encryption.

Connecting to a Postgres database in a secure Spinup space

Spinup spaces that may contain moderate or high risk data have a firewall that only allows several administrative ports and HTTPS, thus it's not possible to connect to a database in such a secure space from your laptop or other computers on the Yale network. You can only connect from servers within the same space. In some cases it may be necessary to connect to the database from outside the space, e.g. to load data. This is possible by establishing an SSH tunnel to a server in that space (note that you need to have a server in the space where the database instance is) and then connecting from your computer to the database via the tunnel.

Here's an example using the ssh client (should work on MacOS and Linux):

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

 This will prompt you for DUO authentication and should log you into the Spinup server. Open a separate terminal window and connect to the database:

  psql -h 127.0.0.1 -p 5433 -U root postgres

This will use the local port 5433 to connect to the database instance at spinup-db000b67.cyynsscieqtk.us-east-1.rds.amazonaws.com via the server spinup-000b65.spinup.yale.edu.