This guide assumes you have PostgreSQL installed, either on a remote server (e.g., institutional), or locally. You (and your adminstrator) will also need a mechanism to run SQL commands. This could be done from the command line (e.g., psql), or an SQL pane in a GUI. We use either pgAdmin or DBeaver (see our guide to essential DB admin tasks in DBeaver).
You will need PostgreSQL accounts to fill these roles. On some institutional server setups, only DB administrators can create roles; talk to your DB admin if this is the case.
| Role | Description | Recommendation | SQL priviledges |
|---|---|---|---|
| db_owner | the owner of this database (not necessarily the admin of the database server). Creates/deletes schemas, tables, views, triggers, etc. Runs backups, grants privileges. | The DB owner should be a person who aready knows PostgreSQL, so most likely, an existing account. All scripts transfer ownership to the owner account. | ALL (DB-level) |
| read_write_user | Optional intermediate user. Updates row-level content, typicallly with a script. You may not need this role until you have scripts/forms for adding content. | create a new account named “read_write_user” | SELECT, UPDATE, INSERT (no DELETE) |
| read_only_user | export, e.g., via script to create EML, or display on a website | create a new account named “read_only_user” | SELECT |
Installation SQL scripts for LTER-core-metabase already include commands to grant the above tiers of privileges to corresponding tokenized role names. You cannot run these SQL scripts directly; first they must reference existing role names. You will have to set these roles – definitely for the db_owner account, since it is tokenized as %db_owner% in our scripts, but not for the non-tokenized read_write_user and read_only_user if you follow our recommendations and create accounts with these names. Simply find-and-replace (via script or text editor) with your own role names in all scripts you intend to run.
Scripts are best for this, since DB extensions that appear in this repository will use the account-tokens, and you’ll have to reset those for every new piece of SQL. E.g., in a linux system, you could use sed (with your own directory names instead of my git-clone and local):
sed 's/%db_owner%/gastil/g' git-clone/0_create_db.sql > local/create_db.sql
The 0_create_db.sql script names the database lter_core_metabase. You may prefer a different name.
New, from-scratch install:
Alternatively, you could build metabase by running all sequentially numbered patches. This would be tedious. There are some delete scripts offered (see maintenance branch) if you prefer to build your own parent tables or want to omit example datasets.