Use SKDB groups to control who can see and modify data¶

SKDB groups define permission rules for users.

You can use a group id as the value of the skdb_access column in a mirrored table to control which users can see or modify the data.

There are three possible permissions a group can grant a user:

read - can this user read the row? This is how you choose who can see the data. A user cannot modify data they cannot see.
insert - can this user insert a row with the skdb_access column set to this group id? Use this to control who can create data.
delete - can this user delete this row?

Updates are possible if a user has both the delete and insert permission.

Use `skdb_access` to control who can see and modify rows¶

A table can only be mirrored if it has an skdb_access column defined as text NOT NULL. For example:

CREATE TABLE cannot_be_mirrored (id text PRIMARY KEY);

CREATE TABLE can_be_mirrored (id text PRIMARY KEY, skdb_access text NOT NULL);

The value stored in the skdb_access column controls who can read or modify the row.

Privacy in SKDB is not something you opt-in to, but must define in order to share data with other users. This is why you cannot mirror a table that does not have an skdb_access column.

The value that you write in the skdb_access column should be a:

user ID (often also referred to as an 'access key')
- only the user defined by this value is able to read or modify this row.
or a group ID
- The group with this ID defines who can read or modify this row.
- To write a row with this value, you must have the 'insert' permission. To read or delete this row, you must have the 'read' or 'delete' permission respectively.
- Please see create and manage a group for a discussion on how groups define access policies.

Create and manage a group¶

SKDB provides the createGroup() method to make group creation easy.

With a connected client, let's create a group:

const group = await skdb.createGroup();

Groups can have a default permission for users. The default is that all users have no permissions. We will use the default permission to allow all users to read for this group.

group.setDefaultPermission("r");

We use the permission 'r' to indicate read. 'i' corresponds to insert and 'd' for delete. You can also use 'w' for insert and delete permissions.

This is how you would specify that some users can read, insert, and delete.

const user = "fGuqIC7ix96La3p0vkBiZeCoUw9";
await group.setMemberPermission(user, "rid");

We can use group.groupID to get the groupID. This can be passed as a param to a SQL statement or query in order to update the value for the skdb_access column.

Groups are defined in the system tables skdb_groups and skdb_group_permissions.

We can query to see that the group definitions have been created.

console.log("The group ID is", group.groupID);

console.table(
  await skdb.exec(
    "SELECT * FROM skdb_groups WHERE groupID = @id",
    {id: group.groupID},
  ),
);

console.table(
  await skdb.exec(
    "SELECT * FROM skdb_group_permissions WHERE groupID = @id",
    {id: group.groupID},
  ),
);

The group ID is 3g476zUQiUqDsj94uB4N3tI-eAE
┌─────────┬───────────────────────────────┬─────────────┬─────────────────────────────────────┬──────────────┐
│ (index) │ groupID                       │ skdb_author │ adminID                             │ skdb_access  │
├─────────┼───────────────────────────────┼─────────────┼─────────────────────────────────────┼──────────────┤
│ 0       │ '3g476zUQiUqDsj94uB4N3tI-eAE' │ 'root'      │ 'admin-3g476zUQiUqDsj94uB4N3tI-eAE' │ 'read-write' │
└─────────┴───────────────────────────────┴─────────────┴─────────────────────────────────────┴──────────────┘
┌─────────┬───────────────────────────────┬───────────────────────────────┬─────────────┬─────────────────────────────────────┐
│ (index) │ groupID                       │ userID                        │ permissions │ skdb_access                         │
├─────────┼───────────────────────────────┼───────────────────────────────┼─────────────┼─────────────────────────────────────┤
│ 0       │ '3g476zUQiUqDsj94uB4N3tI-eAE' │ null                          │ 4           │ 'admin-3g476zUQiUqDsj94uB4N3tI-eAE' │
│ 1       │ '3g476zUQiUqDsj94uB4N3tI-eAE' │ 'fGuqIC7ix96La3p0vkBiZeCoUw9' │ 7           │ 'read-write'                        │
│ 2       │ '3g476zUQiUqDsj94uB4N3tI-eAE' │ 'root'                        │ 7           │ 'admin-3g476zUQiUqDsj94uB4N3tI-eAE' │
└─────────┴───────────────────────────────┴───────────────────────────────┴─────────────┴─────────────────────────────────────┘

Change the permissions for users in an existing group¶

Continuing our group example, let's get hold of a group object from a group ID string and update the permissions for a user using setMemberPermission().

setMemberPermission() inserts a new definition for the user or overwrites any existing definition.

const newGroupHandle = await skdb.lookupGroup(group.groupID);

await newGroupHandle.setMemberPermission(user, "ri");

console.log(
  await skdb.exec(
    `
SELECT
  permissions
FROM
  skdb_group_permissions
WHERE
  groupID = @group
  AND userID = @user`,
    {group: newGroupHandle.groupID, user}
  ),
);

SKDBTable(1) [ { permissions: 6 } ]

The permissions value is 6, corresponding to the 1st and 2nd bits being set (4 + 2 = 6). But usually you would use the group API described here or the skdb_permission() function in SQL to avoid errors.

Mnemonic	Bit	Meaning if set to 1
d	0	User can delete the row.
i	1	User can insert the row.
r	2	User can read the row.

Remove a user from a group¶

With a group created and a user added to it, let's now remove that user:

group.removeMember(user);

Add and remove group administrators¶

Only group admins can alter the permissions of a group. Group admins may

add users with an initial set of permissions
update the permissions for users
remove a user from a group

Only group owners can add and remove group admins.

The user that created the group is the initial owner and also an admin of the group.

With a group created, let's add another user as an administrator:

const newAdmin = "fGypgZw0C_OPajASatBh9Sb4tEB";

await group.addAdmin(newAdmin);

console.log(
  await skdb.exec(
    "SELECT * FROM skdb_group_permissions WHERE userID = @newAdmin AND groupID = @adminGrp",
    {newAdmin, adminGrp: group.adminGroupID},
  ),
);

SKDBTable(1) [
  {
    groupID: 'admin-3g475jnOh6pII38Drp4h5s7nwg',
    userID: 'fGypgZw0C_OPajASatBh9Sb4tEB',
    permissions: 7,
    skdb_access: 'admin-3g475jnOh6pII38Drp4h5s7nwg'
  }
]

Add and remove group owners¶

Only group owners can add and remove group admins. Group owners can also add and remove group owners.

The user that created the group is the initial owner.

With a group created, let's add another user as an owner and then remove them:

const newOwner = "fGuqIC7ix96La3p0vkBiZeCoUw9";

await group.addOwner(newOwner);
await group.removeOwner(newOwner);

Transfer group ownership¶

It is possible to transfer group ownership to another user so that they can add and remove admins from the group.

With a connected client, let's create a group and then transfer ownership to another user:

const group = await skdb.createGroup();

await group.transferOwnership("fGuqIC7ix96La3p0vkBiZeCoUw9");