7 Server Access: SSH, SFTP & PostgreSQL

The CalCOFI server (ssh.calcofi.io, a Google Cloud VM in Iowa) hosts the apps, RStudio Server, ERDDAP — and, since August 2026, a multi-user PostgreSQL database that the CTD team uses for QA/QC: everyone reads and writes the same tables, originals stay immutable, flags and proposed fixes live beside them. This page is the one place that explains how to get in and how to connect, for Mac and Windows.

Two databases, two access paths

The public releases (v2026.08.14 …) are Parquet on a public bucket. No account, no tunnel: Data Access, calcofi4r::cc_get_db(), DuckDB, the browser.
The working PostgreSQL database (calcofi) is private and reachable only over SSH. That is what the rest of this page is about.

7.1 Getting an account

Accounts are personal (no shared logins), key-only (no passwords over SSH), and named after your email local-part — rswalethorp, bmgire, kdvogel, bhuang, esatterthwaite, bebest. Each account gets:

what	where
SSH + SFTP login	`ssh.calcofi.io`, your home directory (small — keep big files under `~/ctd`)
shared CTD folder	`~/ctd` → `/share/data/ctd/{incoming,archive,exports}` (group-writable)
PostgreSQL role	same name as your username, in database `calcofi`; schemas `ctd` (curated), `work` (shared scratch), and your own schema (`rswalethorp.*`)
pgAdmin web login	pgadmin.calcofi.io with your email
RStudio Server login	rstudio.calcofi.io — R in the browser, already on the server (no tunnel needed there)

To request one: send Ben (bebest@ucsd.edu) your SSH public key. Generate it once per computer:

ssh-keygen -t ed25519 -C "you@ucsd.edu"      # accept the default file, set a passphrase
cat ~/.ssh/id_ed25519.pub                      # this ONE line is what you send

Windows 10/11 ships OpenSSH (ssh, ssh-keygen, sftp, scp) — the commands are the same as on Mac. Open PowerShell (not cmd):

ssh-keygen -t ed25519 -C "you@ucsd.edu"      # accept the default file, set a passphrase
Get-Content $env:USERPROFILE\.ssh\id_ed25519.pub   # this ONE line is what you send

If ssh-keygen is not found: Settings ▸ System ▸ Optional features ▸ Add ▸ OpenSSH Client, then reopen PowerShell.

If you already use PuTTY: PuTTYgen ▸ Generate (EdDSA/Ed25519), set a passphrase, Save private key (calcofi.ppk), and copy the text in “Public key for pasting into OpenSSH authorized_keys file” — that is what you send. (A .ppk can also be imported into the built-in OpenSSH with Conversions ▸ Export OpenSSH key.)

Never send the private key (id_ed25519 without .pub). Lost laptop → tell Ben, the key is revoked and you send a new one.

7.2 Step 1 — SSH in

Put the server in your SSH config once, and every tool (ssh, sftp, R, VS Code) can use the short name calcofi. The LocalForward line is the database tunnel (next section).

Create or edit ~/.ssh/config (e.g. nano ~/.ssh/config):

Host calcofi
  HostName ssh.calcofi.io
  User rswalethorp                 # <- your username
  IdentityFile ~/.ssh/id_ed25519
  LocalForward 5432 localhost:5432 # database tunnel (see below)
  ServerAliveInterval 60

Then:

ssh calcofi            # first time: answer "yes" to the host-key prompt

Same file, at C:\Users\<you>\.ssh\config (create it with Notepad; no extension):

Host calcofi
  HostName ssh.calcofi.io
  User rswalethorp
  IdentityFile ~/.ssh/id_ed25519
  LocalForward 5432 localhost:5432
  ServerAliveInterval 60

ssh calcofi

Session: Host Name ssh.calcofi.io, Port 22, Saved Sessions calcofi. Connection ▸ Data: Auto-login username = your username. Connection ▸ SSH ▸ Auth ▸ Credentials: Private key file = your calcofi.ppk. Connection ▸ SSH ▸ Tunnels: Source port 5432, Destination localhost:5432, Add. Back on Session, Save, then Open. Keep the window open while you use the database. (Pageant can hold the key so you type the passphrase once per session.)

You land in your home directory. Useful first commands:

ls -la ~/ctd/          # the shared CTD folder (incoming/ archive/ exports/)
df -h /share           # how much disk is left — it is shared
cat ~/.pgpass          # your database password (Step 2)

