Purpose

This pull request adds two new power-up features to StarbaseDB currently isolated in their own worker deployments. Typically each of these features would be contributed to the project independently but much of the testing performed was to co-test how these features performed together in tandem.

Core features for enabling the possibility to run SQL from the frontend include both having a list of allowed queries that should be executable against the database, and row level security. The allowlist serves as a way to quickly deny any incoming database query at the edge (e.g. Worker) and not burdening our database instance itself with making that determination and potentially adding latency to subsequent queries. In addition as another layer of security RLS (row level security) allows us to expressively write policy rules on a per table basis to make sure only specific actions can be performed and potentially only valid with expected values (e.g. ensure a user can only select rows that match the userId available to the system).

Since the recommended usage of these power-ups is to be placed in the beforeQuery hook (read more) that means every aspect of our StarbaseDB instance including executing REST API queries, web sockets, transactions and more all funnel through this function before executing against the database.

Tasks

• Support storing queries in an allowlist against our Durable Object SQLite storage
• When a query is about to be ran verify the queries AST breakdown matches what is allowed
• Support an early version of row level security policies on tables
• Support storing RLS policies in a table on our Durable Object SQLite storage

Verify

• When a query exists on the allowlist verify that you can run it as structured.
• When a query does not exist on the allowlist, make sure it throws an error and the query is never performed.
• Ensure a table will only apply RLS when at least one rule exists for the table. If no rules exist for a particular table then we assume RLS is "off" for that table and it's fully accessible.
• Ensure when a policy is added for RLS and uses context.id() as the value column that the JWT sub value is used to replace it in the SQL query before execution.
• Test number casting when an RLS policy value_type is defined as a number to make sure quotes do not exist around the value in the produced SQL statement.

Reference the migration.sql files in the PR for the corresponding SQL table definitions. You can follow a similar row INSERT command as shown below for each table to establish enough rules to run a statement to get all results from your table (in this case, assuming the table name is todos).

INSERT INTO "tmp_allowlist_queries"("id", "sql_statement") VALUES(1, 'SELECT * FROM todos');

INSERT INTO "tmp_rls_policies"("id", "actions", "schema", "table", "column", "value", "value_type", "operator") VALUES(1, 'SELECT', NULL, 'todos', 'user_id', 'context.id()', 'string', '=');

Before

After