@@ -52,26 +52,24 @@ The same data are presented in different views for different purpose:
User rights for working with the database depend on PostgreSQL roles. The following roles were defined:
### user (e.g. skittlaus)
Every user gets a login name and password. With this account the user can connect to the database (see section ...). Technically speaking this is the session-user, which is recorded when creating, changing or deleting data. This role has no further privileges. To see, create, modify and delete data, the user needs to take another role by running the command
Every user gets a login name and password. With this account the user can connect to the database (see section ...). Technically speaking this is the session-user, which is recorded when creating, changing or deleting data. This role has no further privileges. To see, create, modify and delete data, the user needs to take an additional role by running the command
`SET ROLE tethys_... ;`.
SQL_Umsetzung: Login, Alter default priviliges notwendig vor Erstellung.
### tethys_reader
This role allows to see and download the quality-checked database content. It has no privileges to create, modify or delete data. Having this role the user can check the database without being able to create any harm to the data.
SQL_Umsetzung: Read, Select, Usage, Temporary, Execute, Connect, SET TRUE, WITH INHERIT TRUE, WITH GRANT OPTION FALSE.
### tethys_user
This role allows to create new data and modify/delete these data created by the user itself. Once the data are quality checked by a quality assessor, the user can not modify or delete the own data any more.
SQL_Umsetzung: Insert, Update, Delete, WITH INHERIT TRUE.
This role allows to create new data and modify/delete these data created by the user itself. Once the data are quality checked by a quality assessor, the user can not modify or delete the own data any more. The tethys_user inherits all rights from the tethys_reader.
### tethys_qa
The quality assessor can see data created by other users. He can comment on these data and mark them as quality checked or send them back to revision.
The quality assessor can see data created by other users. He can comment on these data and mark them as quality checked or send them back to revision. The tethys_qa role inherits all rights from the tethys_reader role.
<details><summary>Click to expand</summary>
Can the right for modifications be limited to specific columns? This would be perfect.
Answer: YES
-- First, we create a function that checks whether the user is allowed to update certain columns. It does this by checkking if some_columns, that he is not allowed to modify stayed the same.
-- First, we create a function that checks whether the user is allowed to update certain columns. It does this by checking if some_columns, that he is not allowed to modify stayed the same.
CREATE OR REPLACE FUNCTION can_bypassrls_in_columns(row_data your_table, new_data your_table)
The content admin has the right to see, modify and delete all data in the database. Additionally to the data visible for other roles, he can see and modify the tables in the schema "hidden", where all data are stored. Thus he can can change controlled vocabulary and restore accidentally deleted data.
The tethys_ca inherits all rights from the tethys_user role.
<details><summary>Click to expand</summary>
SQL_Umsetzung: BYPASSRLS unrestricted, WITH INHERIT TRUE.
</details>
### tethys_admin
The admin role can create new users, grant roles to users and change the structure of the database.
The tethys_admin inherits all rights from the tethys_ca role.
<details><summary>Click to expand</summary>
SQL_Umsetzung: Superuser status → Create, Truncate, Trigger, Alter system, create role status, createDB status, Replication, WITH INHERIT FALSE, WITH GRANT OPTION TRUE, Alter default priviliges