VS Code

The Remote – SSH extension reuses the same ~/.ssh/config: Remote-SSH: Connect to Host… ▸ calcofi gives you a file browser, terminal and editor on the server. Handy for looking at files under /share/data/ctd without SFTP.

7.3 Step 2 — your database password

Your PostgreSQL password was generated when the account was created and is stored only in a file in your home directory on the server — it was never emailed. Read it once:

cat ~/.pgpass
# host:port:database:user:password — the last field is your CalCOFI database password
# localhost:5432:*:rswalethorp:Xxxxxxxxxxxxxxxxxxxxxxxx
# postgis:5432:*:rswalethorp:Xxxxxxxxxxxxxxxxxxxxxxxx

.pgpass is the standard PostgreSQL password file: every PostgreSQL client (psql, R’s RPostgres, Python’s psycopg, pgAdmin desktop, DBeaver, DuckDB) reads it, so you never put the password in a script. Copy the localhost line into the same file on your laptop:

nano ~/.pgpass         # paste the localhost:5432:*:<you>:<password> line, save
chmod 600 ~/.pgpass    # required — libpq ignores the file if others can read it

The file is %APPDATA%\postgresql\pgpass.conf (usually C:\Users\<you>\AppData\Roaming\postgresql\pgpass.conf). Create the folder and the file with Notepad, one line: localhost:5432:*:<you>:<password>. No chmod needed on Windows.

To change the password: psql -h localhost calcofi -c '\password' (through the tunnel), then update both .pgpass files. Until Google sign-in is enabled on pgAdmin, the same password is your pgadmin.calcofi.io password (login = your email).

7.4 Step 3 — the database over the tunnel

PostgreSQL is not exposed to the internet. Your SSH connection carries it: with the LocalForward 5432 localhost:5432 line in place, while ssh calcofi is open (or ssh -N calcofi, which opens the tunnel without a shell), anything on your laptop that connects to localhost:5432 is talking to the server’s database.

ssh -N calcofi &                                  # tunnel only, in the background (Ctrl-C / kill to stop)
psql -h localhost -d calcofi                       # password from ~/.pgpass; user = your OS user…
psql -h localhost -d calcofi -U rswalethorp        # …or say it explicitly if they differ

Inside psql:

\dn                       -- schemas: ctd, work, public, yours
\dt ctd.*                 -- curated CTD tables
SHOW search_path;         -- "$user", work, ctd, public  (unqualified names land in YOUR schema)
CREATE TABLE t AS SELECT 1 AS x;     -- -> rswalethorp.t, visible to colleagues
CREATE TABLE work.t2 AS SELECT 1;    -- -> work.t2, shared scratch, writable by all

“Address already in use” / port 5432 taken

If ssh prints bind [127.0.0.1]:5432: Address already in use, something on your laptop (often a local Postgres or pgAdmin’s bundled server) already owns 5432. Use 15432 instead in two places: LocalForward 15432 localhost:5432 in ~/.ssh/config (or the PuTTY Tunnels source port), and port 15432 / localhost:15432 wherever you connect (psql -p 15432, the .pgpass line, PGPORT=15432 for R/Python).

7.4.1 Graphical clients

client	how	best for
pgAdmin 4 desktop (download)	Register ▸ Server: General name `calcofi`; Connection host `localhost`, port `5432`, database `calcofi`, username you; SSH Tunnel tab: Use SSH tunneling, host `ssh.calcofi.io`, port 22, username you, authentication Identity file → your private key. It opens the tunnel itself — no terminal needed.	Windows users; browsing + query tool + ERD
pgadmin.calcofi.io (web)	log in with your email + database password (Google sign-in coming); servers calcofi (CTD QA/QC) and gis (legacy 2022) are already registered — on first connect replace the username with yours and enter your password (the master password it asks for is a local encryption key of your choosing)	zero install, any machine
DBeaver (free)	New connection ▸ PostgreSQL ▸ host `localhost`, db `calcofi`, user you; SSH tab ▸ host `ssh.calcofi.io`, user you, Public Key auth	people who already use it
RStudio Server (rstudio.calcofi.io)	log in with your username + database password; in R use `host = "postgis"` (it is on the same network, no tunnel); `calcofi4r::cc_pg_connect()` works out of the box	R without installing anything

