Database sharing and access control

[marcua]

Rough description

A database owner should be able to allow other users/entities to have following levels of access

• read-only access
• read/write access
• manager (to be able to control sharing with anyone except the owner)

A database owner should also be able to control whether the public can have various forms of read-only access

• metadata only
• forking
• read-only querying

The plan

• Introduce SQL data model (Migrations for sharing permissions data models #461) and Rust data model to mirror it (Permissions data models in Rust #466).
• Add endpoint to support updates to a database's public sharing level. Make this a generic update_database endpoint.
  • Introduce update_database endpoint to enable public sharing level update #473
  • Support for read-only queries and listing databases (Permissions public_sharing_level read-only/fork support #479)
• Add endpoint to add / update an entity's access to a database. Setting to no_access removes the row if it exists. Permissions: must be manager or owner, managers can't change owner permissions.
• Introduce managers into permissions (except for changing owner permissions)
• Documentation (Documentation for public sharing level and entity-specific permissions #483)
• Fix issue for update_profile and update_database where no arguments results in a SQL syntax error

[sofiaritz]

I think we should combine forking and querying, if someone can fork the database then they can run read-only queries as well :)

[marcua]

Definitely before we have forking support and support for varying memory/CPU for different databases, I agree. :)

The reason I was thinking someone might want to give fork read-only access but not query read-only access is because queries take up resources on your running database, whereas forking doesn't. So perhaps in the future when you want someone to look at your dataset but don't want them to use up your CPU running queries, you can say "fork my dataset and let me know what you find!" without having to worry about beefing up your own running database.

Definitely not something we have to worry about for a while :).

[marcua]

Working on this next! Came up with a rough outline of a plan in the issue description!

[marcua]

SQLite data models work, and started on Rust data models. For next time

• Add instructions on setting up postgres for testing to readme. Need createuser -P postgres_user (enter password test), then
  • [ubuntu] sudo -u postgres psql -c "alter user postgres_user createdb;"
  • [macos] createuser -s postgres, psql -U postgres -c "alter user postgres_user createdb;"
• Add non-linux-specific code to isolation/nsjail path that stderr-warns you nsjail won't work if you have an isolation configuration, but then continues to run
• on permissions-datamodels branch, fix whatever the postgres issue is after getting installation working
• continue developing on permissions-rustdatamodels

[marcua]

• Updated Linux-vs-Mac instructions with Add instructions for setting up test postgres user on Linux vs macOS #463
• Merged Migrations for sharing permissions data models #461
• Continuing on permissions-rustdatamodels!

[marcua]

Done

• permissions-rustdatamodels now has the data models
• fields on existing database CRUD queries handle public_sharing_level
• client and server handle public sharing level on database creation

Next

• Database support for updating public_sharing_level. We already support setting it on creation, so we need an endpoint to update the database.
• Database support for create/update/remove entity_database_permission

[marcua]

Reworked plan to be endpoint-focused (e.g., expose public sharing first) rather than layer-focused (e.g., implementing all DB functionality first, but no endpoints are exposed).

First up: Add endpoint to support updates to a database's public sharing level. Make this a generic update_database endpoint. Permissions: must be manager or owner.

[marcua]

Almost done with endpoint to update public sharing level.

Next

• Finish update public sharing level
• Fix issue for update_profile and update_database where no arguments results in a SQL syntax error
• CRUD support for entity_database_permission

[marcua]

Server-side support for public sharing level updates done. Started writing end-to-end tests.

Next

• Add client-side support, merge
• Finish writing tests for public sharing endpoint and permissions
• Support listing database
• Support read-only

[marcua]

public_sharing_level is now updateable! Introduced read-only mode in SQLite!

Next up:

• Support read-only mode by 1) determining which mode to run queries in the query endpoint, and 2) updating tests to confirm it worked.
• Support listing database

[marcua]

List and query logic and tests are all set (exposed to the query and entity details endpoints, passed through properly to rusqlite). Next

• Clean up some debugging I did to figure out correct SQLite flags (SQLITE_OPEN_CREATE should only co-exist with SQLITE_OPEN_READ_WRITE)
• Catch "executing write on read-only query" from rusqlite and pass it to client properly
• Update tests with appropriate error from previous bullet

[marcua]

Merged working support for fork/read-only public sharing level!

Next

• Documentation
• Version bump
• CRUD for entity-level access to database

[marcua]

Wrote up documentation for both public sharing level and entity-level permissions. Next

• CRUD for entity-level access to database
• Endpoint to call that CRUD
• Update permissions logic to look up entity-level permissions

[marcua]

Database-/endpoint-/CLI-level support for sharing is implemented (but untested) on branch permissions-crud. Next

• Update permissions logic for read-only/read-write permissions
• Update permissions logic for managers
• Add tests (query levels, management permissions, ensure owners are protected)