Configure permissions

Permissions in subZero can be defined in two different ways:

• Using the database permissions system, for the databases that already have a powerful permissions system (PostgreSQL, MySQL, Oracle, etc) this is the recommended approach.
• Using the application level permissions system, for the databases that do not have a permissions system (SQLite, etc) or when you don't have access to define database level roles and permissions (or even when you prefer not to).

In this tutorial we will be using the database permissions system, but in the code blocks below you will also find the equivalent application level permissions system configuration.

const permissions = [
    // permission rules go here
]
// Initialize the rest module
await restInit(app, 'sqlite', dbPool, dbSchemas, {
    useInternalPermissionsCheck: true,
    permissions,
    debugFn: debug('subzero:rest'),
});

Open the file db/include/permissions.sql and let's start defining the permissions for the new table.

grant
    select (id, todo_id, text),
    insert (todo_id, text),
    update (todo_id, text),
    delete
on comments to authenticated;

This statement defines the permissions for the comments table, it says that users with the role authenticated can select, insert, update and delete data from the table. Notice also that the list of columns is specified for each operation, this is useful when you want to restrict access to some columns, for example you could say "authenticated users can select all columns except the user_id column".

grant all on comments_id_seq to authenticated;

This statement grants access to the sequence that is used to generate the id column values. This is more of an implementation detail, but it's required by postgresql to be able to insert data into the table when the id column is not specified (which is what we want) and is provided by the default setting of the column.

If we stopped at that, all authenticated users would be able to access all the data (rows) in the comments table, although of course only for the columns we've specified (for example the user_id column is not accessible at all), but we want to restrict access on a row by row basis.

Let's do that:

alter table comments enable row level security;

The statement above enables row level security for the comments table which tells the database to restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands based on the access policies defined for the table.

From now on, every user (except the ones with database administration privileges) will be able to access only the rows that match the access policies defined for the table.

create policy "users can access their own comments"
on comments to authenticated
using ( user_id = (auth.jwt()->>'sub')::uuid );

This statement defines the access policy for the comments table, it says that users with the role authenticated can access only the rows where the user_id column matches the sub claim of the JWT token.

You can read more about row level security and policies in the PostgreSQL documentation.

In the end, the added statements should look like this:

grant
    select (id, todo_id, text),
    insert (todo_id, text),
    update (todo_id, text),
    delete
on comments to authenticated;

grant all on comments_id_seq to authenticated;

alter table comments enable row level security;

create policy "users can access their own comments"
on comments to authenticated
using ( user_id = (auth.jwt()->>'sub')::uuid );

Now save the file, drum roll .... YES! We can see the comments tables is available in the UI.

After this much excitement, you can be forgiven for overlooking a powerful feature of subZero. 😀

You've just defined the table and the permissions in the database and the REST API and the UI picked up the changes automatically, without any code changes. Not only the database level permissions define which tables/resources are available but also which columns are available for each operation (select, insert, update, delete) and that is reflected in all the lists (with filtering capabilities) and create/update forms in the UI without having to write any code for the backend or the frontend.

You won't find any other system with this level of integration between the database, the backend and the frontend.