Ermrest Howto (set ACL, cookies)

Common ERMrest or database operations

Table of contents

• Catalog operations
  • Check the status of catalogs
  • Create a new catalog w/ new domain types
  • Lookup catalog mapping
  • add column to existing table
  • Make catalog persistent
  • Add alias for a catalog
• hatrac operations
  • drop hatrac and redeploy it
  • update hatrac ACLs
• ACL or webauthn operations
  • Giving read and write access at the catalog level
  • Give isrd-tester group write access
  • Extending webauthn cookie duration
• Server operations
  • Adding a new user and creating their directory

Catalog operations

Check the status of catalogs

This guide is specifically for test catalogs but can be used on any server to verify which catalogs are present. To get an output of the existing catalogs with their deleted_on and created_on value:

We had an issue where dev.isrd had to be wiped and rebuilt, deleting list_catalogs.sh.

To get the list of databases through psql:

ssh dev.isrd.isi.edu
sudo su - ermrest
psql
\list

Create a new catalog w/ new domain types

1. Create the catalog either in psql or using remrestDataUtils
2. Follow this for creating domain types.
   • make sure to psql <catalog_name>
   • domain types for public should be used for other schemas created as well
3. Create schema, tables, and data

Lookup catalog mapping

psql ermrest;
select * from ermrest.simple_registry;

add column to existing table

curl -H 'cookie: webauthn=<insert-cookie-here>' -X POST -H "Content-Type: application/json" -d '{"name": "text", "type": {"typename": "text"}, "default": "default", "nullok": true, "annotations": {}}' -i "<host>/ermrest/catalog/<cid>/schema/<schema_name>/table/<table_name>/column/"

Make catalog persistent

By default catalogs created on dev.derivacloud.org will be automatically deleted after a few days. To prevent that,

1. Navigate to the registry page (https://dev.derivacloud.org/chaise/recordset/#0/ermrest:registry@sort(RID))
2. Find the catalog and click on "edit" icon.
3. Make sure "Is Persistent?" is set to true.

Add alias for a catalog

https://github.com/informatics-isi-edu/ermrest/blob/master/docs/api-doc/rest-catalog.md#catalog-alias-creation

curl -H 'cookie: webauthn=<insert-cookie-here>' -X POST -H "Content-Type: application/json" -d '{"id": "desired alias", "alias_target": "catalog id"}' -i "<host>/ermrest/alias"

hatrac operations

drop hatrac and redeploy it

sudo service httpd stop
sudo -u postgres dropdb hatrac
sudo -u postgres createdb -O hatrac hatrac
sudo -u hatrac hatrac-deploy  "long ugly globus group uri"
sudo service httpd start

update hatrac ACLs

curl -H 'cookie: webauthn=<insert-cookie-here>' -X PUT -H "Content-Type: application/json" -d '["https://auth.globus.org/<insert-globus-id-here>"]' -i "https://dev.isrd.isi.edu/hatrac/;acl/subtree-create"

ACL or webauthn operations

Giving read and write access at the catalog level

For background, see the ERMrest catalog protocol document.

Generally, we will want to add the isrd-testers group to a catalog for interactive testing. The globus ID for this group is https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d.

A script like this will do it locally:

#!/bin/sh
# 1 - database
# 2 - acl
# 3 - rolename (like a globus uri)
psql -c "insert into _ermrest.meta (key, value) values ('${2}', '${3}');" ${1}

The above script can be found at dev.isrd.isi.edu:/home/ermrest/update_acl.sh.

It can be run like:

isrd-dev::ermrest[~] ./update_acl.sh facebase-db "content_write_user" "https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d"

If the acl already exists, it will fail like this:

ERROR:  duplicate key value violates unique constraint "meta_key_value_key"
DETAIL:  Key (key, value)=(content_write_user, https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d) already exists.

Anyone can do this for any catalog they create.

Give isrd-tester group write access

This achieves the same as the above, but is done a different way.

• login to the server and run the following command to access the db directly

ssh dev.isrd.isi.edu
sudo su -
su - ermrest
# get a list of catalog
psql -l
# (or if you only have a catalog number, log in as ermrest user and check the `simple_registry` table)
psql ermrest
SELECT * FROM simple_registry;
# access a specific catalog
psql <catalog name>

• give isrd-tester group write access to a specific catalog Table is now _acl_admin.group_lists

INSERT INTO _ermrest.meta (key, value) VALUES
('content-write-user', 'https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d'),
('write_user','https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d'),
('schema_write_user','https://auth.globus.org/9d596ac6-22b9-11e6-b519-22000aef184d')
ON CONFLICT DO NOTHING;

/* run the following command to view the current acl */
select value, array_agg(key) from _acl_admin.group_lists group by value;

Extending webauthn cookie duration

By default, webauthn cookies expire after 30 minutes. To change your cookie's expiration, here's how:

• If you don't have your webauthn cookie yet, get it by:
  • Going to https://dev.isrd.isi.edu/chaise/search/.
  • Log in.
  • Open your browser's console and type $.cookie("webauthn"). Your cookie will be returned in the console.
• Log in to a machine (e.g. dev.isrd.isi.edu) and become the root user.

  ssh dev.isrd.isi.edu
  sudo su -

• Switch to the ermrest user.

  su - ermrest

• Access the list of sessions from webauthn.

  psql webauthn
  select email, display_name, id, key, expires from webauthn2_globus_auth.session ;

• Set your new expiration timestamp for your cookie. In the following example, my cookie is EwUuy327498dDfuidY32djh3 and I want it expire on 2018-12-31.

  update webauthn2_globus_auth.session set expires='2018-12-31' where key='EwUuy327498dDfuidY32djh3';

Server operations

Adding a new user and creating their directory

The following should be done as the root user:

sudo su -
useradd <username>
mkdir /home/<username>/public_html
mkdir /home/<username>/.ssh
# from your local machine
scp path/to/key.pub <self>@dev.isrd.isi.edu:/home/<self>/
# back to the server
mv /home/<self>/key.pub /home/<username>/.ssh/authorized_keys
chown -R <username>:<username> /home/<username>/.ssh
usermod -a -G wheel <username>
chmod u=rwx,go=rx /home/<username>
chmod u=rwx,go=rx /home/<username>/.ssh
chmod u=rw,go=r /home/<username>/.ssh/authorized_keys
restorecon -rv /home/<username>/public_html

NOTE: After trying the above, I realized that not creating the public_html and letting students to create it themselves is easier. The above instructions doesn't work properly for public_html permissions.