Using SurrealDB row-level security (RLS)

Published: 02-07-2023

In this post, we’ll try to see how the surrealdb Row-level security works, by defining a users table, and giving the users permissions to create and update his own email/password.

Later we will create a second table, to store some user data (tasks from a todo list), and also give user CRUD access to owned records.

Prerequisites

This guide assumes you have the Surreal.db installed. An install guide can be found here.

Start the database

shell

surreal start --user root --pass root memory

The the command above starts a local instance of surreal, which will store the data in memory. So just stop and restart the command if you mess up at any step.

The following commands can be run using Surrealist.app.

Creating the users table

SurrealQL

DEFINE TABLE user SCHEMAFULL
  PERMISSIONS 
    FOR select, update WHERE id = $auth.id, 
    FOR create, delete NONE;
DEFINE FIELD email ON user TYPE string
 ASSERT $value != NONE AND is::email($value);
DEFINE FIELD password ON user TYPE string;
DEFINE INDEX user_email_index ON user COLUMNS email UNIQUE;

Creating the users scope

SurrealQL

DEFINE SCOPE allusers
  SESSION 2w
  SIGNUP ( CREATE user SET email = $email, password = crypto::argon2::generate($password) )
  SIGNIN ( SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(password, $password) )

Let’s check that scope and the table was created successfully.

SurrealQL

INFO FOR DB;

You should get something similar.

json

{
    "dl": {},
    "dt": {},
    "fc": {},
    "pa": {},
    "sc": {
        "allusers": "DEFINE SCOPE allusers SESSION 2w SIGNUP (CREATE user SET email = $email, password = crypto::argon2::generate($password)) SIGNIN (SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(password, $password))"
    },
    "tb": {
        "user": "DEFINE TABLE user SCHEMAFULL PERMISSIONS FOR select, update WHERE id = $auth.id, FOR create, delete NONE"
    }
}

Using the surrealdb.js package

The examples below assume you are using node.js and the surrealdb is running locally on port 8000

Install the package

shell

npm i surrealdb.js

Registering an user

Here you will need to replace my_ns and my_db with the namespace and database used in surrealist.

javascript

import { default as Surreal } from 'surrealdb.js';
const db = new Surreal('http://127.0.0.1:8000/rpc');

const main = async () => {

  try {
    const res = await db.signup({
      NS: 'namespace',
      DB: 'database',
      SC: 'allusers',
      email: 'user@ddlele.com',
      password: 'password'
    });
    console.log(res)
  } catch (e) {
    console.error('ERROR', e);
  }
}

main();

You can see we specify the namespace NS the database DB and the scope SC we defined above which is allusers.

If we start the script, we get a jwt token to the terminal but the script continues executing because the surrealdb maintains a websockets connection. You can stop it now.

JWT token

eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJpYXQiOjE2ODgzMTYxODYsIm5iZiI6MTY4ODMxNjE4NiwiZXhwIjoxNjg5NTI1Nzg2LCJpc3MiOiJTdXJyZWFsREIiLCJOUyI6Im5hbWVzcGFjZSIsIkRCIjoiZGF0YWJhc2UiLCJTQyI6ImFsbHVzZXJzIiwiSUQiOiJ1c2VyOjMxdW1xZXk2cDIzcHJoODE1OG5jIn0.oWwLwL6cmeFE6qAmo-ZNYrdsAURWjFiybDx1XBPALcjnLpH2OVEmMnwv6faexlJ_6u9j1hfTvR9pspwUJxT7BQ

if we inspect the token on jwt.io, we can see the autogenerated ID claim which is unique for each user and has the value of user:31umqey6p23prh8158nc in our case.

jwt.io token decoding

If we look carefully at the payload. We can see that the token expires in 14 days, just as defined above.

Jwt token payload

Now we switch the surrealist to explorer mode

Swithing surrealist to explorer

And we can also see the user here.

pro tip

Don’t forget to press refresh in the surrealist app, or you won’t see anything.

Surrealist.app table explorer

Let’s try to login now.

Logging in the user