7.5 From R

calcofi4r ≥ 1.8.0 (remotes::install_github("calcofi/calcofi4r")) resolves the host, your role and the tunnel for you:

library(calcofi4r)
con <- cc_pg_connect()                       # tunnel already open; role + password from ~/.pgpass
con <- cc_pg_connect(tunnel = TRUE)          # …or let it run `ssh -N calcofi` for you (needs the ~/.ssh/config entry)
con <- cc_pg_connect(port = 15432, tunnel = TRUE)   # if you had to move the local port

DBI::dbListObjects(con, DBI::Id(schema = "ctd"))
library(dplyr)
tbl(con, I("ctd.cast")) |> filter(cruise_key == "2023-04-3322") |> collect()

# write to your own schema or to work — and read it back from any other client
DBI::dbWriteTable(con, DBI::Id(schema = "work", table = "my_check"), my_df, overwrite = TRUE)
DBI::dbDisconnect(con)
cc_pg_tunnel_close()                          # if you used tunnel = TRUE

Plain RPostgres, if you prefer:

con <- DBI::dbConnect(RPostgres::Postgres(),
  host = "localhost", port = 5432, dbname = "calcofi", user = "rswalethorp")  # password: ~/.pgpass

PostGIS geometry comes through sf: sf::st_read(con, query = "SELECT * FROM ctd.cast").

7.6 From Python

calcofi4py mirrors calcofi4r — same verbs, same defaults, password from the same ~/.pgpass:

# pip install "calcofi4py @ git+https://github.com/CalCOFI/calcofi4py"
import calcofi4py as cc

con = cc.cc_pg_connect(tunnel=True)   # opens `ssh -N calcofi` for you; role + password from ~/.pgpass
con.execute("SELECT count(*) FROM ctd.cast WHERE is_best_stage").fetchone()

import pandas as pd
casts = pd.read_sql("SELECT * FROM ctd.v_scan_qc WHERE study = '2304SH' AND cast_id = '2304_020d'", con)

# propose a QC flag (curators accept/reject)
con.execute("""
  INSERT INTO ctd.flag (scan_id, variable, qual_code, reason)
  SELECT scan_id, 'temp1', 4, 'spike vs neighbours'
  FROM ctd.v_scan_best WHERE study=%s AND cast_id=%s AND depth=%s
""", ("2304SH", "2304_001d", 57))
con.commit()
cc.cc_pg_tunnel_close()

It also wraps the public releases (cc.cc_get_db() — DuckDB with every release table as a view, versions pinnable) and the bridge (cc.cc_pg_attach(), next section). Prefer plain libraries? psycopg / SQLAlchemy / GeoPandas read the same .pgpass — connection string postgresql+psycopg://<you>@localhost:5432/calcofi, no password in the URL.

7.7 DuckDB ↔︎ PostgreSQL

DuckDB’s postgres extension lets one query join the public release Parquet with the team’s PostgreSQL tables — and write back. From R:

con <- cc_get_db()                 # DuckDB with the release tables as views (public, no tunnel)
cc_pg_attach(con)                  # ATTACH the PostgreSQL db as `pg` (through your tunnel)
DBI::dbGetQuery(con, "
  SELECT c.cruise_key, count(*) AS n
  FROM pg.ctd.cast c
  JOIN sample s ON s.sample_key = c.sample_key      -- release table
  GROUP BY 1 ORDER BY 2 DESC LIMIT 5")
cc_pg_attach(con, alias = "pgw", read_only = FALSE) # writable: CREATE TABLE pgw.work.x AS SELECT …

The same from Python (calcofi4py) or any DuckDB CLI:

import calcofi4py as cc
con = cc.cc_get_db(tables=["cruise", "sample"])   # release views
cc.cc_pg_attach(con)                               # + the PostgreSQL db as `pg`
con.sql("SELECT count(*) FROM pg.ctd.flag").fetchone()

Raw SQL, in any DuckDB:

INSTALL postgres; LOAD postgres;
ATTACH 'dbname=calcofi host=localhost port=5432 user=rswalethorp' AS pg (TYPE postgres, READ_ONLY);
SELECT * FROM pg.ctd.cast LIMIT 5;
SELECT * FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.14/parquet/sample.parquet') LIMIT 5;

And the other direction — DuckDB inside PostgreSQL. The server runs the pg_duckdb extension, so from psql / pgAdmin you can read the public release Parquet without leaving SQL:

SELECT * FROM release.cruise WHERE year = 2023;        -- views over the current release (cruise, ship, dataset)
SELECT r['sample_key']::text, r['datetime_utc']::timestamp
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.14/parquet/sample.parquet') r
WHERE r['dataset_key']::text = 'calcofi_ctd-cast' LIMIT 10;

(r['column']::type is pg_duckdb’s row syntax. Big tables — obs, obs_ctd_full — are better filtered in DuckDB on your laptop than pulled through the server.)

7.8 The CTD QA/QC database

Database calcofi, three kinds of schema:

schema	what	who writes
`ctd`	curated: the calcofi.org db-CSV cast files loaded verbatim and never edited (`ctd.file`, `ctd.scan` — all 82 source columns, ~10.8 M scans; `ctd.scan_issue` keeps the handful of cells that could not be typed, as text; `ctd.scan_column` is the data dictionary), the flag ledger (`ctd.flag`, IODE codes in `ctd.qual_code`), derived `ctd.cast`, and views: `ctd.v_scan_best` (one archive+stage per cruise × direction), `ctd.v_scan_qc` (originals + `<var>_qc` / `<var>_fix` from accepted flags), `ctd.v_scan_clean` (fixes applied, accepted-bad nulled)	originals: the loader only; flags: everyone proposes (`INSERT INTO ctd.flag (scan_id, variable, qual_code, reason, proposed_value)`), curators set `status` to accepted/rejected
`release`	read-only views over the public DuckDB release Parquet via pg_duckdb (`release.cruise`, `release.ship`, `release.dataset`)	nobody (regenerated per release)
`work`	shared scratch — anything goes, readable and writable by the whole team	everyone
`<you>`	your personal schema (first on your `search_path`); colleagues can read it	you

The rules of the road: originals never change — a problem is a row in ctd.flag (which scan, which variable, what code, optional proposed value, why), a fix is an accepted flag, and every derived product is computed from originals + accepted flags so it can be regenerated. A first flag, end to end:

-- propose: temperature spike on one scan (IODE 4 = bad); 5 = changed, with proposed_value
INSERT INTO ctd.flag (scan_id, variable, qual_code, reason)
SELECT scan_id, 'temp1', 4, 'spike vs neighbours' FROM ctd.v_scan_best
WHERE study = '2304SH' AND cast_id = '2304_001d' AND depth = 57;
-- review (curators): accept or reject; reviewed_by/at are filled in for you
UPDATE ctd.flag SET status = 'accepted', review_note = 'agree' WHERE flag_id = 123;
-- see it: the _qc column carries the code, v_scan_clean nulls the value
SELECT depth, temp1, temp1_qc FROM ctd.v_scan_qc WHERE study = '2304SH' AND cast_id = '2304_001d' AND temp1_qc IS NOT NULL;

Every change to ctd.flag is kept in ctd.flag_audit. The schema and vocabulary are new (August 2026) — propose changes in work, and they will be folded into ctd.

7.9 Files: SFTP and the shared folder

sftp calcofi                       # same key, same alias; cd ctd/incoming ; put file.zip ; get …
scp big.zip calcofi:ctd/incoming/   # one-shot copy

GUI clients: Cyberduck (Mac/Windows), FileZilla, WinSCP (Windows; uses the .ppk or the OpenSSH key) — protocol SFTP, host ssh.calcofi.io, user you, key file.

/share/data/ctd is owned by group calcofi with the group-write bit inherited, so files you upload are editable by teammates. Put uploads in incoming/, leave archive/ to the loader, and use exports/ for things you want others (or yourself, later) to download. Your home directory is on the small system disk — keep it for dotfiles and scripts.

7.10 Etiquette & limits

One 4-vCPU / 15 GB machine runs everything. LIMIT first, \timing on, and ask before a CREATE INDEX on the big ctd.scan table.
Backups: nightly dump → Google Cloud Storage, weekly automated restore test. Still, a DROP TABLE is a DROP TABLE — work is scratch, your schema is yours, ctd is protected.
Disk is shared and finite; delete what you no longer need under ~/ctd.
Problems, keys, password resets, new tables in ctd: Ben Best (bebest@ucsd.edu).