Backs the User Portal: accounts, access requests, workshops, form submissions, and the reference tables the signup forms are built from.

Create¶

Connect as a superuser on the host running PostgreSQL:

psql -h <DATABASE_HOST> -U postgres

create user portal_db_reader with password '<GENERATED_SECRET>';
create database portal with owner portal_db_reader;

The portal's own setup notes also grant the role membership in postgres:

GRANT postgres TO portal_db_reader;

That grant is broader than it looks

Membership in postgres gives the portal role superuser-equivalent reach over every database in the instance, including the iCAT. Grant it only if a portal migration actually needs it, revoke it afterwards, and prefer granting the specific privileges the portal needs. See portal2¹ for what the application itself requires.

Restore the base schema¶

The portal ships a SQL dump rather than an incremental migration history for the initial load:

psql -U portal_db_reader -d portal -f portal.sql

portal.sql comes from the portal2 repository.

Session table¶

The portal stores sessions in the database. Confirm the table exists after the restore:

CREATE TABLE public.session (
    sid character varying NOT NULL,
    sess json NOT NULL,
    expire timestamp(6) without time zone NOT NULL
);
ALTER TABLE public.session OWNER TO portal;
CREATE INDEX "IDX_session_expire" ON public.session USING btree (expire);

Two roles appear in the portal's own SQL

The dump creates the database owned by portal_db_reader but assigns the session table to portal. Reconcile these against the roles your deployment actually uses before the portal starts, or session writes fail with a permission error at first sign-in.

Seed reference data¶

Institutions¶

Institution autocomplete is seeded from the GRID dataset.² Download a release, unzip it, and import with the script from the portal repository:

./import_grid_institutions.py \
    --host <DATABASE_HOST> --user portal_db_reader --database portal grid.csv

The script lives at src/scripts/import_grid_institutions.py in portal2.

Form reference tables¶

The signup and profile forms read from a set of lookup tables. Load each one:

for table in country region gender occupation ethnicity \
             fundingagency awarechannel researcharea; do
  psql -U portal_db_reader -d portal -f "./account_${table}.sql"
done

The SQL files are published at portal2-db.

Administrative queries¶

Promote an existing account to portal administrator:

UPDATE account_user SET is_superuser = true WHERE username = '<USERNAME>';
UPDATE account_user SET is_staff     = true WHERE username = '<USERNAME>';

Check or force email verification:

SELECT has_verified_email FROM account_user WHERE username = '<USERNAME>';
UPDATE account_user SET has_verified_email = true WHERE username = '<USERNAME>';

Prefer the admin panel for routine work. These queries exist for bootstrapping the first administrator and for repairing accounts the UI cannot reach — the normal path is bootstrap.

Migrations¶

Ongoing schema changes come from the portal application itself. See database migrations for the shared procedure.

CyVerse User Portal (portal2) ↩
GRID institution identifiers ↩