Database administration

First setup

Prerequisites

In order to set up the database of specatalog for the first time you need a postgresql database running. If you have such a database you will need the following information:

HOST
PORT
USER
PASSWORD

Make sure that postgresql is installed on your system e.g. via:

sudo apt install postgresql

Open your terminal and log into the database:
```
psql -h HOST -p PORT -U USER
```

Create the specatalog admin role:

CREATE ROLE specatalog_admin WITH LOGIN PASSWORD 'administration_of_specatalog' CREATEDB CREATEROLE;

Create the database:

CREATE DATABASE specatalog OWNER specatalog_admin ENCODING 'UTF8';

Change to the new database:
```
\c specatalog
```

Set the permissions for the specatalog_admin. Run the commands one after the other:

ALTER SCHEMA public OWNER TO specatalog_admin;

GRANT USAGE, CREATE ON SCHEMA public TO specatalog_admin;

REVOKE ALL ON DATABASE specatalog FROM PUBLIC;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Create your user account and run the steps 1 and 2 from the section Create new users.
Create an empty folder where all datafiles will be organised and stored. Note the <absolute_path>.

Open a new terminal and download and install specatalog as described in Installation and Setup.
- username: the new user <user_name> (not the admin!)
- password: password of the new user
- path: <absolute_path>
- url: <HOST>:<PORT>/specatalog
Copy the allowed_values.py-file to the root of the new archive-folder (run the command from the specatalog-root-folder where you have already installed specatalog):
```
cp ./src/specatalog/helpers/allowed_values_not_adapted.py <absolute_path>/allowed_values.py
```
Setup the database and the archive:
```
specatalog-init-db
```
Run steps 2 (again) and 3 of the section Create new users for your new user.
You can create new users for the members of your group. Make sure to give them username, password, the path to the archive-folder and the url of the database (see step 7).
Quit via:
```
\q
```

Create new users

Log in to the postgresdatabase and change to specatalog:
```
psql -h HOST -p PORT -U USER
\c specatalog
```

Create a new database user:

CREATE ROLE <user_name> WITH LOGIN PASSWORD '<password>';

Set permissions. Run the commands one after the other::
GRANT CONNECT ON DATABASE specatalog TO <user_name>;

GRANT USAGE ON SCHEMA public TO <user_name>;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <user_name>;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <user_name>;

Set DB-specific permissions. Run the commands one after the other:

GRANT USAGE, SELECT, UPDATE ON SEQUENCE molecules_id_seq TO <user_name>;

GRANT USAGE, SELECT, UPDATE ON SEQUENCE measurements_id_seq TO <user_name>;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO <user_name>;

Exit via:
```
\q
```