Setup a specific database "readonly" user for ontop <-> oedb access

[jh-RLI]

Description of the issue

We need a new user that can only access the tables stored within the schenario topic (schema) on the oedb. This user will be connected to the ontop server.

Ideas of solution

/* no access to existing tables in public */
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM oepreadonly;
/* read access for existing and new tables in scenario */
GRANT USAGE ON SCHEMA scenario TO oepreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA scenario TO oepreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA scenario GRANT SELECT ON TABLES TO oepreadonly;

Workflow checklist

• [ ] I am aware of the workflow in CONTRIBUTING.md

[jh-RLI]

-- Create the user
CREATE USER oepreadonly WITH PASSWORD 'secret';

-- Revoke all privileges on existing tables in the public schema
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM oepreadonly;

-- Grant usage and read access on the scenario schema
GRANT USAGE ON SCHEMA scenario TO oepreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA scenario TO oepreadonly;

-- Ensure that the new user has read access to any new tables created in the scenario schema
ALTER DEFAULT PRIVILEGES IN SCHEMA scenario GRANT SELECT ON TABLES TO oepreadonly;

[jh-RLI]

Once we have this, I think we should also add the setup to the Alembic migrations or another database setup step.

[wingechr]

I added the user (with a different password of course).

But: this user still will be able to create new tables in schema public, because before postgres 15 (I think), it is the default that cannot easily be changed for the role "public" that every user is part of.

[jh-RLI]

Okay, if we can't avoid it for now, we'll just carefully test what happens on the TOEDB. Ontop should only translate the sparql to sql query.