toaruser is able to *get* information from REST

We created toaruser as the user, who is able to get information via FastAPI's REST interface.
This is not working:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: role "toaruser" is not permitted to log in

The implemented database connection does not work without login.
This is a security issue -- and should be more deeply investigated.
I think, joinuser (in previous Django version) would have also not worked...
join_user in operational version is able to login!

pytests are creating a test database -- this needs "Create DB" privileges (which will not be given to toaruser at all!)
Therefore, pytests are done with the role of toaradmin (on local machine -- with no password shown anywhere!).
The same holds for toaruser's password.
The files holding these passwords for the operational machine (toardb/utils/database.py and toardb/test_base.py) should never be exported to any repository!

For installation on production service (only admin information (f. ex. Sabine)), see https://gitlab.version.fz-juelich.de/toar/toardb_fastapi/-/issues/10#note_53506

added .doing bug labels

Since we do not have any authentication in place right now, the privileges of toaruser are changed to be able to login.
This is done with the command:

ALTER ROLE toaruser WITH LOGIN;

Now, it is pretty clear, that we also have to give read permissions to toaruser.

For the time being, I will also give write permissions to toaruser, so Jianing can use the REST API for testing his upload of OpenAQ data.

Commands for adding privileges for toar_user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO toaruser;

And just as long as authentication/authorization is not in place:

GRANT INSERT, UPDATE ON
  data,
  organisations, persons,
  variables,
  timeseries, timeseries_programmes, timeseries_roles, timeseries_annotations, 
    timeseries_timeseries_roles, timeseries_timeseries_annotations,
  stationmeta_core, stationmeta_roles, stationmeta_annotations, stationmeta_global_services, stationmeta_global,
    stationmeta_aux_doc, stationmeta_aux_image, stationmeta_aux_url, 
    stationmeta_core_stationmeta_roles, stationmeta_core_stationmeta_annotations
TO toaruser;
GRANT UPDATE ON
  organisations_id_seq, persons_id_seq,
  variables_id_seq,
  timeseries_id_seq, timeseries_annotations_id_seq, timeseries_programmes_id_seq, timeseries_roles_id_seq,
  stationmeta_annotations_id_seq, stationmeta_annotations_id_seq, stationmeta_aux_image_id_seq,
    stationmeta_aux_url_id_seq, stationmeta_core_id_seq, stationmeta_global_id_seq, stationmeta_global_services_id_seq,
    stationmeta_roles_id_seq  
TO toaruser;

changed the description

mentioned in commit 934e8e26

This information is only for admin use (e. g. Sabine)
installation on production service:
after installation instructions described in README -- without setup of toaradmin --, do:

For the time being, set up two new roles toarv2_ro, toarv2_rw -- these will only work on toardb_v2.
(Later on, we might switch to toar_ro and toar_rw, but at the moment, there is no authentication method in place!)

• CREATE DATABASE toardb_v2 ENCODING='utf-8';
• CREATE ROLE toarv2_ro;
• GRANT CONNECT ON DATABASE toardb_v2 to toarv2_ro;
• GRANT USAGE ON SCHEMA public TO toarv2_ro;
• GRANT USAGE ON SCHEMA postgis TO toarv2_ro;
• GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA postgis TO toarv2_ro;
• ALTER ROLE toaruser WITH LOGIN;
• GRANT toarv2_ro to toaruser;
• CREATE ROLE toarcurator PASSWORD 'some_secret_password' INHERIT;
• ALTER ROLE toarcurator WITH LOGIN;
• CREATE ROLE toarv2_rw;
• GRANT CONNECT ON DATABASE toardb_v2 to toarv2_rw;
• GRANT USAGE ON SCHEMA public TO toarv2_rw;
• GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO toarv2_rw;
• ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO toarv2_rw;
• GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO toarv2_rw;
• ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO toarv2_rw;
• GRANT USAGE ON SCHEMA postgis TO toarv2_rw;
• GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA postgis TO toarv2_rw;
• GRANT toarv2_rw to toarcurator;

Do not create another superuser!
toaradmin is only needed for pytests on local machine.

changed the description

added .done label and removed .doing label

closed

There were missing permissions for toaruser on the productive database -- executed the following grant commands:

GRANT USAGE ON SCHEMA public TO toarv2_ro;
GRANT USAGE ON SCHEMA postgis TO toarv2_ro;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA postgis TO toarv2_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO toarv2_ro;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO toarv2_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA toar_convoc TO toarv2_ro;