TryLinks Database

TryLinks consists of two databases:

• trylinks for storing data about tutorials, users and their progress
• links for auxiliary tables used in tutorials focused on links-database connections

Similarly, there are two users manipulating these databases through the software

• trylinks having SELECT, INSERT, UPDATE, DELETE privileges to all tables in trylinks database; connects to the DB directly from TryLinks server
• links having limited access to links DB; connects to DB through the links config file upon the user's code compilation in tutorials

Setup steps

1. Login as a superuser

   psql -U postgres

2. Create users. Selected passwords need to be included in .env file - in DB_CONNECTION_STRING and TRYLINKS_CONFIG respectively.

   CREATE USER trylinks WITH LOGIN ENCRYPTED PASSWORD '<trylinks-password>';
   CREATE USER links WITH LOGIN ENCRYPTED PASSWORD '<links-password>';
   

3. Create databases

   CREATE DATABASE trylinks;
   CREATE DATABASE links;
   

4. Populate trylinks DB with db-init.sql and links DB with db-links-init.sql (run from bash)

   psql -U postgres -d trylinks -f db-init.sql
   psql -U postgres -d links -f db-links-init.sql

5. Grant appropriate privileges for links user. Logged-in as a superuser in links database (psql -U postgres -d links), execute:

   GRANT SELECT ON "factorial" to links;
   GRANT SELECT, INSERT, DELETE ON "todo" to links;
   

6. Grant appropriate privileges for trylinks user. Logged-in as a superuser in trylinks database (psql -U postgres -d trylinks), execute:

   GRANT SELECT, INSERT, DELETE, UPDATE ON "LinksUser","LinksTutorial","LinksFile" TO trylinks;
   GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO trylinks;