javascript

import { default as Surreal } from 'surrealdb.js';

const db = new Surreal('http://127.0.0.1:8000/rpc');

const main = async () => {

  try {
    const res = await db.signin({
      NS: 'namespace',
      DB: 'database',
      SC: 'allusers',
      email: 'user@ddlele.com',
      password: 'password'
    });
    console.log(res)
  } catch (e) {
    console.error('ERROR', e);
  }
}

main();

You should get a new token in the terminal, with the same id.

Storing user data

Next, we’ll be making a new table to store some data and giving access to the user. We’ll be storing tasks in this example.

SurrealQL

DEFINE TABLE tasks SCHEMAFULL
  PERMISSIONS 
    FOR create, select, update, delete WHERE user_id = $auth.id;
DEFINE FIELD name ON TABLE tasks TYPE string;
DEFINE FIELD due_on ON TABLE tasks TYPE string;
DEFINE field user_id ON TABLE tasks TYPE string
  ASSERT $value != NONE AND $value = $auth.id;

You can see that we require that the user_id is set to the id from the jwt.

creating a new task

javascript

create_task.js

import { Surreal } from 'surrealdb.js';

const db = new Surreal('http://127.0.0.1:8000/rpc');

const main = async () => {

  try {
    await db.signin({
      NS: 'namespace',
      DB: 'database',
      SC: 'allusers',
      email: 'user@ddlele.com',
      password: 'password'
    });

    const info = await db.info()
    const task = await db.create("tasks", {
      name: "clean the house",
      due_on: new Date().toISOString(),
      user_id: info.id
    });
    console.log(task)
  } catch (e) {
    console.error('ERROR', e);
  }
}

main();

You should get something similar to this.

create_task.js output

[
  {
    due_on: '2023-07-02T16:59:27.444Z',
    id: 'tasks:q4b0tr2db03ufwggqpz0',
    name: 'clean the house',
    user_id: 'user:31umqey6p23prh8158nc'
  }
]

You can see an array containing an object with the id of the freshly created task. We’ll be using the id in the following operations.

Updating the created task

javascript

update_task.js

import { Surreal } from 'surrealdb.js';

const db = new Surreal('http://127.0.0.1:8000/rpc');

const main = async () => {

  try {
    await db.signin({
      NS: 'namespace',
      DB: 'database',
      SC: 'allusers',
      email: 'user@ddlele.com',
      password: 'password'
    });

    const info = await db.info()
    const task = await db.update("tasks:q4b0tr2db03ufwggqpz0", {
      name: "clean the house updated",
      due_on: new Date().toISOString(),
      user_id: info.id
    });
    console.log(task)
  } catch (e) {
    console.error('ERROR', e);
  }
}

main();

The output should be similar to this:

[
  {
    due_on: '2023-07-02T17:00:03.920Z',
    id: 'tasks:q4b0tr2db03ufwggqpz0',
    name: 'clean the house updated',
    user_id: 'user:31umqey6p23prh8158nc'
  }
]

you should also be able to see it in the surrealist app explorer.

The record we just created viewed in surrealist.app

Deleting the task

javascript

delete_task.js

import { Surreal } from 'surrealdb.js';

const db = new Surreal('http://127.0.0.1:8000/rpc');

const main = async () => {

  try {
    await db.signin({
      NS: 'namespace',
      DB: 'database',
      SC: 'allusers',
      email: 'user@ddlele.com',
      password: 'password'
    });

    const task = await db.delete("tasks:q4b0tr2db03ufwggqpz0");
    console.log(task)
  } catch (e) {
    console.error('ERROR', e);
  }
}

main();

The task gets printed to the terminal.

[
  {
    due_on: '2023-07-02T17:00:03.920Z',
    id: 'tasks:q4b0tr2db03ufwggqpz0',
    name: 'clean the house updated',
    user_id: 'user:31umqey6p23prh8158nc'
  }
]

Pressing refresh in the surrealist, confirms that the task was deleted.

Task was deleted picture.

rust

surrealdb

database

surreal

security

RLS