diff --git a/README.md b/README.md index 6ef56a2..e7abd0d 100644 --- a/README.md +++ b/README.md @@ -8,4 +8,81 @@ the 'product' entries the other one should not have access to the table and thus should not be able to retrieve a list of products. 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*](https://surrealdb.com/docs/surrealdb/security/authentication#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*. + +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 +```bash +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 +```bash +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 +```bash +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". For now we will keep it simple though. \ No newline at end of file diff --git a/backend/package-lock.json b/backend/package-lock.json new file mode 100644 index 0000000..696b831 --- /dev/null +++ b/backend/package-lock.json @@ -0,0 +1,116 @@ +{ + "name": "backend", + "lockfileVersion": 3, + "requires": true, + "packages": { + "": { + "dependencies": { + "surrealdb": "^1.2.1" + }, + "devDependencies": { + "@types/node": "^22.13.10", + "typescript": "^5.8.2" + } + }, + "node_modules/@types/node": { + "version": "22.13.10", + "resolved": "https://registry.npmjs.org/@types/node/-/node-22.13.10.tgz", + "integrity": "sha512-I6LPUvlRH+O6VRUqYOcMudhaIdUVWfsjnZavnsraHvpBwaEyMN29ry+0UVJhImYL16xsscu0aske3yA+uPOWfw==", + "dev": true, + "license": "MIT", + "dependencies": { + "undici-types": "~6.20.0" + } + }, + "node_modules/isows": { + "version": "1.0.6", + "resolved": "https://registry.npmjs.org/isows/-/isows-1.0.6.tgz", + "integrity": "sha512-lPHCayd40oW98/I0uvgaHKWCSvkzY27LjWLbtzOm64yQ+G3Q5npjjbdppU65iZXkK1Zt+kH9pfegli0AYfwYYw==", + "funding": [ + { + "type": "github", + "url": "https://github.com/sponsors/wevm" + } + ], + "license": "MIT", + "peerDependencies": { + "ws": "*" + } + }, + "node_modules/surrealdb": { + "version": "1.2.1", + "resolved": "https://registry.npmjs.org/surrealdb/-/surrealdb-1.2.1.tgz", + "integrity": "sha512-WWQ4d7vMCDP6wM2Lo6nil5ub+oVFT4W0fFDhrtNJ7QRWKena/uGMrVosYENk2Oi/ubEyukE+fd7j9gyjE40SPA==", + "license": "Apache-2.0", + "dependencies": { + "isows": "^1.0.6", + "uuidv7": "^1.0.1" + }, + "engines": { + "node": ">=18.0.0" + }, + "peerDependencies": { + "tslib": "^2.6.3", + "typescript": "^5.0.0" + } + }, + "node_modules/tslib": { + "version": "2.8.1", + "resolved": "https://registry.npmjs.org/tslib/-/tslib-2.8.1.tgz", + "integrity": "sha512-oJFu94HQb+KVduSUQL7wnpmqnfmLsOA/nAh6b6EH0wCEoK0/mPeXU6c3wKDV83MkOuHPRHtSXKKU99IBazS/2w==", + "license": "0BSD", + "peer": true + }, + "node_modules/typescript": { + "version": "5.8.2", + "resolved": "https://registry.npmjs.org/typescript/-/typescript-5.8.2.tgz", + "integrity": "sha512-aJn6wq13/afZp/jT9QZmwEjDqqvSGp1VT5GVg+f/t6/oVyrgXM6BY1h9BRh/O5p3PlUPAe+WuiEZOmb/49RqoQ==", + "license": "Apache-2.0", + "bin": { + "tsc": "bin/tsc", + "tsserver": "bin/tsserver" + }, + "engines": { + "node": ">=14.17" + } + }, + "node_modules/undici-types": { + "version": "6.20.0", + "resolved": "https://registry.npmjs.org/undici-types/-/undici-types-6.20.0.tgz", + "integrity": "sha512-Ny6QZ2Nju20vw1SRHe3d9jVu6gJ+4e3+MMpqu7pqE5HT6WsTSlce++GQmK5UXS8mzV8DSYHrQH+Xrf2jVcuKNg==", + "dev": true, + "license": "MIT" + }, + "node_modules/uuidv7": { + "version": "1.0.2", + "resolved": "https://registry.npmjs.org/uuidv7/-/uuidv7-1.0.2.tgz", + "integrity": "sha512-8JQkH4ooXnm1JCIhqTMbtmdnYEn6oKukBxHn1Ic9878jMkL7daTI7anTExfY18VRCX7tcdn5quzvCb6EWrR8PA==", + "license": "Apache-2.0", + "bin": { + "uuidv7": "cli.js" + } + }, + "node_modules/ws": { + "version": "8.18.1", + "resolved": "https://registry.npmjs.org/ws/-/ws-8.18.1.tgz", + "integrity": "sha512-RKW2aJZMXeMxVpnZ6bck+RswznaxmzdULiBr6KY7XkTnW8uvt0iT9H5DkHUChXrc+uurzwa0rVI16n/Xzjdz1w==", + "license": "MIT", + "peer": true, + "engines": { + "node": ">=10.0.0" + }, + "peerDependencies": { + "bufferutil": "^4.0.1", + "utf-8-validate": ">=5.0.2" + }, + "peerDependenciesMeta": { + "bufferutil": { + "optional": true + }, + "utf-8-validate": { + "optional": true + } + } + } + } +} diff --git a/backend/package.json b/backend/package.json new file mode 100644 index 0000000..546b599 --- /dev/null +++ b/backend/package.json @@ -0,0 +1,13 @@ +{ + "devDependencies": { + "@types/node": "^22.13.10", + "typescript": "^5.8.2" + }, + "scripts": { + "compile": "tsc", + "watch": "tsc --watch" + }, + "dependencies": { + "surrealdb": "^1.2.1" + } +} diff --git a/backend/src/init_db.ts b/backend/src/init_db.ts index ca88ebc..a965633 100644 --- a/backend/src/init_db.ts +++ b/backend/src/init_db.ts @@ -10,7 +10,6 @@ const db_name = { namespace: "ts_test", database: "access_test" }; async function main() { - // Extract username and password from command-line arguments const db = new Surreal(); try { await db.connect(db_url, { auth }); @@ -20,77 +19,164 @@ throw (err); } + //define user table try { await db.query(` define table if not exists user schemafull; `); await db.query(` - define field if not exists username on user type string; + define field if not exists email on user type string; define field if not exists password on user type string; - define field if not exists roles on user type set; `); await db.query(` - define index if not exists idx_username on user fields username unique; + define index if not exists idx_email on user fields email unique; `); } catch (err) { - console.error("Could not create table user or its fields: ", err instanceof Error ? err.message : String(err)); + console.error("Could not create table user or its fields: ", + err instanceof Error ? err.message : String(err)); throw (err); } + //insert test user entries try { await db.query(` insert into user [ { - username: "test1", - password: crypto::argon2::generate("test"), - roles: [] + email: "appuser1@example.com", + password: crypto::argon2::generate("test") }, - { - username: "test2", - password: crypto::argon2::generate("test"), - roles: ['product_manager'] + { + email: "appuser2@example.com", + password: crypto::argon2::generate("test") } ]; `) } catch (err) { - console.error("Could not create user entries: ", err instanceof Error ? err.message : String(err)); + console.error("Could not create user entries: ", + err instanceof Error ? err.message : String(err)); } + // define role table try { await db.query(` - define table if not exists product schemafull - permissions for select where $auth.roles contains 'product_manager'; - define field if not exists code on product type string; - define field if not exists available on product type bool; - define index if not exists idx_code on product fields code; + define table role schemafull; + `); + + await db.query(` + define field name on role type string; `); } catch (err) { - console.error("Could not create table product: ", err instanceof Error ? err.message : String(err)); + console.error("Could not create table role or its field: ", + err instanceof Error ? err.message : String(err)); + throw (err); + } + + // define product_manager role + try { + await db.query(` + create role:product_manager content { + name: "product_manager" + } + `); + } catch (err) { + console.error("Could not create role: ", + err instanceof Error ? err.message : String(err)); + throw (err); + } + + // define has_role table + try { + await db.query(` + define table has_role schemafull type relation from user to role enforced; + `); + } catch (err) { + console.error("Could not create has_role: ", + err instanceof Error ? err.message : String(err)); + throw (err); + } + + // define *can_do* tables + try { + // additional controlled tables could be added here like: + // define table can_select schemafull type relation from role to product|person|other_table enforced; + // or simpler: + // define table can_select type relation; + // with no restriction on the types of *in* and *out* tables. + await db.query(` + define table can_select schemafull type relation from role to product enforced; + define table can_create schemafull type relation from role to product enforced; + define table can_update schemafull type relation from role to product enforced; + define table can_delete schemafull type relation from role to product enforced; + `); + } catch (err) { + console.error("Could not create relation table: ", + err instanceof Error ? err.message : String(err)); + throw (err); + } + + // create relation entries + try { + await db.query(` + relate user:appuser1->has_role->role:product_manager; + relate role:product_manager->can_select->(select * from product); + `); + } catch (err) { + console.error("Could not create relation entry: ", + err instanceof Error ? err.message : String(err)); + throw (err); + } + + // define access *account* of type record + try { + await db.query(` + DEFINE ACCESS overwrite account ON DATABASE TYPE RECORD + SIGNUP ( CREATE user SET email = $email, password = crypto::argon2::generate($password) ) + SIGNIN ( SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(password, $password) ) + DURATION FOR TOKEN 15m, FOR SESSION 12h + `); + } catch (err) { + console.error("Could not define access method: ", err instanceof Error ? err.message : String(err)); + throw (err); + } + + // define product table + // This is where the permissions are ultimately defined. + // We only want users to be able to select from the product table that are assigned a role that itself + // is connected to the product table with the right relationship that is an entry in the *can_select* relation table. + // Additional permissions for create, update, delete are left out here for the sake of brevity. + // Additional permissions would make use of the same subquery though with *can_select* replaced by the respective + // relation table name (*can_create* etc.). + try { + await db.query(` + 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; + `); + + await db.query(` + define field code on product type string; + define field available on product type bool; + `); + } catch (err) { + console.error("Could not create table product or one of its fields: ", + err instanceof Error ? err.message : String(err)); throw (err); } try { await db.query(` - insert into product { - code: "test_product1", - available: true + create product:testproduct content { + code: "testproduct", + available: true }; `) } catch (err) { - console.error("Could not create user entries: ", err instanceof Error ? err.message : String(err)); + console.error("Could not product entry: ", err instanceof Error ? err.message : String(err)); } - - try { - await db.query(` - define access overwrite user on database type record - signup (create user set username = $username, password = crypto::argon2::generate($password), roles=[]) - signin (select * from user where username = $username and crypto::argon2::compare(password, $password)); - `) - } catch (err) { - console.error("Could not define access: ", err instanceof Error ? err.message : String(err)); - } } main(); \ No newline at end of file diff --git a/backend/src/main.ts b/backend/src/main.ts index 5fc79d5..f17bace 100644 --- a/backend/src/main.ts +++ b/backend/src/main.ts @@ -4,10 +4,10 @@ async function main() { // Extract username and password from command-line arguments - const username = argv[2]; + const email = argv[2]; const password = argv[3]; - if (!username || !password) { + if (!email || !password) { console.error('Usage: node main.ts '); process.exit(1); } @@ -17,11 +17,16 @@ // Connect to the local SurrealDB instance try { await db.connect("ws://localhost:8000"); - await db.signin({ - username: username, - password: password + + await db.signin({ + namespace: 'ts_test', + database: 'access_test', + access: 'account', + variables: { + email: email, + password: password + } }); - await db.use({ namespace: 'ts_test', database: 'ts_test'}); const products = await db.select('product'); diff --git a/backend/src/user.ts b/backend/src/user.ts index e73c221..3804189 100644 --- a/backend/src/user.ts +++ b/backend/src/user.ts @@ -1,6 +1,6 @@ export type User = { id: string; - user_name: string; + username: string; password: string; roles: Array; } \ No newline at end of file diff --git a/backend/tsconfig.json b/backend/tsconfig.json new file mode 100644 index 0000000..f1514cf --- /dev/null +++ b/backend/tsconfig.json @@ -0,0 +1,9 @@ +{ + "compilerOptions": { + "target": "ES2015", /* Set the JavaScript language version for emitted JavaScript and include compatible library declarations. */ + "module": "NodeNext", /* Specify what module code is generated. */ + "esModuleInterop": true, /* Emit additional JavaScript to ease support for importing CommonJS modules. This enables 'a */ + "strict": true, + "skipLibCheck": true /* Skip type checking all .d.ts files. */ + } +}