Portal Database¶

This Database is used for CyVerse User portal Service.

Initialize database¶

Access Database¶

ssh root@DB_HOST.com
psql -U postgres

Create required Database and User¶

Restore from dump¶

For this we have to download portal.sql file.

# restore to user portal_db_reader and database portal
psql -U portal_db_reader -d portal -f portal.sql

Populate Database¶

make sure session Table exist¶

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);

Import GRID institutions¶

download required grid file¶

Official website

# download grid
wget https://digitalscience.figshare.com/ndownloader/files/30895309

# unzip
unzip 30895309

import to the database¶

For imorting this grid file we will use the script from portal2/scripts/import_grid_institutions.py.

./import_grid_institutions.py --host root@DB_HOST.com --user portal_db_reader --database portal grid.csv

Populate these Tables¶

These sql files can be found here.

psql -U portal_db_reader -d portal -f ./account_country.sql
psql -U portal_db_reader -d portal -f ./account_region.sql
psql -U portal_db_reader -d portal -f ./account_gender.sql
psql -U portal_db_reader -d portal -f ./account_occupation.sql
psql -U portal_db_reader -d portal -f ./account_ethnicity.sql
psql -U portal_db_reader -d portal -f ./account_fundingagency.sql
psql -U portal_db_reader -d portal -f ./account_awarechannel.sql
psql -U portal_db_reader -d portal -f ./account_researcharea.sql

Extra¶

Give Admin privilege to a user¶

--update is_superuser
UPDATE account_user SET is_superuser = true WHERE username='USERNAME';

---update is_staff 
UPDATE account_user SET is_staff = true WHERE username='USERNAME';

Verify User email¶

-- check if its verified
select has_verified_email from account_user where username='USERNAME';

--Verify email
UPDATE account_user SET has_verified_email = true WHERE username='USERNAME';

Insert Services¶

by default the table api_service will be empty, in order to add a new service, we will to INSERT INTO the database.

For this we need to add first the api_servicemaintainer column, and then api_service - as an example we will be adding a DE(Discovery environment) service.

-- check the tables content
select * from api_servicemaintainer;
select * from api_service;

-- First Insert into api_servicemaintainer
-- save the id of this record - which we will use in the next step
INSERT INTO api_servicemaintainer (name, website_url, created_at, updated_at) VALUES ('CyVerse', 'https://cyverse.tugraz.at', now(), now());

-- Second Insert into api_service
INSERT INTO api_service (name, description, about, service_url, is_public, icon_url, created_at, updated_at, service_maintainer_id, approval_key, subtitle) VALUES ('Discovery Environment', 'Use hundreds of bioinformatics apps and manage data in the CyVerse Data Store from a simple web interface', 'By providing a consistent user interface for access to the tools and computing resources needed for specialized scientific analyses, the Discovery Environment facilitates data exploration and scientific discovery.\r', 'https://de.cyverse.at/de', true, 'https://user.cyverse.at/assets/images/de.png', now(), now(), 3, 'DISCOVERY_ENVIRONMENT', '');

Insert Restricted Username (PENDING)¶

INSERT INTO account_restrictedusername (username, created_at, updated_at) VALUES ('username', now(), now());

Migrate Database¶

TODO