surreal_access_control_system

This repository explores how to build a role based access control system on top of SurrealDB using TypeScript. We will build a nodejs backend command line application that uses the SurrealDB Javascript/Typescript SDK to connect to a local SurrealDB instance. We will authenticate with various users and display a list of 'product' entries from a test database. The built-in access control system in SurrealDB has "access granularity levels" for Root, Namespace, Database and Scope with the roles Owner, Editor and Viewer. DEFINE SCOPE is deprecated in favor of DEFINE ACCESS though.

We will use DEFINE ACCESS statements to define a log in method that makes use of user entries in a table aka record users. This will allow us to come up with a more fine grained access control system than the built-in one.

We will have two test users one of which should be able to select from a product table and display a list of products. The other test user should not have access to this table.

First we add the SDK to our project.

npm install --save surrealdb

Then we need to initialize our database.

ts-node backend/src/init_db.ts

The setup

We have the three essential tables user, role and product. The table product could be replaced by any other table that should be controlled via this mechanism, though.

A user entry can be linked to a role by an entry in the has_role table. Each role can be assigned create, select update, or delete rights to a given table by creating an entry in the can_create, can_select, can_update, can_delete relation tables.

Semi-graphically this could maybe represented something like this:

user [-> has_role ->] role  [ -> can_create ->] product
                            [ -> can_select ->]
                            [ -> can_update ->]
                            [ -> can_delete ->]

For the actual table definitions see backend/src/init_db.ts.

We create two example users appuser1 and appuser2 and the role product_manager. Also we need a product so we create testproduct in the product table.

Users having the role product_manager should be able to select from the product table the others should not. Record users have no rights initially by definition and thus must be granted permissions first.

So we create links from user:appuser1 to role:product_manager in the has_role table and a link in the can_select table from role:product_manager to product:testproduct.

relate user:appuser1->has_role->role:product_manager;
relate role:product_manager->can_select->(select * from product)

With this setup we can define the necessary permission for select in the product table like so

define table overwrite product schemafull
    permissions for select 
    where $access = "account"
    and (select <-can_select.in<-has_role.in[0]
        from product)[0]["<-can_select"]["in"]["<-has_role"]["in"] contains $auth.id;

The crucial part here is the subquery

(select <-can_select.in<-has_role.in[0]
        from product)[0]["<-can_select"]["in"]["<-has_role"]["in"] contains $auth.id;

This gets us all user ids that are linked to any role that can_select from the products table and checks whether $auth.id (the id of the currently logged in user) is among those. If so the user is granted select rights. Else ... not. That's the theory at least. Let's test this in action.

Running the client with various user credentials

Our client logs the user with the given email and password in, tries to read all entries from the product table and display those in the console.

As a user who is assigned a role with select permissions on the product table

ts-node backend/src/main.ts appuser1@example.com test
Products:
{
  "available": true,
  "code": "testproduct",
  "id": "product:testproduct"
}

This returns our test product as expected.

As a user who is not assigned a role with select permissions on the product table

ts-node backend/src/main.ts appuser2@example.com test
Products:

No products are returned as it should be.

With credentials for which there is no entry in the user table

ts-node backend/src/main.ts nouser@example.com test
Could not connect to db There was a problem with the database: No record was returned

Signin fails.

Discussion

This implementation works at this small, prototypical scale but there are certainly ways to improve this design. In SurrealDB relation tables can have fields. Maybe the relation tables can_select, can_create etc. should be collapsed into a single table rights having a field of type set with the optional values "select", "create", "update", "delete". This might be easier to maintain than four different relation tables.

When creating our relation entries in the can_select relation table we use the following SurrealQL query:

relate role:product_manager->can_select->(select * from product)

This gives us row level access control which is nice. For example we could restrict select access to yellow products for product_managers.

However there are two things two keep in mind. This query would need to be run as a database function or similar triggered whenever rows are inserted. This approach potentially creates a link for each pair of records in the product and role tables. Also one should make sure that these entries are unique. This could lead to a quick growth in entries - maybe access control with table granularity would be enough for some use cases.